pgBadger-
Global information
- Generated on Fri Aug 28 04:15:04 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260827
- Parsed 28,138 log entries in 3s
- Log start from 2026-08-27 00:00:01 to 2026-08-27 23:59:14
-
Overview
Global Stats
- 94 Number of unique normalized queries
- 230 Number of queries
- 6h44m12s Total query duration
- 2026-08-27 00:09:23 First query
- 2026-08-27 23:53:15 Last query
- 2 queries/s at 2026-08-27 05:20:42 Query peak
- 6h44m12s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 6h44m12s Execute total duration
- 7 Number of events
- 7 Number of unique normalized events
- 1 Max number of times the same event was reported
- 0 Number of cancellation
- 43 Total number of automatic vacuums
- 66 Total number of automatic analyzes
- 2,192 Number temporary file
- 1.00 GiB Max size of temporary file
- 295.81 MiB Average size of temporary file
- 2,342 Total number of sessions
- 155 sessions at 2026-08-27 23:53:04 Session peak
- 44d16h22m56s Total duration of sessions
- 27m28s Average duration of sessions
- 0 Average queries per session
- 10s355ms Average queries duration per session
- 27m18s Average idle time per session
- 2,347 Total number of connections
- 14 connections/s at 2026-08-27 06:43:43 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 2 queries/s Query Peak
- 2026-08-27 05:20:42 Date
SELECT Traffic
Key values
- 2 queries/s Query Peak
- 2026-08-27 05:20:42 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-08-27 06:07:19 Date
Queries duration
Key values
- 6h44m12s 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) Aug 27 00 2 0ms 9m22s 4m44s 0ms 0ms 9m29s 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 0 0ms 0ms 0ms 0ms 0ms 0ms 03 3 0ms 6s807ms 6s61ms 0ms 5s340ms 6s807ms 04 4 0ms 19s913ms 15s649ms 0ms 0ms 56s448ms 05 25 0ms 1m58s 28s616ms 15s151ms 1m44s 4m1s 06 10 0ms 1m53s 22s962ms 21s168ms 48s833ms 1m53s 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 12 0ms 37s216ms 23s241ms 35s522ms 36s360ms 48s794ms 09 3 0ms 40s128ms 28s822ms 0ms 10s100ms 40s128ms 10 9 0ms 1m54s 25s114ms 22s82ms 49s854ms 1m54s 11 0 0ms 0ms 0ms 0ms 0ms 0ms 12 0 0ms 0ms 0ms 0ms 0ms 0ms 13 14 0ms 5m44s 35s816ms 18s795ms 40s714ms 5m44s 14 40 0ms 3m18s 51s239ms 2m14s 2m31s 3m18s 15 7 0ms 2m32s 33s253ms 6s981ms 16s729ms 2m32s 16 3 0ms 17m21s 6m31s 0ms 2m1s 17m31s 17 7 0ms 31m26s 6m23s 1m48s 4m18s 31m26s 18 18 0ms 35m55s 3m4s 1m 1m53s 36m4s 19 7 0ms 1m1s 33s179ms 43s114ms 56s734ms 1m1s 20 1 0ms 53m14s 53m14s 0ms 0ms 53m14s 21 20 0ms 1h13m19s 5m8s 2m24s 6m58s 1h13m50s 22 34 0ms 5m26s 1m4s 2m24s 3m24s 5m26s 23 11 0ms 1m8s 26s261ms 30s172ms 1m2s 1m8s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 27 00 1 0 9m22s 0ms 0ms 9m22s 01 0 0 0ms 0ms 0ms 0ms 02 0 0 0ms 0ms 0ms 0ms 03 3 0 6s61ms 0ms 0ms 6s807ms 04 4 0 15s649ms 0ms 0ms 56s448ms 05 24 0 29s579ms 12s528ms 15s151ms 4m1s 06 1 9 22s962ms 0ms 21s168ms 1m53s 07 0 0 0ms 0ms 0ms 0ms 08 12 0 23s241ms 10s243ms 35s522ms 48s794ms 09 3 0 28s822ms 0ms 0ms 40s128ms 10 0 9 25s114ms 0ms 22s82ms 1m54s 11 0 0 0ms 0ms 0ms 0ms 12 0 0 0ms 0ms 0ms 0ms 13 13 0 37s870ms 0ms 18s795ms 5m44s 14 31 9 51s239ms 1m54s 2m7s 3m18s 15 3 0 10s37ms 0ms 0ms 16s729ms 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 9 9 3m4s 0ms 1m 36m4s 19 7 0 33s179ms 0ms 43s114ms 1m1s 20 1 0 53m14s 0ms 0ms 53m14s 21 20 0 5m8s 1m2s 2m24s 1h13m50s 22 9 0 1m3s 0ms 14s381ms 5m26s 23 11 0 26s261ms 11s764ms 30s172ms 1m8s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 27 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 0 0 0 0 0ms 0ms 0ms 0ms 13 1 0 0 0 9s108ms 0ms 0ms 0ms 14 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 3 0 0 0 6m31s 0ms 0ms 2m1s 17 7 0 0 0 6m23s 0ms 0ms 5m24s 18 0 0 0 0 0ms 0ms 0ms 0ms 19 0 0 0 0 0ms 0ms 0ms 0ms 20 0 0 0 0 0ms 0ms 0ms 0ms 21 0 0 0 0 0ms 0ms 0ms 0ms 22 0 0 0 0 0ms 0ms 0ms 0ms 23 0 0 0 0 0ms 0ms 0ms 0ms Day Hour Prepare Bind Bind/Prepare Percentage of prepare Aug 27 00 0 0 0.00 0.00% 01 0 0 0.00 0.00% 02 0 0 0.00 0.00% 03 0 3 3.00 0.00% 04 0 4 4.00 0.00% 05 0 25 25.00 0.00% 06 0 1 1.00 0.00% 07 0 0 0.00 0.00% 08 0 12 12.00 0.00% 09 0 3 3.00 0.00% 10 0 0 0.00 0.00% 11 0 0 0.00 0.00% 12 0 0 0.00 0.00% 13 0 13 13.00 0.00% 14 0 31 31.00 0.00% 15 0 2 2.00 0.00% 16 0 3 3.00 0.00% 17 0 7 7.00 0.00% 18 0 9 9.00 0.00% 19 0 7 7.00 0.00% 20 0 1 1.00 0.00% 21 0 20 20.00 0.00% 22 0 34 34.00 0.00% 23 0 11 11.00 0.00% Day Hour Count Average / Second Aug 27 00 76 0.02/s 01 79 0.02/s 02 77 0.02/s 03 121 0.03/s 04 112 0.03/s 05 182 0.05/s 06 106 0.03/s 07 75 0.02/s 08 152 0.04/s 09 127 0.04/s 10 77 0.02/s 11 71 0.02/s 12 87 0.02/s 13 131 0.04/s 14 129 0.04/s 15 77 0.02/s 16 76 0.02/s 17 78 0.02/s 18 91 0.03/s 19 76 0.02/s 20 76 0.02/s 21 84 0.02/s 22 92 0.03/s 23 95 0.03/s Day Hour Count Average Duration Average idle time Aug 27 00 76 31m47s 31m39s 01 79 30m58s 30m58s 02 77 31m31s 31m31s 03 121 20m2s 20m1s 04 112 20m46s 20m45s 05 182 13m16s 13m12s 06 106 23m9s 23m7s 07 75 31m18s 31m18s 08 152 16m51s 16m50s 09 127 18m12s 18m12s 10 77 30m20s 30m17s 11 71 33m18s 33m18s 12 83 27m 27m 13 130 19m21s 19m18s 14 130 18m25s 18m9s 15 77 32m36s 32m33s 16 75 32m13s 31m57s 17 78 31m54s 31m20s 18 91 27m11s 26m35s 19 76 31m13s 31m10s 20 76 31m57s 31m15s 21 84 31m12s 29m59s 22 92 1h33m9s 1h32m45s 23 95 25m59s 25m56s -
Connections
Established Connections
Key values
- 14 connections Connection Peak
- 2026-08-27 06:43:43 Date
Connections per database
Key values
- ctdprd51 Main Database
- 2,347 connections Total
Connections per user
Key values
- pubeu Main User
- 2,347 connections Total
-
Sessions
Simultaneous sessions
Key values
- 155 sessions Session Peak
- 2026-08-27 23:53:04 Date
Histogram of session times
Key values
- 1,732 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,342 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,342 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 2,342 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 970,121 buffers Checkpoint Peak
- 2026-08-27 18:30:01 Date
- 1619.825 seconds Highest write time
- 0.457 seconds Sync time
Checkpoints Wal files
Key values
- 566 files Wal files usage Peak
- 2026-08-27 15:09:44 Date
Checkpoints distance
Key values
- 17,249.86 Mo Distance Peak
- 2026-08-27 22:05:01 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Aug 27 00 353 35.554s 0.002s 35.565s 01 102 10.383s 0.002s 10.391s 02 193 19.507s 0.002s 19.516s 03 135 13.7s 0.002s 13.708s 04 131 13.317s 0.002s 13.328s 05 91 9.95s 0.012s 10.182s 06 5,734 574.164s 0.003s 574.225s 07 269 27.149s 0.002s 27.159s 08 181 18.348s 0.002s 18.357s 09 92 9.408s 0.002s 9.417s 10 233 23.544s 0.002s 23.552s 11 3,610 361.762s 0.002s 361.773s 12 2,249 225.373s 0.102s 225.63s 13 8,028 1,619.223s 0.01s 1,619.383s 14 385,246 1,202.339s 0.839s 1,210.557s 15 84,500 1,935.795s 0.01s 1,938.792s 16 1,167,503 758.586s 0.244s 760.886s 17 2,747,887 1,292.533s 0.637s 1,297.311s 18 970,121 1,619.354s 0.009s 1,620.402s 19 36,298 1,624.829s 0.002s 1,624.855s 20 46 4.957s 0.002s 4.966s 21 83 8.512s 0.002s 8.527s 22 851,416 1,826.466s 0.36s 1,836.299s 23 7,319 733.356s 0.002s 733.567s Day Hour Added Removed Recycled Synced files Longest sync Average sync Aug 27 00 0 0 0 64 0.001s 0.002s 01 0 0 0 20 0.001s 0.002s 02 0 0 0 33 0.001s 0.002s 03 0 0 0 33 0.001s 0.002s 04 0 0 0 29 0.001s 0.002s 05 0 0 0 18 0.012s 0.001s 06 0 0 4 138 0.001s 0.003s 07 0 0 0 117 0.001s 0.002s 08 0 0 0 37 0.001s 0.002s 09 0 0 0 22 0.001s 0.002s 10 0 0 0 101 0.001s 0.002s 11 0 0 1 124 0.001s 0.002s 12 0 0 1 773 0.001s 0.002s 13 0 32 19 222 0.001s 0.001s 14 0 130 3,202 383 0.451s 0.02s 15 0 0 1,457 299 0.001s 0.003s 16 0 31 1,077 136 0.032s 0.005s 17 0 0 2,152 288 0.163s 0.026s 18 0 0 454 218 0.001s 0.001s 19 0 0 0 50 0.001s 0.002s 20 0 0 0 12 0.001s 0.002s 21 0 0 0 19 0.001s 0.002s 22 0 33 3,766 309 0.344s 0.02s 23 0 0 29 60 0.001s 0.001s Day Hour Count Avg time (sec) Aug 27 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 Aug 27 00 1,213.50 kB 32,334.50 kB 01 29.00 kB 26,196.00 kB 02 288.00 kB 21,262.00 kB 03 209.00 kB 17,271.00 kB 04 194.00 kB 14,028.00 kB 05 369.00 kB 12,000.00 kB 06 19,893.33 kB 52,665.67 kB 07 840.50 kB 40,580.50 kB 08 457.00 kB 32,966.00 kB 09 83.50 kB 26,737.00 kB 10 522.50 kB 21,735.50 kB 11 13,362.50 kB 24,353.50 kB 12 8,249.00 kB 20,642.00 kB 13 302,897.00 kB 302,897.00 kB 14 7,865,745.86 kB 7,869,167.29 kB 15 7,978,085.00 kB 8,708,517.67 kB 16 5,873,408.00 kB 8,449,139.67 kB 17 8,813,436.75 kB 8,818,891.25 kB 18 7,964,400.00 kB 8,734,016.00 kB 19 416.00 kB 7,467,660.00 kB 20 23.00 kB 6,048,810.00 kB 21 80.50 kB 4,899,548.50 kB 22 8,818,553.14 kB 8,828,159.43 kB 23 1,001,184.00 kB 8,042,129.00 kB -
Temporary Files
Size of temporary files
Key values
- 43.80 GiB Temp Files size Peak
- 2026-08-27 21:44:26 Date
Number of temporary files
Key values
- 44 per second Temp Files Peak
- 2026-08-27 21:44:26 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Aug 27 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 0 0 0 13 335 7.42 GiB 22.68 MiB 14 893 66.77 GiB 76.57 MiB 15 115 7.00 GiB 62.33 MiB 16 0 0 0 17 0 0 0 18 0 0 0 19 31 30.85 GiB 1019.06 MiB 20 65 64.91 GiB 1022.58 MiB 21 318 316.75 GiB 1019.98 MiB 22 260 121.67 GiB 479.18 MiB 23 175 17.84 GiB 104.42 MiB Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 1,413 100.71 GiB 8.00 KiB 1.00 GiB 72.98 MiB select * from pgbulkload.pg_bulkload (?);-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 21:57:48 Duration: 7m47s Database: ctdprd51 User: load Application: pg_bulkload
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-08-27 13:59:48 Duration: 5m44s
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 22:36:05 Duration: 5m26s
2 311 310.20 GiB 414.98 MiB 1.00 GiB 1021.37 MiB select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.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 pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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-08-27 21:44:11 Duration: 0ms
3 65 64.91 GiB 931.80 MiB 1.00 GiB 1022.58 MiB select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.object_type where cd = ?), ptr.term_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.phenotype_term_reference ptr, pub2.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 pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.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-08-27 20:13:59 Duration: 0ms
4 35 1.26 GiB 26.60 MiB 57.16 MiB 36.73 MiB vacuum full analyze ixn_actor;-
vacuum FULL analyze ixn_actor;
Date: 2026-08-27 15:14:52 Duration: 28s924ms
-
vacuum FULL analyze ixn_actor;
Date: 2026-08-27 15:14:30 Duration: 0ms
5 35 5.12 GiB 85.14 MiB 253.05 MiB 149.73 MiB vacuum full analyze db_link;-
vacuum FULL analyze db_link;
Date: 2026-08-27 15:18:13 Duration: 2m32s
-
vacuum FULL analyze db_link;
Date: 2026-08-27 15:16:08 Duration: 0ms
6 31 30.85 GiB 870.77 MiB 1.00 GiB 1019.06 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 pub2.gene_chem_reference gcr, pub2.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 pub2.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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;
Date: 2026-08-27 19:16:46 Duration: 0ms
7 25 414.74 MiB 14.57 MiB 22.02 MiB 16.59 MiB vacuum full analyze ixn;-
vacuum FULL analyze ixn;
Date: 2026-08-27 15:15:28 Duration: 8s570ms
-
vacuum FULL analyze ixn;
Date: 2026-08-27 15:15:22 Duration: 0ms
8 20 14.53 GiB 8.00 KiB 1.00 GiB 743.72 MiB create unique index gene_disease_reference_ak1 on pub2.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 pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-08-27 22:06:11 Duration: 4m16s
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-08-27 22:06:10 Duration: 0ms
9 20 227.24 MiB 4.04 MiB 19.33 MiB 11.36 MiB vacuum full analyze term;-
vacuum FULL analyze TERM;
Date: 2026-08-27 15:14:46 Duration: 12s622ms
-
vacuum FULL analyze TERM;
Date: 2026-08-27 15:14:36 Duration: 0ms
10 15 8.07 GiB 8.00 KiB 1.00 GiB 550.90 MiB alter table pub2.gene_disease_reference add constraint gene_disease_reference_pk primary key (id);-
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:01:55 Duration: 2m41s
-
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:01:55 Duration: 0ms Database: ctdprd51 User: pub2
11 10 480.75 MiB 8.00 KiB 97.95 MiB 48.08 MiB create unique index chem_disease_reference_ak1 on pub2.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 pub2.chem_disease_reference USING btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);
Date: 2026-08-27 22:26:28 Duration: 6s889ms
-
CREATE UNIQUE INDEX chem_disease_reference_ak1 ON pub2.chem_disease_reference USING btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);
Date: 2026-08-27 22:26:27 Duration: 0ms
12 10 8.07 GiB 566.77 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_ref_dis_gene on pub2.gene_disease_reference using btree (disease_id, gene_id);-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-08-27 22:16:50 Duration: 2m24s
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-08-27 22:16:49 Duration: 0ms
13 10 8.07 GiB 586.48 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_reference_ixn on pub2.gene_disease_reference using btree (ixn_id);-
CREATE INDEX ix_gene_disease_reference_ixn ON pub2.gene_disease_reference USING btree (ixn_id);
Date: 2026-08-27 22:18:40 Duration: 1m50s
-
CREATE INDEX ix_gene_disease_reference_ixn ON pub2.gene_disease_reference USING btree (ixn_id);
Date: 2026-08-27 22:18:40 Duration: 0ms
14 10 8.07 GiB 570.02 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_ref_reference on pub2.gene_disease_reference using btree (reference_id);-
CREATE INDEX ix_gene_disease_ref_reference ON pub2.gene_disease_reference USING btree (reference_id);
Date: 2026-08-27 22:14:25 Duration: 1m49s
-
CREATE INDEX ix_gene_disease_ref_reference ON pub2.gene_disease_reference USING btree (reference_id);
Date: 2026-08-27 22:14:24 Duration: 0ms
15 10 8.07 GiB 611.91 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_ref_src_db on pub2.gene_disease_reference using btree (source_acc_db_id);-
CREATE INDEX ix_gene_disease_ref_src_db ON pub2.gene_disease_reference USING btree (source_acc_db_id);
Date: 2026-08-27 22:07:21 Duration: 1m9s
-
CREATE INDEX ix_gene_disease_ref_src_db ON pub2.gene_disease_reference USING btree (source_acc_db_id);
Date: 2026-08-27 22:07:21 Duration: 0ms
16 10 8.07 GiB 566.77 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_ref_disease on pub2.gene_disease_reference using btree (disease_id);-
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2026-08-27 22:12:35 Duration: 1m54s
-
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2026-08-27 22:12:35 Duration: 0ms
17 10 263.84 MiB 8.00 KiB 53.34 MiB 26.38 MiB alter table pub2.chem_disease_reference add constraint chem_disease_reference_pk primary key (id);-
ALTER TABLE pub2.chem_disease_reference ADD CONSTRAINT chem_disease_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:26:21 Duration: 0ms
18 10 8.07 GiB 426.24 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_ref_source_cd on pub2.gene_disease_reference using btree (source_cd);-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub2.gene_disease_reference USING btree (source_cd);
Date: 2026-08-27 22:08:44 Duration: 1m22s
-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub2.gene_disease_reference USING btree (source_cd);
Date: 2026-08-27 22:08:43 Duration: 0ms
19 10 8.07 GiB 566.77 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_ref_chem on pub2.gene_disease_reference using btree (via_chem_id);-
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2026-08-27 22:10:41 Duration: 1m57s
-
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2026-08-27 22:10:41 Duration: 0ms
20 10 1.21 GiB 8.00 KiB 255.37 MiB 123.68 MiB alter table pub2.phenotype_term_reference add constraint phenotype_term_reference_pk primary key (id);-
ALTER TABLE pub2.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:23:57 Duration: 17s881ms
-
ALTER TABLE pub2.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:23:56 Duration: 0ms
21 10 8.07 GiB 566.77 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_ref_net_sc on pub2.gene_disease_reference using btree (network_score);-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-08-27 22:23:38 Duration: 3m6s
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-08-27 22:23:37 Duration: 0ms
22 10 8.07 GiB 566.77 MiB 1.00 GiB 826.35 MiB create index ix_gene_disease_ref_mod_tm on pub2.gene_disease_reference using btree (mod_tm);-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub2.gene_disease_reference USING btree (mod_tm);
Date: 2026-08-27 22:20:31 Duration: 1m51s
-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub2.gene_disease_reference USING btree (mod_tm);
Date: 2026-08-27 22:20:31 Duration: 0ms
23 7 6.55 GiB 563.64 MiB 1.00 GiB 958.23 MiB select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub2.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.object_type where cd = ?), cdr.mod_tm from pub2.chem_disease_reference cdr, pub2.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id;-
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub2.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub2.CHEM_DISEASE_REFERENCE cdr, pub2.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id;
Date: 2026-08-27 21:48:59 Duration: 0ms
24 5 263.81 MiB 46.96 MiB 57.88 MiB 52.76 MiB create index ix_chem_disease_reference_dis on pub2.chem_disease_reference using btree (disease_id);-
CREATE INDEX ix_chem_disease_reference_dis ON pub2.chem_disease_reference USING btree (disease_id);
Date: 2026-08-27 22:26:32 Duration: 0ms
25 5 263.80 MiB 51.60 MiB 55.30 MiB 52.76 MiB create index ix_chem_disease_ref_mod_tm on pub2.chem_disease_reference using btree (mod_tm);-
CREATE INDEX ix_chem_disease_ref_mod_tm ON pub2.chem_disease_reference USING btree (mod_tm);
Date: 2026-08-27 22:26:50 Duration: 0ms
26 5 1.21 GiB 232.25 MiB 257.41 MiB 247.36 MiB create index ix_phenotype_term_ref_taxon_id on pub2.phenotype_term_reference using btree (taxon_id);-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub2.phenotype_term_reference USING btree (taxon_id);
Date: 2026-08-27 22:24:57 Duration: 9s715ms
-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub2.phenotype_term_reference USING btree (taxon_id);
Date: 2026-08-27 22:24:57 Duration: 0ms
27 5 1.21 GiB 234.13 MiB 259.05 MiB 247.35 MiB create index ix_phenotype_term_ref_evidence_cd on pub2.phenotype_term_reference using btree (evidence_cd);-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub2.phenotype_term_reference USING btree (evidence_cd);
Date: 2026-08-27 22:25:07 Duration: 9s956ms
-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub2.phenotype_term_reference USING btree (evidence_cd);
Date: 2026-08-27 22:25:07 Duration: 0ms
28 5 1.21 GiB 215.06 MiB 263.61 MiB 247.35 MiB create index ix_phenotype_term_ref_object_type_id on pub2.phenotype_term_reference using btree (term_object_type_id);-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub2.phenotype_term_reference USING btree (term_object_type_id);
Date: 2026-08-27 22:24:32 Duration: 10s563ms
-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub2.phenotype_term_reference USING btree (term_object_type_id);
Date: 2026-08-27 22:24:32 Duration: 0ms
29 5 263.80 MiB 51.62 MiB 55.53 MiB 52.76 MiB create index ix_chem_disease_ref_net_sc on pub2.chem_disease_reference using btree (network_score);-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub2.chem_disease_reference USING btree (network_score);
Date: 2026-08-27 22:26:56 Duration: 5s637ms
-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub2.chem_disease_reference USING btree (network_score);
Date: 2026-08-27 22:26:56 Duration: 0ms
30 5 263.80 MiB 51.53 MiB 55.52 MiB 52.76 MiB create index ix_chem_disease_reference_gene on pub2.chem_disease_reference using btree (via_gene_id);-
CREATE INDEX ix_chem_disease_reference_gene ON pub2.chem_disease_reference USING btree (via_gene_id);
Date: 2026-08-27 22:26:43 Duration: 0ms
31 5 263.80 MiB 51.25 MiB 53.39 MiB 52.76 MiB create index ix_chem_disease_reference_ref on pub2.chem_disease_reference using btree (reference_id);-
CREATE INDEX ix_chem_disease_reference_ref ON pub2.chem_disease_reference USING btree (reference_id);
Date: 2026-08-27 22:26:35 Duration: 0ms
32 5 263.81 MiB 48.66 MiB 58.10 MiB 52.76 MiB create index ix_chem_disease_ref_src_db on pub2.chem_disease_reference using btree (source_acc_db_id);-
CREATE INDEX ix_chem_disease_ref_src_db ON pub2.chem_disease_reference USING btree (source_acc_db_id);
Date: 2026-08-27 22:26:40 Duration: 0ms
33 5 263.80 MiB 51.83 MiB 54.57 MiB 52.76 MiB create index ix_chem_disease_reference_ixn on pub2.chem_disease_reference using btree (ixn_id);-
CREATE INDEX ix_chem_disease_reference_ixn ON pub2.chem_disease_reference USING btree (ixn_id);
Date: 2026-08-27 22:26:47 Duration: 0ms
34 5 1.21 GiB 216.05 MiB 272.77 MiB 247.36 MiB create index ix_phenotype_term_reference_ixn_id on pub2.phenotype_term_reference using btree (ixn_id);-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub2.phenotype_term_reference USING btree (ixn_id);
Date: 2026-08-27 22:25:46 Duration: 14s240ms
-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub2.phenotype_term_reference USING btree (ixn_id);
Date: 2026-08-27 22:25:46 Duration: 0ms
35 5 1.21 GiB 228.19 MiB 270.41 MiB 247.36 MiB create index ix_phenotype_term_reference_term_reference_id on pub2.phenotype_term_reference using btree (term_reference_id);-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub2.phenotype_term_reference USING btree (term_reference_id);
Date: 2026-08-27 22:25:32 Duration: 14s151ms
-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub2.phenotype_term_reference USING btree (term_reference_id);
Date: 2026-08-27 22:25:32 Duration: 0ms
36 5 1.69 GiB 341.84 MiB 353.99 MiB 347.13 MiB create index ix_phenotype_term_ref_ids on pub2.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 pub2.phenotype_term_reference USING btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);
Date: 2026-08-27 22:26:17 Duration: 16s446ms
-
CREATE INDEX ix_phenotype_term_ref_ids ON pub2.phenotype_term_reference USING btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);
Date: 2026-08-27 22:26:17 Duration: 0ms
37 5 1.21 GiB 231.12 MiB 256.70 MiB 247.36 MiB create index ix_phenotype_term_ref_phenotype_id on pub2.phenotype_term_reference using btree (phenotype_id);-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub2.phenotype_term_reference USING btree (phenotype_id);
Date: 2026-08-27 22:24:09 Duration: 12s217ms
-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub2.phenotype_term_reference USING btree (phenotype_id);
Date: 2026-08-27 22:24:09 Duration: 0ms
38 5 1.21 GiB 217.26 MiB 259.41 MiB 247.35 MiB create index ix_phenotype_term_ref_term_id on pub2.phenotype_term_reference using btree (term_id);-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub2.phenotype_term_reference USING btree (term_id);
Date: 2026-08-27 22:24:22 Duration: 12s961ms
-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub2.phenotype_term_reference USING btree (term_id);
Date: 2026-08-27 22:24:22 Duration: 0ms
39 5 1.21 GiB 235.05 MiB 257.26 MiB 247.36 MiB create index ix_phenotype_term_ref_via_term_id on pub2.phenotype_term_reference using btree (via_term_id);-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub2.phenotype_term_reference USING btree (via_term_id);
Date: 2026-08-27 22:26:01 Duration: 14s543ms
-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub2.phenotype_term_reference USING btree (via_term_id);
Date: 2026-08-27 22:26:00 Duration: 0ms
40 5 263.80 MiB 51.74 MiB 54.89 MiB 52.76 MiB create index ix_chem_disease_ref_source_cd on pub2.chem_disease_reference using btree (source_cd);-
CREATE INDEX ix_chem_disease_ref_source_cd ON pub2.chem_disease_reference USING btree (source_cd);
Date: 2026-08-27 22:26:37 Duration: 0ms
41 5 1.21 GiB 222.52 MiB 253.88 MiB 247.35 MiB create index ix_phenotype_term_reference_source_acc_db_id on pub2.phenotype_term_reference using btree (source_acc_db_id);-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub2.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2026-08-27 22:25:18 Duration: 10s334ms
-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub2.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2026-08-27 22:25:18 Duration: 0ms
42 5 1.21 GiB 211.05 MiB 273.93 MiB 247.35 MiB create index ix_phenotype_term_ref_reference_id on pub2.phenotype_term_reference using btree (reference_id);-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub2.phenotype_term_reference USING btree (reference_id);
Date: 2026-08-27 22:24:48 Duration: 15s319ms
-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub2.phenotype_term_reference USING btree (reference_id);
Date: 2026-08-27 22:24:48 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB SELECT * FROM pgbulkload.pg_bulkload ($1);[ Date: 2026-08-27 14:30:49 - 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:46 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:46 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:46 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:46 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:46 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:46 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn;[ Date: 2026-08-27 19:16:47 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 86.37 sec Highest CPU-cost vacuum
Table pub2.gene_go_annot
Database ctdprd51 - 2026-08-27 17:34:34 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 86.37 sec Highest CPU-cost vacuum
Table pub2.gene_go_annot
Database ctdprd51 - 2026-08-27 17:34:34 Date
Analyzes per table
Key values
- pubc.log_query (12) Main table analyzed (database ctdprd51)
- 66 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 12 ctdprd51.pg_catalog.pg_class 3 postgres.pg_catalog.pg_shdepend 2 ctdprd51.pub2.db 2 ctdprd51.pg_catalog.pg_attribute 2 ctdprd51.pub2.term 2 ctdprd51.pg_catalog.pg_index 2 ctdprd51.pg_catalog.pg_description 1 ctdprd51.pub2.chem_conc 1 ctdprd51.pg_catalog.pg_trigger 1 ctdprd51.load.data_load 1 ctdprd51.edit.list_db_report 1 ctdprd51.pub2.term_pathway 1 ctdprd51.pub2.db_report 1 ctdprd51.edit.chem_conc_uom 1 ctdprd51.edit.reference_db_link 1 ctdprd51.pg_catalog.pg_shdepend 1 ctdprd51.edit.country 1 ctdprd51.pg_catalog.pg_depend 1 ctdprd51.pub2.db_link 1 ctdprd51.edit.object_note 1 ctdprd51.edit.action_type_path 1 ctdprd51.pub2.gene_taxon 1 ctdprd51.pub2.action_type 1 ctdprd51.pg_catalog.pg_attrdef 1 ctdprd51.edit.db_report_site 1 ctdprd51.pub2.chem_conc_anatomy 1 ctdprd51.pub1.term_set_enrichment_agent 1 ctdprd51.edit.action_degree 1 ctdprd51.pg_catalog.pg_constraint 1 ctdprd51.pub2.reference_party 1 ctdprd51.edit.db_report 1 ctdprd51.edit.db_link 1 ctdprd51.pg_catalog.pg_type 1 ctdprd51.pub1.term_set_enrichment 1 ctdprd51.pub2.dag_edge 1 ctdprd51.edit.db 1 ctdprd51.pub2.gene_go_annot 1 ctdprd51.pub2.list_db_report 1 ctdprd51.edit.tobacco_use 1 ctdprd51.pub2.reference_party_role 1 ctdprd51.edit.action_type 1 ctdprd51.pub2.img 1 ctdprd51.pub2.dag_node 1 ctdprd51.pub2.reference 1 ctdprd51.pub2.term_label 1 ctdprd51.pub2.db_report_site 1 ctdprd51.edit.age_qualifier 1 Total 66 Vacuums per table
Key values
- pub2.term (2) Main table vacuumed on database ctdprd51
- 43 vacuums Total
Index Buffer usage Skipped WAL usage Frozen Table Vacuums scans hits misses dirtied pins frozen records full page bytes pages tuples ctdprd51.pub2.term 2 0 136,327 0 6 0 0 48,217 5 2,873,974 0 0 ctdprd51.pg_catalog.pg_class 2 2 815 0 79 0 0 384 68 269,606 0 0 ctdprd51.pub2.dag_edge 1 0 1,053 0 5 0 0 482 2 39,421 0 0 ctdprd51.pub2.db 1 1 152 0 4 0 0 20 2 15,267 0 0 ctdprd51.pg_catalog.pg_attribute 1 1 840 0 80 0 37 415 79 371,989 0 0 ctdprd51.pg_catalog.pg_type 1 1 151 0 10 0 0 61 7 29,303 0 0 ctdprd51.pub1.term_set_enrichment 1 0 3,684 0 1,518 0 0 1,515 2 103,640 0 0 ctdprd51.pg_toast.pg_toast_12200718 1 0 91,358 0 4 0 0 45,671 2 2,711,112 0 0 ctdprd51.edit.race 1 0 57 0 2 0 0 3 2 14,657 0 0 ctdprd51.pg_catalog.pg_statistic 1 1 855 0 73 0 120 600 68 185,356 0 0 ctdprd51.edit.action_type 1 0 174 0 2 0 0 7 2 14,449 0 0 ctdprd51.pub2.reference_party_role 1 0 13,860 0 4 0 0 6,903 1 415,696 0 0 ctdprd51.pubc.log_query 1 1 209 0 21 0 0 52 12 88,074 0 0 ctdprd51.pub2.gene_go_annot 1 0 728,115 0 363,946 0 0 363,931 13 21,573,431 0 0 ctdprd51.edit.age_qualifier 1 0 44 0 4 0 0 2 1 8,610 0 0 ctdprd51.pub2.img 1 0 1,108 0 4 0 0 524 1 39,335 0 0 ctdprd51.pub2.reference 1 0 79,207 0 5 0 0 39,493 3 2,348,771 0 0 ctdprd51.pub2.term_label 1 0 240,760 0 6 0 0 120,325 4 7,132,185 0 0 ctdprd51.pub2.dag_node 1 0 87,556 0 5 0 0 43,651 3 2,594,661 0 0 ctdprd51.pg_catalog.pg_index 1 1 195 0 25 0 0 103 21 98,990 0 0 ctdprd51.pg_catalog.pg_trigger 1 1 356 0 30 0 0 139 34 168,085 0 0 ctdprd51.pub2.term_pathway 1 0 3,331 0 4 0 0 1,614 2 107,381 0 0 ctdprd51.edit.list_db_report 1 0 53 0 1 0 0 7 1 9,345 0 0 ctdprd51.pub2.chem_conc 1 0 768 0 3 0 0 369 1 30,190 0 0 ctdprd51.pg_catalog.pg_description 1 1 226 0 33 0 42 119 24 91,863 0 0 ctdprd51.pg_toast.pg_toast_486223 1 0 48 0 0 0 0 1 0 188 0 0 ctdprd51.edit.reference_db_link 1 0 7,519 0 4 0 0 3,747 1 229,399 0 0 ctdprd51.pg_catalog.pg_depend 1 1 671 0 91 0 65 329 98 372,346 0 0 ctdprd51.edit.country 1 0 63 0 0 0 0 8 5 24,221 0 0 ctdprd51.pg_catalog.pg_attrdef 1 1 88 0 3 0 0 23 1 10,928 0 0 ctdprd51.pub2.chem_conc_anatomy 1 0 525 0 4 0 0 233 2 25,078 0 0 postgres.pg_catalog.pg_shdepend 1 1 174 0 56 0 0 98 41 157,219 0 0 ctdprd51.pub2.db_link 1 0 340,251 0 133,625 0 0 169,998 6 10,079,286 0 0 ctdprd51.edit.object_note 1 1 185 0 1 0 0 25 2 17,977 0 0 ctdprd51.pub2.gene_taxon 1 0 194,187 0 12,649 0 0 97,033 3 5,749,760 0 0 ctdprd51.edit.action_type_path 1 0 48 0 0 0 0 4 1 9,059 0 0 ctdprd51.edit.db_link 1 0 7,731 0 3 0 0 3,747 1 229,468 0 0 ctdprd51.edit.action_degree_type 1 0 82 0 2 0 0 3 2 14,097 0 0 ctdprd51.pub2.reference_party 1 0 5,184 0 3 0 0 2,558 1 159,341 0 0 ctdprd51.pg_catalog.pg_constraint 1 1 297 0 15 0 0 112 17 70,374 0 0 ctdprd51.edit.action_degree 1 0 45 0 0 0 0 12 1 9,451 0 0 Total 43 15 1,948,352 579 512,330 0 264 952,538 542 58,493,583 0 0 Vacuum throughput per table
Key values
- pub2.gene_go_annot (86.37) Max CPU elapsed for vacuum on database ctdprd51
- unknown (0 ms) Max I/O read time for vacuum on database ctdprd51
- unknown (0 ms) Max I/O write time for vacuum on database ctdprd51
I/O timing (ms) CPU (s) Table read write elapsed ctdprd51.pub2.term 0 0 1.74 ctdprd51.pg_catalog.pg_class 0 0 0.02 ctdprd51.pub2.dag_edge 0 0 0.01 ctdprd51.pub2.db 0 0 0 ctdprd51.pg_catalog.pg_attribute 0 0 0.03 ctdprd51.pg_catalog.pg_type 0 0 0 ctdprd51.pub1.term_set_enrichment 0 0 0.35 ctdprd51.pg_toast.pg_toast_12200718 0 0 1.03 ctdprd51.edit.race 0 0 0 ctdprd51.pg_catalog.pg_statistic 0 0 0.02 ctdprd51.edit.action_type 0 0 0 ctdprd51.pub2.reference_party_role 0 0 0.19 ctdprd51.pubc.log_query 0 0 0 ctdprd51.pub2.gene_go_annot 0 0 86.37 ctdprd51.edit.age_qualifier 0 0 0 ctdprd51.pub2.img 0 0 0.01 ctdprd51.pub2.reference 0 0 0.87 ctdprd51.pub2.term_label 0 0 3.1 ctdprd51.pub2.dag_node 0 0 1.07 ctdprd51.pg_catalog.pg_index 0 0 0 ctdprd51.pg_catalog.pg_trigger 0 0 0.01 ctdprd51.pub2.term_pathway 0 0 0.04 ctdprd51.edit.list_db_report 0 0 0 ctdprd51.pub2.chem_conc 0 0 0 ctdprd51.pg_catalog.pg_description 0 0 0.01 ctdprd51.pg_toast.pg_toast_486223 0 0 0 ctdprd51.edit.reference_db_link 0 0 0.09 ctdprd51.pg_catalog.pg_depend 0 0 0.04 ctdprd51.edit.country 0 0 0 ctdprd51.pg_catalog.pg_attrdef 0 0 0 ctdprd51.pub2.chem_conc_anatomy 0 0 0 postgres.pg_catalog.pg_shdepend 0 0 0.02 ctdprd51.pub2.db_link 0 0 32.27 ctdprd51.edit.object_note 0 0 0 ctdprd51.pub2.gene_taxon 0 0 5.37 ctdprd51.edit.action_type_path 0 0 0 ctdprd51.edit.db_link 0 0 0.12 ctdprd51.edit.action_degree_type 0 0 0 ctdprd51.pub2.reference_party 0 0 0.07 ctdprd51.pg_catalog.pg_constraint 0 0 0 ctdprd51.edit.action_degree 0 0 0 Total 0 0 132.85 Tuples removed per table
Key values
- pg_catalog.pg_attribute (1963) Main table with removed tuples on database ctdprd51
- 9326 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pg_catalog.pg_attribute 1 1 1,963 8,969 0 0 236 ctdprd51.pg_catalog.pg_depend 1 1 1,767 13,754 0 0 153 ctdprd51.pg_catalog.pg_description 1 1 1,118 5,770 0 0 90 ctdprd51.pg_catalog.pg_statistic 1 1 952 2,811 0 0 410 ctdprd51.pg_catalog.pg_class 2 2 710 3,631 0 0 188 postgres.pg_catalog.pg_shdepend 1 1 602 1,625 0 0 22 ctdprd51.pg_catalog.pg_trigger 1 1 496 1,889 0 0 52 ctdprd51.pg_catalog.pg_constraint 1 1 232 911 0 0 40 ctdprd51.pg_catalog.pg_index 1 1 199 1,188 0 0 39 ctdprd51.edit.object_note 1 1 169 169 0 0 9 ctdprd51.edit.country 1 0 163 249 0 0 4 ctdprd51.edit.age_qualifier 1 0 140 5 0 0 1 ctdprd51.pub2.db 1 1 134 134 0 0 7 ctdprd51.pg_catalog.pg_type 1 1 113 1,171 0 0 35 ctdprd51.edit.action_type_path 1 0 106 106 0 0 2 ctdprd51.edit.action_degree 1 0 96 219 0 0 6 ctdprd51.edit.list_db_report 1 0 92 183 0 0 3 ctdprd51.edit.race 1 0 81 27 0 0 1 ctdprd51.edit.action_degree_type 1 0 65 13 0 0 1 ctdprd51.edit.action_type 1 0 64 60 0 0 3 ctdprd51.pg_catalog.pg_attrdef 1 1 62 246 0 0 12 ctdprd51.pubc.log_query 1 1 2 543 0 0 24 ctdprd51.pub2.dag_edge 1 0 0 88,931 0 0 481 ctdprd51.pub1.term_set_enrichment 1 0 0 535,936 0 0 8,892 ctdprd51.pg_toast.pg_toast_12200718 1 0 0 246,900 0 0 45,670 ctdprd51.pub2.reference_party_role 1 0 0 1,276,861 0 0 6,902 ctdprd51.pub2.term 2 0 0 2,267,124 0 0 72,374 ctdprd51.pub2.gene_go_annot 1 0 0 57,138,273 0 0 363,930 ctdprd51.pub2.img 1 0 0 50,667 0 0 523 ctdprd51.pub2.reference 1 0 0 203,438 0 0 39,492 ctdprd51.pub2.term_label 1 0 0 8,428,945 0 0 120,324 ctdprd51.pub2.dag_node 1 0 0 1,818,807 0 0 43,650 ctdprd51.pub2.term_pathway 1 0 0 135,792 0 0 1,613 ctdprd51.pub2.chem_conc 1 0 0 11,314 0 0 368 ctdprd51.pg_toast.pg_toast_486223 1 0 0 0 0 0 0 ctdprd51.edit.reference_db_link 1 0 0 336,072 0 0 3,746 ctdprd51.pub2.chem_conc_anatomy 1 0 0 24,747 0 0 232 ctdprd51.pub2.db_link 1 0 0 23,432,516 0 0 169,997 ctdprd51.pub2.gene_taxon 1 0 0 15,233,976 0 0 97,032 ctdprd51.edit.db_link 1 0 0 336,072 0 0 3,746 ctdprd51.pub2.reference_party 1 0 0 457,686 0 0 2,557 Total 43 15 9,326 112,067,730 0 0 982,867 Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Pages removed per tables
NO DATASET
Table Number of vacuums Index scans Tuples removed Pages removed ctdprd51.pub2.dag_edge 1 0 0 0 ctdprd51.pub2.db 1 1 134 0 ctdprd51.pg_catalog.pg_attribute 1 1 1963 0 ctdprd51.pg_catalog.pg_type 1 1 113 0 ctdprd51.pub1.term_set_enrichment 1 0 0 0 ctdprd51.pg_toast.pg_toast_12200718 1 0 0 0 ctdprd51.edit.race 1 0 81 0 ctdprd51.pg_catalog.pg_statistic 1 1 952 0 ctdprd51.edit.action_type 1 0 64 0 ctdprd51.pub2.reference_party_role 1 0 0 0 ctdprd51.pub2.term 2 0 0 0 ctdprd51.pubc.log_query 1 1 2 0 ctdprd51.pub2.gene_go_annot 1 0 0 0 ctdprd51.edit.age_qualifier 1 0 140 0 ctdprd51.pub2.img 1 0 0 0 ctdprd51.pub2.reference 1 0 0 0 ctdprd51.pub2.term_label 1 0 0 0 ctdprd51.pub2.dag_node 1 0 0 0 ctdprd51.pg_catalog.pg_index 1 1 199 0 ctdprd51.pg_catalog.pg_trigger 1 1 496 0 ctdprd51.pub2.term_pathway 1 0 0 0 ctdprd51.edit.list_db_report 1 0 92 0 ctdprd51.pub2.chem_conc 1 0 0 0 ctdprd51.pg_catalog.pg_description 1 1 1118 0 ctdprd51.pg_toast.pg_toast_486223 1 0 0 0 ctdprd51.edit.reference_db_link 1 0 0 0 ctdprd51.pg_catalog.pg_depend 1 1 1767 0 ctdprd51.edit.country 1 0 163 0 ctdprd51.pg_catalog.pg_attrdef 1 1 62 0 ctdprd51.pub2.chem_conc_anatomy 1 0 0 0 postgres.pg_catalog.pg_shdepend 1 1 602 0 ctdprd51.pub2.db_link 1 0 0 0 ctdprd51.edit.object_note 1 1 169 0 ctdprd51.pub2.gene_taxon 1 0 0 0 ctdprd51.edit.action_type_path 1 0 106 0 ctdprd51.edit.db_link 1 0 0 0 ctdprd51.edit.action_degree_type 1 0 65 0 ctdprd51.pg_catalog.pg_class 2 2 710 0 ctdprd51.pub2.reference_party 1 0 0 0 ctdprd51.pg_catalog.pg_constraint 1 1 232 0 ctdprd51.edit.action_degree 1 0 96 0 Total 43 15 9,326 0 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Aug 27 00 1 0 01 0 1 02 0 1 03 0 1 04 0 1 05 1 4 06 0 0 07 0 1 08 0 1 09 0 0 10 0 0 11 0 0 12 11 12 13 10 16 14 0 0 15 1 3 16 4 9 17 13 12 18 1 0 19 0 0 20 0 0 21 1 2 22 0 2 23 0 0 - 86.37 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- AccessExclusiveLock Main Lock Type
- 1 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query 1 1 23s941ms 23s941ms 23s941ms 23s941ms select * from pgbulkload.pg_bulkload (?);-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 21:57:48 Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-08-27 13:59:48 Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 22:36:05 Bind query: yes
Queries that waited the most
Rank Wait time Query 1 23s941ms SELECT * FROM pgbulkload.pg_bulkload ($1);[ Date: 2026-08-27 14:01:19 ]
-
Queries
Queries by type
Key values
- 152 Total read queries
- 73 Total write queries
Queries by database
Key values
- unknown Main database
- 152 Requests
- 4h48m19s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 184 Requests
User Request type Count Duration edit Total 1 9s108ms insert 1 9s108ms load Total 19 1h2m13s select 19 1h2m13s postgres Total 16 17m52s copy to 16 17m52s pub2 Total 2 17m30s insert 1 17m21s select 1 8s637ms pubc Total 1 9m22s select 1 9m22s pubeu Total 62 21m27s select 62 21m27s qaeu Total 3 17s721ms cte 1 5s508ms select 2 12s213ms unknown Total 184 4h55m50s copy to 56 12m2s ddl 25 27m14s insert 9 46m59s others 5 3m29s select 89 3h26m4s Duration by user
Key values
- 4h55m50s (unknown) Main time consuming user
User Request type Count Duration edit Total 1 9s108ms insert 1 9s108ms load Total 19 1h2m13s select 19 1h2m13s postgres Total 16 17m52s copy to 16 17m52s pub2 Total 2 17m30s insert 1 17m21s select 1 8s637ms pubc Total 1 9m22s select 1 9m22s pubeu Total 62 21m27s select 62 21m27s qaeu Total 3 17s721ms cte 1 5s508ms select 2 12s213ms unknown Total 184 4h55m50s copy to 56 12m2s ddl 25 27m14s insert 9 46m59s others 5 3m29s select 89 3h26m4s Queries by host
Key values
- unknown Main host
- 288 Requests
- 7h4m42s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 208 Requests
- 6h13m58s (unknown)
- Main time consuming application
Application Request type Count Duration pgAdmin 4 - CONN:7727537 Total 1 9s108ms insert 1 9s108ms pg_bulkload Total 12 11m51s select 12 11m51s pg_dump Total 8 8m51s copy to 8 8m51s psql Total 1 9m22s select 1 9m22s unknown Total 208 6h13m58s copy to 28 6m1s cte 1 5s508ms ddl 25 27m14s insert 10 1h4m20s others 5 3m29s select 139 4h32m46s Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-08-27 14:05:46 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 161 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 1h13m19s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2026-08-27 21:27:22 - Bind query: yes ]
2 53m14s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2026-08-27 20:13:56 - Bind query: yes ]
3 35m55s 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-08-27 18:18:20 - Database: ctdprd51 - User: load - Bind query: yes ]
4 31m26s insert into pub2.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-08-27 17:32:45 - Bind query: yes ]
5 17m21s insert into pub2.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-08-27 16:54:45 - Database: ctdprd51 - User: pub2 - Bind query: yes ]
6 13m15s 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');[ Date: 2026-08-27 18:34:32 - Database: ctdprd51 - User: load - Bind query: yes ]
7 9m22s /* * 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-08-27 00:09:23 - Database: ctdprd51 - User: pubc - Application: psql ]
8 7m47s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 21:57:48 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
9 6m58s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2026-08-27 21:40:47 - Bind query: yes ]
10 5m44s 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-08-27 13:59:48 - Bind query: yes ]
11 5m26s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 22:36:05 - Bind query: yes ]
12 5m24s insert into pub2.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-08-27 17:38:09 - Bind query: yes ]
13 4m18s INSERT INTO pub2.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 pub2.TERM);[ Date: 2026-08-27 17:01:17 - Bind query: yes ]
14 4m16s CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);[ Date: 2026-08-27 22:06:11 - Bind query: yes ]
15 3m18s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/misc/uniprot/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/misc/uniprot/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/misc/uniprot/output/dbLink.txt.DUPE}');[ Date: 2026-08-27 14:41:39 - Bind query: yes ]
16 3m6s CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);[ Date: 2026-08-27 22:23:38 - Bind query: yes ]
17 2m48s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2026-08-27 21:43:36 - Bind query: yes ]
18 2m41s ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);[ Date: 2026-08-27 22:01:55 - Bind query: yes ]
19 2m32s vacuum FULL analyze db_link;[ Date: 2026-08-27 15:18:13 ]
20 2m24s CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);[ Date: 2026-08-27 22:16:50 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 1h30m7s 13 9s484ms 1h13m19s 6m55s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 27 21 13 1h30m7s 6m55s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:27:22 Duration: 1h13m19s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:40:47 Duration: 6m58s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:43:36 Duration: 2m48s Bind query: yes
2 1h 59 5s239ms 7m47s 1m1s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Aug 27 13 13 8m12s 37s870ms 14 28 29m38s 1m3s 18 3 1m11s 23s942ms 21 3 9m12s 3m4s 22 4 7m22s 1m50s 23 8 4m21s 32s674ms [ User: load - Total duration: 11m51s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m51s - Times executed: 12 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 21:57:48 Duration: 7m47s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-08-27 13:59:48 Duration: 5m44s Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 22:36:05 Duration: 5m26s Bind query: yes
3 53m14s 1 53m14s 53m14s 53m14s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.object_type where cd = ?), ptr.term_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.phenotype_term_reference ptr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 27 20 1 53m14s 53m14s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 20:13:56 Duration: 53m14s Bind query: yes
4 35m55s 1 35m55s 35m55s 35m55s select i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) from edit.ixn i, edit.reference_ixn r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in (...) order by i.id asc;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 27 18 1 35m55s 35m55s [ User: load - Total duration: 35m55s - 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-08-27 18:18:20 Duration: 35m55s Database: ctdprd51 User: load Bind query: yes
5 31m26s 1 31m26s 31m26s 31m26s insert into pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 27 17 1 31m26s 31m26s -
insert into pub2.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-08-27 17:32:45 Duration: 31m26s Bind query: yes
6 17m35s 10 5s3ms 13m15s 1m45s 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 pub2.gene_chem_reference gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 27 18 4 14m26s 3m36s 19 6 3m9s 31s523ms [ User: load - Total duration: 13m15s - 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 18:34:32 Duration: 13m15s 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 19:06:07 Duration: 1m1s 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 18:53:31 Duration: 1m Bind query: yes
7 17m21s 1 17m21s 17m21s 17m21s insert into pub2.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 Aug 27 16 1 17m21s 17m21s [ User: pub2 - Total duration: 17m21s - Times executed: 1 ]
-
insert into pub2.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-08-27 16:54:45 Duration: 17m21s Database: ctdprd51 User: pub2 Bind query: yes
8 9m53s 10 34s861ms 1m58s 59s306ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by gd.network_score nulls last, g.nm_sort, d.nm_sort limit ? offset ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Aug 27 05 10 9m53s 59s306ms [ User: pubeu - Total duration: 9m7s - Times executed: 9 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 100;
Date: 2026-08-27 05:22:50 Duration: 1m58s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 100;
Date: 2026-08-27 05:23:01 Duration: 1m44s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 50;
Date: 2026-08-27 05:22:19 Duration: 1m1s Database: ctdprd51 User: pubeu Bind query: yes
9 9m22s 1 9m22s 9m22s 9m22s select maint_query_logs_archive ();Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 27 00 1 9m22s 9m22s [ User: pubc - Total duration: 9m22s - Times executed: 1 ]
[ Application: psql - Total duration: 9m22s - 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-08-27 00:09:23 Duration: 9m22s Database: ctdprd51 User: pubc Application: psql
10 7m34s 4 1m53s 1m54s 1m53s copy pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Aug 27 06 1 1m53s 1m53s 10 1 1m54s 1m54s 14 1 1m53s 1m53s 18 1 1m53s 1m53s [ User: postgres - Total duration: 7m34s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m34s - 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-08-27 10:06:55 Duration: 1m54s 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-08-27 14:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-27 06:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
11 5m28s 13 5s2ms 40s128ms 25s258ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 27 05 5 1m1s 12s314ms 08 5 3m2s 36s452ms 09 2 1m16s 38s183ms 22 1 8s153ms 8s153ms [ User: pubeu - Total duration: 4m16s - Times executed: 11 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 09:28:38 Duration: 40s128ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 08:36:32 Duration: 37s216ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 08:52:13 Duration: 36s962ms Database: ctdprd51 User: pubeu Bind query: yes
12 5m24s 1 5m24s 5m24s 5m24s insert into pub2.gene_taxon (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.gene_taxon;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 27 17 1 5m24s 5m24s -
insert into pub2.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-08-27 17:38:09 Duration: 5m24s Bind query: yes
13 4m18s 1 4m18s 4m18s 4m18s insert into pub2.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 pub2.term);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Aug 27 17 1 4m18s 4m18s -
INSERT INTO pub2.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 pub2.TERM);
Date: 2026-08-27 17:01:17 Duration: 4m18s Bind query: yes
14 4m16s 1 4m16s 4m16s 4m16s create unique index gene_disease_reference_ak1 on pub2.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 #14
Day Hour Count Duration Avg duration Aug 27 22 1 4m16s 4m16s -
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-08-27 22:06:11 Duration: 4m16s Bind query: yes
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-08-27 22:06:10 Duration: 0ms
15 3m6s 1 3m6s 3m6s 3m6s create index ix_gene_disease_ref_net_sc on pub2.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Aug 27 22 1 3m6s 3m6s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-08-27 22:23:38 Duration: 3m6s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-08-27 22:23:37 Duration: 0ms
16 2m41s 1 2m41s 2m41s 2m41s alter table pub2.gene_disease_reference add constraint gene_disease_reference_pk primary key (id);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Aug 27 22 1 2m41s 2m41s -
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:01:55 Duration: 2m41s Bind query: yes
-
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:01:55 Duration: 0ms Database: ctdprd51 User: pub2
17 2m32s 1 2m32s 2m32s 2m32s vacuum full analyze db_link;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Aug 27 15 1 2m32s 2m32s -
vacuum FULL analyze db_link;
Date: 2026-08-27 15:18:13 Duration: 2m32s
-
vacuum FULL analyze db_link;
Date: 2026-08-27 15:16:08 Duration: 0ms
18 2m24s 1 2m24s 2m24s 2m24s create index ix_gene_disease_ref_dis_gene on pub2.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 27 22 1 2m24s 2m24s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-08-27 22:16:50 Duration: 2m24s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-08-27 22:16:49 Duration: 0ms
19 2m24s 1 2m24s 2m24s 2m24s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub2.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.object_type where cd = ?), cdr.mod_tm from pub2.chem_disease_reference cdr, pub2.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 pub2.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 27 21 1 2m24s 2m24s -
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub2.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub2.CHEM_DISEASE_REFERENCE cdr, pub2.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 pub2.IXN_AXN WHERE action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:46:51 Duration: 2m24s Bind query: yes
20 2m1s 1 2m1s 2m1s 2m1s insert into pub2.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 Aug 27 16 1 2m1s 2m1s -
INSERT INTO pub2.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-08-27 16:56:59 Duration: 2m1s Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 59 1h 5s239ms 7m47s 1m1s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 27 13 13 8m12s 37s870ms 14 28 29m38s 1m3s 18 3 1m11s 23s942ms 21 3 9m12s 3m4s 22 4 7m22s 1m50s 23 8 4m21s 32s674ms [ User: load - Total duration: 11m51s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m51s - Times executed: 12 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 21:57:48 Duration: 7m47s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-08-27 13:59:48 Duration: 5m44s Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 22:36:05 Duration: 5m26s Bind query: yes
2 13 1h30m7s 9s484ms 1h13m19s 6m55s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Aug 27 21 13 1h30m7s 6m55s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:27:22 Duration: 1h13m19s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:40:47 Duration: 6m58s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:43:36 Duration: 2m48s Bind query: yes
3 13 5m28s 5s2ms 40s128ms 25s258ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 27 05 5 1m1s 12s314ms 08 5 3m2s 36s452ms 09 2 1m16s 38s183ms 22 1 8s153ms 8s153ms [ User: pubeu - Total duration: 4m16s - Times executed: 11 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 09:28:38 Duration: 40s128ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 08:36:32 Duration: 37s216ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 08:52:13 Duration: 36s962ms Database: ctdprd51 User: pubeu Bind query: yes
4 10 17m35s 5s3ms 13m15s 1m45s 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 pub2.gene_chem_reference gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 27 18 4 14m26s 3m36s 19 6 3m9s 31s523ms [ User: load - Total duration: 13m15s - 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 18:34:32 Duration: 13m15s 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 19:06:07 Duration: 1m1s 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 18:53:31 Duration: 1m Bind query: yes
5 10 9m53s 34s861ms 1m58s 59s306ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by gd.network_score nulls last, g.nm_sort, d.nm_sort limit ? offset ?;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 27 05 10 9m53s 59s306ms [ User: pubeu - Total duration: 9m7s - Times executed: 9 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 100;
Date: 2026-08-27 05:22:50 Duration: 1m58s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 100;
Date: 2026-08-27 05:23:01 Duration: 1m44s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 50;
Date: 2026-08-27 05:22:19 Duration: 1m1s Database: ctdprd51 User: pubeu Bind query: yes
6 6 37s513ms 5s936ms 6s807ms 6s252ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.id ixnid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct r.acc_txt, ?)) as references, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id inner join reference r on gcr.reference_id = r.id left outer join term taxonterm on gcr.taxon_id = taxonterm.id where exists ( select ? from gene_chem_ref_gene_form gf where gf.gene_chem_reference_id = gcr.id and gf.gene_id = gcr.gene_id and gf.actor_form_type_nm in ( select tc.nm from actor_form_type tp, actor_form_type tc where tc.subset_left_no between tp.subset_left_no and tp.subset_right_no and (tp.nm = ?))) and gcr.chem_id = any (array ( select dp.descendant_object_id from dag_path dp inner join term t on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.taxon_id = any (array ( select dp.descendant_object_id from dag_path dp inner join dag_node n on n.id = dp.ancestor_dag_node_id where n.acc_txt = ? and n.dag_id = ?)) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by c.nm_sort, g.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 27 03 1 6s807ms 6s807ms 04 1 6s150ms 6s150ms 05 4 24s554ms 6s138ms [ User: pubeu - Total duration: 24s918ms - Times executed: 4 ]
-
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'BENZO(A)PYRENE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9606' AND n.dag_id = 7)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt LIMIT 50;
Date: 2026-08-27 03:48:18 Duration: 6s807ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'BENZO(A)PYRENE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9606' AND n.dag_id = 7)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt LIMIT 50;
Date: 2026-08-27 05:04:55 Duration: 6s341ms Bind query: yes
-
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'BENZO(A)PYRENE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9606' AND n.dag_id = 7)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt LIMIT 50;
Date: 2026-08-27 05:06:37 Duration: 6s253ms Bind query: yes
7 5 1m19s 10s243ms 35s76ms 15s915ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ? offset ?;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Aug 27 08 5 1m19s 15s915ms [ User: pubeu - Total duration: 1m19s - Times executed: 5 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 4119850;
Date: 2026-08-27 08:40:58 Duration: 35s76ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 100;
Date: 2026-08-27 08:37:03 Duration: 11s926ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 50;
Date: 2026-08-27 08:36:18 Duration: 11s577ms Database: ctdprd51 User: pubeu Bind query: yes
8 4 7m34s 1m53s 1m54s 1m53s copy pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Aug 27 06 1 1m53s 1m53s 10 1 1m54s 1m54s 14 1 1m53s 1m53s 18 1 1m53s 1m53s [ User: postgres - Total duration: 7m34s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m34s - 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-08-27 10:06:55 Duration: 1m54s 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-08-27 14:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-27 06:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
9 4 1m37s 24s187ms 24s694ms 24s389ms 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 #9
Day Hour Count Duration Avg duration Aug 27 06 1 24s244ms 24s244ms 10 1 24s431ms 24s431ms 14 1 24s694ms 24s694ms 18 1 24s187ms 24s187ms -
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-08-27 14:07:20 Duration: 24s694ms
-
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-08-27 10:07:20 Duration: 24s431ms
-
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-08-27 06:07:19 Duration: 24s244ms
10 4 1m17s 15s951ms 20s882ms 19s284ms 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 #10
Day Hour Count Duration Avg duration Aug 27 06 1 20s104ms 20s104ms 10 1 20s199ms 20s199ms 14 1 15s951ms 15s951ms 18 1 20s882ms 20s882ms [ User: postgres - Total duration: 1m17s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 1m17s - Times executed: 4 ]
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-27 18:00:23 Duration: 20s882ms 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-08-27 10:00:22 Duration: 20s199ms 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-08-27 06:00:21 Duration: 20s104ms Database: ctdprd51 User: postgres Application: pg_dump
11 4 1m2s 15s494ms 15s947ms 15s660ms 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 #11
Day Hour Count Duration Avg duration Aug 27 06 1 15s494ms 15s494ms 10 1 15s628ms 15s628ms 14 1 15s947ms 15s947ms 18 1 15s569ms 15s569ms -
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-08-27 14:07:36 Duration: 15s947ms
-
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-08-27 10:07:36 Duration: 15s628ms
-
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-08-27 18:07:35 Duration: 15s569ms
12 4 1m 14s924ms 15s571ms 15s116ms 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 #12
Day Hour Count Duration Avg duration Aug 27 06 1 14s924ms 14s924ms 10 1 15s571ms 15s571ms 14 1 15s 15s 18 1 14s969ms 14s969ms -
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-08-27 10:00:54 Duration: 15s571ms
-
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-08-27 14:00:49 Duration: 15s
-
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-08-27 18:00:54 Duration: 14s969ms
13 4 59s80ms 14s548ms 15s255ms 14s770ms 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 #13
Day Hour Count Duration Avg duration Aug 27 06 1 14s634ms 14s634ms 10 1 15s255ms 15s255ms 14 1 14s548ms 14s548ms 18 1 14s642ms 14s642ms -
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-08-27 10:01:09 Duration: 15s255ms
-
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-08-27 18:01:09 Duration: 14s642ms
-
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-08-27 06:01:07 Duration: 14s634ms
14 4 30s409ms 7s521ms 7s709ms 7s602ms 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 #14
Day Hour Count Duration Avg duration Aug 27 06 1 7s521ms 7s521ms 10 1 7s611ms 7s611ms 14 1 7s566ms 7s566ms 18 1 7s709ms 7s709ms -
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-08-27 18:00:33 Duration: 7s709ms
-
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-08-27 10:00:32 Duration: 7s611ms
-
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-08-27 14:00:27 Duration: 7s566ms
15 4 26s521ms 6s527ms 6s826ms 6s630ms 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 #15
Day Hour Count Duration Avg duration Aug 27 06 1 6s534ms 6s534ms 10 1 6s826ms 6s826ms 14 1 6s527ms 6s527ms 18 1 6s632ms 6s632ms -
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-08-27 10:01:18 Duration: 6s826ms
-
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-08-27 18:01:17 Duration: 6s632ms
-
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-08-27 06:01:15 Duration: 6s534ms
16 4 25s237ms 6s232ms 6s472ms 6s309ms 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 #16
Day Hour Count Duration Avg duration Aug 27 06 1 6s282ms 6s282ms 10 1 6s472ms 6s472ms 14 1 6s232ms 6s232ms 18 1 6s250ms 6s250ms -
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-08-27 10:00:38 Duration: 6s472ms
-
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-08-27 06:00:37 Duration: 6s282ms
-
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-08-27 18:00:39 Duration: 6s250ms
17 4 24s558ms 5s909ms 6s338ms 6s139ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.id ixnid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct r.acc_txt, ?)) as references, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id inner join reference r on gcr.reference_id = r.id left outer join term taxonterm on gcr.taxon_id = taxonterm.id where exists ( select ? from gene_chem_ref_gene_form gf where gf.gene_chem_reference_id = gcr.id and gf.gene_id = gcr.gene_id and gf.actor_form_type_nm in ( select tc.nm from actor_form_type tp, actor_form_type tc where tc.subset_left_no between tp.subset_left_no and tp.subset_right_no and (tp.nm = ?))) and gcr.chem_id = any (array ( select dp.descendant_object_id from dag_path dp inner join term t on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.taxon_id = any (array ( select dp.descendant_object_id from dag_path dp inner join dag_node n on n.id = dp.ancestor_dag_node_id where n.acc_txt = ? and n.dag_id = ?)) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by c.nm_sort, g.nm_sort, i.sort_txt;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Aug 27 03 1 6s35ms 6s35ms 05 3 18s523ms 6s174ms [ User: pubeu - Total duration: 12s374ms - Times executed: 2 ]
-
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'BENZO(A)PYRENE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9606' AND n.dag_id = 7)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt;
Date: 2026-08-27 05:28:25 Duration: 6s338ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'BENZO(A)PYRENE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9606' AND n.dag_id = 7)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt;
Date: 2026-08-27 05:06:47 Duration: 6s274ms Bind query: yes
-
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'BENZO(A)PYRENE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9606' AND n.dag_id = 7)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt;
Date: 2026-08-27 03:51:20 Duration: 6s35ms Database: ctdprd51 User: pubeu Bind query: yes
18 3 56s448ms 17s162ms 19s913ms 18s816ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by gd.network_score nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 27 04 3 56s448ms 18s816ms [ User: pubeu - Total duration: 56s448ms - Times executed: 3 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-08-27 04:51:52 Duration: 19s913ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-08-27 04:51:56 Duration: 19s372ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-08-27 04:51:51 Duration: 17s162ms Database: ctdprd51 User: pubeu Bind query: yes
19 3 26s440ms 5s340ms 10s999ms 8s813ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 27 03 1 5s340ms 5s340ms 08 1 10s999ms 10s999ms 09 1 10s100ms 10s100ms [ User: pubeu - Total duration: 26s440ms - Times executed: 3 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-08-27 08:28:56 Duration: 10s999ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-08-27 09:25:14 Duration: 10s100ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194153') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-08-27 03:24:22 Duration: 5s340ms Database: ctdprd51 User: pubeu Bind query: yes
20 3 18s242ms 6s16ms 6s171ms 6s80ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 27 05 2 12s187ms 6s93ms 08 1 6s54ms 6s54ms [ User: pubeu - Total duration: 12s70ms - Times executed: 2 ]
[ User: qaeu - Total duration: 6s171ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1403103)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-08-27 05:43:44 Duration: 6s171ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1403103)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-08-27 08:48:06 Duration: 6s54ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1403103)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-08-27 05:48:42 Duration: 6s16ms Database: ctdprd51 User: pubeu Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 53m14s 53m14s 53m14s 1 53m14s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.object_type where cd = ?), ptr.term_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.phenotype_term_reference ptr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 27 20 1 53m14s 53m14s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 20:13:56 Duration: 53m14s Bind query: yes
2 35m55s 35m55s 35m55s 1 35m55s 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 Aug 27 18 1 35m55s 35m55s [ User: load - Total duration: 35m55s - 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-08-27 18:18:20 Duration: 35m55s Database: ctdprd51 User: load Bind query: yes
3 31m26s 31m26s 31m26s 1 31m26s insert into pub2.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 Aug 27 17 1 31m26s 31m26s -
insert into pub2.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-08-27 17:32:45 Duration: 31m26s Bind query: yes
4 17m21s 17m21s 17m21s 1 17m21s insert into pub2.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 27 16 1 17m21s 17m21s [ User: pub2 - Total duration: 17m21s - Times executed: 1 ]
-
insert into pub2.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-08-27 16:54:45 Duration: 17m21s Database: ctdprd51 User: pub2 Bind query: yes
5 9m22s 9m22s 9m22s 1 9m22s select maint_query_logs_archive ();Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 27 00 1 9m22s 9m22s [ User: pubc - Total duration: 9m22s - Times executed: 1 ]
[ Application: psql - Total duration: 9m22s - 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-08-27 00:09:23 Duration: 9m22s Database: ctdprd51 User: pubc Application: psql
6 9s484ms 1h13m19s 6m55s 13 1h30m7s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 27 21 13 1h30m7s 6m55s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:27:22 Duration: 1h13m19s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:40:47 Duration: 6m58s Bind query: yes
-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.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 pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:43:36 Duration: 2m48s Bind query: yes
7 5m24s 5m24s 5m24s 1 5m24s insert into pub2.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 #7
Day Hour Count Duration Avg duration Aug 27 17 1 5m24s 5m24s -
insert into pub2.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-08-27 17:38:09 Duration: 5m24s Bind query: yes
8 4m18s 4m18s 4m18s 1 4m18s insert into pub2.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 pub2.term);Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Aug 27 17 1 4m18s 4m18s -
INSERT INTO pub2.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 pub2.TERM);
Date: 2026-08-27 17:01:17 Duration: 4m18s Bind query: yes
9 4m16s 4m16s 4m16s 1 4m16s create unique index gene_disease_reference_ak1 on pub2.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 Aug 27 22 1 4m16s 4m16s -
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-08-27 22:06:11 Duration: 4m16s Bind query: yes
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2026-08-27 22:06:10 Duration: 0ms
10 3m6s 3m6s 3m6s 1 3m6s create index ix_gene_disease_ref_net_sc on pub2.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Aug 27 22 1 3m6s 3m6s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-08-27 22:23:38 Duration: 3m6s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-08-27 22:23:37 Duration: 0ms
11 2m41s 2m41s 2m41s 1 2m41s alter table pub2.gene_disease_reference add constraint gene_disease_reference_pk primary key (id);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 27 22 1 2m41s 2m41s -
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:01:55 Duration: 2m41s Bind query: yes
-
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-08-27 22:01:55 Duration: 0ms Database: ctdprd51 User: pub2
12 2m32s 2m32s 2m32s 1 2m32s vacuum full analyze db_link;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 27 15 1 2m32s 2m32s -
vacuum FULL analyze db_link;
Date: 2026-08-27 15:18:13 Duration: 2m32s
-
vacuum FULL analyze db_link;
Date: 2026-08-27 15:16:08 Duration: 0ms
13 2m24s 2m24s 2m24s 1 2m24s create index ix_gene_disease_ref_dis_gene on pub2.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Aug 27 22 1 2m24s 2m24s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-08-27 22:16:50 Duration: 2m24s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-08-27 22:16:49 Duration: 0ms
14 2m24s 2m24s 2m24s 1 2m24s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub2.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.object_type where cd = ?), cdr.mod_tm from pub2.chem_disease_reference cdr, pub2.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 pub2.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Aug 27 21 1 2m24s 2m24s -
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub2.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub2.CHEM_DISEASE_REFERENCE cdr, pub2.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 pub2.IXN_AXN WHERE action_degree_type_nm = 'does not affect');
Date: 2026-08-27 21:46:51 Duration: 2m24s Bind query: yes
15 2m1s 2m1s 2m1s 1 2m1s insert into pub2.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 Aug 27 16 1 2m1s 2m1s -
INSERT INTO pub2.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-08-27 16:56:59 Duration: 2m1s Bind query: yes
16 1m53s 1m54s 1m53s 4 7m34s 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 #16
Day Hour Count Duration Avg duration Aug 27 06 1 1m53s 1m53s 10 1 1m54s 1m54s 14 1 1m53s 1m53s 18 1 1m53s 1m53s [ User: postgres - Total duration: 7m34s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m34s - 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-08-27 10:06:55 Duration: 1m54s 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-08-27 14:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-27 06:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
17 5s3ms 13m15s 1m45s 10 17m35s 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 pub2.gene_chem_reference gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Aug 27 18 4 14m26s 3m36s 19 6 3m9s 31s523ms [ User: load - Total duration: 13m15s - 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 18:34:32 Duration: 13m15s 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 19:06:07 Duration: 1m1s 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 pub2.GENE_CHEM_REFERENCE gcr, pub2.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 pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2026-08-27 18:53:31 Duration: 1m Bind query: yes
18 5s239ms 7m47s 1m1s 59 1h select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 27 13 13 8m12s 37s870ms 14 28 29m38s 1m3s 18 3 1m11s 23s942ms 21 3 9m12s 3m4s 22 4 7m22s 1m50s 23 8 4m21s 32s674ms [ User: load - Total duration: 11m51s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m51s - Times executed: 12 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 21:57:48 Duration: 7m47s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=edit.DB_LINK,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/voc/gene/output/dbLink.txt.DUPE}');
Date: 2026-08-27 13:59:48 Duration: 5m44s Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.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-08-27 22:36:05 Duration: 5m26s Bind query: yes
19 34s861ms 1m58s 59s306ms 10 9m53s select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by gd.network_score nulls last, g.nm_sort, d.nm_sort limit ? offset ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 27 05 10 9m53s 59s306ms [ User: pubeu - Total duration: 9m7s - Times executed: 9 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 100;
Date: 2026-08-27 05:22:50 Duration: 1m58s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 100;
Date: 2026-08-27 05:23:01 Duration: 1m44s Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2197184') ORDER BY gd.network_score NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 50;
Date: 2026-08-27 05:22:19 Duration: 1m1s Database: ctdprd51 User: pubeu Bind query: yes
20 5s2ms 40s128ms 25s258ms 13 5m28s select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 27 05 5 1m1s 12s314ms 08 5 3m2s 36s452ms 09 2 1m16s 38s183ms 22 1 8s153ms 8s153ms [ User: pubeu - Total duration: 4m16s - Times executed: 11 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 09:28:38 Duration: 40s128ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 08:36:32 Duration: 37s216ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2203494') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-08-27 08:52:13 Duration: 36s962ms 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,041 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 2 FATAL entries
- 2 ERROR entries
- 0 WARNING entries
- 3 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 1 Max number of times the same event was reported
- 7 Total events found
Rank Times reported Error 1 1 LOG: process ... still waiting for AccessExclusiveLock on relation ... of database ... after ... ms
Times Reported Most Frequent Error / Event #1
Day Hour Count Aug 27 14 1 - LOG: process 2050982 still waiting for AccessExclusiveLock on relation 2633821 of database 484829 after 1000.064 ms
Detail: Process holding the lock: 2050666. Wait queue: 2050982.
Statement: SELECT * FROM pgbulkload.pg_bulkload($1)Date: 2026-08-27 14:00:56 Database: ctdprd51 Application: pg_bulkload User: load Remote:
2 1 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #2
Day Hour Count Aug 27 22 1 - ERROR: canceling statement due to user request
Statement: SELECT count(*) FROM pg_catalog.pg_stat_all_tables WHERE (n_dead_tup/(n_live_tup+n_dead_tup)::float8) > 0.2 AND (n_live_tup+n_dead_tup) > 50;
Date: 2026-08-27 22:00:10
3 1 ERROR: function get_ixn_prose(...) does not exist
Times Reported Most Frequent Error / Event #3
Day Hour Count Aug 27 14 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 ,pub1.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-08-27 14:14:55 Database: ctdprd51 Application: pgAdmin 4 - CONN:774039 User: load Remote:
4 1 FATAL: canceling authentication due to timeout
Times Reported Most Frequent Error / Event #4
Day Hour Count Aug 27 08 1 - FATAL: canceling authentication due to timeout
Date: 2026-08-27 08:44:04
5 1 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #5
Day Hour Count Aug 27 22 1 - FATAL: connection to client lost
Date: 2026-08-27 22:00:10
6 1 LOG: could not receive data from client: Connection reset by peer
Times Reported Most Frequent Error / Event #6
Day Hour Count Aug 27 15 1 - LOG: could not receive data from client: Connection reset by peer
Date: 2026-08-27 15:23:16
7 1 LOG: could not send data to client: Broken pipe
Times Reported Most Frequent Error / Event #7
Day Hour Count Aug 27 22 1 - LOG: could not send data to client: Broken pipe
Date: 2026-08-27 22:00:10