pgBadger-
Global information
- Generated on Thu Jul 30 04:15:04 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260729
- Parsed 26,435 log entries in 2s
- Log start from 2026-07-29 00:00:01 to 2026-07-29 23:59:58
-
Overview
Global Stats
- 100 Number of unique normalized queries
- 232 Number of queries
- 6h41m59s Total query duration
- 2026-07-29 00:09:23 First query
- 2026-07-29 23:59:40 Last query
- 1 queries/s at 2026-07-29 21:03:00 Query peak
- 6h41m59s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 6h41m59s Execute total duration
- 11 Number of events
- 9 Number of unique normalized events
- 2 Max number of times the same event was reported
- 0 Number of cancellation
- 37 Total number of automatic vacuums
- 72 Total number of automatic analyzes
- 2,264 Number temporary file
- 1.00 GiB Max size of temporary file
- 296.34 MiB Average size of temporary file
- 2,087 Total number of sessions
- 164 sessions at 2026-07-29 23:59:58 Session peak
- 47d12h28m45s Total duration of sessions
- 32m47s Average duration of sessions
- 0 Average queries per session
- 11s556ms Average queries duration per session
- 32m35s Average idle time per session
- 2,094 Total number of connections
- 9 connections/s at 2026-07-29 05:45:08 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 1 queries/s Query Peak
- 2026-07-29 21:03:00 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2026-07-29 21:35:23 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-07-29 21:03:00 Date
Queries duration
Key values
- 6h41m59s Total query duration
Prepared queries ratio
Key values
- 0.00 Ratio of bind vs prepare
- 0.00 % Ratio between prepared and "usual" statements
General Activity
↑ Back to the top of the General Activity tableDay Hour Count Min duration Max duration Avg duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jul 29 00 2 0ms 9m21s 4m44s 0ms 0ms 9m28s 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 0 0ms 0ms 0ms 0ms 0ms 0ms 03 1 0ms 22s317ms 22s317ms 0ms 0ms 22s317ms 04 0 0ms 0ms 0ms 0ms 0ms 0ms 05 2 0ms 5s585ms 5s525ms 0ms 0ms 5s585ms 06 9 0ms 1m52s 24s720ms 0ms 39s844ms 1m52s 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 1 0ms 13s262ms 13s262ms 0ms 0ms 13s262ms 09 1 0ms 5s121ms 5s121ms 0ms 0ms 5s121ms 10 9 0ms 1m53s 24s763ms 0ms 39s593ms 1m53s 11 11 0ms 15s156ms 8s581ms 0ms 24s322ms 30s758ms 12 35 0ms 5m31s 1m2s 1m59s 2m13s 5m31s 13 5 0ms 2m30s 41s28ms 6s803ms 39s412ms 2m30s 14 9 0ms 1m52s 24s785ms 0ms 39s704ms 1m52s 15 0 0ms 0ms 0ms 0ms 0ms 0ms 16 10 0ms 30m48s 6m21s 2m1s 5m21s 30m48s 17 8 0ms 36m12s 6m8s 5s870ms 1m10s 36m20s 18 13 0ms 1m55s 31s626ms 47s197ms 49s109ms 1m55s 19 1 0ms 50m34s 50m34s 0ms 0ms 50m34s 20 21 0ms 1h8m36s 4m55s 2m19s 7m18s 1h8m36s 21 28 0ms 5m28s 57s 1m49s 2m25s 5m28s 22 10 0ms 1m7s 26s892ms 28s614ms 43s315ms 1m35s 23 56 0ms 13m17s 36s716ms 2m8s 2m58s 13m17s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jul 29 00 1 0 9m21s 0ms 0ms 9m21s 01 0 0 0ms 0ms 0ms 0ms 02 0 0 0ms 0ms 0ms 0ms 03 1 0 22s317ms 0ms 0ms 22s317ms 04 0 0 0ms 0ms 0ms 0ms 05 2 0 5s525ms 0ms 0ms 5s585ms 06 0 9 24s720ms 0ms 0ms 1m52s 07 0 0 0ms 0ms 0ms 0ms 08 1 0 13s262ms 0ms 0ms 13s262ms 09 1 0 5s121ms 0ms 0ms 5s121ms 10 0 9 24s763ms 0ms 0ms 1m53s 11 10 0 8s540ms 0ms 0ms 30s758ms 12 35 0 1m2s 1m37s 1m59s 3m50s 13 1 0 6s803ms 0ms 0ms 6s803ms 14 0 9 24s785ms 0ms 0ms 1m52s 15 0 0 0ms 0ms 0ms 0ms 16 0 0 0ms 0ms 0ms 0ms 17 8 0 6m8s 0ms 5s870ms 36m20s 18 4 9 31s626ms 21s285ms 47s197ms 1m55s 19 1 0 50m34s 0ms 0ms 50m34s 20 17 0 5m32s 55s466ms 1m27s 1h8m36s 21 7 0 1m20s 0ms 14s678ms 5m28s 22 10 0 26s892ms 0ms 28s614ms 1m35s 23 51 0 16s646ms 54s3ms 1m2s 2m58s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jul 29 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 1 0 0 0 8s999ms 0ms 0ms 0ms 12 0 0 0 0 0ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 10 0 0 0 6m21s 0ms 0ms 17m32s 17 0 0 0 0 0ms 0ms 0ms 0ms 18 0 0 0 0 0ms 0ms 0ms 0ms 19 0 0 0 0 0ms 0ms 0ms 0ms 20 0 0 0 0 0ms 0ms 0ms 0ms 21 0 0 0 0 0ms 0ms 0ms 0ms 22 0 0 0 0 0ms 0ms 0ms 0ms 23 0 0 0 0 0ms 0ms 0ms 0ms Day Hour Prepare Bind Bind/Prepare Percentage of prepare Jul 29 00 0 0 0.00 0.00% 01 0 0 0.00 0.00% 02 0 0 0.00 0.00% 03 0 1 1.00 0.00% 04 0 0 0.00 0.00% 05 0 2 2.00 0.00% 06 0 0 0.00 0.00% 07 0 0 0.00 0.00% 08 0 1 1.00 0.00% 09 0 1 1.00 0.00% 10 0 0 0.00 0.00% 11 0 10 10.00 0.00% 12 0 35 35.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 10 10.00 0.00% 17 0 8 8.00 0.00% 18 0 4 4.00 0.00% 19 0 1 1.00 0.00% 20 0 21 21.00 0.00% 21 0 28 28.00 0.00% 22 0 10 10.00 0.00% 23 0 56 56.00 0.00% Day Hour Count Average / Second Jul 29 00 83 0.02/s 01 77 0.02/s 02 85 0.02/s 03 87 0.02/s 04 75 0.02/s 05 99 0.03/s 06 74 0.02/s 07 76 0.02/s 08 76 0.02/s 09 97 0.03/s 10 73 0.02/s 11 129 0.04/s 12 132 0.04/s 13 83 0.02/s 14 80 0.02/s 15 78 0.02/s 16 77 0.02/s 17 90 0.03/s 18 80 0.02/s 19 78 0.02/s 20 82 0.02/s 21 90 0.03/s 22 105 0.03/s 23 88 0.02/s Day Hour Count Average Duration Average idle time Jul 29 00 83 30m35s 30m28s 01 77 29m48s 29m48s 02 85 28m4s 28m4s 03 87 28m43s 28m43s 04 75 31m49s 31m49s 05 99 24m22s 24m22s 06 74 30m30s 30m27s 07 76 32m56s 32m56s 08 76 30m52s 30m52s 09 91 27m20s 27m20s 10 73 28m54s 28m51s 11 128 18m46s 18m45s 12 133 18m44s 18m28s 13 83 29m19s 29m17s 14 80 30m48s 30m45s 15 77 31m28s 31m28s 16 77 31m10s 30m20s 17 90 26m50s 26m18s 18 80 30m17s 30m12s 19 78 31m4s 30m25s 20 82 32m37s 31m21s 21 90 27m23s 27m5s 22 105 23m50s 23m47s 23 88 2h24m3s 2h23m40s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2026-07-29 05:45:08 Date
Connections per database
Key values
- ctdprd51 Main Database
- 2,094 connections Total
Connections per user
Key values
- pubeu Main User
- 2,094 connections Total
-
Sessions
Simultaneous sessions
Key values
- 164 sessions Session Peak
- 2026-07-29 23:59:58 Date
Histogram of session times
Key values
- 1,772 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,087 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,087 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 2,087 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 2,078,348 buffers Checkpoint Peak
- 2026-07-29 23:29:55 Date
- 1619.838 seconds Highest write time
- 0.750 seconds Sync time
Checkpoints Wal files
Key values
- 571 files Wal files usage Peak
- 2026-07-29 20:53:59 Date
Checkpoints distance
Key values
- 17,258.10 Mo Distance Peak
- 2026-07-29 20:53:59 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Jul 29 00 516 51.862s 0.003s 51.909s 01 102 10.397s 0.003s 10.406s 02 79 7.993s 0.001s 7.997s 03 143,044 1,660.903s 0.003s 1,661.007s 04 157 15.882s 0.002s 15.891s 05 164 16.586s 0.002s 16.595s 06 369 37.034s 0.004s 37.044s 07 320 32.215s 0.002s 32.224s 08 670 67.298s 0.002s 67.308s 09 188 19.01s 0.003s 19.021s 10 3,025 302.75s 0.09s 302.988s 11 112 11.324s 0.001s 11.363s 12 331,363 2,480.16s 1.449s 2,489.604s 13 84,211 2,939.004s 0.01s 2,941.153s 14 1,090 109.385s 0.002s 109.824s 15 788,383 542.916s 0.305s 544.77s 16 2,756,759 2,582.168s 0.872s 2,587.19s 17 1,081,557 1,619.088s 0.012s 1,620.117s 18 378,227 1,623.483s 0.008s 1,624.194s 19 16 1.808s 0.002s 1.82s 20 3,915 392.8s 0.006s 397.656s 21 99 10.363s 1.287s 17.134s 22 801,678 1,623.014s 0.009s 1,624.219s 23 2,806,580 2,779.838s 0.022s 2,785.045s Day Hour Added Removed Recycled Synced files Longest sync Average sync Jul 29 00 0 0 1 78 0.001s 0.002s 01 0 0 0 27 0.001s 0.002s 02 0 0 0 17 0.001s 0.001s 03 0 0 32 76 0.001s 0.003s 04 0 0 0 34 0.001s 0.002s 05 0 0 0 29 0.001s 0.002s 06 0 0 0 93 0.001s 0.002s 07 0 0 0 111 0.001s 0.002s 08 0 0 0 81 0.001s 0.002s 09 0 0 0 26 0.001s 0.002s 10 0 0 1 836 0.001s 0.002s 11 0 0 1 51 0.001s 0.001s 12 0 218 3,336 517 0.577s 0.042s 13 0 0 1,076 155 0.001s 0.002s 14 0 0 156 89 0.001s 0.002s 15 0 207 590 117 0.033s 0.014s 16 0 0 2,152 207 0.131s 0.026s 17 0 0 538 137 0.001s 0.001s 18 0 0 274 253 0.001s 0.002s 19 0 0 0 15 0.001s 0.002s 20 0 0 1,090 103 0.001s 0.004s 21 0 0 2,105 96 0.748s 0.064s 22 0 0 553 110 0.001s 0.002s 23 0 0 1,675 160 0.003s 0.004s Day Hour Count Avg time (sec) Jul 29 00 0 0s 01 0 0s 02 0 0s 03 0 0s 04 0 0s 05 0 0s 06 0 0s 07 0 0s 08 0 0s 09 0 0s 10 0 0s 11 0 0s 12 0 0s 13 0 0s 14 0 0s 15 0 0s 16 0 0s 17 0 0s 18 0 0s 19 0 0s 20 0 0s 21 0 0s 22 0 0s 23 0 0s Day Hour Mean distance Mean estimate Jul 29 00 1,394.50 kB 22,113.50 kB 01 171.00 kB 17,985.00 kB 02 331.00 kB 15,382.00 kB 03 175,661.67 kB 474,191.00 kB 04 373.50 kB 363,716.00 kB 05 322.50 kB 294,663.50 kB 06 960.00 kB 238,836.00 kB 07 928.50 kB 193,651.00 kB 08 1,484.50 kB 157,068.50 kB 09 467.00 kB 127,412.00 kB 10 8,738.50 kB 104,766.00 kB 11 721.00 kB 89,506.00 kB 12 7,213,560.62 kB 7,222,886.25 kB 13 8,820,517.50 kB 8,825,852.50 kB 14 1,534,623.50 kB 7,837,174.50 kB 15 4,177,794.00 kB 7,292,402.67 kB 16 8,815,025.00 kB 8,817,323.75 kB 17 8,813,928.00 kB 8,818,672.00 kB 18 2,508,975.50 kB 8,016,665.50 kB 19 20.50 kB 6,493,506.50 kB 20 4,409,739.50 kB 7,047,112.75 kB 21 8,821,779.75 kB 8,830,500.25 kB 22 4,241,655.00 kB 8,356,280.50 kB 23 8,074,116.00 kB 8,745,671.75 kB -
Temporary Files
Size of temporary files
Key values
- 37.00 GiB Temp Files size Peak
- 2026-07-29 20:35:17 Date
Number of temporary files
Key values
- 37 per second Temp Files Peak
- 2026-07-29 20:35:17 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Jul 29 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 0 0 0 07 0 0 0 08 0 0 0 09 0 0 0 10 0 0 0 11 240 2.30 GiB 9.80 MiB 12 988 71.59 GiB 74.20 MiB 13 115 6.96 GiB 62.02 MiB 14 0 0 0 15 0 0 0 16 0 0 0 17 0 0 0 18 32 31.02 GiB 992.68 MiB 19 65 64.38 GiB 1014.24 MiB 20 364 350.55 GiB 986.15 MiB 21 210 82.06 GiB 400.16 MiB 22 175 17.62 GiB 103.09 MiB 23 75 28.71 GiB 391.96 MiB Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 1,413 100.17 GiB 8.00 KiB 1.00 GiB 72.60 MiB select * from pgbulkload.pg_bulkload (?);-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2026-07-29 20:48:34 Duration: 7m45s Database: ctdprd51 User: load Application: pg_bulkload
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-07-29 12:08:43 Duration: 5m31s
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2026-07-29 21:26:08 Duration: 5m28s
2 307 305.84 GiB 222.13 MiB 1.00 GiB 1020.12 MiB select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.gene_go_annot gga, pub1.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in;-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in;
Date: 2026-07-29 20:35:05 Duration: 0ms
3 65 64.38 GiB 389.71 MiB 1.00 GiB 1014.24 MiB select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.object_type where cd = ?), ptr.term_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.phenotype_term_reference ptr, pub1.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in;-
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in;
Date: 2026-07-29 19:13:16 Duration: 0ms
4 35 1.25 GiB 26.45 MiB 52.38 MiB 36.48 MiB vacuum full analyze ixn_actor;-
vacuum FULL analyze ixn_actor;
Date: 2026-07-29 13:14:52 Duration: 28s168ms
-
vacuum FULL analyze ixn_actor;
Date: 2026-07-29 13:14:31 Duration: 0ms
5 35 5.09 GiB 87.49 MiB 247.19 MiB 149.04 MiB vacuum full analyze db_link;-
vacuum FULL analyze db_link;
Date: 2026-07-29 13:17:56 Duration: 2m30s
-
vacuum FULL analyze db_link;
Date: 2026-07-29 13:15:53 Duration: 0ms
6 32 31.02 GiB 21.85 MiB 1.00 GiB 992.68 MiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub1.gene_chem_reference gcr, pub1.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;
Date: 2026-07-29 18:18:38 Duration: 0ms
7 25 16.14 GiB 8.00 KiB 1.00 GiB 661.15 MiB alter table pub1.term_enrichment_agent add constraint term_enrichment_agent_pk primary key (term_id, enriched_term_id, agent_term_id);-
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-07-29 23:30:06 Duration: 3m22s
-
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-07-29 23:30:05 Duration: 0ms
8 25 411.71 MiB 11.64 MiB 24.24 MiB 16.47 MiB vacuum full analyze ixn;-
vacuum FULL analyze ixn;
Date: 2026-07-29 13:15:10 Duration: 8s669ms
-
vacuum FULL analyze ixn;
Date: 2026-07-29 13:15:04 Duration: 0ms
9 20 14.32 GiB 8.00 KiB 1.00 GiB 733.25 MiB create unique index gene_disease_reference_ak1 on pub1.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-07-29 20:54:33 Duration: 4m33s
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-07-29 20:54:33 Duration: 0ms Database: ctdprd51 User: pub1
10 20 227.07 MiB 6.48 MiB 16.18 MiB 11.35 MiB vacuum full analyze term;-
vacuum FULL analyze TERM;
Date: 2026-07-29 13:14:55 Duration: 11s243ms
-
vacuum FULL analyze TERM;
Date: 2026-07-29 13:14:46 Duration: 0ms
11 15 7.96 GiB 8.00 KiB 1.00 GiB 543.15 MiB alter table pub1.gene_disease_reference add constraint gene_disease_reference_pk primary key (id);-
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-07-29 21:13:33 Duration: 1m28s
-
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-07-29 21:13:33 Duration: 0ms
12 15 11.53 GiB 261.86 MiB 1.00 GiB 787.07 MiB create index ix_term_enrich_agent_enr_term on pub1.term_enrichment_agent using btree (enriched_term_id);-
CREATE INDEX ix_term_enrich_agent_enr_term ON pub1.term_enrichment_agent USING btree (enriched_term_id);
Date: 2026-07-29 23:32:10 Duration: 2m4s
-
CREATE INDEX ix_term_enrich_agent_enr_term ON pub1.term_enrichment_agent USING btree (enriched_term_id);
Date: 2026-07-29 23:32:10 Duration: 0ms
13 10 7.96 GiB 592.65 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_ref_src_db on pub1.gene_disease_reference using btree (source_acc_db_id);-
CREATE INDEX ix_gene_disease_ref_src_db ON pub1.gene_disease_reference USING btree (source_acc_db_id);
Date: 2026-07-29 20:55:56 Duration: 1m22s
-
CREATE INDEX ix_gene_disease_ref_src_db ON pub1.gene_disease_reference USING btree (source_acc_db_id);
Date: 2026-07-29 20:55:55 Duration: 0ms
14 10 7.96 GiB 594.93 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_ref_dis_gene on pub1.gene_disease_reference using btree (disease_id, gene_id);-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-07-29 21:05:26 Duration: 2m25s
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-07-29 21:05:25 Duration: 0ms
15 10 7.96 GiB 587.04 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_reference_ixn on pub1.gene_disease_reference using btree (ixn_id);-
CREATE INDEX ix_gene_disease_reference_ixn ON pub1.gene_disease_reference USING btree (ixn_id);
Date: 2026-07-29 21:07:15 Duration: 1m49s
-
CREATE INDEX ix_gene_disease_reference_ixn ON pub1.gene_disease_reference USING btree (ixn_id);
Date: 2026-07-29 21:07:15 Duration: 0ms
16 10 156.41 MiB 8.00 KiB 31.72 MiB 15.64 MiB alter table pub1.term_enrichment add constraint term_enrichment_pk primary key (term_id, enriched_term_id);-
ALTER TABLE pub1.term_enrichment ADD CONSTRAINT term_enrichment_pk PRIMARY KEY (term_id, enriched_term_id);
Date: 2026-07-29 23:13:13 Duration: 0ms Database: ctdprd51 User: pub1
17 10 7.96 GiB 564.88 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_ref_disease on pub1.gene_disease_reference using btree (disease_id);-
CREATE INDEX ix_gene_disease_ref_disease ON pub1.gene_disease_reference USING btree (disease_id);
Date: 2026-07-29 21:01:11 Duration: 1m52s
-
CREATE INDEX ix_gene_disease_ref_disease ON pub1.gene_disease_reference USING btree (disease_id);
Date: 2026-07-29 21:01:10 Duration: 0ms
18 10 7.96 GiB 564.88 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_ref_reference on pub1.gene_disease_reference using btree (reference_id);-
CREATE INDEX ix_gene_disease_ref_reference ON pub1.gene_disease_reference USING btree (reference_id);
Date: 2026-07-29 21:03:00 Duration: 1m48s
-
CREATE INDEX ix_gene_disease_ref_reference ON pub1.gene_disease_reference USING btree (reference_id);
Date: 2026-07-29 21:02:59 Duration: 0ms
19 10 7.96 GiB 564.94 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_ref_chem on pub1.gene_disease_reference using btree (via_chem_id);-
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2026-07-29 20:59:19 Duration: 1m55s
-
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2026-07-29 20:59:18 Duration: 0ms
20 10 263.23 MiB 8.00 KiB 54.27 MiB 26.32 MiB alter table pub1.chem_disease_reference add constraint chem_disease_reference_pk primary key (id);-
ALTER TABLE pub1.chem_disease_reference ADD CONSTRAINT chem_disease_reference_pk PRIMARY KEY (id);
Date: 2026-07-29 21:16:54 Duration: 0ms
21 10 7.96 GiB 564.88 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_ref_net_sc on pub1.gene_disease_reference using btree (network_score);-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-07-29 21:12:05 Duration: 3m
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-07-29 21:12:05 Duration: 0ms
22 10 479.61 MiB 8.00 KiB 96.91 MiB 47.96 MiB create unique index chem_disease_reference_ak1 on pub1.chem_disease_reference using btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);-
CREATE UNIQUE INDEX chem_disease_reference_ak1 ON pub1.chem_disease_reference USING btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);
Date: 2026-07-29 21:16:25 Duration: 8s345ms
-
CREATE UNIQUE INDEX chem_disease_reference_ak1 ON pub1.chem_disease_reference USING btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);
Date: 2026-07-29 21:16:25 Duration: 0ms
23 10 7.96 GiB 565.03 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_ref_mod_tm on pub1.gene_disease_reference using btree (mod_tm);-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub1.gene_disease_reference USING btree (mod_tm);
Date: 2026-07-29 21:09:05 Duration: 1m49s
-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub1.gene_disease_reference USING btree (mod_tm);
Date: 2026-07-29 21:09:05 Duration: 0ms
24 10 1.20 GiB 8.00 KiB 247.79 MiB 123.16 MiB alter table pub1.phenotype_term_reference add constraint phenotype_term_reference_pk primary key (id);-
ALTER TABLE pub1.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2026-07-29 21:13:53 Duration: 18s429ms
-
ALTER TABLE pub1.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2026-07-29 21:13:52 Duration: 0ms
25 10 7.96 GiB 575.39 MiB 1.00 GiB 814.72 MiB create index ix_gene_disease_ref_source_cd on pub1.gene_disease_reference using btree (source_cd);-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub1.gene_disease_reference USING btree (source_cd);
Date: 2026-07-29 20:57:24 Duration: 1m27s
-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub1.gene_disease_reference USING btree (source_cd);
Date: 2026-07-29 20:57:23 Duration: 0ms
26 7 6.52 GiB 531.98 MiB 1.00 GiB 953.71 MiB select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub1.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.object_type where cd = ?), cdr.mod_tm from pub1.chem_disease_reference cdr, pub1.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id;-
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub1.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub1.CHEM_DISEASE_REFERENCE cdr, pub1.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id;
Date: 2026-07-29 20:39:47 Duration: 0ms
27 5 1.20 GiB 236.55 MiB 260.30 MiB 246.32 MiB create index ix_phenotype_term_reference_ixn_id on pub1.phenotype_term_reference using btree (ixn_id);-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub1.phenotype_term_reference USING btree (ixn_id);
Date: 2026-07-29 21:15:47 Duration: 14s818ms
-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub1.phenotype_term_reference USING btree (ixn_id);
Date: 2026-07-29 21:15:47 Duration: 0ms
28 5 263.20 MiB 50.66 MiB 54.52 MiB 52.64 MiB create index ix_chem_disease_ref_net_sc on pub1.chem_disease_reference using btree (network_score);-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub1.chem_disease_reference USING btree (network_score);
Date: 2026-07-29 21:16:51 Duration: 5s606ms
-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub1.chem_disease_reference USING btree (network_score);
Date: 2026-07-29 21:16:51 Duration: 0ms
29 5 1.20 GiB 241.16 MiB 251.06 MiB 246.32 MiB create index ix_phenotype_term_ref_reference_id on pub1.phenotype_term_reference using btree (reference_id);-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub1.phenotype_term_reference USING btree (reference_id);
Date: 2026-07-29 21:14:43 Duration: 14s624ms
-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub1.phenotype_term_reference USING btree (reference_id);
Date: 2026-07-29 21:14:43 Duration: 0ms
30 5 1.20 GiB 241.30 MiB 249.46 MiB 246.32 MiB create index ix_phenotype_term_ref_taxon_id on pub1.phenotype_term_reference using btree (taxon_id);-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub1.phenotype_term_reference USING btree (taxon_id);
Date: 2026-07-29 21:14:54 Duration: 10s878ms
-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub1.phenotype_term_reference USING btree (taxon_id);
Date: 2026-07-29 21:14:54 Duration: 0ms
31 5 156.38 MiB 30.80 MiB 32.05 MiB 31.28 MiB create index ix_term_enrich_enr_obj_type on pub1.term_enrichment using btree (enriched_object_type_id);-
CREATE INDEX ix_term_enrich_enr_obj_type ON pub1.term_enrichment USING btree (enriched_object_type_id);
Date: 2026-07-29 23:13:17 Duration: 0ms
32 5 263.19 MiB 51.92 MiB 53.61 MiB 52.64 MiB create index ix_chem_disease_ref_src_db on pub1.chem_disease_reference using btree (source_acc_db_id);-
CREATE INDEX ix_chem_disease_ref_src_db ON pub1.chem_disease_reference USING btree (source_acc_db_id);
Date: 2026-07-29 21:16:36 Duration: 0ms
33 5 1.20 GiB 240.22 MiB 254.98 MiB 246.32 MiB create index ix_phenotype_term_ref_evidence_cd on pub1.phenotype_term_reference using btree (evidence_cd);-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub1.phenotype_term_reference USING btree (evidence_cd);
Date: 2026-07-29 21:15:06 Duration: 11s304ms
-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub1.phenotype_term_reference USING btree (evidence_cd);
Date: 2026-07-29 21:15:05 Duration: 0ms
34 5 1.20 GiB 238.73 MiB 256.80 MiB 246.32 MiB create index ix_phenotype_term_ref_term_id on pub1.phenotype_term_reference using btree (term_id);-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub1.phenotype_term_reference USING btree (term_id);
Date: 2026-07-29 21:14:18 Duration: 12s902ms
-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub1.phenotype_term_reference USING btree (term_id);
Date: 2026-07-29 21:14:18 Duration: 0ms
35 5 156.38 MiB 30.57 MiB 32.24 MiB 31.27 MiB create index ix_term_enrich_tgt_match on pub1.term_enrichment using btree (target_match_qty);-
CREATE INDEX ix_term_enrich_tgt_match ON pub1.term_enrichment USING btree (target_match_qty);
Date: 2026-07-29 23:13:16 Duration: 0ms
36 5 263.20 MiB 51.77 MiB 54.77 MiB 52.64 MiB create index ix_chem_disease_reference_ref on pub1.chem_disease_reference using btree (reference_id);-
CREATE INDEX ix_chem_disease_reference_ref ON pub1.chem_disease_reference USING btree (reference_id);
Date: 2026-07-29 21:16:32 Duration: 0ms
37 5 1.20 GiB 240.78 MiB 251.54 MiB 246.32 MiB create index ix_phenotype_term_ref_via_term_id on pub1.phenotype_term_reference using btree (via_term_id);-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub1.phenotype_term_reference USING btree (via_term_id);
Date: 2026-07-29 21:16:01 Duration: 13s832ms
-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub1.phenotype_term_reference USING btree (via_term_id);
Date: 2026-07-29 21:16:01 Duration: 0ms
38 5 263.20 MiB 49.55 MiB 54.99 MiB 52.64 MiB create index ix_chem_disease_ref_source_cd on pub1.chem_disease_reference using btree (source_cd);-
CREATE INDEX ix_chem_disease_ref_source_cd ON pub1.chem_disease_reference USING btree (source_cd);
Date: 2026-07-29 21:16:34 Duration: 0ms
39 5 156.38 MiB 26.70 MiB 37.67 MiB 31.28 MiB create index ix_term_enrich_obj_type on pub1.term_enrichment using btree (object_type_id);-
CREATE INDEX ix_term_enrich_obj_type ON pub1.term_enrichment USING btree (object_type_id);
Date: 2026-07-29 23:13:14 Duration: 0ms
40 5 1.20 GiB 236.55 MiB 254.16 MiB 246.32 MiB create index ix_phenotype_term_reference_term_reference_id on pub1.phenotype_term_reference using btree (term_reference_id);-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub1.phenotype_term_reference USING btree (term_reference_id);
Date: 2026-07-29 21:15:33 Duration: 15s164ms
-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub1.phenotype_term_reference USING btree (term_reference_id);
Date: 2026-07-29 21:15:33 Duration: 0ms
41 5 1.69 GiB 336.46 MiB 352.09 MiB 345.67 MiB create index ix_phenotype_term_ref_ids on pub1.phenotype_term_reference using btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);-
CREATE INDEX ix_phenotype_term_ref_ids ON pub1.phenotype_term_reference USING btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);
Date: 2026-07-29 21:16:17 Duration: 15s612ms
-
CREATE INDEX ix_phenotype_term_ref_ids ON pub1.phenotype_term_reference USING btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);
Date: 2026-07-29 21:16:17 Duration: 0ms
42 5 1.20 GiB 235.47 MiB 259.53 MiB 246.32 MiB create index ix_phenotype_term_ref_object_type_id on pub1.phenotype_term_reference using btree (term_object_type_id);-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub1.phenotype_term_reference USING btree (term_object_type_id);
Date: 2026-07-29 21:14:29 Duration: 10s531ms
-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub1.phenotype_term_reference USING btree (term_object_type_id);
Date: 2026-07-29 21:14:29 Duration: 0ms
43 5 263.20 MiB 51.41 MiB 53.42 MiB 52.64 MiB create index ix_chem_disease_reference_gene on pub1.chem_disease_reference using btree (via_gene_id);-
CREATE INDEX ix_chem_disease_reference_gene ON pub1.chem_disease_reference USING btree (via_gene_id);
Date: 2026-07-29 21:16:39 Duration: 0ms
44 5 1.20 GiB 233.94 MiB 260.02 MiB 246.32 MiB create index ix_phenotype_term_ref_phenotype_id on pub1.phenotype_term_reference using btree (phenotype_id);-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub1.phenotype_term_reference USING btree (phenotype_id);
Date: 2026-07-29 21:14:05 Duration: 12s344ms
-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub1.phenotype_term_reference USING btree (phenotype_id);
Date: 2026-07-29 21:14:05 Duration: 0ms
45 5 263.20 MiB 50.21 MiB 57.17 MiB 52.64 MiB create index ix_chem_disease_reference_dis on pub1.chem_disease_reference using btree (disease_id);-
CREATE INDEX ix_chem_disease_reference_dis ON pub1.chem_disease_reference USING btree (disease_id);
Date: 2026-07-29 21:16:28 Duration: 0ms
46 5 263.20 MiB 51.70 MiB 54.19 MiB 52.64 MiB create index ix_chem_disease_reference_ixn on pub1.chem_disease_reference using btree (ixn_id);-
CREATE INDEX ix_chem_disease_reference_ixn ON pub1.chem_disease_reference USING btree (ixn_id);
Date: 2026-07-29 21:16:43 Duration: 0ms
47 5 218.30 MiB 43.12 MiB 45.78 MiB 43.66 MiB create index ix_term_enrich_raw_p_val on pub1.term_enrichment using btree (raw_p_val);-
CREATE INDEX ix_term_enrich_raw_p_val ON pub1.term_enrichment USING btree (raw_p_val);
Date: 2026-07-29 23:13:26 Duration: 0ms
48 5 1.20 GiB 239.41 MiB 249.19 MiB 246.32 MiB create index ix_phenotype_term_reference_source_acc_db_id on pub1.phenotype_term_reference using btree (source_acc_db_id);-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub1.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2026-07-29 21:15:17 Duration: 11s953ms
-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub1.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2026-07-29 21:15:17 Duration: 0ms
49 5 218.31 MiB 42.72 MiB 44.43 MiB 43.66 MiB create index ix_term_enrich_corr_p_val on pub1.term_enrichment using btree (corrected_p_val);-
CREATE INDEX ix_term_enrich_corr_p_val ON pub1.term_enrichment USING btree (corrected_p_val);
Date: 2026-07-29 23:13:21 Duration: 0ms
50 5 263.20 MiB 49.57 MiB 53.59 MiB 52.64 MiB create index ix_chem_disease_ref_mod_tm on pub1.chem_disease_reference using btree (mod_tm);-
CREATE INDEX ix_chem_disease_ref_mod_tm ON pub1.chem_disease_reference USING btree (mod_tm);
Date: 2026-07-29 21:16:46 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB SELECT * FROM pgbulkload.pg_bulkload ($1);[ Date: 2026-07-29 12:38:36 - Database: ctdprd51 - User: load - Application: pg_bulkload ]
2 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
3 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
4 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
5 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
6 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
7 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
8 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
9 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
10 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
11 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
12 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
13 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
14 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
15 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
16 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
17 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
18 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
19 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
20 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2026-07-29 18:18:38 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 72.67 sec Highest CPU-cost vacuum
Table pub1.gene_go_annot
Database ctdprd51 - 2026-07-29 16:40:47 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 72.67 sec Highest CPU-cost vacuum
Table pub1.gene_go_annot
Database ctdprd51 - 2026-07-29 16:40:47 Date
Analyzes per table
Key values
- pubc.log_query (15) Main table analyzed (database ctdprd51)
- 72 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 15 ctdprd51.pg_catalog.pg_class 3 ctdprd51.pg_catalog.pg_index 2 ctdprd51.pub1.term 2 postgres.pg_catalog.pg_shdepend 2 ctdprd51.pub1.db 2 ctdprd51.pg_catalog.pg_attribute 2 ctdprd51.edit.list_db_report 1 ctdprd51.pub1.action_type 1 ctdprd51.pub1.list_db_report 1 ctdprd51.pub1.reference_party 1 ctdprd51.edit.db_report_site 1 ctdprd51.edit.db 1 ctdprd51.edit.object_note 1 ctdprd51.edit.db_link 1 ctdprd51.pg_catalog.pg_depend 1 ctdprd51.pg_catalog.pg_type 1 ctdprd51.pub1.chem_conc 1 ctdprd51.edit.slim_term 1 ctdprd51.edit.action_degree 1 ctdprd51.pg_catalog.pg_constraint 1 ctdprd51.pg_catalog.pg_proc 1 ctdprd51.edit.db_report 1 ctdprd51.pub1.chem_conc_anatomy 1 ctdprd51.pub2.term_set_enrichment 1 ctdprd51.pub1.reference_party_role 1 ctdprd51.pub1.term_pathway 1 ctdprd51.pub1.dag_node 1 ctdprd51.pub1.term_label 1 ctdprd51.edit.study_factor 1 ctdprd51.load.data_load 1 ctdprd51.edit.chem_conc_exp_route 1 ctdprd51.pub1.db_report 1 ctdprd51.pub1.gene_taxon 1 ctdprd51.pg_catalog.pg_description 1 ctdprd51.edit.term_label_type 1 ctdprd51.pg_catalog.pg_trigger 1 ctdprd51.edit.action_type_path 1 ctdprd51.pub1.reference 1 ctdprd51.edit.chem_conc_uom 1 ctdprd51.edit.country 1 ctdprd51.edit.race 1 ctdprd51.pg_catalog.pg_attrdef 1 ctdprd51.pub1.dag_edge 1 ctdprd51.edit.reference_db_link 1 ctdprd51.pub1.db_link 1 ctdprd51.edit.action_type 1 ctdprd51.pub1.img 1 ctdprd51.edit.geographic_region 1 ctdprd51.pub1.gene_go_annot 1 ctdprd51.pub1.db_report_site 1 Total 72 Vacuums per table
Key values
- pg_catalog.pg_class (2) Main table vacuumed on database ctdprd51
- 37 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pg_catalog.pg_class 2 2 744 0 67 0 31 284 60 247,145 ctdprd51.pub1.term 2 0 107,261 0 5 0 0 48,199 3 2,863,307 ctdprd51.pub2.term_set_enrichment_agent 1 0 225,749 0 95,621 0 0 112,815 5 6,697,292 ctdprd51.pg_catalog.pg_depend 1 1 718 0 93 0 65 325 100 399,516 ctdprd51.edit.db_link 1 0 7,717 0 3 0 0 3,740 1 229,055 ctdprd51.pubc.log_query 1 1 237 0 28 0 0 74 22 158,921 ctdprd51.pub1.chem_conc 1 0 767 0 4 0 0 369 2 35,510 ctdprd51.edit.action_degree 1 0 45 0 0 0 0 12 1 9,451 ctdprd51.pg_catalog.pg_proc 1 1 441 0 95 0 79 290 91 173,386 ctdprd51.pg_catalog.pg_constraint 1 1 291 0 19 0 0 118 20 96,724 ctdprd51.pg_catalog.pg_statistic 1 1 889 0 127 0 124 761 120 366,139 ctdprd51.pg_toast.pg_toast_2619 1 1 4,954 0 1,433 0 9,922 4,841 972 574,176 ctdprd51.pub1.reference_party 1 0 5,173 0 4 0 0 2,553 2 165,050 ctdprd51.pg_toast.pg_toast_11936346 1 0 91,036 0 4 0 0 45,510 2 2,701,613 ctdprd51.edit.object_note 1 1 197 0 3 0 0 28 2 13,632 ctdprd51.edit.country 1 0 63 0 0 0 0 8 1 9,627 postgres.pg_catalog.pg_shdepend 1 1 224 0 16 0 0 99 14 46,922 ctdprd51.edit.reference_db_link 1 0 7,506 0 4 0 0 3,740 1 228,986 ctdprd51.pub1.db_link 1 0 338,691 0 153,987 0 0 169,205 6 10,032,499 ctdprd51.pub1.db 1 1 151 0 13 0 0 20 10 36,683 ctdprd51.pub1.dag_edge 1 0 1,053 0 5 0 0 482 2 39,585 ctdprd51.pg_catalog.pg_attribute 1 1 647 0 23 0 55 301 22 118,384 ctdprd51.pub1.gene_go_annot 1 0 718,256 0 301,870 0 0 359,004 12 21,276,985 ctdprd51.pub1.img 1 0 1,109 0 5 0 0 524 2 45,375 ctdprd51.pub1.chem_conc_anatomy 1 0 525 0 3 0 0 233 1 22,166 ctdprd51.pg_catalog.pg_index 1 1 206 0 27 0 0 112 20 80,725 ctdprd51.pub1.term_pathway 1 0 3,337 0 4 0 0 1,614 2 107,917 ctdprd51.pub1.reference_party_role 1 0 13,815 0 4 0 0 6,880 1 414,339 ctdprd51.pub1.dag_node 1 0 87,484 0 5 0 0 43,613 3 2,594,171 ctdprd51.edit.evidence 1 0 32 0 2 0 0 3 2 15,339 ctdprd51.pub1.term_label 1 0 240,627 0 90,763 0 0 120,258 5 7,132,452 ctdprd51.pub1.gene_taxon 1 0 193,311 0 6 0 0 96,595 4 5,729,958 ctdprd51.edit.action_type_path 1 0 48 0 0 0 0 4 1 9,059 ctdprd51.pg_catalog.pg_trigger 1 1 384 0 34 0 0 158 38 201,801 ctdprd51.pub1.reference 1 0 79,108 0 5 0 0 39,441 3 2,348,927 Total 37 14 2,132,796 1,802 644,282 0 10,276 1,062,213 1,553 65,222,817 Tuples removed per table
Key values
- pg_toast.pg_toast_2619 (6160) Main table with removed tuples on database ctdprd51
- 12785 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pg_toast.pg_toast_2619 1 1 6,160 19,211 0 0 12,592 ctdprd51.pg_catalog.pg_depend 1 1 1,684 13,747 0 0 153 ctdprd51.pg_catalog.pg_attribute 1 1 1,112 9,526 0 0 236 ctdprd51.pg_catalog.pg_statistic 1 1 1,069 2,884 0 0 410 ctdprd51.pg_catalog.pg_trigger 1 1 676 1,889 0 13 45 postgres.pg_catalog.pg_shdepend 1 1 645 2,119 0 0 22 ctdprd51.pg_catalog.pg_index 1 1 259 1,188 0 0 39 ctdprd51.pg_catalog.pg_constraint 1 1 210 911 0 0 40 ctdprd51.edit.object_note 1 1 169 169 0 5 5 ctdprd51.edit.country 1 0 163 249 0 0 4 ctdprd51.pg_catalog.pg_proc 1 1 150 1,589 0 0 205 ctdprd51.pub1.db 1 1 134 134 0 0 7 ctdprd51.edit.action_type_path 1 0 106 106 0 0 2 ctdprd51.edit.action_degree 1 0 96 219 0 0 6 ctdprd51.pg_catalog.pg_class 2 2 81 4,619 0 0 188 ctdprd51.edit.evidence 1 0 54 18 0 0 1 ctdprd51.pubc.log_query 1 1 17 702 0 0 32 ctdprd51.pub2.term_set_enrichment_agent 1 0 0 50,023,047 0 0 568,445 ctdprd51.edit.db_link 1 0 0 335,486 0 0 3,739 ctdprd51.pub1.chem_conc 1 0 0 11,335 0 0 368 ctdprd51.pub1.reference_party 1 0 0 456,853 0 0 2,552 ctdprd51.pg_toast.pg_toast_11936346 1 0 0 246,034 0 0 45,509 ctdprd51.edit.reference_db_link 1 0 0 335,486 0 0 3,739 ctdprd51.pub1.db_link 1 0 0 23,324,378 0 0 169,204 ctdprd51.pub1.dag_edge 1 0 0 88,931 0 0 481 ctdprd51.pub1.gene_go_annot 1 0 0 56,364,442 0 0 359,003 ctdprd51.pub1.img 1 0 0 50,667 0 0 523 ctdprd51.pub1.chem_conc_anatomy 1 0 0 24,778 0 0 232 ctdprd51.pub1.term_pathway 1 0 0 135,792 0 0 1,613 ctdprd51.pub1.reference_party_role 1 0 0 1,272,475 0 0 6,879 ctdprd51.pub1.dag_node 1 0 0 1,817,172 0 0 43,612 ctdprd51.pub1.term_label 1 0 0 8,423,122 0 0 120,257 ctdprd51.pub1.gene_taxon 1 0 0 15,165,253 0 0 96,594 ctdprd51.pub1.term 2 0 0 2,322,295 0 0 57,704 ctdprd51.pub1.reference 1 0 0 203,145 0 0 39,440 Total 37 14 12,785 160,659,971 0 18 1,533,881 Pages removed per table
Key values
- pg_catalog.pg_trigger (13) Main table with removed pages on database ctdprd51
- 18 pages Total removed
Table Number of vacuums Index scans Tuples removed Pages removed ctdprd51.pg_catalog.pg_trigger 1 1 676 13 ctdprd51.edit.object_note 1 1 169 5 ctdprd51.pub2.term_set_enrichment_agent 1 0 0 0 ctdprd51.pg_catalog.pg_depend 1 1 1684 0 ctdprd51.edit.db_link 1 0 0 0 ctdprd51.pubc.log_query 1 1 17 0 ctdprd51.pub1.chem_conc 1 0 0 0 ctdprd51.edit.action_degree 1 0 96 0 ctdprd51.pg_catalog.pg_proc 1 1 150 0 ctdprd51.pg_catalog.pg_constraint 1 1 210 0 ctdprd51.pg_catalog.pg_statistic 1 1 1069 0 ctdprd51.pg_toast.pg_toast_2619 1 1 6160 0 ctdprd51.pub1.reference_party 1 0 0 0 ctdprd51.pg_toast.pg_toast_11936346 1 0 0 0 ctdprd51.edit.country 1 0 163 0 postgres.pg_catalog.pg_shdepend 1 1 645 0 ctdprd51.edit.reference_db_link 1 0 0 0 ctdprd51.pub1.db_link 1 0 0 0 ctdprd51.pub1.db 1 1 134 0 ctdprd51.pub1.dag_edge 1 0 0 0 ctdprd51.pg_catalog.pg_attribute 1 1 1112 0 ctdprd51.pub1.gene_go_annot 1 0 0 0 ctdprd51.pub1.img 1 0 0 0 ctdprd51.pub1.chem_conc_anatomy 1 0 0 0 ctdprd51.pg_catalog.pg_index 1 1 259 0 ctdprd51.pub1.term_pathway 1 0 0 0 ctdprd51.pub1.reference_party_role 1 0 0 0 ctdprd51.pub1.dag_node 1 0 0 0 ctdprd51.edit.evidence 1 0 54 0 ctdprd51.pg_catalog.pg_class 2 2 81 0 ctdprd51.pub1.term_label 1 0 0 0 ctdprd51.pub1.gene_taxon 1 0 0 0 ctdprd51.pub1.term 2 0 0 0 ctdprd51.edit.action_type_path 1 0 106 0 ctdprd51.pub1.reference 1 0 0 0 Total 37 14 12,785 18 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Jul 29 00 0 0 01 0 2 02 1 3 03 0 2 04 0 1 05 1 4 06 0 1 07 0 0 08 0 0 09 10 12 10 0 0 11 6 18 12 0 1 13 1 4 14 0 0 15 0 0 16 17 21 17 1 0 18 0 0 19 0 0 20 0 0 21 0 2 22 0 1 23 0 0 - 72.67 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- unknown Main Lock Type
- 0 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query NO DATASET
Queries that waited the most
Rank Wait time Query NO DATASET
-
Queries
Queries by type
Key values
- 150 Total read queries
- 75 Total write queries
Queries by database
Key values
- unknown Main database
- 190 Requests
- 5h1m27s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 218 Requests
User Request type Count Duration edit Total 1 8s999ms insert 1 8s999ms load Total 22 1h3m28s select 22 1h3m28s postgres Total 16 17m46s copy to 16 17m46s pub1 Total 2 17m30s insert 1 17m22s select 1 8s542ms pubc Total 1 9m21s select 1 9m21s pubeu Total 15 2m select 15 2m qaeu Total 3 18s111ms select 3 18s111ms unknown Total 218 5h7m24s copy to 56 11m57s ddl 28 45m13s insert 9 46m17s others 7 4m48s select 118 3h19m7s Duration by user
Key values
- 5h7m24s (unknown) Main time consuming user
User Request type Count Duration edit Total 1 8s999ms insert 1 8s999ms load Total 22 1h3m28s select 22 1h3m28s postgres Total 16 17m46s copy to 16 17m46s pub1 Total 2 17m30s insert 1 17m22s select 1 8s542ms pubc Total 1 9m21s select 1 9m21s pubeu Total 15 2m select 15 2m qaeu Total 3 18s111ms select 3 18s111ms unknown Total 218 5h7m24s copy to 56 11m57s ddl 28 45m13s insert 9 46m17s others 7 4m48s select 118 3h19m7s Queries by host
Key values
- unknown Main host
- 278 Requests
- 6h57m59s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 207 Requests
- 6h8m26s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-07-29 20:55:55 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 169 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 1h8m36s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2026-07-29 20:21:55 - Bind query: yes ]
2 50m34s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2026-07-29 19:13:14 - Bind query: yes ]
3 36m12s SELECT i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) FROM edit.IXN i, edit.REFERENCE_IXN r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in ('bogusName') order by i.id asc;[ Date: 2026-07-29 17:25:27 - Database: ctdprd51 - User: load - Bind query: yes ]
4 30m48s insert into pub1.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;[ Date: 2026-07-29 16:39:31 - Bind query: yes ]
5 17m22s insert into pub1.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;[ Date: 2026-07-29 16:02:11 - Database: ctdprd51 - User: pub1 - Bind query: yes ]
6 13m17s ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enr_agent_term_enr_fk FOREIGN KEY (term_id, enriched_term_id) REFERENCES term_enrichment (term_id, enriched_term_id);[ Date: 2026-07-29 23:26:44 - Bind query: yes ]
7 10m46s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');[ Date: 2026-07-29 17:39:08 - Database: ctdprd51 - User: load - Bind query: yes ]
8 9m21s /* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();[ Date: 2026-07-29 00:09:23 - Database: ctdprd51 - User: pubc - Application: psql ]
9 7m45s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');[ Date: 2026-07-29 20:48:34 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
10 6m47s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2026-07-29 20:34:13 - Bind query: yes ]
11 5m31s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');[ Date: 2026-07-29 12:08:43 - Bind query: yes ]
12 5m28s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');[ Date: 2026-07-29 21:26:08 - Bind query: yes ]
13 5m21s insert into pub1.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;[ Date: 2026-07-29 16:44:53 - Bind query: yes ]
14 4m33s CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);[ Date: 2026-07-29 20:54:33 - Bind query: yes ]
15 4m18s INSERT INTO pub1.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub1.TERM);[ Date: 2026-07-29 16:08:42 - Bind query: yes ]
16 3m22s ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-07-29 23:30:06 - Bind query: yes ]
17 3m13s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/misc/uniprot/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/misc/uniprot/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/misc/uniprot/output/dbLink.txt.DUPE}');[ Date: 2026-07-29 12:49:17 - Bind query: yes ]
18 3m CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);[ Date: 2026-07-29 21:12:05 - Bind query: yes ]
19 2m58s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.TERM_ENRICHMENT_AGENT,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.log,parse-badfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.DUPE}');[ Date: 2026-07-29 23:10:34 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
20 2m30s vacuum FULL analyze db_link;[ Date: 2026-07-29 13:17:56 ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 1h21m41s 12 16s498ms 1h8m36s 6m48s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.gene_go_annot gga, pub1.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 29 20 12 1h21m41s 6m48s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:21:55 Duration: 1h8m36s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:34:13 Duration: 6m47s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:24:47 Duration: 1m27s Bind query: yes
2 1h2m23s 63 5s96ms 7m45s 59s426ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jul 29 11 10 1m25s 8s540ms 12 32 35m37s 1m6s 17 3 1m10s 23s408ms 20 3 9m11s 3m3s 21 4 7m23s 1m50s 22 8 4m13s 31s706ms 23 3 3m21s 1m7s [ User: load - Total duration: 15m8s - Times executed: 15 ]
[ Application: pg_bulkload - Total duration: 15m8s - Times executed: 15 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2026-07-29 20:48:34 Duration: 7m45s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-07-29 12:08:43 Duration: 5m31s Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2026-07-29 21:26:08 Duration: 5m28s Bind query: yes
3 50m34s 1 50m34s 50m34s 50m34s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.object_type where cd = ?), ptr.term_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.phenotype_term_reference ptr, pub1.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jul 29 19 1 50m34s 50m34s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 19:13:14 Duration: 50m34s Bind query: yes
4 36m12s 1 36m12s 36m12s 36m12s select i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) from edit.ixn i, edit.reference_ixn r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in (...) order by i.id asc;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jul 29 17 1 36m12s 36m12s [ User: load - Total duration: 36m12s - Times executed: 1 ]
-
SELECT i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) FROM edit.IXN i, edit.REFERENCE_IXN r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in ('bogusName') order by i.id asc;
Date: 2026-07-29 17:25:27 Duration: 36m12s Database: ctdprd51 User: load Bind query: yes
5 30m48s 1 30m48s 30m48s 30m48s insert into pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jul 29 16 1 30m48s 30m48s -
insert into pub1.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;
Date: 2026-07-29 16:39:31 Duration: 30m48s Bind query: yes
6 17m22s 1 17m22s 17m22s 17m22s insert into pub1.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jul 29 16 1 17m22s 17m22s [ User: pub1 - Total duration: 17m22s - Times executed: 1 ]
-
insert into pub1.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;
Date: 2026-07-29 16:02:11 Duration: 17m22s Database: ctdprd51 User: pub1 Bind query: yes
7 13m55s 5 46s252ms 10m46s 2m47s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub1.gene_chem_reference gcr, pub1.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 29 17 2 11m33s 5m46s 18 3 2m21s 47s188ms [ User: load - Total duration: 10m46s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 17:39:08 Duration: 10m46s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 18:08:41 Duration: 48s115ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 18:03:08 Duration: 47s197ms Bind query: yes
8 13m17s 1 13m17s 13m17s 13m17s alter table pub1.term_enrichment_agent add constraint term_enr_agent_term_enr_fk foreign key (term_id, enriched_term_id) references term_enrichment (term_id, enriched_term_id);Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 29 23 1 13m17s 13m17s -
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enr_agent_term_enr_fk FOREIGN KEY (term_id, enriched_term_id) REFERENCES term_enrichment (term_id, enriched_term_id);
Date: 2026-07-29 23:26:44 Duration: 13m17s Bind query: yes
9 10m17s 45 5s936ms 23s131ms 13s722ms select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub1.gene_disease_reference order by gene_id, disease_id;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 29 23 45 10m17s 13s722ms -
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub1.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-07-29 23:34:26 Duration: 23s131ms Bind query: yes
-
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub1.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-07-29 23:34:49 Duration: 23s29ms Bind query: yes
-
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub1.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-07-29 23:35:11 Duration: 22s164ms Bind query: yes
10 9m21s 1 9m21s 9m21s 9m21s select maint_query_logs_archive ();Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 29 00 1 9m21s 9m21s [ User: pubc - Total duration: 9m21s - Times executed: 1 ]
[ Application: psql - Total duration: 9m21s - Times executed: 1 ]
-
/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();
Date: 2026-07-29 00:09:23 Duration: 9m21s Database: ctdprd51 User: pubc Application: psql
11 7m33s 4 1m52s 1m55s 1m53s copy pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 29 06 1 1m52s 1m52s 10 1 1m53s 1m53s 14 1 1m52s 1m52s 18 1 1m55s 1m55s [ User: postgres - Total duration: 7m33s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m33s - Times executed: 4 ]
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 18:06:56 Duration: 1m55s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 10:06:54 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 14:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
12 5m21s 1 5m21s 5m21s 5m21s insert into pub1.gene_taxon (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.gene_taxon;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 29 16 1 5m21s 5m21s -
insert into pub1.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;
Date: 2026-07-29 16:44:53 Duration: 5m21s Bind query: yes
13 4m33s 1 4m33s 4m33s 4m33s create unique index gene_disease_reference_ak1 on pub1.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 29 20 1 4m33s 4m33s -
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-07-29 20:54:33 Duration: 4m33s Bind query: yes
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-07-29 20:54:33 Duration: 0ms Database: ctdprd51 User: pub1
14 4m18s 1 4m18s 4m18s 4m18s insert into pub1.term_label (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.term t, load.term_label l where t.id = l.term_id and t.id in ( select id from pub1.term);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 29 16 1 4m18s 4m18s -
INSERT INTO pub1.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub1.TERM);
Date: 2026-07-29 16:08:42 Duration: 4m18s Bind query: yes
15 3m22s 1 3m22s 3m22s 3m22s alter table pub1.term_enrichment_agent add constraint term_enrichment_agent_pk primary key (term_id, enriched_term_id, agent_term_id);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 29 23 1 3m22s 3m22s -
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-07-29 23:30:06 Duration: 3m22s Bind query: yes
-
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-07-29 23:30:05 Duration: 0ms
16 3m 1 3m 3m 3m create index ix_gene_disease_ref_net_sc on pub1.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 29 21 1 3m 3m -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-07-29 21:12:05 Duration: 3m Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-07-29 21:12:05 Duration: 0ms
17 2m30s 1 2m30s 2m30s 2m30s vacuum full analyze db_link;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 29 13 1 2m30s 2m30s -
vacuum FULL analyze db_link;
Date: 2026-07-29 13:17:56 Duration: 2m30s
-
vacuum FULL analyze db_link;
Date: 2026-07-29 13:15:53 Duration: 0ms
18 2m25s 1 2m25s 2m25s 2m25s create index ix_gene_disease_ref_dis_gene on pub1.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jul 29 21 1 2m25s 2m25s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-07-29 21:05:26 Duration: 2m25s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-07-29 21:05:25 Duration: 0ms
19 2m19s 1 2m19s 2m19s 2m19s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub1.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.object_type where cd = ?), cdr.mod_tm from pub1.chem_disease_reference cdr, pub1.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id and ptr.source_cd = ? and cdr.source_cd = ? and ptr.ixn_id not in ( select ixn_id from pub1.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jul 29 20 1 2m19s 2m19s -
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub1.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub1.CHEM_DISEASE_REFERENCE cdr, pub1.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id AND ptr.source_cd = 'C' AND cdr.source_cd = 'C' AND ptr.ixn_id NOT IN ( SELECT ixn_id FROM pub1.IXN_AXN WHERE action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:37:39 Duration: 2m19s Bind query: yes
20 2m4s 1 2m4s 2m4s 2m4s create index ix_term_enrich_agent_enr_term on pub1.term_enrichment_agent using btree (enriched_term_id);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jul 29 23 1 2m4s 2m4s -
CREATE INDEX ix_term_enrich_agent_enr_term ON pub1.term_enrichment_agent USING btree (enriched_term_id);
Date: 2026-07-29 23:32:10 Duration: 2m4s Bind query: yes
-
CREATE INDEX ix_term_enrich_agent_enr_term ON pub1.term_enrichment_agent USING btree (enriched_term_id);
Date: 2026-07-29 23:32:10 Duration: 0ms
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 63 1h2m23s 5s96ms 7m45s 59s426ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 29 11 10 1m25s 8s540ms 12 32 35m37s 1m6s 17 3 1m10s 23s408ms 20 3 9m11s 3m3s 21 4 7m23s 1m50s 22 8 4m13s 31s706ms 23 3 3m21s 1m7s [ User: load - Total duration: 15m8s - Times executed: 15 ]
[ Application: pg_bulkload - Total duration: 15m8s - Times executed: 15 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2026-07-29 20:48:34 Duration: 7m45s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-07-29 12:08:43 Duration: 5m31s Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2026-07-29 21:26:08 Duration: 5m28s Bind query: yes
2 45 10m17s 5s936ms 23s131ms 13s722ms select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub1.gene_disease_reference order by gene_id, disease_id;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jul 29 23 45 10m17s 13s722ms -
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub1.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-07-29 23:34:26 Duration: 23s131ms Bind query: yes
-
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub1.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-07-29 23:34:49 Duration: 23s29ms Bind query: yes
-
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub1.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-07-29 23:35:11 Duration: 22s164ms Bind query: yes
3 12 1h21m41s 16s498ms 1h8m36s 6m48s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.gene_go_annot gga, pub1.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jul 29 20 12 1h21m41s 6m48s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:21:55 Duration: 1h8m36s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:34:13 Duration: 6m47s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:24:47 Duration: 1m27s Bind query: yes
4 5 13m55s 46s252ms 10m46s 2m47s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub1.gene_chem_reference gcr, pub1.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jul 29 17 2 11m33s 5m46s 18 3 2m21s 47s188ms [ User: load - Total duration: 10m46s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 17:39:08 Duration: 10m46s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 18:08:41 Duration: 48s115ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 18:03:08 Duration: 47s197ms Bind query: yes
5 4 7m33s 1m52s 1m55s 1m53s copy pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jul 29 06 1 1m52s 1m52s 10 1 1m53s 1m53s 14 1 1m52s 1m52s 18 1 1m55s 1m55s [ User: postgres - Total duration: 7m33s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m33s - Times executed: 4 ]
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 18:06:56 Duration: 1m55s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 10:06:54 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 14:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
6 4 1m37s 24s94ms 24s457ms 24s269ms copy pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jul 29 06 1 24s284ms 24s284ms 10 1 24s94ms 24s94ms 14 1 24s240ms 24s240ms 18 1 24s457ms 24s457ms -
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 18:07:20 Duration: 24s457ms
-
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 06:07:18 Duration: 24s284ms
-
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 14:07:19 Duration: 24s240ms
7 4 1m20s 19s977ms 20s346ms 20s182ms copy edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 29 06 1 19s977ms 19s977ms 10 1 20s180ms 20s180ms 14 1 20s225ms 20s225ms 18 1 20s346ms 20s346ms [ User: postgres - Total duration: 1m20s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 1m20s - Times executed: 4 ]
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 18:00:21 Duration: 20s346ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 14:00:22 Duration: 20s225ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 10:00:22 Duration: 20s180ms Database: ctdprd51 User: postgres Application: pg_dump
8 4 1m2s 15s463ms 15s796ms 15s579ms copy pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 29 06 1 15s560ms 15s560ms 10 1 15s498ms 15s498ms 14 1 15s463ms 15s463ms 18 1 15s796ms 15s796ms -
COPY pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) TO stdout;
Date: 2026-07-29 18:07:36 Duration: 15s796ms
-
COPY pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) TO stdout;
Date: 2026-07-29 06:07:34 Duration: 15s560ms
-
COPY pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) TO stdout;
Date: 2026-07-29 10:07:34 Duration: 15s498ms
9 4 1m 14s931ms 15s160ms 15s16ms copy edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 29 06 1 14s979ms 14s979ms 10 1 14s931ms 14s931ms 14 1 15s160ms 15s160ms 18 1 14s996ms 14s996ms -
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 14:00:54 Duration: 15s160ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 18:00:53 Duration: 14s996ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 06:00:53 Duration: 14s979ms
10 4 58s721ms 14s666ms 14s692ms 14s680ms copy edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 29 06 1 14s666ms 14s666ms 10 1 14s679ms 14s679ms 14 1 14s682ms 14s682ms 18 1 14s692ms 14s692ms -
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 18:01:08 Duration: 14s692ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 14:01:08 Duration: 14s682ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 10:01:08 Duration: 14s679ms
11 4 30s115ms 7s476ms 7s571ms 7s528ms copy edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 29 06 1 7s476ms 7s476ms 10 1 7s571ms 7s571ms 14 1 7s548ms 7s548ms 18 1 7s518ms 7s518ms -
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 10:00:31 Duration: 7s571ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 14:00:32 Duration: 7s548ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 18:00:31 Duration: 7s518ms
12 4 26s221ms 6s482ms 6s638ms 6s555ms copy edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 29 06 1 6s482ms 6s482ms 10 1 6s507ms 6s507ms 14 1 6s638ms 6s638ms 18 1 6s592ms 6s592ms -
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 14:01:17 Duration: 6s638ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 18:01:16 Duration: 6s592ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 10:01:16 Duration: 6s507ms
13 4 24s982ms 6s210ms 6s264ms 6s245ms copy edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 29 06 1 6s210ms 6s210ms 10 1 6s264ms 6s264ms 14 1 6s258ms 6s258ms 18 1 6s247ms 6s247ms -
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 10:00:38 Duration: 6s264ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 14:00:38 Duration: 6s258ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-29 18:00:38 Duration: 6s247ms
14 2 11s51ms 5s466ms 5s585ms 5s525ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 29 05 2 11s51ms 5s525ms [ User: qaeu - Total duration: 5s585ms - Times executed: 1 ]
[ User: pubeu - Total duration: 5s466ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1400764)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-07-29 05:43:42 Duration: 5s585ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1400764)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-07-29 05:48:45 Duration: 5s466ms Database: ctdprd51 User: pubeu Bind query: yes
15 2 10s167ms 5s46ms 5s121ms 5s83ms select d.abbr dagabbr, d.nm dagnm, gt.level_min_no daglevelmin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pvalcorrected, te.raw_p_val pvalraw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, count(*) over () fullrowcount from term_enrichment te inner join dag_node gt on te.enriched_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where te.term_id = ? and te.enriched_object_type_id = ? order by te.corrected_p_val, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 29 09 1 5s121ms 5s121ms 21 1 5s46ms 5s46ms [ User: pubeu - Total duration: 10s167ms - Times executed: 2 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1465981' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-29 09:05:09 Duration: 5s121ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1373715' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-29 21:53:07 Duration: 5s46ms Database: ctdprd51 User: pubeu Bind query: yes
16 1 50m34s 50m34s 50m34s 50m34s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.object_type where cd = ?), ptr.term_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.phenotype_term_reference ptr, pub1.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 29 19 1 50m34s 50m34s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 19:13:14 Duration: 50m34s Bind query: yes
17 1 36m12s 36m12s 36m12s 36m12s select i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) from edit.ixn i, edit.reference_ixn r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in (...) order by i.id asc;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 29 17 1 36m12s 36m12s [ User: load - Total duration: 36m12s - Times executed: 1 ]
-
SELECT i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) FROM edit.IXN i, edit.REFERENCE_IXN r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in ('bogusName') order by i.id asc;
Date: 2026-07-29 17:25:27 Duration: 36m12s Database: ctdprd51 User: load Bind query: yes
18 1 30m48s 30m48s 30m48s 30m48s insert into pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jul 29 16 1 30m48s 30m48s -
insert into pub1.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;
Date: 2026-07-29 16:39:31 Duration: 30m48s Bind query: yes
19 1 17m22s 17m22s 17m22s 17m22s insert into pub1.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jul 29 16 1 17m22s 17m22s [ User: pub1 - Total duration: 17m22s - Times executed: 1 ]
-
insert into pub1.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;
Date: 2026-07-29 16:02:11 Duration: 17m22s Database: ctdprd51 User: pub1 Bind query: yes
20 1 13m17s 13m17s 13m17s 13m17s alter table pub1.term_enrichment_agent add constraint term_enr_agent_term_enr_fk foreign key (term_id, enriched_term_id) references term_enrichment (term_id, enriched_term_id);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jul 29 23 1 13m17s 13m17s -
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enr_agent_term_enr_fk FOREIGN KEY (term_id, enriched_term_id) REFERENCES term_enrichment (term_id, enriched_term_id);
Date: 2026-07-29 23:26:44 Duration: 13m17s Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 50m34s 50m34s 50m34s 1 50m34s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.object_type where cd = ?), ptr.term_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.phenotype_term_reference ptr, pub1.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 29 19 1 50m34s 50m34s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 19:13:14 Duration: 50m34s Bind query: yes
2 36m12s 36m12s 36m12s 1 36m12s select i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) from edit.ixn i, edit.reference_ixn r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in (...) order by i.id asc;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jul 29 17 1 36m12s 36m12s [ User: load - Total duration: 36m12s - Times executed: 1 ]
-
SELECT i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) FROM edit.IXN i, edit.REFERENCE_IXN r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in ('bogusName') order by i.id asc;
Date: 2026-07-29 17:25:27 Duration: 36m12s Database: ctdprd51 User: load Bind query: yes
3 30m48s 30m48s 30m48s 1 30m48s insert into pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jul 29 16 1 30m48s 30m48s -
insert into pub1.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;
Date: 2026-07-29 16:39:31 Duration: 30m48s Bind query: yes
4 17m22s 17m22s 17m22s 1 17m22s insert into pub1.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jul 29 16 1 17m22s 17m22s [ User: pub1 - Total duration: 17m22s - Times executed: 1 ]
-
insert into pub1.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;
Date: 2026-07-29 16:02:11 Duration: 17m22s Database: ctdprd51 User: pub1 Bind query: yes
5 13m17s 13m17s 13m17s 1 13m17s alter table pub1.term_enrichment_agent add constraint term_enr_agent_term_enr_fk foreign key (term_id, enriched_term_id) references term_enrichment (term_id, enriched_term_id);Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jul 29 23 1 13m17s 13m17s -
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enr_agent_term_enr_fk FOREIGN KEY (term_id, enriched_term_id) REFERENCES term_enrichment (term_id, enriched_term_id);
Date: 2026-07-29 23:26:44 Duration: 13m17s Bind query: yes
6 9m21s 9m21s 9m21s 1 9m21s select maint_query_logs_archive ();Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jul 29 00 1 9m21s 9m21s [ User: pubc - Total duration: 9m21s - Times executed: 1 ]
[ Application: psql - Total duration: 9m21s - Times executed: 1 ]
-
/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();
Date: 2026-07-29 00:09:23 Duration: 9m21s Database: ctdprd51 User: pubc Application: psql
7 16s498ms 1h8m36s 6m48s 12 1h21m41s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.gene_go_annot gga, pub1.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 29 20 12 1h21m41s 6m48s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:21:55 Duration: 1h8m36s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:34:13 Duration: 6m47s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:24:47 Duration: 1m27s Bind query: yes
8 5m21s 5m21s 5m21s 1 5m21s insert into pub1.gene_taxon (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.gene_taxon;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 29 16 1 5m21s 5m21s -
insert into pub1.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;
Date: 2026-07-29 16:44:53 Duration: 5m21s Bind query: yes
9 4m33s 4m33s 4m33s 1 4m33s create unique index gene_disease_reference_ak1 on pub1.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 29 20 1 4m33s 4m33s -
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-07-29 20:54:33 Duration: 4m33s Bind query: yes
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-07-29 20:54:33 Duration: 0ms Database: ctdprd51 User: pub1
10 4m18s 4m18s 4m18s 1 4m18s insert into pub1.term_label (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.term t, load.term_label l where t.id = l.term_id and t.id in ( select id from pub1.term);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 29 16 1 4m18s 4m18s -
INSERT INTO pub1.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub1.TERM);
Date: 2026-07-29 16:08:42 Duration: 4m18s Bind query: yes
11 3m22s 3m22s 3m22s 1 3m22s alter table pub1.term_enrichment_agent add constraint term_enrichment_agent_pk primary key (term_id, enriched_term_id, agent_term_id);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 29 23 1 3m22s 3m22s -
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-07-29 23:30:06 Duration: 3m22s Bind query: yes
-
ALTER TABLE pub1.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-07-29 23:30:05 Duration: 0ms
12 3m 3m 3m 1 3m create index ix_gene_disease_ref_net_sc on pub1.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 29 21 1 3m 3m -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-07-29 21:12:05 Duration: 3m Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-07-29 21:12:05 Duration: 0ms
13 46s252ms 10m46s 2m47s 5 13m55s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub1.gene_chem_reference gcr, pub1.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 29 17 2 11m33s 5m46s 18 3 2m21s 47s188ms [ User: load - Total duration: 10m46s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 17:39:08 Duration: 10m46s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 18:08:41 Duration: 48s115ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-07-29 18:03:08 Duration: 47s197ms Bind query: yes
14 2m30s 2m30s 2m30s 1 2m30s vacuum full analyze db_link;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 29 13 1 2m30s 2m30s -
vacuum FULL analyze db_link;
Date: 2026-07-29 13:17:56 Duration: 2m30s
-
vacuum FULL analyze db_link;
Date: 2026-07-29 13:15:53 Duration: 0ms
15 2m25s 2m25s 2m25s 1 2m25s create index ix_gene_disease_ref_dis_gene on pub1.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 29 21 1 2m25s 2m25s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-07-29 21:05:26 Duration: 2m25s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-07-29 21:05:25 Duration: 0ms
16 2m19s 2m19s 2m19s 1 2m19s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub1.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.object_type where cd = ?), cdr.mod_tm from pub1.chem_disease_reference cdr, pub1.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id and ptr.source_cd = ? and cdr.source_cd = ? and ptr.ixn_id not in ( select ixn_id from pub1.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 29 20 1 2m19s 2m19s -
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub1.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub1.CHEM_DISEASE_REFERENCE cdr, pub1.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id AND ptr.source_cd = 'C' AND cdr.source_cd = 'C' AND ptr.ixn_id NOT IN ( SELECT ixn_id FROM pub1.IXN_AXN WHERE action_degree_type_nm = 'does not affect');
Date: 2026-07-29 20:37:39 Duration: 2m19s Bind query: yes
17 2m4s 2m4s 2m4s 1 2m4s create index ix_term_enrich_agent_enr_term on pub1.term_enrichment_agent using btree (enriched_term_id);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 29 23 1 2m4s 2m4s -
CREATE INDEX ix_term_enrich_agent_enr_term ON pub1.term_enrichment_agent USING btree (enriched_term_id);
Date: 2026-07-29 23:32:10 Duration: 2m4s Bind query: yes
-
CREATE INDEX ix_term_enrich_agent_enr_term ON pub1.term_enrichment_agent USING btree (enriched_term_id);
Date: 2026-07-29 23:32:10 Duration: 0ms
18 1m52s 1m55s 1m53s 4 7m33s copy pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jul 29 06 1 1m52s 1m52s 10 1 1m53s 1m53s 14 1 1m52s 1m52s 18 1 1m55s 1m55s [ User: postgres - Total duration: 7m33s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m33s - Times executed: 4 ]
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 18:06:56 Duration: 1m55s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 10:06:54 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 14:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
19 5s96ms 7m45s 59s426ms 63 1h2m23s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jul 29 11 10 1m25s 8s540ms 12 32 35m37s 1m6s 17 3 1m10s 23s408ms 20 3 9m11s 3m3s 21 4 7m23s 1m50s 22 8 4m13s 31s706ms 23 3 3m21s 1m7s [ User: load - Total duration: 15m8s - Times executed: 15 ]
[ Application: pg_bulkload - Total duration: 15m8s - Times executed: 15 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2026-07-29 20:48:34 Duration: 7m45s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-07-29 12:08:43 Duration: 5m31s Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2026-07-29 21:26:08 Duration: 5m28s Bind query: yes
20 24s94ms 24s457ms 24s269ms 4 1m37s copy pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jul 29 06 1 24s284ms 24s284ms 10 1 24s94ms 24s94ms 14 1 24s240ms 24s240ms 18 1 24s457ms 24s457ms -
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 18:07:20 Duration: 24s457ms
-
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 06:07:18 Duration: 24s284ms
-
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-07-29 14:07:19 Duration: 24s240ms
Time consuming prepare
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
Time consuming bind
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
-
Events
Log levels
Key values
- 13,161 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 1 FATAL entries
- 9 ERROR entries
- 0 WARNING entries
- 1 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 2 Max number of times the same event was reported
- 11 Total events found
Rank Times reported Error 1 2 ERROR: relation "..." does not exist
Times Reported Most Frequent Error / Event #1
Day Hour Count Jul 29 13 1 15 1 - ERROR: relation "ixn_actor" does not exist
- ERROR: relation "exi" does not exist at character 27
Statement: vacuum FULL analyze ixn_actor
Date: 2026-07-29 13:14:07
Statement: select * -- count(*) from exi order by create_tm desc limit 100
Date: 2026-07-29 15:42:42
2 2 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #2
Day Hour Count Jul 29 10 1 15 1 - ERROR: syntax error at or near ".159" at character 112
- ERROR: syntax error at or near "=" at character 10
Statement: select count(*) from log_query_archive where results_qty =18901 and type_cd = 'ixn' and remote_addr in (103.172.159.94 ,114.143.215.162 ,165.99.8.22 )
Date: 2026-07-29 10:38:18
Statement: select * =-- count(*) from exposure order by create_tm desc limit 100
Date: 2026-07-29 15:42:23
3 1 ERROR: unterminated quoted identifier at or near ""..."
Times Reported Most Frequent Error / Event #3
Day Hour Count Jul 29 09 1 - ERROR: unterminated quoted identifier at or near "" " at character 402
Statement: -- This provides discrepancies introduced this month - it is NOT aggregate select nm as Underlying_Term_Name ,acc_txt as Underlying_Term_Accession ,synonym ,reference_acc_txt ,notes ,create_by ,create_tm from edit.term_label where ( acc_txt, object_type_id ) not in ( -- Current Month's PUB select acc_txt, object_type_id from pub2.term ) order by acc_txt "
Date: 2026-07-29 09:34:13 Database: ctdprd51 Application: pgAdmin 4 - CONN:774039 User: load Remote:
4 1 ERROR: permission denied for table ...
Times Reported Most Frequent Error / Event #4
Day Hour Count Jul 29 12 1 - ERROR: permission denied for table term
Statement: begin transaction; update term set secondary_nm = '7461-02-1' where secondary_nm = '7461-02-1 (+-)-' and object_type_id = 2 and acc_txt = 'C014212'
Date: 2026-07-29 12:30:54
5 1 ERROR: function get_ixn_prose(...) does not exist
Times Reported Most Frequent Error / Event #5
Day Hour Count Jul 29 11 1 - ERROR: function get_ixn_prose(integer) does not exist at character 66
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Statement: select reference_acc_txt ,taxon_acc_txt ,pubTerm.nm ,get_ixn_prose( ixn_id ) ,create_by ,create_tm from edit.reference_ixn ri ,pub2.term pubTerm -- set to CURRENT PRODUCTION PUB!!!!! where taxon_acc_txt not in ( select acc_txt from load.term where object_type_id = ( select id from edit.object_type where cd = 'taxon' ) ) and pubTerm.acc_txt = ri.taxon_acc_txt and object_type_id = ( select id from edit.object_type where cd = 'taxon' ) and taxon_acc_txt is not null and taxon_acc_txt <> ''Date: 2026-07-29 11:35:32
6 1 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #6
Day Hour Count Jul 29 23 1 7 1 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #7
Day Hour Count Jul 29 23 1 - ERROR: canceling statement due to user request
Statement: SELECT pg_database_size(datname::text) FROM pg_catalog.pg_database WHERE datistemplate = false AND datname = $1;
Date: 2026-07-29 23:29:06
8 1 ERROR: syntax error at end of input
Times Reported Most Frequent Error / Event #8
Day Hour Count Jul 29 10 1 - ERROR: syntax error at end of input at character 190
Statement: select count(*) from log_query --where results_qty =18901 and type_cd = 'ixn' --and remote_addr in ('103.172.159.94' where remote_addr in ('103.172.159.94' ,'114.143.215.162' ,'165.99.8.22'
Date: 2026-07-29 10:41:15
9 1 LOG: could not send data to client: Broken pipe
Times Reported Most Frequent Error / Event #9
Day Hour Count Jul 29 23 1