pgBadger-
Global information
- Generated on Tue Mar 31 04:15:04 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260330
- Parsed 28,957 log entries in 2s
- Log start from 2026-03-30 00:00:01 to 2026-03-30 23:57:55
-
Overview
Global Stats
- 92 Number of unique normalized queries
- 214 Number of queries
- 7h30m32s Total query duration
- 2026-03-30 00:09:17 First query
- 2026-03-30 23:43:58 Last query
- 2 queries/s at 2026-03-30 14:31:54 Query peak
- 7h30m32s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 7h30m32s Execute total duration
- 22 Number of events
- 11 Number of unique normalized events
- 4 Max number of times the same event was reported
- 0 Number of cancellation
- 85 Total number of automatic vacuums
- 158 Total number of automatic analyzes
- 2,366 Number temporary file
- 1.00 GiB Max size of temporary file
- 246.26 MiB Average size of temporary file
- 2,267 Total number of sessions
- 206 sessions at 2026-03-30 23:55:11 Session peak
- 51d19h17m51s Total duration of sessions
- 32m54s Average duration of sessions
- 0 Average queries per session
- 11s924ms Average queries duration per session
- 32m42s Average idle time per session
- 2,283 Total number of connections
- 9 connections/s at 2026-03-30 05:45:08 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 2 queries/s Query Peak
- 2026-03-30 14:31:54 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2026-03-30 08:39:17 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-03-30 06:01:13 Date
Queries duration
Key values
- 7h30m32s 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) Mar 30 00 2 0ms 9m15s 4m41s 0ms 0ms 9m22s 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 0 0ms 0ms 0ms 0ms 0ms 0ms 03 0 0ms 0ms 0ms 0ms 0ms 0ms 04 0 0ms 0ms 0ms 0ms 0ms 0ms 05 7 0ms 8s619ms 6s920ms 5s79ms 8s619ms 14s872ms 06 9 0ms 1m50s 24s172ms 20s808ms 46s681ms 1m50s 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 2 0ms 7s847ms 7s839ms 0ms 0ms 7s847ms 09 5 0ms 35s848ms 35s358ms 35s5ms 35s308ms 35s848ms 10 12 0ms 18m30s 3m26s 35s301ms 47s74ms 36m58s 11 2 0ms 47s217ms 34s963ms 0ms 22s708ms 47s217ms 12 12 0ms 15s164ms 9s856ms 15s164ms 29s684ms 36s482ms 13 22 0ms 14s298ms 8s181ms 11s227ms 30s550ms 43s687ms 14 43 0ms 5m5s 46s350ms 1m43s 1m52s 5m23s 15 6 0ms 3m3s 1m2s 6s679ms 36s784ms 3m3s 16 4 0ms 16m7s 5m32s 0ms 2m4s 16m17s 17 2 0ms 29m10s 17m 0ms 4m50s 29m10s 18 16 0ms 28m46s 3m49s 1m57s 4m50s 28m46s 19 9 0ms 34m30s 5m30s 0ms 1m46s 34m38s 20 5 0ms 1m2s 55s633ms 56s360ms 56s806ms 1m2s 21 3 0ms 47m40s 15m57s 0ms 5s786ms 47m40s 22 22 0ms 1h5m23s 4m11s 2m15s 5m11s 1h5m52s 23 31 0ms 5m6s 1m8s 2m20s 3m25s 5m6s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Mar 30 00 1 0 9m15s 0ms 0ms 9m15s 01 0 0 0ms 0ms 0ms 0ms 02 0 0 0ms 0ms 0ms 0ms 03 0 0 0ms 0ms 0ms 0ms 04 0 0 0ms 0ms 0ms 0ms 05 7 0 6s920ms 0ms 5s79ms 14s872ms 06 0 9 24s172ms 0ms 20s808ms 1m50s 07 0 0 0ms 0ms 0ms 0ms 08 2 0 7s839ms 0ms 0ms 7s847ms 09 5 0 35s358ms 0ms 35s5ms 35s848ms 10 3 9 3m26s 0ms 35s301ms 36m58s 11 2 0 34s963ms 0ms 0ms 47s217ms 12 11 0 9s939ms 0ms 15s164ms 36s482ms 13 19 0 8s41ms 0ms 11s227ms 43s687ms 14 33 8 47s405ms 1m28s 1m33s 1m52s 15 2 0 1m35s 0ms 0ms 3m3s 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 0 9 24s463ms 0ms 0ms 1m52s 19 6 0 7m57s 0ms 0ms 34m38s 20 5 0 55s633ms 0ms 56s360ms 1m2s 21 3 0 15m57s 0ms 0ms 47m40s 22 22 0 4m11s 1m8s 2m15s 1h5m52s 23 6 0 1m27s 0ms 13s581ms 5m6s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Mar 30 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 0 0 0 0 0ms 0ms 0ms 0ms 12 1 0 0 0 8s944ms 0ms 0ms 0ms 13 3 0 0 0 9s70ms 0ms 0ms 9s48ms 14 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 4 0 0 0 5m32s 0ms 0ms 3m49s 17 2 0 0 0 17m 0ms 0ms 4m50s 18 7 0 0 0 8m12s 0ms 0ms 16m17s 19 3 0 0 0 35s449ms 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 Mar 30 00 0 0 0.00 0.00% 01 0 0 0.00 0.00% 02 0 0 0.00 0.00% 03 0 0 0.00 0.00% 04 0 0 0.00 0.00% 05 0 7 7.00 0.00% 06 0 0 0.00 0.00% 07 0 0 0.00 0.00% 08 0 2 2.00 0.00% 09 0 5 5.00 0.00% 10 0 3 3.00 0.00% 11 0 2 2.00 0.00% 12 0 11 11.00 0.00% 13 0 19 19.00 0.00% 14 0 33 33.00 0.00% 15 0 1 1.00 0.00% 16 0 4 4.00 0.00% 17 0 2 2.00 0.00% 18 0 7 7.00 0.00% 19 0 9 9.00 0.00% 20 0 5 5.00 0.00% 21 0 3 3.00 0.00% 22 0 22 22.00 0.00% 23 0 31 31.00 0.00% Day Hour Count Average / Second Mar 30 00 84 0.02/s 01 78 0.02/s 02 83 0.02/s 03 95 0.03/s 04 84 0.02/s 05 96 0.03/s 06 77 0.02/s 07 79 0.02/s 08 79 0.02/s 09 90 0.03/s 10 107 0.03/s 11 80 0.02/s 12 126 0.04/s 13 230 0.06/s 14 136 0.04/s 15 81 0.02/s 16 79 0.02/s 17 87 0.02/s 18 86 0.02/s 19 93 0.03/s 20 75 0.02/s 21 79 0.02/s 22 88 0.02/s 23 91 0.03/s Day Hour Count Average Duration Average idle time Mar 30 00 84 29m20s 29m13s 01 78 30m39s 30m39s 02 83 29m8s 29m8s 03 95 25m42s 25m42s 04 84 29m11s 29m11s 05 96 25m6s 25m5s 06 77 30m33s 30m30s 07 79 29m44s 29m44s 08 79 31m8s 31m7s 09 86 28m59s 28m57s 10 103 23m38s 23m14s 11 80 30m43s 30m42s 12 126 18m49s 18m48s 13 229 9m58s 9m58s 14 136 18m22s 18m8s 15 82 28m41s 28m36s 16 75 31m21s 31m3s 17 87 28m46s 28m23s 18 82 30m6s 29m21s 19 93 26m58s 26m26s 20 75 31m4s 31m1s 21 79 29m12s 28m36s 22 88 3h35m45s 3h34m42s 23 91 27m29s 27m6s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2026-03-30 05:45:08 Date
Connections per database
Key values
- ctdprd51 Main Database
- 2,283 connections Total
Connections per user
Key values
- pubeu Main User
- 2,283 connections Total
-
Sessions
Simultaneous sessions
Key values
- 206 sessions Session Peak
- 2026-03-30 23:55:11 Date
Histogram of session times
Key values
- 1,805 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,267 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,267 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 2,267 sessions Total
Host Count Total Duration Average Duration 10.12.5.45 382 7d23h59m4s 30m9s 10.12.5.46 376 8d11m19s 30m40s 10.12.5.52 42 52s896ms 1s259ms 10.12.5.53 426 8d1h2m21s 27m11s 10.12.5.54 376 7d23h33m51s 30m34s 10.12.5.55 372 7d23h46m51s 30m55s 10.12.5.56 282 7h57m50s 1m41s 192.168.201.10 1 1s45ms 1s45ms ::1 10 11d10h45m39s 1d3h28m33s -
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 944,243 buffers Checkpoint Peak
- 2026-03-30 17:49:46 Date
- 1619.982 seconds Highest write time
- 0.743 seconds Sync time
Checkpoints Wal files
Key values
- 562 files Wal files usage Peak
- 2026-03-30 23:49:35 Date
Checkpoints distance
Key values
- 17,244.54 Mo Distance Peak
- 2026-03-30 23:03:41 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Mar 30 00 386 38.846s 0.002s 38.858s 01 52,673 1,629.94s 0.003s 1,630.052s 02 1,148 115.171s 0.002s 115.219s 03 94 9.605s 0.002s 9.614s 04 138 14.009s 0.002s 14.017s 05 2,158 216.364s 0.002s 216.382s 06 184 18.62s 0.002s 18.628s 07 469 47.197s 0.002s 47.207s 08 4,606 461.364s 0.002s 461.471s 09 63,874 1,621.73s 0.002s 1,621.739s 10 1,957 196.088s 0.088s 196.312s 11 1,055 105.86s 0.002s 105.869s 12 20 2.089s 0.001s 2.094s 13 8,721 2,943.911s 0.035s 2,944.289s 14 310,985 2,215.05s 1.067s 2,223.783s 15 36,544 1,683.711s 0.009s 1,685.816s 16 2,565,885 1,552.91s 1.053s 1,558.345s 17 1,683,289 3,104.394s 0.191s 3,106.621s 18 2,911,698 2,774.642s 0.747s 2,781.07s 19 763,087 1,619.982s 0.01s 1,621.07s 20 302,388 1,622.289s 0.008s 1,622.816s 21 60 6.198s 0.002s 6.217s 22 28 2.896s 0.001s 2.901s 23 649,184 1,888.474s 0.769s 1,900.089s Day Hour Added Removed Recycled Synced files Longest sync Average sync Mar 30 00 0 0 0 66 0.001s 0.002s 01 0 0 35 54 0.001s 0.002s 02 0 0 1 35 0.001s 0.002s 03 0 0 0 23 0.001s 0.002s 04 0 0 0 30 0.001s 0.002s 05 0 0 1 48 0.001s 0.002s 06 0 0 0 77 0.001s 0.002s 07 0 0 0 117 0.001s 0.002s 08 0 0 35 42 0.001s 0.002s 09 0 0 0 36 0.001s 0.002s 10 0 0 1 690 0.001s 0.002s 11 0 0 0 31 0.001s 0.002s 12 0 0 0 8 0.001s 0.001s 13 0 0 141 477 0.001s 0.002s 14 0 36 3,335 590 0.390s 0.032s 15 0 0 1,076 147 0.001s 0.002s 16 0 0 2,293 351 0.138s 0.025s 17 0 0 1,008 111 0.076s 0.009s 18 0 31 2,691 834 0.197s 0.018s 19 0 0 538 135 0.001s 0.001s 20 0 0 186 233 0.001s 0.002s 21 0 0 0 25 0.001s 0.002s 22 0 0 0 9 0.001s 0.001s 23 0 32 3,229 254 0.742s 0.038s Day Hour Count Avg time (sec) Mar 30 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 Mar 30 00 1,535.50 kB 49,698.00 kB 01 288,904.00 kB 548,343.50 kB 02 3,337.50 kB 444,817.50 kB 03 204.00 kB 360,328.00 kB 04 257.50 kB 291,924.50 kB 05 7,983.00 kB 237,280.50 kB 06 505.50 kB 192,973.50 kB 07 1,852.50 kB 156,594.50 kB 08 23,195.00 kB 129,266.50 kB 09 263,895.50 kB 501,268.00 kB 10 7,317.00 kB 406,787.00 kB 11 3,710.50 kB 330,834.50 kB 12 100.00 kB 282,095.00 kB 13 892,175.50 kB 892,175.50 kB 14 7,890,749.57 kB 7,893,930.86 kB 15 8,810,186.50 kB 8,820,838.50 kB 16 7,513,576.60 kB 8,683,794.00 kB 17 8,522,282.00 kB 8,789,446.00 kB 18 8,813,699.40 kB 8,814,463.40 kB 19 8,808,643.00 kB 8,818,208.00 kB 20 1,785,632.50 kB 7,878,833.00 kB 21 105.00 kB 6,381,875.00 kB 22 121.00 kB 5,441,405.00 kB 23 7,557,499.00 kB 8,264,434.71 kB -
Temporary Files
Size of temporary files
Key values
- 54.00 GiB Temp Files size Peak
- 2026-03-30 22:44:46 Date
Number of temporary files
Key values
- 54 per second Temp Files Peak
- 2026-03-30 22:44:46 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Mar 30 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 0 0 0 12 180 1.56 GiB 8.87 MiB 13 444 3.90 GiB 9.01 MiB 14 948 59.92 GiB 64.73 MiB 15 155 11.48 GiB 75.82 MiB 16 0 0 0 17 0 0 0 18 0 0 0 19 0 0 0 20 29 28.89 GiB 1020.25 MiB 21 61 60.24 GiB 1011.17 MiB 22 289 286.43 GiB 1014.91 MiB 23 260 116.56 GiB 459.07 MiB Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 1,622 78.21 GiB 8.00 KiB 1.00 GiB 49.37 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-03-30 22:57:24 Duration: 7m25s Database: ctdprd51 User: load Application: pg_bulkload
-
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-03-30 23:34:19 Duration: 5m6s
-
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-03-30 14:16:26 Duration: 5m5s
2 282 280.14 GiB 17.49 MiB 1.00 GiB 1017.24 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-03-30 22:44:33 Duration: 0ms
3 61 60.24 GiB 241.49 MiB 1.00 GiB 1011.17 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-03-30 21:24:36 Duration: 0ms
4 35 4.82 GiB 77.67 MiB 204.59 MiB 141.05 MiB vacuum full analyze db_link;-
vacuum FULL analyze db_link;
Date: 2026-03-30 15:24:50 Duration: 2m18s
-
vacuum FULL analyze db_link;
Date: 2026-03-30 15:22:57 Duration: 0ms
5 35 1.22 GiB 25.05 MiB 51.15 MiB 35.82 MiB vacuum full analyze ixn_actor;-
vacuum FULL analyze ixn_actor;
Date: 2026-03-30 15:21:58 Duration: 26s636ms
-
vacuum FULL analyze ixn_actor;
Date: 2026-03-30 15:21:39 Duration: 0ms
6 29 28.89 GiB 915.33 MiB 1.00 GiB 1020.25 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-03-30 20:32:36 Duration: 0ms
7 25 404.06 MiB 11.74 MiB 22.72 MiB 16.16 MiB vacuum full analyze ixn;-
vacuum FULL analyze ixn;
Date: 2026-03-30 15:22:20 Duration: 8s529ms
-
vacuum FULL analyze ixn;
Date: 2026-03-30 15:22:15 Duration: 0ms
8 20 220.25 MiB 7.66 MiB 16.91 MiB 11.01 MiB vacuum full analyze term;-
vacuum FULL analyze TERM;
Date: 2026-03-30 15:21:28 Duration: 10s147ms
-
vacuum FULL analyze TERM;
Date: 2026-03-30 15:21:20 Duration: 0ms
9 20 13.92 GiB 8.00 KiB 1.00 GiB 712.90 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-03-30 23:05:29 Duration: 4m6s
-
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-03-30 23:05:28 Duration: 0ms
10 15 7.74 GiB 8.00 KiB 1.00 GiB 528.08 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-03-30 23:01:23 Duration: 2m35s
-
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-03-30 23:01:22 Duration: 0ms Database: ctdprd51 User: pub1
11 10 7.74 GiB 539.83 MiB 1.00 GiB 792.11 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-03-30 23:19:25 Duration: 1m49s
-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub1.gene_disease_reference USING btree (mod_tm);
Date: 2026-03-30 23:19:25 Duration: 0ms
12 10 7.74 GiB 542.15 MiB 1.00 GiB 792.11 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-03-30 23:13:29 Duration: 1m44s
-
CREATE INDEX ix_gene_disease_ref_reference ON pub1.gene_disease_reference USING btree (reference_id);
Date: 2026-03-30 23:13:29 Duration: 0ms
13 10 7.74 GiB 540.54 MiB 1.00 GiB 792.11 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-03-30 23:22:22 Duration: 2m56s
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-03-30 23:22:21 Duration: 0ms
14 10 7.74 GiB 536.74 MiB 1.00 GiB 792.11 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-03-30 23:17:36 Duration: 1m46s
-
CREATE INDEX ix_gene_disease_reference_ixn ON pub1.gene_disease_reference USING btree (ixn_id);
Date: 2026-03-30 23:17:35 Duration: 0ms
15 10 7.74 GiB 548.63 MiB 1.00 GiB 792.11 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-03-30 23:15:49 Duration: 2m20s
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-03-30 23:15:49 Duration: 0ms
16 10 473.07 MiB 8.00 KiB 99.60 MiB 47.31 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-03-30 23:25:08 Duration: 6s826ms
-
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-03-30 23:25:08 Duration: 0ms
17 10 7.74 GiB 542.88 MiB 1.00 GiB 792.11 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-03-30 23:08:01 Duration: 1m22s
-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub1.gene_disease_reference USING btree (source_cd);
Date: 2026-03-30 23:08:01 Duration: 0ms
18 10 259.66 MiB 8.00 KiB 52.83 MiB 25.97 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-03-30 23:25:01 Duration: 0ms
19 10 7.74 GiB 530.62 MiB 1.00 GiB 792.11 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-03-30 23:06:38 Duration: 1m9s
-
CREATE INDEX ix_gene_disease_ref_src_db ON pub1.gene_disease_reference USING btree (source_acc_db_id);
Date: 2026-03-30 23:06:38 Duration: 0ms
20 10 7.74 GiB 540.54 MiB 1.00 GiB 792.11 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-03-30 23:09:54 Duration: 1m53s
-
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2026-03-30 23:09:54 Duration: 0ms
21 10 1.17 GiB 8.00 KiB 244.66 MiB 120.03 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-03-30 23:22:40 Duration: 16s933ms
-
ALTER TABLE pub1.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2026-03-30 23:22:39 Duration: 0ms
22 10 7.74 GiB 539.13 MiB 1.00 GiB 792.11 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-03-30 23:11:44 Duration: 1m50s
-
CREATE INDEX ix_gene_disease_ref_disease ON pub1.gene_disease_reference USING btree (disease_id);
Date: 2026-03-30 23:11:44 Duration: 0ms
23 7 6.29 GiB 301.87 MiB 1.00 GiB 920.84 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-03-30 22:49:02 Duration: 0ms
24 5 1.17 GiB 233.91 MiB 257.03 MiB 240.05 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-03-30 23:23:16 Duration: 12s8ms
-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub1.phenotype_term_reference USING btree (term_object_type_id);
Date: 2026-03-30 23:23:16 Duration: 0ms
25 5 259.61 MiB 50.49 MiB 53.94 MiB 51.92 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-03-30 23:25:30 Duration: 0ms
26 5 259.61 MiB 50.63 MiB 53.92 MiB 51.92 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-03-30 23:25:23 Duration: 0ms
27 5 1.17 GiB 231.36 MiB 254.32 MiB 240.05 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-03-30 23:24:29 Duration: 12s837ms
-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub1.phenotype_term_reference USING btree (ixn_id);
Date: 2026-03-30 23:24:29 Duration: 0ms
28 5 259.61 MiB 48.41 MiB 55.55 MiB 51.92 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-03-30 23:25:12 Duration: 0ms
29 5 259.61 MiB 48.62 MiB 54.57 MiB 51.92 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-03-30 23:25:19 Duration: 0ms
30 5 1.17 GiB 236.20 MiB 244.05 MiB 240.05 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-03-30 23:23:52 Duration: 10s388ms
-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub1.phenotype_term_reference USING btree (evidence_cd);
Date: 2026-03-30 23:23:52 Duration: 0ms
31 5 1.17 GiB 231.62 MiB 246.62 MiB 240.05 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-03-30 23:23:04 Duration: 13s105ms
-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub1.phenotype_term_reference USING btree (term_id);
Date: 2026-03-30 23:23:04 Duration: 0ms
32 5 1.17 GiB 233.49 MiB 244.04 MiB 240.05 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-03-30 23:23:42 Duration: 10s610ms
-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub1.phenotype_term_reference USING btree (taxon_id);
Date: 2026-03-30 23:23:42 Duration: 0ms
33 5 259.61 MiB 50.87 MiB 53.42 MiB 51.92 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-03-30 23:25:15 Duration: 0ms
34 5 259.60 MiB 49.00 MiB 54.12 MiB 51.92 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-03-30 23:25:35 Duration: 5s467ms
-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub1.chem_disease_reference USING btree (network_score);
Date: 2026-03-30 23:25:35 Duration: 0ms
35 5 1.17 GiB 237.69 MiB 242.43 MiB 240.05 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-03-30 23:23:31 Duration: 14s684ms
-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub1.phenotype_term_reference USING btree (reference_id);
Date: 2026-03-30 23:23:31 Duration: 0ms
36 5 259.62 MiB 49.34 MiB 54.23 MiB 51.92 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-03-30 23:25:17 Duration: 0ms
37 5 1.17 GiB 228.91 MiB 250.94 MiB 240.05 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-03-30 23:24:16 Duration: 13s399ms
-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub1.phenotype_term_reference USING btree (term_reference_id);
Date: 2026-03-30 23:24:16 Duration: 0ms
38 5 1.17 GiB 233.27 MiB 244.65 MiB 240.05 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-03-30 23:22:51 Duration: 11s798ms
-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub1.phenotype_term_reference USING btree (phenotype_id);
Date: 2026-03-30 23:22:51 Duration: 0ms
39 5 1.64 GiB 332.05 MiB 344.23 MiB 336.87 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-03-30 23:24:58 Duration: 15s262ms
-
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-03-30 23:24:58 Duration: 0ms
40 5 1.17 GiB 233.90 MiB 246.35 MiB 240.05 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-03-30 23:24:43 Duration: 13s396ms
-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub1.phenotype_term_reference USING btree (via_term_id);
Date: 2026-03-30 23:24:43 Duration: 0ms
41 5 1.17 GiB 236.87 MiB 243.74 MiB 240.05 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-03-30 23:24:03 Duration: 10s848ms
-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub1.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2026-03-30 23:24:03 Duration: 0ms
42 5 259.61 MiB 45.52 MiB 55.58 MiB 51.92 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-03-30 23:25:26 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB SELECT * FROM pgbulkload.pg_bulkload ($1);[ Date: 2026-03-30 14:40:06 - 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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:36 ]
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-03-30 20:32:37 ]
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-03-30 20:32:37 ]
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-03-30 20:32:37 ]
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-03-30 20:32:37 ]
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-03-30 20:32:37 ]
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-03-30 20:32:37 ]
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-03-30 20:32:37 ]
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-03-30 20:32:37 ]
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-03-30 20:32:37 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 72.60 sec Highest CPU-cost vacuum
Table pub1.gene_go_annot
Database ctdprd51 - 2026-03-30 17:17:45 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 72.60 sec Highest CPU-cost vacuum
Table pub1.gene_go_annot
Database ctdprd51 - 2026-03-30 17:17:45 Date
Analyzes per table
Key values
- pubc.log_query (16) Main table analyzed (database ctdprd51)
- 158 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 16 ctdprd51.pg_catalog.pg_class 8 ctdprd51.pg_catalog.pg_index 5 ctdprd51.pg_catalog.pg_trigger 5 ctdprd51.pg_catalog.pg_attribute 5 ctdprd51.pg_catalog.pg_constraint 5 ctdprd51.edit.action_degree 4 ctdprd51.edit.action_type 4 ctdprd51.pub1.db 4 ctdprd51.edit.chem_conc_uom 4 ctdprd51.edit.list_db_report 4 ctdprd51.edit.db_report 4 ctdprd51.edit.db_report_site 4 ctdprd51.edit.action_type_path 4 ctdprd51.pg_catalog.pg_depend 4 ctdprd51.edit.country 4 ctdprd51.pg_catalog.pg_type 4 ctdprd51.edit.db_link 4 ctdprd51.pub1.term 4 ctdprd51.edit.db 4 postgres.pg_catalog.pg_shdepend 3 ctdprd51.pg_catalog.pg_description 3 ctdprd51.pg_catalog.pg_attrdef 3 ctdprd51.edit.term_label_type 2 ctdprd51.pub1.list_db_report 2 ctdprd51.edit.race 2 ctdprd51.pub1.gene_taxon 2 ctdprd51.pub1.db_report 2 ctdprd51.edit.geographic_region 2 ctdprd51.pub1.db_link 2 ctdprd51.pub1.db_report_site 2 ctdprd51.pub1.term_label 2 ctdprd51.pub1.action_type 2 ctdprd51.pub1.img 2 ctdprd51.pub1.gene_go_annot 2 ctdprd51.pg_catalog.pg_shdepend 2 ctdprd51.edit.chem_conc_exp_route 2 ctdprd51.edit.slim_term 2 ctdprd51.edit.object_note 1 ctdprd51.edit.actor_form_type 1 ctdprd51.edit.receptor 1 ctdprd51.pub1.term_pathway 1 ctdprd51.pub2.term_set_enrichment 1 ctdprd51.pub1.reference_party 1 ctdprd51.edit.reference_db_link 1 ctdprd51.pub2.term_set_enrichment_agent 1 ctdprd51.load.data_load 1 ctdprd51.edit.study_factor 1 ctdprd51.pub1.reference 1 ctdprd51.pub1.chem_conc_anatomy 1 ctdprd51.edit.action_degree_type 1 ctdprd51.pub1.chem_conc 1 ctdprd51.edit.evidence 1 ctdprd51.pg_catalog.pg_proc 1 ctdprd51.pub1.dag_node 1 ctdprd51.pub1.dag_edge 1 ctdprd51.pub1.reference_party_role 1 Total 158 Vacuums per table
Key values
- edit.action_degree (4) Main table vacuumed on database ctdprd51
- 85 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.edit.action_degree 4 0 189 0 0 0 0 48 2 21,410 ctdprd51.edit.action_type_path 4 0 201 0 0 0 0 16 2 19,842 ctdprd51.edit.db_link 4 0 30,539 0 12 0 0 14,868 4 910,792 ctdprd51.pg_catalog.pg_class 4 4 1,564 0 98 0 20 609 93 357,836 ctdprd51.edit.country 4 0 273 0 0 0 0 32 2 22,114 ctdprd51.pub1.term 4 0 294,078 0 8 0 0 93,657 6 5,568,103 ctdprd51.pg_catalog.pg_attribute 3 3 2,174 0 248 0 111 1,063 192 884,685 postgres.pg_catalog.pg_shdepend 2 2 454 0 42 0 0 159 36 146,754 ctdprd51.pg_catalog.pg_index 2 2 373 0 44 0 0 215 34 140,214 ctdprd51.pub1.db 2 2 302 0 29 0 0 40 20 70,330 ctdprd51.pub1.gene_taxon 2 0 356,146 0 11 0 0 177,954 7 10,552,380 ctdprd51.edit.race 2 0 151 0 2 0 0 6 3 22,352 ctdprd51.edit.action_type 2 0 351 0 2 0 0 14 2 15,965 ctdprd51.edit.list_db_report 2 0 110 0 2 0 0 14 3 25,154 ctdprd51.pubc.log_query 2 1 419 0 49 0 0 101 24 175,904 ctdprd51.pub1.db_link 2 0 641,049 0 267,647 0 0 320,222 11 18,980,012 ctdprd51.pub1.term_label 2 0 442,037 0 58,430 0 0 220,910 10 13,109,642 ctdprd51.pg_catalog.pg_trigger 2 2 773 0 74 0 0 305 80 327,859 ctdprd51.pg_catalog.pg_statistic 2 2 1,453 0 108 0 257 927 103 330,354 ctdprd51.pub1.gene_go_annot 2 0 1,352,970 0 592,741 0 0 676,242 24 40,086,976 ctdprd51.pub1.img 2 0 2,216 0 10 0 0 1,048 4 88,454 ctdprd51.pg_catalog.pg_type 2 2 310 0 20 0 0 134 20 78,817 ctdprd51.pg_catalog.pg_constraint 2 2 571 0 60 0 0 253 61 189,006 ctdprd51.pg_catalog.pg_attrdef 2 2 187 0 14 0 0 50 8 34,505 ctdprd51.pg_toast.pg_toast_2619 1 1 4,381 0 1,215 0 10,342 3,085 996 432,968 ctdprd51.edit.actor_form_type 1 0 81 0 1 0 0 2 1 8,490 ctdprd51.edit.receptor 1 0 55 0 1 0 0 2 1 8,502 ctdprd51.pg_toast.pg_toast_10886064 1 0 90,300 0 4 0 0 45,142 2 2,679,901 ctdprd51.edit.geographic_region 1 0 65 0 2 0 0 3 2 12,721 ctdprd51.edit.chem_conc_uom 1 1 35 0 5 0 0 7 2 10,589 ctdprd51.edit.reference_db_link 1 0 7,459 0 4 0 0 3,717 1 227,629 ctdprd51.pub1.reference_party 1 0 5,189 0 4 0 0 2,544 2 162,371 ctdprd51.pub1.term_pathway 1 0 3,365 0 4 0 0 1,614 2 107,665 ctdprd51.pub2.term_set_enrichment 1 0 8,071 0 3,587 0 0 3,982 2 246,545 ctdprd51.pub1.reference 1 0 78,718 0 4 0 0 39,266 2 2,333,310 ctdprd51.edit.db_report_site 1 0 60 0 3 0 0 11 2 13,171 ctdprd51.edit.db_report 1 0 96 0 1 0 0 9 1 9,452 ctdprd51.pub1.chem_conc 1 0 619 0 3 0 0 294 1 25,765 ctdprd51.pub1.chem_conc_anatomy 1 0 402 0 3 0 0 186 1 19,393 ctdprd51.pg_catalog.pg_proc 1 1 434 0 90 0 79 289 86 153,711 ctdprd51.edit.evidence 1 0 33 0 1 0 0 3 1 8,755 ctdprd51.pg_catalog.pg_shdepend 1 1 149 0 42 0 0 86 30 85,818 ctdprd51.pg_catalog.pg_depend 1 1 748 0 122 0 65 303 105 376,124 ctdprd51.pub1.reference_party_role 1 0 13,772 0 2 0 0 6,847 2 418,316 ctdprd51.pub1.dag_node 1 0 84,402 0 317 0 0 42,059 3 2,503,381 ctdprd51.pub1.dag_edge 1 0 1,024 0 5 0 0 482 2 42,273 ctdprd51.pg_catalog.pg_description 1 1 230 0 60 0 42 117 41 142,011 ctdprd51.edit.slim_term 1 0 53 0 1 0 0 3 1 8,841 Total 85 30 3,428,631 1,129 925,132 0 10,916 1,658,940 2,040 102,197,162 Tuples removed per table
Key values
- pg_catalog.pg_attribute (4625) Main table with removed tuples on database ctdprd51
- 21520 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pg_catalog.pg_attribute 3 3 4,625 25,175 0 0 690 ctdprd51.pg_toast.pg_toast_2619 1 1 3,824 19,492 143 0 12,592 ctdprd51.pg_catalog.pg_depend 1 1 2,379 13,681 0 0 153 ctdprd51.pg_catalog.pg_statistic 2 2 1,463 5,692 0 0 820 ctdprd51.pg_catalog.pg_trigger 2 2 1,337 3,018 0 0 111 postgres.pg_catalog.pg_shdepend 2 2 1,198 4,238 0 0 44 ctdprd51.pg_catalog.pg_description 1 1 775 5,346 0 0 90 ctdprd51.pg_catalog.pg_index 2 2 694 2,042 0 0 76 ctdprd51.pg_catalog.pg_class 4 4 677 7,692 0 0 376 ctdprd51.edit.country 4 0 652 996 0 0 16 ctdprd51.pg_catalog.pg_constraint 2 2 551 1,539 0 0 82 ctdprd51.pg_catalog.pg_shdepend 1 1 473 1,615 0 0 22 ctdprd51.edit.action_type_path 4 0 424 424 0 0 8 ctdprd51.pg_catalog.pg_type 2 2 388 2,338 0 0 69 ctdprd51.edit.action_degree 4 0 384 876 0 0 24 ctdprd51.pub1.db 2 2 268 268 0 0 14 ctdprd51.pg_catalog.pg_proc 1 1 193 1,588 0 0 205 ctdprd51.edit.race 2 0 189 54 0 0 2 ctdprd51.edit.list_db_report 2 0 184 366 0 0 6 ctdprd51.pg_catalog.pg_attrdef 2 2 140 422 0 2 21 ctdprd51.edit.action_type 2 0 128 120 0 0 6 ctdprd51.edit.db_report 1 0 97 162 0 0 4 ctdprd51.edit.db_report_site 1 0 90 164 0 0 5 ctdprd51.edit.geographic_region 1 0 83 51 0 0 1 ctdprd51.edit.receptor 1 0 64 16 0 0 1 ctdprd51.edit.chem_conc_uom 1 1 61 80 0 0 2 ctdprd51.edit.slim_term 1 0 61 38 0 0 1 ctdprd51.edit.evidence 1 0 58 18 0 0 1 ctdprd51.edit.actor_form_type 1 0 54 18 0 0 1 ctdprd51.pubc.log_query 2 1 6 1,503 0 0 54 ctdprd51.pub1.gene_taxon 2 0 0 27,938,556 0 0 177,952 ctdprd51.pg_toast.pg_toast_10886064 1 0 0 244,007 0 0 45,141 ctdprd51.edit.reference_db_link 1 0 0 333,427 0 0 3,716 ctdprd51.pub1.reference_party 1 0 0 455,308 0 0 2,543 ctdprd51.pub1.term_pathway 1 0 0 135,792 0 0 1,613 ctdprd51.pub2.term_set_enrichment 1 0 0 1,130,388 0 0 18,716 ctdprd51.pub1.reference 1 0 0 202,112 0 0 39,265 ctdprd51.pub1.db_link 2 0 0 44,163,206 0 0 320,220 ctdprd51.pub1.term_label 2 0 0 15,139,938 0 0 220,908 ctdprd51.pub1.gene_go_annot 2 0 0 106,171,950 0 0 676,240 ctdprd51.pub1.chem_conc 1 0 0 9,119 0 0 293 ctdprd51.pub1.img 2 0 0 101,308 0 0 1,046 ctdprd51.pub1.chem_conc_anatomy 1 0 0 19,688 0 0 185 ctdprd51.edit.db_link 4 0 0 1,333,708 0 0 14,864 ctdprd51.pub1.reference_party_role 1 0 0 1,266,510 0 0 6,846 ctdprd51.pub1.dag_node 1 0 0 1,751,403 0 0 42,058 ctdprd51.pub1.term 4 0 0 4,372,766 0 0 155,600 ctdprd51.pub1.dag_edge 1 0 0 88,931 0 0 481 Total 85 30 21,520 204,957,149 143 2 1,743,184 Pages removed per table
Key values
- pg_catalog.pg_attrdef (2) Main table with removed pages on database ctdprd51
- 2 pages Total removed
Table Number of vacuums Index scans Tuples removed Pages removed ctdprd51.pg_catalog.pg_attrdef 2 2 140 2 postgres.pg_catalog.pg_shdepend 2 2 1198 0 ctdprd51.edit.action_degree 4 0 384 0 ctdprd51.pg_toast.pg_toast_2619 1 1 3824 0 ctdprd51.pg_catalog.pg_index 2 2 694 0 ctdprd51.pub1.db 2 2 268 0 ctdprd51.edit.actor_form_type 1 0 54 0 ctdprd51.pub1.gene_taxon 2 0 0 0 ctdprd51.edit.race 2 0 189 0 ctdprd51.edit.action_type 2 0 128 0 ctdprd51.edit.receptor 1 0 64 0 ctdprd51.pg_toast.pg_toast_10886064 1 0 0 0 ctdprd51.edit.geographic_region 1 0 83 0 ctdprd51.edit.chem_conc_uom 1 1 61 0 ctdprd51.edit.reference_db_link 1 0 0 0 ctdprd51.pub1.reference_party 1 0 0 0 ctdprd51.edit.list_db_report 2 0 184 0 ctdprd51.pub1.term_pathway 1 0 0 0 ctdprd51.pub2.term_set_enrichment 1 0 0 0 ctdprd51.pubc.log_query 2 1 6 0 ctdprd51.pub1.reference 1 0 0 0 ctdprd51.edit.action_type_path 4 0 424 0 ctdprd51.edit.db_report_site 1 0 90 0 ctdprd51.pub1.db_link 2 0 0 0 ctdprd51.edit.db_report 1 0 97 0 ctdprd51.pub1.term_label 2 0 0 0 ctdprd51.pg_catalog.pg_trigger 2 2 1337 0 ctdprd51.pg_catalog.pg_statistic 2 2 1463 0 ctdprd51.pub1.gene_go_annot 2 0 0 0 ctdprd51.pub1.chem_conc 1 0 0 0 ctdprd51.pub1.img 2 0 0 0 ctdprd51.pub1.chem_conc_anatomy 1 0 0 0 ctdprd51.pg_catalog.pg_proc 1 1 193 0 ctdprd51.edit.db_link 4 0 0 0 ctdprd51.pg_catalog.pg_class 4 4 677 0 ctdprd51.edit.evidence 1 0 58 0 ctdprd51.pg_catalog.pg_type 2 2 388 0 ctdprd51.edit.country 4 0 652 0 ctdprd51.pg_catalog.pg_shdepend 1 1 473 0 ctdprd51.pg_catalog.pg_depend 1 1 2379 0 ctdprd51.pg_catalog.pg_constraint 2 2 551 0 ctdprd51.pub1.reference_party_role 1 0 0 0 ctdprd51.pub1.dag_node 1 0 0 0 ctdprd51.pub1.term 4 0 0 0 ctdprd51.pub1.dag_edge 1 0 0 0 ctdprd51.pg_catalog.pg_description 1 1 775 0 ctdprd51.edit.slim_term 1 0 61 0 ctdprd51.pg_catalog.pg_attribute 3 3 4625 0 Total 85 30 21,520 2 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Mar 30 00 1 1 01 0 7 02 0 0 03 0 1 04 0 1 05 1 3 06 0 0 07 0 0 08 0 2 09 0 1 10 10 19 11 0 0 12 7 17 13 30 57 14 0 0 15 2 3 16 7 11 17 10 12 18 10 15 19 7 6 20 0 0 21 0 0 22 0 0 23 0 2 - 72.60 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- RowExclusiveLock Main Lock Type
- 2 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query 1 2 48s715ms 23s392ms 25s322ms 24s357ms begin transaction; update edit.db_link set acc_txt = ? where acc_txt = ? and object_type_id = ?;-
begin transaction; update edit.db_link set acc_txt = '7461-02-1' where acc_txt = '7461-02-1 (+-)-' and object_type_id = 2;
Date: 2026-03-30 14:33:42
-
begin transaction; update edit.db_link set acc_txt = '7461-02-1' where acc_txt = '7461-02-1 (+-)-' and object_type_id = 2;
Date: 2026-03-30 14:31:54
-
begin transaction; update edit.db_link set acc_txt = '7461-02-1' where acc_txt = '7461-02-1 (+-)-' and object_type_id = 2;
Date: 2026-03-30 14:31:54
Queries that waited the most
Rank Wait time Query 1 25s322ms begin transaction; update edit.db_link set acc_txt = '7461-02-1' where acc_txt = '7461-02-1 (+-)-' and object_type_id = 2;[ Date: 2026-03-30 14:33:41 ]
2 23s392ms begin transaction; update edit.db_link set acc_txt = '7461-02-1' where acc_txt = '7461-02-1 (+-)-' and object_type_id = 2;[ Date: 2026-03-30 14:31:54 ]
-
Queries
Queries by type
Key values
- 127 Total read queries
- 82 Total write queries
Queries by database
Key values
- unknown Main database
- 154 Requests
- 4h57m17s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 156 Requests
User Request type Count Duration edit Total 1 8s944ms insert 1 8s944ms load Total 24 59m24s select 24 59m24s postgres Total 8 8m31s copy to 8 8m31s pub1 Total 2 32m14s insert 2 32m14s pubc Total 1 9m15s select 1 9m15s pubeu Total 27 44m30s select 27 44m30s qaeu Total 5 32s667ms select 5 32s667ms unknown Total 156 4h57m33s copy to 27 5m46s ddl 25 26m21s insert 17 1h23m37s others 5 3m10s select 80 2h57m47s tcl 2 49s414ms Duration by user
Key values
- 4h57m33s (unknown) Main time consuming user
User Request type Count Duration edit Total 1 8s944ms insert 1 8s944ms load Total 24 59m24s select 24 59m24s postgres Total 8 8m31s copy to 8 8m31s pub1 Total 2 32m14s insert 2 32m14s pubc Total 1 9m15s select 1 9m15s pubeu Total 27 44m30s select 27 44m30s qaeu Total 5 32s667ms select 5 32s667ms unknown Total 156 4h57m33s copy to 27 5m46s ddl 25 26m21s insert 17 1h23m37s others 5 3m10s select 80 2h57m47s tcl 2 49s414ms Queries by host
Key values
- unknown Main host
- 224 Requests
- 7h32m11s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 186 Requests
- 7h33s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-03-30 19:27:10 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 145 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 1h5m23s 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-03-30 22:30:04 - Bind query: yes ]
2 47m40s 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-03-30 21:24:34 - Bind query: yes ]
3 34m30s 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-03-30 19:36:06 - Database: ctdprd51 - User: load - Bind query: yes ]
4 29m10s 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-03-30 17:15:38 - Bind query: yes ]
5 28m46s 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-03-30 18:52:31 - Bind query: yes ]
6 18m30s SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279362') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;[ Date: 2026-03-30 10:15:53 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
7 18m28s SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279362') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;[ Date: 2026-03-30 10:15:47 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
8 16m7s 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-03-30 16:40:22 - Database: ctdprd51 - User: pub1 - Bind query: yes ]
9 16m6s 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-03-30 18:17:44 - Database: ctdprd51 - User: pub1 - Bind query: yes ]
10 11m59s 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-03-30 19:50:59 - Database: ctdprd51 - User: load - Bind query: yes ]
11 9m15s /* * 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-03-30 00:09:17 - Database: ctdprd51 - User: pubc - Application: psql ]
12 7m25s 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-03-30 22:57:24 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
13 5m6s 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-03-30 23:34:19 - Bind query: yes ]
14 5m5s 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-03-30 14:16:26 - Bind query: yes ]
15 5m3s 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-03-30 22:40:30 - Bind query: yes ]
16 4m50s 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-03-30 17:20:29 - Bind query: yes ]
17 4m50s 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-03-30 18:57:21 - Bind query: yes ]
18 4m6s 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-03-30 23:05:29 - Bind query: yes ]
19 3m51s 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-03-30 18:23:44 - Bind query: yes ]
20 3m49s 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-03-30 16:46:27 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 1h19m49s 13 12s604ms 1h5m23s 6m8s 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 Mar 30 22 13 1h19m49s 6m8s -
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-03-30 22:30:04 Duration: 1h5m23s 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-03-30 22:40:30 Duration: 5m3s 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-03-30 22:43:46 Duration: 3m15s Bind query: yes
2 57m57s 2 28m46s 29m10s 28m58s 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 #2
Day Hour Count Duration Avg duration Mar 30 17 1 29m10s 29m10s 18 1 28m46s 28m46s -
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-03-30 17:15:38 Duration: 29m10s Bind query: yes
-
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-03-30 18:52:31 Duration: 28m46s Bind query: yes
3 51m56s 68 5s61ms 7m25s 45s824ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Mar 30 12 8 1m4s 8s60ms 13 19 2m32s 8s41ms 14 30 28m24s 56s814ms 15 1 3m3s 3m3s 19 3 1m7s 22s643ms 22 3 8m47s 2m55s 23 4 6m55s 1m43s [ User: load - Total duration: 12m3s - Times executed: 18 ]
[ Application: pg_bulkload - Total duration: 12m3s - Times executed: 18 ]
-
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-03-30 22:57:24 Duration: 7m25s Database: ctdprd51 User: load Application: pg_bulkload 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-03-30 23:34:19 Duration: 5m6s 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-03-30 14:16:26 Duration: 5m5s Bind query: yes
4 47m40s 1 47m40s 47m40s 47m40s 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 #4
Day Hour Count Duration Avg duration Mar 30 21 1 47m40s 47m40s -
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-03-30 21:24:34 Duration: 47m40s Bind query: yes
5 36m58s 2 18m28s 18m30s 18m29s select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where phenotypeterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? order by chemnetworkcount desc, genenetworkcount desc limit ?;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Mar 30 10 2 36m58s 18m29s [ User: pubeu - Total duration: 36m58s - Times executed: 2 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279362') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-03-30 10:15:53 Duration: 18m30s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279362') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-03-30 10:15:47 Duration: 18m28s Database: ctdprd51 User: pubeu Bind query: yes
6 34m30s 1 34m30s 34m30s 34m30s 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 #6
Day Hour Count Duration Avg duration Mar 30 19 1 34m30s 34m30s [ User: load - Total duration: 34m30s - 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-03-30 19:36:06 Duration: 34m30s Database: ctdprd51 User: load Bind query: yes
7 32m14s 2 16m6s 16m7s 16m7s 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 #7
Day Hour Count Duration Avg duration Mar 30 16 1 16m7s 16m7s 18 1 16m6s 16m6s [ User: pub1 - Total duration: 32m14s - Times executed: 2 ]
-
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-03-30 16:40:22 Duration: 16m7s Database: ctdprd51 User: pub1 Bind query: yes
-
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-03-30 18:17:44 Duration: 16m6s Database: ctdprd51 User: pub1 Bind query: yes
8 15m51s 5 56s360ms 11m59s 3m10s 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 #8
Day Hour Count Duration Avg duration Mar 30 19 1 11m59s 11m59s 20 4 3m52s 58s187ms [ User: load - Total duration: 11m59s - 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-03-30 19:50:59 Duration: 11m59s 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-03-30 20:10:42 Duration: 1m2s 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-03-30 20:22:35 Duration: 56s806ms Bind query: yes
9 9m40s 2 4m50s 4m50s 4m50s 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 #9
Day Hour Count Duration Avg duration Mar 30 17 1 4m50s 4m50s 18 1 4m50s 4m50s -
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-03-30 17:20:29 Duration: 4m50s Bind query: yes
-
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-03-30 18:57:21 Duration: 4m50s Bind query: yes
10 9m15s 1 9m15s 9m15s 9m15s select maint_query_logs_archive ();Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Mar 30 00 1 9m15s 9m15s [ User: pubc - Total duration: 9m15s - Times executed: 1 ]
[ Application: psql - Total duration: 9m15s - 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-03-30 00:09:17 Duration: 9m15s Database: ctdprd51 User: pubc Application: psql
11 7m40s 2 3m49s 3m51s 3m50s 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 #11
Day Hour Count Duration Avg duration Mar 30 16 1 3m49s 3m49s 18 1 3m51s 3m51s -
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-03-30 18:23:44 Duration: 3m51s Bind query: yes
-
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-03-30 16:46:27 Duration: 3m49s Bind query: yes
12 7m27s 4 1m50s 1m52s 1m51s 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 #12
Day Hour Count Duration Avg duration Mar 30 06 1 1m50s 1m50s 10 1 1m51s 1m51s 14 1 1m52s 1m52s 18 1 1m52s 1m52s [ User: postgres - Total duration: 7m27s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m27s - 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-03-30 18:06:54 Duration: 1m52s 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-03-30 14:06:53 Duration: 1m52s 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-03-30 10:06:53 Duration: 1m51s Database: ctdprd51 User: postgres Application: pg_dump
13 4m6s 1 4m6s 4m6s 4m6s 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 Mar 30 23 1 4m6s 4m6s -
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-03-30 23:05:29 Duration: 4m6s 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-03-30 23:05:28 Duration: 0ms
14 4m2s 10 7s137ms 35s848ms 24s233ms select ? "Input", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( select string_agg(stm.slim_term_nm, ? order by stm.slim_term_nm) from slim_term_mapping stm where stm.mapped_term_id = d.id) "DiseaseCategories", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(distinct r.acc_txt, ?) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id where d.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) group by g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by d.nm_sort, g.nm, "DirectEvidence", c.nm;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Mar 30 09 5 2m56s 35s358ms 10 1 35s301ms 35s301ms 22 4 30s241ms 7s560ms [ User: pubeu - Total duration: 3m47s - Times executed: 8 ]
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:56:37 Duration: 35s848ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:40:50 Duration: 35s380ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:10:49 Duration: 35s308ms Database: ctdprd51 User: pubeu Bind query: yes
15 4m1s 2 1m57s 2m4s 2m insert into pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) select t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) as db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), ?, ?, ?, ?, ?, ?, ? from load.term t where object_type_id not in (...);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Mar 30 16 1 2m4s 2m4s 18 1 1m57s 1m57s -
INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2026-03-30 16:42:38 Duration: 2m4s Bind query: yes
-
INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2026-03-30 18:19:53 Duration: 1m57s Bind query: yes
16 2m56s 1 2m56s 2m56s 2m56s 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 Mar 30 23 1 2m56s 2m56s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-03-30 23:22:22 Duration: 2m56s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-03-30 23:22:21 Duration: 0ms
17 2m35s 1 2m35s 2m35s 2m35s alter table pub1.gene_disease_reference add constraint gene_disease_reference_pk primary key (id);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Mar 30 23 1 2m35s 2m35s -
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-03-30 23:01:23 Duration: 2m35s Bind query: yes
-
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-03-30 23:01:22 Duration: 0ms Database: ctdprd51 User: pub1
18 2m20s 1 2m20s 2m20s 2m20s 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 Mar 30 23 1 2m20s 2m20s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-03-30 23:15:49 Duration: 2m20s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-03-30 23:15:49 Duration: 0ms
19 2m18s 1 2m18s 2m18s 2m18s vacuum full analyze db_link;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Mar 30 15 1 2m18s 2m18s -
vacuum FULL analyze db_link;
Date: 2026-03-30 15:24:50 Duration: 2m18s
-
vacuum FULL analyze db_link;
Date: 2026-03-30 15:22:57 Duration: 0ms
20 2m15s 1 2m15s 2m15s 2m15s 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 #20
Day Hour Count Duration Avg duration Mar 30 22 1 2m15s 2m15s -
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-03-30 22:47:03 Duration: 2m15s Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 68 51m56s 5s61ms 7m25s 45s824ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Mar 30 12 8 1m4s 8s60ms 13 19 2m32s 8s41ms 14 30 28m24s 56s814ms 15 1 3m3s 3m3s 19 3 1m7s 22s643ms 22 3 8m47s 2m55s 23 4 6m55s 1m43s [ User: load - Total duration: 12m3s - Times executed: 18 ]
[ Application: pg_bulkload - Total duration: 12m3s - Times executed: 18 ]
-
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-03-30 22:57:24 Duration: 7m25s Database: ctdprd51 User: load Application: pg_bulkload 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-03-30 23:34:19 Duration: 5m6s 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-03-30 14:16:26 Duration: 5m5s Bind query: yes
2 13 1h19m49s 12s604ms 1h5m23s 6m8s 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 #2
Day Hour Count Duration Avg duration Mar 30 22 13 1h19m49s 6m8s -
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-03-30 22:30:04 Duration: 1h5m23s 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-03-30 22:40:30 Duration: 5m3s 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-03-30 22:43:46 Duration: 3m15s Bind query: yes
3 10 4m2s 7s137ms 35s848ms 24s233ms select ? "Input", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( select string_agg(stm.slim_term_nm, ? order by stm.slim_term_nm) from slim_term_mapping stm where stm.mapped_term_id = d.id) "DiseaseCategories", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(distinct r.acc_txt, ?) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id where d.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) group by g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by d.nm_sort, g.nm, "DirectEvidence", c.nm;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Mar 30 09 5 2m56s 35s358ms 10 1 35s301ms 35s301ms 22 4 30s241ms 7s560ms [ User: pubeu - Total duration: 3m47s - Times executed: 8 ]
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:56:37 Duration: 35s848ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:40:50 Duration: 35s380ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:10:49 Duration: 35s308ms Database: ctdprd51 User: pubeu Bind query: yes
4 5 15m51s 56s360ms 11m59s 3m10s 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 Mar 30 19 1 11m59s 11m59s 20 4 3m52s 58s187ms [ User: load - Total duration: 11m59s - 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-03-30 19:50:59 Duration: 11m59s 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-03-30 20:10:42 Duration: 1m2s 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-03-30 20:22:35 Duration: 56s806ms Bind query: yes
5 4 7m27s 1m50s 1m52s 1m51s 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 Mar 30 06 1 1m50s 1m50s 10 1 1m51s 1m51s 14 1 1m52s 1m52s 18 1 1m52s 1m52s [ User: postgres - Total duration: 7m27s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m27s - 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-03-30 18:06:54 Duration: 1m52s 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-03-30 14:06:53 Duration: 1m52s 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-03-30 10:06:53 Duration: 1m51s Database: ctdprd51 User: postgres Application: pg_dump
6 4 1m35s 23s793ms 24s188ms 23s957ms 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 Mar 30 06 1 23s798ms 23s798ms 10 1 23s793ms 23s793ms 14 1 24s188ms 24s188ms 18 1 24s48ms 24s48ms -
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-03-30 14:07:18 Duration: 24s188ms
-
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-03-30 18:07:18 Duration: 24s48ms
-
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-03-30 06:07:16 Duration: 23s798ms
7 4 1m2s 15s382ms 15s614ms 15s509ms 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 #7
Day Hour Count Duration Avg duration Mar 30 06 1 15s382ms 15s382ms 10 1 15s439ms 15s439ms 14 1 15s614ms 15s614ms 18 1 15s603ms 15s603ms -
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-03-30 14:07:33 Duration: 15s614ms
-
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-03-30 18:07:33 Duration: 15s603ms
-
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-03-30 10:07:32 Duration: 15s439ms
8 4 58s637ms 14s553ms 14s756ms 14s659ms 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 #8
Day Hour Count Duration Avg duration Mar 30 06 1 14s553ms 14s553ms 10 1 14s756ms 14s756ms 14 1 14s648ms 14s648ms 18 1 14s677ms 14s677ms -
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-03-30 10:00:51 Duration: 14s756ms
-
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-03-30 18:00:51 Duration: 14s677ms
-
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-03-30 14:00:34 Duration: 14s648ms
9 4 57s784ms 14s404ms 14s519ms 14s446ms 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 #9
Day Hour Count Duration Avg duration Mar 30 06 1 14s404ms 14s404ms 10 1 14s454ms 14s454ms 14 1 14s519ms 14s519ms 18 1 14s405ms 14s405ms -
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-03-30 14:00:49 Duration: 14s519ms
-
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-03-30 10:01:06 Duration: 14s454ms
-
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-03-30 18:01:05 Duration: 14s405ms
10 4 36s155ms 8s944ms 9s124ms 9s38ms insert into edit.db_link select * from edit.reference_db_link;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Mar 30 12 1 8s944ms 8s944ms 13 3 27s211ms 9s70ms [ User: edit - Total duration: 8s944ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:6933350 - Total duration: 8s944ms - Times executed: 1 ]
-
insert into edit.db_link select * from edit.reference_db_link;
Date: 2026-03-30 13:19:44 Duration: 9s124ms
-
insert into edit.db_link select * from edit.reference_db_link;
Date: 2026-03-30 13:36:23 Duration: 9s48ms
-
insert into edit.db_link select * from edit.reference_db_link;
Date: 2026-03-30 13:41:31 Duration: 9s38ms
11 4 29s258ms 7s298ms 7s330ms 7s314ms 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 Mar 30 06 1 7s300ms 7s300ms 10 1 7s328ms 7s328ms 14 1 7s330ms 7s330ms 18 1 7s298ms 7s298ms [ User: postgres - Total duration: 7s330ms - Times executed: 1 ]
[ Application: pg_dump - Total duration: 7s330ms - Times executed: 1 ]
-
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-03-30 14:00:13 Duration: 7s330ms Database: ctdprd51 User: postgres Application: pg_dump
-
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-03-30 10:00:30 Duration: 7s328ms
-
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-03-30 06:00:29 Duration: 7s300ms
12 4 25s743ms 6s389ms 6s476ms 6s435ms 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 Mar 30 06 1 6s404ms 6s404ms 10 1 6s473ms 6s473ms 14 1 6s476ms 6s476ms 18 1 6s389ms 6s389ms -
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-03-30 14:00:57 Duration: 6s476ms
-
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-03-30 10:01:14 Duration: 6s473ms
-
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-03-30 06:01:13 Duration: 6s404ms
13 4 24s331ms 6s66ms 6s111ms 6s82ms 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 Mar 30 06 1 6s73ms 6s73ms 10 1 6s111ms 6s111ms 14 1 6s79ms 6s79ms 18 1 6s66ms 6s66ms -
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-03-30 10:00:36 Duration: 6s111ms
-
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-03-30 14:00:19 Duration: 6s79ms
-
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-03-30 06:00:36 Duration: 6s73ms
14 3 56s831ms 18s752ms 19s201ms 18s943ms 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 #14
Day Hour Count Duration Avg duration Mar 30 06 1 18s752ms 18s752ms 10 1 18s877ms 18s877ms 18 1 19s201ms 19s201ms [ User: postgres - Total duration: 56s831ms - Times executed: 3 ]
[ Application: pg_dump - Total duration: 56s831ms - Times executed: 3 ]
-
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-03-30 18:00:20 Duration: 19s201ms 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-03-30 10:00:20 Duration: 18s877ms 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-03-30 06:00:20 Duration: 18s752ms Database: ctdprd51 User: postgres Application: pg_dump
15 2 57m57s 28m46s 29m10s 28m58s 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 #15
Day Hour Count Duration Avg duration Mar 30 17 1 29m10s 29m10s 18 1 28m46s 28m46s -
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-03-30 17:15:38 Duration: 29m10s Bind query: yes
-
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-03-30 18:52:31 Duration: 28m46s Bind query: yes
16 2 36m58s 18m28s 18m30s 18m29s select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where phenotypeterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? order by chemnetworkcount desc, genenetworkcount desc limit ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Mar 30 10 2 36m58s 18m29s [ User: pubeu - Total duration: 36m58s - Times executed: 2 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279362') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-03-30 10:15:53 Duration: 18m30s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279362') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-03-30 10:15:47 Duration: 18m28s Database: ctdprd51 User: pubeu Bind query: yes
17 2 32m14s 16m6s 16m7s 16m7s 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 #17
Day Hour Count Duration Avg duration Mar 30 16 1 16m7s 16m7s 18 1 16m6s 16m6s [ User: pub1 - Total duration: 32m14s - Times executed: 2 ]
-
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-03-30 16:40:22 Duration: 16m7s Database: ctdprd51 User: pub1 Bind query: yes
-
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-03-30 18:17:44 Duration: 16m6s Database: ctdprd51 User: pub1 Bind query: yes
18 2 9m40s 4m50s 4m50s 4m50s 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 #18
Day Hour Count Duration Avg duration Mar 30 17 1 4m50s 4m50s 18 1 4m50s 4m50s -
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-03-30 17:20:29 Duration: 4m50s Bind query: yes
-
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-03-30 18:57:21 Duration: 4m50s Bind query: yes
19 2 7m40s 3m49s 3m51s 3m50s 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 #19
Day Hour Count Duration Avg duration Mar 30 16 1 3m49s 3m49s 18 1 3m51s 3m51s -
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-03-30 18:23:44 Duration: 3m51s Bind query: yes
-
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-03-30 16:46:27 Duration: 3m49s Bind query: yes
20 2 4m1s 1m57s 2m4s 2m insert into pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) select t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) as db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), ?, ?, ?, ?, ?, ?, ? from load.term t where object_type_id not in (...);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Mar 30 16 1 2m4s 2m4s 18 1 1m57s 1m57s -
INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2026-03-30 16:42:38 Duration: 2m4s Bind query: yes
-
INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2026-03-30 18:19:53 Duration: 1m57s Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 47m40s 47m40s 47m40s 1 47m40s 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 Mar 30 21 1 47m40s 47m40s -
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-03-30 21:24:34 Duration: 47m40s Bind query: yes
2 34m30s 34m30s 34m30s 1 34m30s 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 Mar 30 19 1 34m30s 34m30s [ User: load - Total duration: 34m30s - 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-03-30 19:36:06 Duration: 34m30s Database: ctdprd51 User: load Bind query: yes
3 28m46s 29m10s 28m58s 2 57m57s 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 Mar 30 17 1 29m10s 29m10s 18 1 28m46s 28m46s -
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-03-30 17:15:38 Duration: 29m10s Bind query: yes
-
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-03-30 18:52:31 Duration: 28m46s Bind query: yes
4 18m28s 18m30s 18m29s 2 36m58s select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where phenotypeterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? order by chemnetworkcount desc, genenetworkcount desc limit ?;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Mar 30 10 2 36m58s 18m29s [ User: pubeu - Total duration: 36m58s - Times executed: 2 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279362') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-03-30 10:15:53 Duration: 18m30s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279362') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-03-30 10:15:47 Duration: 18m28s Database: ctdprd51 User: pubeu Bind query: yes
5 16m6s 16m7s 16m7s 2 32m14s 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 #5
Day Hour Count Duration Avg duration Mar 30 16 1 16m7s 16m7s 18 1 16m6s 16m6s [ User: pub1 - Total duration: 32m14s - Times executed: 2 ]
-
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-03-30 16:40:22 Duration: 16m7s Database: ctdprd51 User: pub1 Bind query: yes
-
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-03-30 18:17:44 Duration: 16m6s Database: ctdprd51 User: pub1 Bind query: yes
6 9m15s 9m15s 9m15s 1 9m15s select maint_query_logs_archive ();Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Mar 30 00 1 9m15s 9m15s [ User: pubc - Total duration: 9m15s - Times executed: 1 ]
[ Application: psql - Total duration: 9m15s - 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-03-30 00:09:17 Duration: 9m15s Database: ctdprd51 User: pubc Application: psql
7 12s604ms 1h5m23s 6m8s 13 1h19m49s 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 Mar 30 22 13 1h19m49s 6m8s -
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-03-30 22:30:04 Duration: 1h5m23s 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-03-30 22:40:30 Duration: 5m3s 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-03-30 22:43:46 Duration: 3m15s Bind query: yes
8 4m50s 4m50s 4m50s 2 9m40s 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 Mar 30 17 1 4m50s 4m50s 18 1 4m50s 4m50s -
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-03-30 17:20:29 Duration: 4m50s Bind query: yes
-
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-03-30 18:57:21 Duration: 4m50s Bind query: yes
9 4m6s 4m6s 4m6s 1 4m6s 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 Mar 30 23 1 4m6s 4m6s -
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-03-30 23:05:29 Duration: 4m6s 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-03-30 23:05:28 Duration: 0ms
10 3m49s 3m51s 3m50s 2 7m40s 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 Mar 30 16 1 3m49s 3m49s 18 1 3m51s 3m51s -
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-03-30 18:23:44 Duration: 3m51s Bind query: yes
-
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-03-30 16:46:27 Duration: 3m49s Bind query: yes
11 56s360ms 11m59s 3m10s 5 15m51s 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 #11
Day Hour Count Duration Avg duration Mar 30 19 1 11m59s 11m59s 20 4 3m52s 58s187ms [ User: load - Total duration: 11m59s - 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-03-30 19:50:59 Duration: 11m59s 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-03-30 20:10:42 Duration: 1m2s 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-03-30 20:22:35 Duration: 56s806ms Bind query: yes
12 2m56s 2m56s 2m56s 1 2m56s 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 Mar 30 23 1 2m56s 2m56s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-03-30 23:22:22 Duration: 2m56s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2026-03-30 23:22:21 Duration: 0ms
13 2m35s 2m35s 2m35s 1 2m35s alter table pub1.gene_disease_reference add constraint gene_disease_reference_pk primary key (id);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Mar 30 23 1 2m35s 2m35s -
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-03-30 23:01:23 Duration: 2m35s Bind query: yes
-
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-03-30 23:01:22 Duration: 0ms Database: ctdprd51 User: pub1
14 2m20s 2m20s 2m20s 1 2m20s create index ix_gene_disease_ref_dis_gene on pub1.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Mar 30 23 1 2m20s 2m20s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-03-30 23:15:49 Duration: 2m20s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-03-30 23:15:49 Duration: 0ms
15 2m18s 2m18s 2m18s 1 2m18s vacuum full analyze db_link;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Mar 30 15 1 2m18s 2m18s -
vacuum FULL analyze db_link;
Date: 2026-03-30 15:24:50 Duration: 2m18s
-
vacuum FULL analyze db_link;
Date: 2026-03-30 15:22:57 Duration: 0ms
16 2m15s 2m15s 2m15s 1 2m15s 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 Mar 30 22 1 2m15s 2m15s -
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-03-30 22:47:03 Duration: 2m15s Bind query: yes
17 1m57s 2m4s 2m 2 4m1s insert into pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) select t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) as db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), ?, ?, ?, ?, ?, ?, ? from load.term t where object_type_id not in (...);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Mar 30 16 1 2m4s 2m4s 18 1 1m57s 1m57s -
INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2026-03-30 16:42:38 Duration: 2m4s Bind query: yes
-
INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2026-03-30 18:19:53 Duration: 1m57s Bind query: yes
18 1m50s 1m52s 1m51s 4 7m27s 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 Mar 30 06 1 1m50s 1m50s 10 1 1m51s 1m51s 14 1 1m52s 1m52s 18 1 1m52s 1m52s [ User: postgres - Total duration: 7m27s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m27s - 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-03-30 18:06:54 Duration: 1m52s 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-03-30 14:06:53 Duration: 1m52s 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-03-30 10:06:53 Duration: 1m51s Database: ctdprd51 User: postgres Application: pg_dump
19 5s61ms 7m25s 45s824ms 68 51m56s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Mar 30 12 8 1m4s 8s60ms 13 19 2m32s 8s41ms 14 30 28m24s 56s814ms 15 1 3m3s 3m3s 19 3 1m7s 22s643ms 22 3 8m47s 2m55s 23 4 6m55s 1m43s [ User: load - Total duration: 12m3s - Times executed: 18 ]
[ Application: pg_bulkload - Total duration: 12m3s - Times executed: 18 ]
-
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-03-30 22:57:24 Duration: 7m25s Database: ctdprd51 User: load Application: pg_bulkload 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-03-30 23:34:19 Duration: 5m6s 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-03-30 14:16:26 Duration: 5m5s Bind query: yes
20 7s137ms 35s848ms 24s233ms 10 4m2s select ? "Input", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( select string_agg(stm.slim_term_nm, ? order by stm.slim_term_nm) from slim_term_mapping stm where stm.mapped_term_id = d.id) "DiseaseCategories", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(distinct r.acc_txt, ?) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id where d.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) group by g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by d.nm_sort, g.nm, "DirectEvidence", c.nm;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Mar 30 09 5 2m56s 35s358ms 10 1 35s301ms 35s301ms 22 4 30s241ms 7s560ms [ User: pubeu - Total duration: 3m47s - Times executed: 8 ]
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:56:37 Duration: 35s848ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:40:50 Duration: 35s380ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'inflammation' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131663) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-03-30 09:10:49 Duration: 35s308ms Database: ctdprd51 User: pubeu Bind query: yes
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
- 14,326 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 4 FATAL entries
- 8 ERROR entries
- 4 WARNING entries
- 6 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 4 Max number of times the same event was reported
- 22 Total events found
Rank Times reported Error 1 4 LOG: could not send data to client: Broken pipe
Times Reported Most Frequent Error / Event #1
Day Hour Count Mar 30 10 2 22 1 23 1 2 4 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #2
Day Hour Count Mar 30 10 2 22 1 23 1 3 3 ERROR: relation "..." does not exist
Times Reported Most Frequent Error / Event #3
Day Hour Count Mar 30 13 1 15 1 18 1 - ERROR: relation "voc_term_label" does not exist at character 15
- ERROR: relation "reference_contact" does not exist at character 51
- ERROR: relation "exp_anatomy" does not exist at character 15
Statement: select * from voc_term_label where nm like '1492952-76-7%'
Date: 2026-03-30 13:06:41
Statement: select reference_acc_txt, create_tm, sent_tm from reference_contact where reference_acc_txt not in ( select reference_acc_txt from reference_ixn union select reference_acc_txt from reference_exp ) order by mod_tm desc
Date: 2026-03-30 15:02:55
Statement: select * from exp_anatomy limit 10
Date: 2026-03-30 18:11:14 Database: ctdprd51 Application: pgAdmin 4 - CONN:1227085 User: load Remote:
4 2 WARNING: Maximum parse error count exceeded - 1 error(...) found in input file
Times Reported Most Frequent Error / Event #4
Day Hour Count Mar 30 12 1 13 1 5 2 LOG: process ... still waiting for RowExclusiveLock on relation ... of database ... after ... ms
Times Reported Most Frequent Error / Event #5
Day Hour Count Mar 30 14 2 - LOG: process 2150704 still waiting for RowExclusiveLock on relation 2633821 of database 484829 after 1000.059 ms at character 27
Detail: Processes holding the lock: 2164078, 2164513, 2164514, 2164516, 2164515. Wait queue: 2150704.
Statement: begin transaction; update edit.db_link set acc_txt = '7461-02-1' where acc_txt = '7461-02-1 (+-)-' and object_type_id = 2Date: 2026-03-30 14:31:31
6 2 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #6
Day Hour Count Mar 30 22 1 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-03-30 22:59:08
7 1 ERROR: function get_ixn_prose(...) does not exist
Times Reported Most Frequent Error / Event #7
Day Hour Count Mar 30 13 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-03-30 13:45:17 Database: ctdprd51 Application: pgAdmin 4 - CONN:4053406 User: load Remote:
8 1 WARNING: Parse error Record 1: Input Record 316260: Rejected - column 4. missing data for column "..."
Times Reported Most Frequent Error / Event #8
Day Hour Count Mar 30 12 1 9 1 ERROR: unterminated quoted string at or near "..."
Times Reported Most Frequent Error / Event #9
Day Hour Count Mar 30 17 1 - ERROR: unterminated quoted string at or near "'1638601 '' limit 100" at character 45
Statement: select * from load.DAG_node where acc_txt = '1638601 '' limit 100
Date: 2026-03-30 17:32:01
10 1 WARNING: Parse error Record 1: Input Record 320224: Rejected - column 4. missing data for column "..."
Times Reported Most Frequent Error / Event #10
Day Hour Count Mar 30 13 1 11 1 ERROR: new row for relation "..." violates check constraint "..."
Times Reported Most Frequent Error / Event #11
Day Hour Count Mar 30 17 1 - ERROR: new row for relation "dag_node" violates check constraint "dag_node_no_cycle_ck"
Detail: Failing row contains (1, 7, 1, 608365, 1638601, TAXON, species, t, 0, 2198893, 2198894, 1638601, Mytilus platensis, MYTILUS PLATENSIS, Mytilus platensis, null, f, f, f, f, f, f, f).
Statement: INSERT INTO pub1.DAG_NODE (id ,dag_id ,object_id ,parent_id ,acc_txt ,acc_db_cd ,rank_nm ,is_leaf ,subset_left_no ,subset_right_no ,nm ,nm_sort ,secondary_nm ,object_acc_txt ,NM_HTML ,LEVEL_MIN_NO ,has_chems ,has_diseases ,has_genes ,has_marrays ) SELECT n.id ,n.dag_id ,n.object_id ,(SELECT p.id FROM load.DAG_NODE p WHERE p.acc_txt = n.parent_acc_txt AND p.acc_db_id = n.parent_acc_db_id AND p.dag_id = n.dag_id ) AS parent_id ,n.acc_txt ,get_db_cd (n.acc_db_id) AS acc_db_cd ,n.rank_nm ,n.is_leaf ,n.subset_left_no ,n.subset_right_no ,t.nm ,t.nm_sort ,t.secondary_nm ,t.acc_txt ,break_html_words(t.nm) ,n.LEVEL_MIN_NO ,'f' ,'f' ,'f' ,'f' FROM load.DAG_NODE n ,load.TERM t WHERE t.id = n.object_idDate: 2026-03-30 17:20:30