pgBadger-
Global information
- Generated on Sat Jun 27 04:15:03 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260626
- Parsed 26,187 log entries in 2s
- Log start from 2026-06-26 00:00:01 to 2026-06-26 23:59:42
-
Overview
Global Stats
- 95 Number of unique normalized queries
- 194 Number of queries
- 6h12m57s Total query duration
- 2026-06-26 00:09:19 First query
- 2026-06-26 23:40:22 Last query
- 2 queries/s at 2026-06-26 14:49:36 Query peak
- 6h12m57s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 6h12m57s Execute total duration
- 15 Number of events
- 10 Number of unique normalized events
- 2 Max number of times the same event was reported
- 0 Number of cancellation
- 45 Total number of automatic vacuums
- 69 Total number of automatic analyzes
- 2,207 Number temporary file
- 1.00 GiB Max size of temporary file
- 283.12 MiB Average size of temporary file
- 2,094 Total number of sessions
- 157 sessions at 2026-06-26 23:45:41 Session peak
- 70d2h35s Total duration of sessions
- 48m11s Average duration of sessions
- 0 Average queries per session
- 10s686ms Average queries duration per session
- 48m1s Average idle time per session
- 2,101 Total number of connections
- 9 connections/s at 2026-06-26 05:40:08 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 2 queries/s Query Peak
- 2026-06-26 14:49:36 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2026-06-26 21:39:00 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-06-26 22:10:29 Date
Queries duration
Key values
- 6h12m57s 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) Jun 26 00 2 0ms 9m17s 4m42s 0ms 0ms 9m24s 01 1 0ms 5s628ms 5s628ms 0ms 0ms 5s628ms 02 6 0ms 10s645ms 8s955ms 5s534ms 10s314ms 21s33ms 03 3 0ms 15s225ms 14s511ms 0ms 14s53ms 15s225ms 04 0 0ms 0ms 0ms 0ms 0ms 0ms 05 1 0ms 5s613ms 5s613ms 0ms 0ms 5s613ms 06 9 0ms 1m52s 24s615ms 20s926ms 48s388ms 1m52s 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 0 0ms 0ms 0ms 0ms 0ms 0ms 09 0 0ms 0ms 0ms 0ms 0ms 0ms 10 11 0ms 1m52s 23s491ms 26s630ms 47s941ms 1m52s 11 0 0ms 0ms 0ms 0ms 0ms 0ms 12 11 0ms 15s86ms 8s654ms 8s993ms 30s795ms 30s884ms 13 30 0ms 5m34s 55s116ms 1m39s 1m44s 5m34s 14 25 0ms 3m11s 44s310ms 1m53s 2m26s 6m40s 15 1 0ms 10s3ms 10s3ms 0ms 0ms 10s3ms 16 2 0ms 16m47s 8m28s 0ms 0ms 16m57s 17 8 0ms 29m19s 5m33s 1m48s 4m15s 29m19s 18 18 0ms 35m8s 3m3s 1m1s 1m52s 35m8s 19 3 0ms 58s610ms 53s542ms 0ms 57s894ms 58s610ms 20 1 0ms 51m46s 51m46s 0ms 0ms 51m46s 21 22 0ms 1h8m14s 4m48s 2m16s 7m4s 1h8m40s 22 27 0ms 5m24s 54s312ms 1m49s 2m27s 5m24s 23 13 0ms 1m6s 21s930ms 28s386ms 46s635ms 1m35s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jun 26 00 1 0 9m17s 0ms 0ms 9m17s 01 1 0 5s628ms 0ms 0ms 5s628ms 02 6 0 8s955ms 0ms 5s534ms 21s33ms 03 3 0 14s511ms 0ms 0ms 15s225ms 04 0 0 0ms 0ms 0ms 0ms 05 1 0 5s613ms 0ms 0ms 5s613ms 06 0 9 24s615ms 0ms 20s926ms 1m52s 07 0 0 0ms 0ms 0ms 0ms 08 0 0 0ms 0ms 0ms 0ms 09 0 0 0ms 0ms 0ms 0ms 10 2 9 23s491ms 0ms 26s630ms 1m52s 11 0 0 0ms 0ms 0ms 0ms 12 10 0 8s620ms 0ms 0ms 30s884ms 13 30 0 55s116ms 1m34s 1m39s 5m34s 14 9 9 41s439ms 7s812ms 44s538ms 3m47s 15 1 0 10s3ms 0ms 0ms 10s3ms 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 9 9 3m3s 20s871ms 1m1s 35m8s 19 3 0 53s542ms 0ms 0ms 58s610ms 20 1 0 51m46s 0ms 0ms 51m46s 21 17 0 5m32s 52s624ms 1m40s 1h8m40s 22 7 0 1m18s 0ms 13s327ms 5m24s 23 13 0 21s930ms 5s122ms 28s386ms 1m35s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jun 26 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 0 0 0 0 0ms 0ms 0ms 0ms 12 1 0 0 0 8s993ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 2 0 0 0 8m28s 0ms 0ms 0ms 17 8 0 0 0 5m33s 0ms 0ms 5m18s 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 Jun 26 00 0 0 0.00 0.00% 01 0 1 1.00 0.00% 02 0 6 6.00 0.00% 03 0 3 3.00 0.00% 04 0 0 0.00 0.00% 05 0 1 1.00 0.00% 06 0 0 0.00 0.00% 07 0 0 0.00 0.00% 08 0 0 0.00 0.00% 09 0 0 0.00 0.00% 10 0 2 2.00 0.00% 11 0 0 0.00 0.00% 12 0 10 10.00 0.00% 13 0 30 30.00 0.00% 14 0 4 4.00 0.00% 15 0 1 1.00 0.00% 16 0 2 2.00 0.00% 17 0 8 8.00 0.00% 18 0 9 9.00 0.00% 19 0 3 3.00 0.00% 20 0 1 1.00 0.00% 21 0 22 22.00 0.00% 22 0 27 27.00 0.00% 23 0 13 13.00 0.00% Day Hour Count Average / Second Jun 26 00 79 0.02/s 01 74 0.02/s 02 88 0.02/s 03 113 0.03/s 04 81 0.02/s 05 102 0.03/s 06 79 0.02/s 07 76 0.02/s 08 67 0.02/s 09 78 0.02/s 10 90 0.03/s 11 74 0.02/s 12 127 0.04/s 13 122 0.03/s 14 96 0.03/s 15 83 0.02/s 16 78 0.02/s 17 78 0.02/s 18 94 0.03/s 19 78 0.02/s 20 77 0.02/s 21 83 0.02/s 22 87 0.02/s 23 97 0.03/s Day Hour Count Average Duration Average idle time Jun 26 00 79 30m50s 30m43s 01 74 32m17s 32m17s 02 88 27m6s 27m5s 03 113 21m41s 21m40s 04 81 28m21s 28m21s 05 102 23m51s 23m51s 06 79 30m21s 30m18s 07 76 32m20s 32m20s 08 67 31m34s 31m34s 09 75 32m31s 32m31s 10 89 26m10s 26m8s 11 72 34m51s 34m51s 12 126 19m28s 19m27s 13 122 20m39s 20m25s 14 97 25m10s 24m58s 15 83 29m56s 29m56s 16 77 31m37s 31m24s 17 78 31m20s 30m46s 18 94 26m25s 25m50s 19 78 31m54s 31m52s 20 77 31m2s 30m22s 21 83 9h5m23s 9h4m7s 22 87 28m18s 28m1s 23 97 24m45s 24m42s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2026-06-26 05:40:08 Date
Connections per database
Key values
- ctdprd51 Main Database
- 2,101 connections Total
Connections per user
Key values
- pubeu Main User
- 2,101 connections Total
-
Sessions
Simultaneous sessions
Key values
- 157 sessions Session Peak
- 2026-06-26 23:45:41 Date
Histogram of session times
Key values
- 1,746 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,094 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,094 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 2,094 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 1,102,696 buffers Checkpoint Peak
- 2026-06-26 18:02:38 Date
- 1619.843 seconds Highest write time
- 0.780 seconds Sync time
Checkpoints Wal files
Key values
- 642 files Wal files usage Peak
- 2026-06-26 23:08:53 Date
Checkpoints distance
Key values
- 17,254.12 Mo Distance Peak
- 2026-06-26 21:53:52 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Jun 26 00 430 43.279s 0.003s 43.291s 01 121 12.308s 0.003s 12.317s 02 99 10.098s 0.002s 10.107s 03 95 9.698s 0.002s 9.708s 04 1,566 157.036s 0.002s 157.081s 05 163 16.529s 0.002s 16.539s 06 92 9.406s 0.002s 9.416s 07 25 2.6s 0.001s 2.604s 08 6,971 697.469s 0.004s 697.496s 09 622 62.521s 0.002s 62.53s 10 2,547 255.129s 0.089s 255.371s 11 118 11.921s 0.001s 11.926s 12 52,288 1,619.063s 0.001s 1,619.173s 13 326,645 2,241.004s 0.919s 2,249.44s 14 48,212 1,291.262s 0.008s 1,293.081s 15 36,626 1,655.09s 0.007s 1,656.525s 16 526,386 281.936s 0.199s 283.277s 17 2,731,873 2,551.327s 1.232s 2,556.602s 18 1,102,696 1,619.217s 0.012s 1,620.258s 19 424,765 1,620.761s 0.008s 1,621.487s 20 15 1.689s 0.002s 1.699s 21 3,850 114.122s 0.703s 117.756s 22 88 9.291s 1.472s 17.516s 23 797,824 1,619.756s 0.007s 1,621.097s Day Hour Added Removed Recycled Synced files Longest sync Average sync Jun 26 00 0 0 0 68 0.001s 0.002s 01 0 0 0 24 0.001s 0.002s 02 0 0 0 26 0.001s 0.002s 03 0 0 0 25 0.001s 0.002s 04 0 0 1 56 0.001s 0.002s 05 0 0 0 33 0.001s 0.002s 06 0 0 0 25 0.001s 0.002s 07 0 0 0 12 0.001s 0.001s 08 0 0 6 168 0.001s 0.003s 09 0 0 0 133 0.001s 0.002s 10 0 0 1 727 0.001s 0.002s 11 0 0 0 58 0.001s 0.001s 12 0 0 35 33 0.001s 0.001s 13 0 119 3,158 535 0.492s 0.016s 14 0 0 888 127 0.001s 0.002s 15 0 0 658 218 0.001s 0.002s 16 0 31 539 90 0.078s 0.004s 17 0 0 2,152 225 0.176s 0.037s 18 0 0 538 132 0.001s 0.001s 19 0 0 307 245 0.001s 0.002s 20 0 0 0 12 0.001s 0.002s 21 0 35 985 89 0.683s 0.014s 22 0 0 2,079 83 0.779s 0.067s 23 0 0 642 98 0.001s 0.001s Day Hour Count Avg time (sec) Jun 26 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 Jun 26 00 1,471.00 kB 321,077.00 kB 01 151.00 kB 260,091.00 kB 02 161.00 kB 210,703.50 kB 03 179.50 kB 170,703.00 kB 04 4,637.00 kB 138,838.50 kB 05 338.50 kB 112,821.50 kB 06 63.50 kB 91,423.00 kB 07 68.00 kB 77,959.00 kB 08 35,021.33 kB 93,503.33 kB 09 1,788.00 kB 72,049.50 kB 10 7,734.00 kB 59,192.50 kB 11 360.00 kB 51,222.00 kB 12 559,628.00 kB 559,628.00 kB 13 7,597,420.43 kB 7,602,639.71 kB 14 7,531,626.50 kB 8,695,650.50 kB 15 5,392,944.50 kB 8,480,465.00 kB 16 4,403,818.00 kB 8,065,381.00 kB 17 8,815,320.25 kB 8,816,935.75 kB 18 8,811,673.00 kB 8,816,088.00 kB 19 2,781,810.50 kB 8,066,298.50 kB 20 26.00 kB 6,533,706.50 kB 21 5,882,878.67 kB 7,745,702.33 kB 22 8,818,488.50 kB 8,829,331.25 kB 23 8,359,640.00 kB 8,781,303.00 kB -
Temporary Files
Size of temporary files
Key values
- 52.00 GiB Temp Files size Peak
- 2026-06-26 21:33:46 Date
Number of temporary files
Key values
- 52 per second Temp Files Peak
- 2026-06-26 21:33:46 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Jun 26 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 240 2.29 GiB 9.79 MiB 13 878 56.36 GiB 65.73 MiB 14 260 22.45 GiB 88.44 MiB 15 0 0 0 16 0 0 0 17 0 0 0 18 0 0 0 19 31 30.83 GiB 1018.25 MiB 20 64 63.25 GiB 1012.07 MiB 21 359 343.46 GiB 979.68 MiB 22 200 73.93 GiB 378.53 MiB 23 175 17.61 GiB 103.06 MiB Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 1,413 99.24 GiB 8.00 KiB 1.00 GiB 71.92 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-06-26 21:46:42 Duration: 7m37s 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-06-26 13:18:56 Duration: 5m34s
-
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-06-26 22:24:22 Duration: 5m24s
2 292 290.91 GiB 71.73 MiB 1.00 GiB 1020.18 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-06-26 21:33:32 Duration: 0ms
3 70 2.49 GiB 25.01 MiB 53.09 MiB 36.40 MiB vacuum full analyze ixn_actor;-
vacuum FULL analyze ixn_actor;
Date: 2026-06-26 14:49:36 Duration: 28s79ms
-
vacuum FULL analyze ixn_actor;
Date: 2026-06-26 14:50:28 Duration: 26s917ms
-
vacuum FULL analyze ixn_actor;
Date: 2026-06-26 14:49:15 Duration: 0ms
4 64 63.25 GiB 260.49 MiB 1.00 GiB 1012.07 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-06-26 20:11:14 Duration: 0ms
5 35 5.03 GiB 84.48 MiB 232.00 MiB 147.24 MiB vacuum full analyze db_link;-
vacuum FULL analyze db_link;
Date: 2026-06-26 14:53:05 Duration: 2m26s
-
vacuum FULL analyze db_link;
Date: 2026-06-26 14:51:05 Duration: 0ms
6 31 30.83 GiB 845.80 MiB 1.00 GiB 1018.25 MiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from 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-06-26 19:15:34 Duration: 0ms
7 25 410.73 MiB 12.35 MiB 21.16 MiB 16.43 MiB vacuum full analyze ixn;-
vacuum FULL analyze ixn;
Date: 2026-06-26 14:49:52 Duration: 9s135ms
-
vacuum FULL analyze ixn;
Date: 2026-06-26 14:49:46 Duration: 0ms
8 20 226.90 MiB 5.91 MiB 18.94 MiB 11.34 MiB vacuum full analyze term;-
vacuum FULL analyze TERM;
Date: 2026-06-26 14:49:36 Duration: 11s643ms
-
vacuum FULL analyze TERM;
Date: 2026-06-26 14:49:27 Duration: 0ms
9 20 14.30 GiB 8.00 KiB 1.00 GiB 731.97 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-06-26 21:52:55 Duration: 4m47s
-
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-06-26 21:52:54 Duration: 0ms Database: ctdprd51 User: pub2
10 15 7.94 GiB 8.00 KiB 1.00 GiB 542.20 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-06-26 22:11:59 Duration: 1m29s
-
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2026-06-26 22:11:58 Duration: 0ms
11 10 7.94 GiB 462.88 MiB 1.00 GiB 813.30 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-06-26 21:55:47 Duration: 1m40s
-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub2.gene_disease_reference USING btree (source_cd);
Date: 2026-06-26 21:55:46 Duration: 0ms
12 10 7.94 GiB 564.88 MiB 1.00 GiB 813.30 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-06-26 22:05:38 Duration: 1m50s
-
CREATE INDEX ix_gene_disease_reference_ixn ON pub2.gene_disease_reference USING btree (ixn_id);
Date: 2026-06-26 22:05:38 Duration: 0ms
13 10 7.94 GiB 564.88 MiB 1.00 GiB 813.30 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-06-26 22:01:20 Duration: 1m45s
-
CREATE INDEX ix_gene_disease_ref_reference ON pub2.gene_disease_reference USING btree (reference_id);
Date: 2026-06-26 22:01:19 Duration: 0ms
14 10 7.94 GiB 568.62 MiB 1.00 GiB 813.30 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-06-26 21:54:06 Duration: 1m10s
-
CREATE INDEX ix_gene_disease_ref_src_db ON pub2.gene_disease_reference USING btree (source_acc_db_id);
Date: 2026-06-26 21:54:05 Duration: 0ms
15 10 262.67 MiB 8.00 KiB 56.42 MiB 26.27 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-06-26 22:15:15 Duration: 0ms
16 10 7.94 GiB 564.88 MiB 1.00 GiB 813.30 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-06-26 22:10:29 Duration: 3m1s
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-06-26 22:10:29 Duration: 0ms
17 10 7.94 GiB 564.88 MiB 1.00 GiB 813.30 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-06-26 22:03:48 Duration: 2m27s
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-06-26 22:03:47 Duration: 0ms
18 10 1.20 GiB 8.00 KiB 249.17 MiB 122.51 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-06-26 22:14:38 Duration: 12s279ms
-
ALTER TABLE pub2.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2026-06-26 22:14:38 Duration: 0ms
19 10 7.94 GiB 564.88 MiB 1.00 GiB 813.30 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-06-26 21:57:42 Duration: 1m55s
-
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2026-06-26 21:57:41 Duration: 0ms
20 10 7.94 GiB 564.88 MiB 1.00 GiB 813.30 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-06-26 21:59:35 Duration: 1m52s
-
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2026-06-26 21:59:34 Duration: 0ms
21 10 478.60 MiB 8.00 KiB 98.34 MiB 47.86 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-06-26 22:14:46 Duration: 7s792ms
-
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-06-26 22:14:46 Duration: 0ms
22 10 7.94 GiB 564.88 MiB 1.00 GiB 813.30 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-06-26 22:07:28 Duration: 1m49s
-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub2.gene_disease_reference USING btree (mod_tm);
Date: 2026-06-26 22:07:27 Duration: 0ms
23 7 6.48 GiB 495.90 MiB 1.00 GiB 948.56 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-06-26 21:38:07 Duration: 0ms
24 5 262.63 MiB 48.90 MiB 53.77 MiB 52.53 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-06-26 22:15:00 Duration: 0ms
25 5 262.64 MiB 49.33 MiB 55.02 MiB 52.53 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-06-26 22:14:49 Duration: 0ms
26 5 1.68 GiB 334.27 MiB 355.35 MiB 343.82 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-06-26 22:14:26 Duration: 16s410ms
-
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-06-26 22:14:26 Duration: 0ms
27 5 262.64 MiB 50.15 MiB 55.88 MiB 52.53 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-06-26 22:15:03 Duration: 0ms
28 5 1.20 GiB 224.05 MiB 260.60 MiB 245.00 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-06-26 22:12:56 Duration: 14s507ms
-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub2.phenotype_term_reference USING btree (reference_id);
Date: 2026-06-26 22:12:56 Duration: 0ms
29 5 1.20 GiB 237.66 MiB 248.26 MiB 245.00 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-06-26 22:13:16 Duration: 10s814ms
-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub2.phenotype_term_reference USING btree (evidence_cd);
Date: 2026-06-26 22:13:16 Duration: 0ms
30 5 262.64 MiB 50.28 MiB 54.54 MiB 52.53 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-06-26 22:15:12 Duration: 5s615ms
-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub2.chem_disease_reference USING btree (network_score);
Date: 2026-06-26 22:15:12 Duration: 0ms
31 5 1.20 GiB 226.31 MiB 261.34 MiB 245.00 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-06-26 22:12:30 Duration: 12s434ms
-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub2.phenotype_term_reference USING btree (term_id);
Date: 2026-06-26 22:12:30 Duration: 0ms
32 5 1.20 GiB 234.54 MiB 253.64 MiB 245.00 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-06-26 22:14:09 Duration: 13s804ms
-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub2.phenotype_term_reference USING btree (via_term_id);
Date: 2026-06-26 22:14:09 Duration: 0ms
33 5 1.20 GiB 240.73 MiB 248.80 MiB 245.00 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-06-26 22:13:28 Duration: 11s631ms
-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub2.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2026-06-26 22:13:28 Duration: 0ms
34 5 262.64 MiB 50.95 MiB 55.34 MiB 52.53 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-06-26 22:14:57 Duration: 0ms
35 5 1.20 GiB 239.62 MiB 249.79 MiB 245.00 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-06-26 22:13:05 Duration: 9s422ms
-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub2.phenotype_term_reference USING btree (taxon_id);
Date: 2026-06-26 22:13:05 Duration: 0ms
36 5 262.64 MiB 50.92 MiB 54.98 MiB 52.53 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-06-26 22:14:55 Duration: 0ms
37 5 1.20 GiB 239.95 MiB 247.60 MiB 245.00 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-06-26 22:13:56 Duration: 13s511ms
-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub2.phenotype_term_reference USING btree (ixn_id);
Date: 2026-06-26 22:13:56 Duration: 0ms
38 5 1.20 GiB 236.83 MiB 250.66 MiB 245.00 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-06-26 22:13:42 Duration: 14s205ms
-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub2.phenotype_term_reference USING btree (term_reference_id);
Date: 2026-06-26 22:13:42 Duration: 0ms
39 5 262.63 MiB 51.57 MiB 53.00 MiB 52.53 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-06-26 22:15:07 Duration: 0ms
40 5 262.65 MiB 44.12 MiB 55.17 MiB 52.53 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-06-26 22:14:52 Duration: 0ms
41 5 1.20 GiB 240.11 MiB 253.00 MiB 245.00 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-06-26 22:12:17 Duration: 17s569ms
-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub2.phenotype_term_reference USING btree (phenotype_id);
Date: 2026-06-26 22:12:17 Duration: 0ms
42 5 1.20 GiB 233.45 MiB 260.45 MiB 245.00 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-06-26 22:12:42 Duration: 11s864ms
-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub2.phenotype_term_reference USING btree (term_object_type_id);
Date: 2026-06-26 22:12:41 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB SELECT * FROM pgbulkload.pg_bulkload ($1);[ Date: 2026-06-26 13:51:28 - 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-06-26 19:15:34 ]
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-06-26 19:15:34 ]
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-06-26 19:15:34 ]
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-06-26 19:15:34 ]
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-06-26 19:15:34 ]
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-06-26 19:15:34 ]
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-06-26 19:15:34 ]
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-06-26 19:15:34 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
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-06-26 19:15:35 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 64.68 sec Highest CPU-cost vacuum
Table pub2.gene_go_annot
Database ctdprd51 - 2026-06-26 17:35:15 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 64.68 sec Highest CPU-cost vacuum
Table pub2.gene_go_annot
Database ctdprd51 - 2026-06-26 17:35:15 Date
Analyzes per table
Key values
- pubc.log_query (13) Main table analyzed (database ctdprd51)
- 69 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 13 ctdprd51.pg_catalog.pg_class 3 ctdprd51.pub2.term 2 ctdprd51.pg_catalog.pg_index 2 ctdprd51.pg_catalog.pg_attribute 2 postgres.pg_catalog.pg_shdepend 2 ctdprd51.pub2.db 2 ctdprd51.edit.db_link 2 ctdprd51.edit.ixn_type 1 ctdprd51.pub2.db_report 1 ctdprd51.pub2.db_report_site 1 ctdprd51.pub1.term_set_enrichment 1 ctdprd51.edit.db 1 ctdprd51.edit.reference_db_link 1 ctdprd51.edit.list_db_report 1 ctdprd51.pub2.action_type 1 ctdprd51.load.data_load 1 ctdprd51.pg_catalog.pg_type 1 ctdprd51.pg_catalog.pg_shdepend 1 ctdprd51.pg_catalog.pg_depend 1 ctdprd51.pub2.reference_party 1 ctdprd51.edit.chem_conc_uom 1 ctdprd51.edit.action_type_path 1 ctdprd51.pub2.list_db_report 1 ctdprd51.edit.actor_form_type 1 ctdprd51.edit.db_report_site 1 ctdprd51.pub2.dag_edge 1 ctdprd51.pg_catalog.pg_trigger 1 ctdprd51.pub2.img 1 ctdprd51.pub2.chem_conc 1 ctdprd51.pub2.db_link 1 ctdprd51.pg_catalog.pg_attrdef 1 ctdprd51.pub2.chem_conc_anatomy 1 ctdprd51.edit.action_type 1 ctdprd51.pub2.dag_node 1 ctdprd51.pub2.reference_party_role 1 ctdprd51.pg_catalog.pg_description 1 ctdprd51.edit.object_note 1 ctdprd51.edit.db_report 1 ctdprd51.pub2.gene_go_annot 1 ctdprd51.pub2.gene_taxon 1 ctdprd51.edit.action_degree 1 ctdprd51.edit.evidence 1 ctdprd51.pg_catalog.pg_constraint 1 ctdprd51.edit.country 1 ctdprd51.pub2.term_pathway 1 ctdprd51.pub2.reference 1 ctdprd51.edit.exp_stressor_src_type 1 ctdprd51.pub2.term_label 1 Total 69 Vacuums per table
Key values
- pg_catalog.pg_class (3) Main table vacuumed on database ctdprd51
- 45 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pg_catalog.pg_class 3 3 1,126 0 135 0 13 460 123 541,637 ctdprd51.pub2.term 2 0 159,940 0 6 0 0 48,174 5 2,874,701 postgres.pg_catalog.pg_shdepend 1 1 175 0 49 0 0 95 39 144,496 ctdprd51.pub2.db_link 1 0 334,623 0 133,635 0 0 167,159 7 9,916,489 ctdprd51.pg_catalog.pg_attrdef 1 1 86 0 2 0 0 24 1 10,715 ctdprd51.pub2.img 1 0 1,108 0 5 0 0 524 2 44,419 ctdprd51.pub2.chem_conc 1 0 622 0 3 0 0 296 1 25,883 ctdprd51.pub2.reference_party_role 1 0 13,792 0 4 0 0 6,869 1 413,690 ctdprd51.pg_toast.pg_toast_1255 1 1 108 0 32 0 0 100 30 26,277 ctdprd51.pub2.chem_conc_anatomy 1 0 431 0 4 0 0 186 2 25,161 ctdprd51.edit.action_type 1 0 174 0 2 0 0 7 2 15,389 ctdprd51.pub2.dag_node 1 0 86,956 0 5 0 0 43,350 3 2,578,758 ctdprd51.pg_toast.pg_toast_486223 1 0 31 0 0 0 0 1 0 188 ctdprd51.pub2.gene_go_annot 1 0 684,202 0 265,156 0 0 341,979 12 20,272,234 ctdprd51.edit.db_report 1 0 96 0 1 0 0 9 1 9,452 ctdprd51.edit.object_note 1 1 191 0 6 0 0 32 4 26,440 ctdprd51.pg_catalog.pg_description 1 1 249 0 29 0 42 119 19 81,898 ctdprd51.edit.action_degree 1 0 45 0 0 0 0 12 1 9,451 ctdprd51.pub2.db 1 1 150 0 16 0 0 20 10 37,477 ctdprd51.pub2.gene_taxon 1 0 190,166 0 6 0 0 95,022 4 5,636,195 ctdprd51.pg_catalog.pg_constraint 1 1 315 0 22 0 0 127 22 102,898 ctdprd51.pg_toast.pg_toast_11672013 1 0 90,808 0 4 0 0 45,396 2 2,694,887 ctdprd51.pub2.term_pathway 1 0 3,331 0 3 0 0 1,614 1 103,645 ctdprd51.edit.country 1 0 63 0 0 0 0 8 1 9,627 ctdprd51.edit.db_link 1 0 7,729 0 3 0 0 3,735 1 228,760 ctdprd51.pub2.term_label 1 0 240,606 0 22,276 0 0 120,248 5 7,131,658 ctdprd51.pg_catalog.pg_statistic 1 1 857 0 80 0 117 627 85 275,816 ctdprd51.pub2.reference 1 0 79,039 0 5 0 0 39,409 3 2,346,051 ctdprd51.pubc.log_query 1 1 200 0 18 0 0 47 12 90,656 ctdprd51.edit.reference_db_link 1 0 5,809 0 1,690 0 0 3,735 1 228,691 ctdprd51.pg_toast.pg_toast_2619 1 1 4,573 0 1,222 0 10,347 3,482 1,117 557,798 ctdprd51.pg_catalog.pg_attribute 1 1 812 0 86 0 53 409 85 407,343 ctdprd51.pg_catalog.pg_index 1 1 203 0 18 0 0 112 14 59,638 ctdprd51.pg_catalog.pg_depend 1 1 756 0 87 0 65 371 95 368,390 ctdprd51.pub2.reference_party 1 0 5,170 0 3 0 0 2,551 1 158,928 ctdprd51.pg_catalog.pg_type 1 1 160 0 9 0 0 71 9 22,798 ctdprd51.edit.geographic_region 1 0 64 0 2 0 0 3 2 13,633 ctdprd51.edit.action_type_path 1 0 48 0 0 0 0 4 1 9,059 ctdprd51.pub2.dag_edge 1 0 1,053 0 5 0 0 482 2 40,873 ctdprd51.edit.actor_form_type 1 0 81 0 1 0 0 2 1 8,490 ctdprd51.edit.db_report_site 1 1 74 0 3 0 0 15 2 9,983 ctdprd51.pg_catalog.pg_trigger 1 1 404 0 35 0 0 157 37 153,795 Total 45 19 1,916,426 2,583 424,668 0 10,637 927,043 1,766 57,714,367 Tuples removed per table
Key values
- pg_toast.pg_toast_2619 (4345) Main table with removed tuples on database ctdprd51
- 12944 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pg_toast.pg_toast_2619 1 1 4,345 21,049 0 0 12,592 ctdprd51.pg_catalog.pg_depend 1 1 1,834 13,732 0 0 153 ctdprd51.pg_catalog.pg_description 1 1 1,202 5,356 0 15 75 ctdprd51.pg_catalog.pg_statistic 1 1 978 2,820 0 0 410 ctdprd51.pg_catalog.pg_attribute 1 1 928 8,697 0 0 236 ctdprd51.pg_catalog.pg_class 3 3 651 5,818 0 0 282 ctdprd51.pg_catalog.pg_trigger 1 1 583 1,889 0 0 58 postgres.pg_catalog.pg_shdepend 1 1 511 1,692 0 0 22 ctdprd51.pg_catalog.pg_index 1 1 339 1,187 0 0 39 ctdprd51.pg_catalog.pg_constraint 1 1 196 910 0 0 40 ctdprd51.pg_catalog.pg_type 1 1 180 1,169 0 0 35 ctdprd51.edit.object_note 1 1 168 169 0 0 10 ctdprd51.edit.country 1 0 163 249 0 0 4 ctdprd51.pub2.db 1 1 134 134 0 0 7 ctdprd51.edit.action_type_path 1 0 106 106 0 0 2 ctdprd51.edit.db_report 1 0 97 162 0 0 4 ctdprd51.edit.action_degree 1 0 96 219 0 0 6 ctdprd51.edit.db_report_site 1 1 91 164 0 0 5 ctdprd51.pg_toast.pg_toast_1255 1 1 81 12 0 0 33 ctdprd51.pg_catalog.pg_attrdef 1 1 71 238 0 0 11 ctdprd51.edit.geographic_region 1 0 67 51 0 0 1 ctdprd51.edit.action_type 1 0 64 60 0 0 3 ctdprd51.edit.actor_form_type 1 0 54 18 0 0 1 ctdprd51.pubc.log_query 1 1 5 508 0 0 19 ctdprd51.pub2.db_link 1 0 0 23,045,859 0 0 167,158 ctdprd51.pub2.img 1 0 0 50,656 0 0 523 ctdprd51.pub2.chem_conc 1 0 0 9,118 0 0 295 ctdprd51.pub2.reference_party_role 1 0 0 1,270,411 0 0 6,868 ctdprd51.pub2.chem_conc_anatomy 1 0 0 19,683 0 0 185 ctdprd51.pub2.dag_node 1 0 0 1,814,565 0 0 43,349 ctdprd51.pg_toast.pg_toast_486223 1 0 0 0 0 0 0 ctdprd51.pub2.gene_go_annot 1 0 0 53,691,565 0 0 341,978 ctdprd51.pub2.gene_taxon 1 0 0 14,918,288 0 0 95,021 ctdprd51.pg_toast.pg_toast_11672013 1 0 0 245,385 0 0 45,395 ctdprd51.pub2.term_pathway 1 0 0 135,792 0 0 1,613 ctdprd51.edit.db_link 1 0 0 335,011 0 0 3,734 ctdprd51.pub2.term_label 1 0 0 8,421,969 0 0 120,247 ctdprd51.pub2.reference 1 0 0 202,907 0 0 39,408 ctdprd51.edit.reference_db_link 1 0 0 335,011 0 0 3,734 ctdprd51.pub2.term 2 0 0 2,257,288 0 0 84,014 ctdprd51.pub2.reference_party 1 0 0 456,471 0 0 2,550 ctdprd51.pub2.dag_edge 1 0 0 88,931 0 0 481 Total 45 19 12,944 107,365,319 0 15 970,601 Pages removed per table
Key values
- pg_catalog.pg_description (15) Main table with removed pages on database ctdprd51
- 15 pages Total removed
Table Number of vacuums Index scans Tuples removed Pages removed ctdprd51.pg_catalog.pg_description 1 1 1202 15 postgres.pg_catalog.pg_shdepend 1 1 511 0 ctdprd51.pub2.db_link 1 0 0 0 ctdprd51.pg_catalog.pg_attrdef 1 1 71 0 ctdprd51.pub2.img 1 0 0 0 ctdprd51.pub2.chem_conc 1 0 0 0 ctdprd51.pub2.reference_party_role 1 0 0 0 ctdprd51.pg_toast.pg_toast_1255 1 1 81 0 ctdprd51.pub2.chem_conc_anatomy 1 0 0 0 ctdprd51.edit.action_type 1 0 64 0 ctdprd51.pub2.dag_node 1 0 0 0 ctdprd51.pg_toast.pg_toast_486223 1 0 0 0 ctdprd51.pg_catalog.pg_class 3 3 651 0 ctdprd51.pub2.gene_go_annot 1 0 0 0 ctdprd51.edit.db_report 1 0 97 0 ctdprd51.edit.object_note 1 1 168 0 ctdprd51.edit.action_degree 1 0 96 0 ctdprd51.pub2.db 1 1 134 0 ctdprd51.pub2.gene_taxon 1 0 0 0 ctdprd51.pg_catalog.pg_constraint 1 1 196 0 ctdprd51.pg_toast.pg_toast_11672013 1 0 0 0 ctdprd51.pub2.term_pathway 1 0 0 0 ctdprd51.edit.country 1 0 163 0 ctdprd51.edit.db_link 1 0 0 0 ctdprd51.pub2.term_label 1 0 0 0 ctdprd51.pg_catalog.pg_statistic 1 1 978 0 ctdprd51.pub2.reference 1 0 0 0 ctdprd51.pubc.log_query 1 1 5 0 ctdprd51.edit.reference_db_link 1 0 0 0 ctdprd51.pg_toast.pg_toast_2619 1 1 4345 0 ctdprd51.pub2.term 2 0 0 0 ctdprd51.pg_catalog.pg_attribute 1 1 928 0 ctdprd51.pg_catalog.pg_index 1 1 339 0 ctdprd51.pg_catalog.pg_depend 1 1 1834 0 ctdprd51.pub2.reference_party 1 0 0 0 ctdprd51.pg_catalog.pg_type 1 1 180 0 ctdprd51.edit.geographic_region 1 0 67 0 ctdprd51.edit.action_type_path 1 0 106 0 ctdprd51.pub2.dag_edge 1 0 0 0 ctdprd51.edit.actor_form_type 1 0 54 0 ctdprd51.edit.db_report_site 1 1 91 0 ctdprd51.pg_catalog.pg_trigger 1 1 583 0 Total 45 19 12,944 15 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Jun 26 00 2 0 01 0 1 02 0 1 03 0 2 04 0 1 05 1 3 06 0 0 07 0 0 08 0 1 09 0 1 10 12 12 11 1 2 12 10 17 13 0 0 14 2 3 15 0 1 16 3 8 17 14 13 18 0 0 19 0 0 20 0 0 21 0 0 22 0 2 23 0 1 - 64.68 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- AccessShareLock Main Lock Type
- 1 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query 1 1 2m8s 2m8s 2m8s 2m8s lock table edit.db_link in access share mode;-
LOCK TABLE edit.db_link IN ACCESS SHARE MODE;
Date: 2026-06-26 14:02:10
-
LOCK TABLE edit.db_link IN ACCESS SHARE MODE;
Date: 2026-06-26 14:02:10
Queries that waited the most
Rank Wait time Query 1 2m8s LOCK TABLE edit.db_link IN ACCESS SHARE MODE;[ Date: 2026-06-26 14:02:10 ]
-
Queries
Queries by type
Key values
- 114 Total read queries
- 73 Total write queries
Queries by database
Key values
- unknown Main database
- 153 Requests
- 4h35m38s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 182 Requests
User Request type Count Duration editeu Total 1 10s643ms select 1 10s643ms load Total 18 1h33s select 18 1h33s postgres Total 15 17m18s copy to 15 17m18s pub2 Total 2 16m55s insert 1 16m47s select 1 8s671ms pubc Total 1 9m17s select 1 9m17s pubeu Total 23 3m19s select 23 3m19s unknown Total 182 4h41m42s copy to 57 12m8s ddl 25 26m42s insert 10 44m50s others 7 5m56s select 82 3h11m51s tcl 1 11s486ms Duration by user
Key values
- 4h41m42s (unknown) Main time consuming user
User Request type Count Duration editeu Total 1 10s643ms select 1 10s643ms load Total 18 1h33s select 18 1h33s postgres Total 15 17m18s copy to 15 17m18s pub2 Total 2 16m55s insert 1 16m47s select 1 8s671ms pubc Total 1 9m17s select 1 9m17s pubeu Total 23 3m19s select 23 3m19s unknown Total 182 4h41m42s copy to 57 12m8s ddl 25 26m42s insert 10 44m50s others 7 5m56s select 82 3h11m51s tcl 1 11s486ms Queries by host
Key values
- unknown Main host
- 242 Requests
- 6h29m17s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 174 Requests
- 5h43m32s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-06-26 22:30:42 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 143 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 1h8m14s 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-06-26 21:19:33 - Bind query: yes ]
2 51m46s 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-06-26 20:11:12 - Bind query: yes ]
3 35m8s 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-06-26 18:18:52 - Database: ctdprd51 - User: load - Bind query: yes ]
4 29m19s 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-06-26 17:34:09 - Bind query: yes ]
5 16m47s 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-06-26 16:58:19 - Database: ctdprd51 - User: pub2 - Bind query: yes ]
6 12m45s 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-06-26 18:34:31 - Database: ctdprd51 - User: load - Bind query: yes ]
7 9m17s /* * 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-06-26 00:09:19 - Database: ctdprd51 - User: pubc - Application: psql ]
8 7m37s 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-06-26 21:46:42 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
9 7m4s 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-06-26 21:32:33 - Bind query: yes ]
10 5m34s 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-06-26 13:18:56 - Bind query: yes ]
11 5m24s 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-06-26 22:24:22 - Bind query: yes ]
12 5m18s 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-06-26 17:39:28 - Bind query: yes ]
13 4m47s 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-06-26 21:52:55 - Bind query: yes ]
14 4m15s 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-06-26 17:04:49 - Bind query: yes ]
15 3m11s 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-06-26 14:02:10 - Bind query: yes ]
16 3m1s CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);[ Date: 2026-06-26 22:10:29 - Bind query: yes ]
17 2m27s CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);[ Date: 2026-06-26 22:03:48 - Bind query: yes ]
18 2m26s vacuum FULL analyze db_link;[ Date: 2026-06-26 14:53:05 ]
19 2m16s 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-06-26 21:36:03 - Bind query: yes ]
20 2m10s 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/DO/output/dbLink.txt.log,parse-badfile=/home/load/ctdLoadData/misc/DO/output/dbLink.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/misc/DO/output/dbLink.txt.DUPE}');[ Date: 2026-06-26 14:07:54 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 1h22m8s 12 16s461ms 1h8m14s 6m50s 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 Jun 26 21 12 1h22m8s 6m50s -
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-06-26 21:19:33 Duration: 1h8m14s 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-06-26 21:32:33 Duration: 7m4s 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-06-26 21:25:29 Duration: 1m40s Bind query: yes
2 57m59s 59 5s306ms 7m37s 58s976ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jun 26 12 10 1m26s 8s620ms 13 28 27m20s 58s589ms 14 3 7m32s 2m30s 18 3 1m10s 23s558ms 21 3 9m2s 3m 22 4 7m14s 1m48s 23 8 4m13s 31s694ms [ User: load - Total duration: 11m36s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m36s - 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-06-26 21:46:42 Duration: 7m37s 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-06-26 13:18:56 Duration: 5m34s 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-06-26 22:24:22 Duration: 5m24s Bind query: yes
3 51m46s 1 51m46s 51m46s 51m46s 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 Jun 26 20 1 51m46s 51m46s -
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-06-26 20:11:12 Duration: 51m46s Bind query: yes
4 35m8s 1 35m8s 35m8s 35m8s 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 Jun 26 18 1 35m8s 35m8s [ User: load - Total duration: 35m8s - 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-06-26 18:18:52 Duration: 35m8s Database: ctdprd51 User: load Bind query: yes
5 29m19s 1 29m19s 29m19s 29m19s 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 Jun 26 17 1 29m19s 29m19s -
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-06-26 17:34:09 Duration: 29m19s Bind query: yes
6 16m51s 6 5s8ms 12m45s 2m48s 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 Jun 26 18 4 14m54s 3m43s 19 2 1m56s 58s252ms [ User: load - Total duration: 12m45s - 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-06-26 18:34:31 Duration: 12m45s 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-06-26 18:52:55 Duration: 1m2s Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from 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-06-26 18:59:24 Duration: 1m1s Bind query: yes
7 16m47s 1 16m47s 16m47s 16m47s 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 Jun 26 16 1 16m47s 16m47s [ User: pub2 - Total duration: 16m47s - 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-06-26 16:58:19 Duration: 16m47s Database: ctdprd51 User: pub2 Bind query: yes
8 9m17s 1 9m17s 9m17s 9m17s select maint_query_logs_archive ();Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jun 26 00 1 9m17s 9m17s [ User: pubc - Total duration: 9m17s - Times executed: 1 ]
[ Application: psql - Total duration: 9m17s - 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-06-26 00:09:19 Duration: 9m17s Database: ctdprd51 User: pubc Application: psql
9 7m31s 4 1m52s 1m53s 1m52s 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 #9
Day Hour Count Duration Avg duration Jun 26 06 1 1m52s 1m52s 10 1 1m52s 1m52s 14 1 1m53s 1m53s 18 1 1m52s 1m52s [ User: postgres - Total duration: 7m31s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m31s - 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-06-26 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-06-26 06:06:54 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-06-26 10:06:54 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
10 5m18s 1 5m18s 5m18s 5m18s 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 #10
Day Hour Count Duration Avg duration Jun 26 17 1 5m18s 5m18s -
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-06-26 17:39:28 Duration: 5m18s Bind query: yes
11 4m47s 1 4m47s 4m47s 4m47s 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 #11
Day Hour Count Duration Avg duration Jun 26 21 1 4m47s 4m47s -
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-06-26 21:52:55 Duration: 4m47s 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-06-26 21:52:54 Duration: 0ms Database: ctdprd51 User: pub2
12 4m15s 1 4m15s 4m15s 4m15s 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 #12
Day Hour Count Duration Avg duration Jun 26 17 1 4m15s 4m15s -
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-06-26 17:04:49 Duration: 4m15s Bind query: yes
13 3m1s 1 3m1s 3m1s 3m1s create index ix_gene_disease_ref_net_sc on pub2.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jun 26 22 1 3m1s 3m1s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-06-26 22:10:29 Duration: 3m1s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-06-26 22:10:29 Duration: 0ms
14 2m27s 1 2m27s 2m27s 2m27s create index ix_gene_disease_ref_dis_gene on pub2.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jun 26 22 1 2m27s 2m27s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-06-26 22:03:48 Duration: 2m27s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-06-26 22:03:47 Duration: 0ms
15 2m26s 1 2m26s 2m26s 2m26s vacuum full analyze db_link;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jun 26 14 1 2m26s 2m26s -
vacuum FULL analyze db_link;
Date: 2026-06-26 14:53:05 Duration: 2m26s
-
vacuum FULL analyze db_link;
Date: 2026-06-26 14:51:05 Duration: 0ms
16 2m16s 1 2m16s 2m16s 2m16s 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 #16
Day Hour Count Duration Avg duration Jun 26 21 1 2m16s 2m16s -
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-06-26 21:36:03 Duration: 2m16s Bind query: yes
17 2m8s 1 2m8s 2m8s 2m8s lock table edit.db_link in access share mode;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jun 26 14 1 2m8s 2m8s -
LOCK TABLE edit.db_link IN ACCESS SHARE MODE;
Date: 2026-06-26 14:02:10 Duration: 2m8s
-
LOCK TABLE edit.db_link IN ACCESS SHARE MODE;
Date: 2026-06-26 14:02:10 Duration: 0ms
18 2m2s 1 2m2s 2m2s 2m2s 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 #18
Day Hour Count Duration Avg duration Jun 26 17 1 2m2s 2m2s -
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-06-26 17:00:34 Duration: 2m2s Bind query: yes
19 1m55s 1 1m55s 1m55s 1m55s create index ix_gene_disease_ref_chem on pub2.gene_disease_reference using btree (via_chem_id);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jun 26 21 1 1m55s 1m55s -
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2026-06-26 21:57:42 Duration: 1m55s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2026-06-26 21:57:41 Duration: 0ms
20 1m52s 1 1m52s 1m52s 1m52s create index ix_gene_disease_ref_disease on pub2.gene_disease_reference using btree (disease_id);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jun 26 21 1 1m52s 1m52s -
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2026-06-26 21:59:35 Duration: 1m52s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2026-06-26 21:59:34 Duration: 0ms
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 59 57m59s 5s306ms 7m37s 58s976ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jun 26 12 10 1m26s 8s620ms 13 28 27m20s 58s589ms 14 3 7m32s 2m30s 18 3 1m10s 23s558ms 21 3 9m2s 3m 22 4 7m14s 1m48s 23 8 4m13s 31s694ms [ User: load - Total duration: 11m36s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m36s - 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-06-26 21:46:42 Duration: 7m37s 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-06-26 13:18:56 Duration: 5m34s 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-06-26 22:24:22 Duration: 5m24s Bind query: yes
2 12 1h22m8s 16s461ms 1h8m14s 6m50s 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 Jun 26 21 12 1h22m8s 6m50s -
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-06-26 21:19:33 Duration: 1h8m14s 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-06-26 21:32:33 Duration: 7m4s 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-06-26 21:25:29 Duration: 1m40s Bind query: yes
3 6 16m51s 5s8ms 12m45s 2m48s 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 #3
Day Hour Count Duration Avg duration Jun 26 18 4 14m54s 3m43s 19 2 1m56s 58s252ms [ User: load - Total duration: 12m45s - 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-06-26 18:34:31 Duration: 12m45s 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-06-26 18:52:55 Duration: 1m2s Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from 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-06-26 18:59:24 Duration: 1m1s Bind query: yes
4 4 7m31s 1m52s 1m53s 1m52s 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 #4
Day Hour Count Duration Avg duration Jun 26 06 1 1m52s 1m52s 10 1 1m52s 1m52s 14 1 1m53s 1m53s 18 1 1m52s 1m52s [ User: postgres - Total duration: 7m31s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m31s - 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-06-26 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-06-26 06:06:54 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-06-26 10:06:54 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
5 4 1m36s 24s71ms 24s420ms 24s182ms 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 #5
Day Hour Count Duration Avg duration Jun 26 06 1 24s71ms 24s71ms 10 1 24s145ms 24s145ms 14 1 24s420ms 24s420ms 18 1 24s93ms 24s93ms -
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-06-26 14:07:19 Duration: 24s420ms
-
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-06-26 10:07:18 Duration: 24s145ms
-
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-06-26 18:07:18 Duration: 24s93ms
6 4 1m14s 15s799ms 20s3ms 18s743ms 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 #6
Day Hour Count Duration Avg duration Jun 26 06 1 19s679ms 19s679ms 10 1 19s491ms 19s491ms 14 1 15s799ms 15s799ms 18 1 20s3ms 20s3ms [ User: postgres - Total duration: 59s174ms - Times executed: 3 ]
[ Application: pg_dump - Total duration: 59s174ms - Times executed: 3 ]
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-06-26 18:00:22 Duration: 20s3ms 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-06-26 06:00:21 Duration: 19s679ms 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-06-26 10:00:21 Duration: 19s491ms Database: ctdprd51 User: postgres Application: pg_dump
7 4 1m2s 15s443ms 15s582ms 15s504ms copy pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jun 26 06 1 15s443ms 15s443ms 10 1 15s525ms 15s525ms 14 1 15s582ms 15s582ms 18 1 15s465ms 15s465ms -
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-06-26 14:07:35 Duration: 15s582ms
-
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-06-26 10:07:34 Duration: 15s525ms
-
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-06-26 18:07:34 Duration: 15s465ms
8 4 59s593ms 14s790ms 15s25ms 14s898ms copy edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jun 26 06 1 14s964ms 14s964ms 10 1 14s813ms 14s813ms 14 1 15s25ms 15s25ms 18 1 14s790ms 14s790ms -
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-06-26 14:02:57 Duration: 15s25ms
-
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-06-26 06:00:52 Duration: 14s964ms
-
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-06-26 10:00:52 Duration: 14s813ms
9 4 57s973ms 14s467ms 14s557ms 14s493ms copy edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jun 26 06 1 14s469ms 14s469ms 10 1 14s467ms 14s467ms 14 1 14s557ms 14s557ms 18 1 14s478ms 14s478ms -
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-06-26 14:03:12 Duration: 14s557ms
-
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-06-26 18:01:07 Duration: 14s478ms
-
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-06-26 06:01:07 Duration: 14s469ms
10 4 29s985ms 7s464ms 7s548ms 7s496ms 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 #10
Day Hour Count Duration Avg duration Jun 26 06 1 7s548ms 7s548ms 10 1 7s465ms 7s465ms 14 1 7s507ms 7s507ms 18 1 7s464ms 7s464ms -
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-06-26 06:00:31 Duration: 7s548ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-06-26 14:02:35 Duration: 7s507ms
-
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-06-26 10:00:30 Duration: 7s465ms
11 4 25s816ms 6s431ms 6s468ms 6s454ms 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 #11
Day Hour Count Duration Avg duration Jun 26 06 1 6s456ms 6s456ms 10 1 6s431ms 6s431ms 14 1 6s468ms 6s468ms 18 1 6s459ms 6s459ms -
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-06-26 14:03:20 Duration: 6s468ms
-
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-06-26 18:01:15 Duration: 6s459ms
-
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-06-26 06:01:15 Duration: 6s456ms
12 4 24s727ms 6s154ms 6s205ms 6s181ms 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 #12
Day Hour Count Duration Avg duration Jun 26 06 1 6s196ms 6s196ms 10 1 6s171ms 6s171ms 14 1 6s205ms 6s205ms 18 1 6s154ms 6s154ms -
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-06-26 14:02:42 Duration: 6s205ms
-
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-06-26 06:00:37 Duration: 6s196ms
-
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-06-26 10:00:37 Duration: 6s171ms
13 4 21s289ms 5s122ms 5s735ms 5s322ms select coalesce(st.alt_nm, t.nm) slimtermnm, ( select count(*) from slim_term_mapping stm inner join chem_disease cd on cd.disease_id = stm.mapped_term_id where cd.chem_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) and stm.slim_term_id = st.slim_term_id and cd.curated_reference_qty > ?) curatedcount, ( select count(*) from slim_term_mapping stm inner join chem_disease cd on cd.disease_id = stm.mapped_term_id where cd.chem_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) and stm.slim_term_id = st.slim_term_id and cd.indirect_gene_qty > ?) inferredcount from slim_term st inner join term t on st.slim_term_id = t.id where st.slim_id = ? order by ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jun 26 22 1 5s189ms 5s189ms 23 3 16s100ms 5s366ms [ User: pubeu - Total duration: 16s46ms - Times executed: 3 ]
-
SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1497516') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1497516') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;
Date: 2026-06-26 23:01:51 Duration: 5s735ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1496939') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1496939') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;
Date: 2026-06-26 23:07:03 Duration: 5s242ms Bind query: yes
-
SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1458788') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1458788') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;
Date: 2026-06-26 22:44:29 Duration: 5s189ms Database: ctdprd51 User: pubeu Bind query: yes
14 3 43s535ms 14s53ms 15s225ms 14s511ms 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 #14
Day Hour Count Duration Avg duration Jun 26 03 3 43s535ms 14s511ms [ User: pubeu - Total duration: 14s53ms - Times executed: 1 ]
-
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 = '2195504') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-06-26 03:55:08 Duration: 15s225ms 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 = '2195504') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-06-26 03:51:33 Duration: 14s257ms 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 = '2195504') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-06-26 03:38:11 Duration: 14s53ms Database: ctdprd51 User: pubeu Bind query: yes
15 3 23s556ms 7s812ms 7s877ms 7s852ms select ia.object_nm, ia.acc_txt, ia.acc_db_id, t.nm, t.acc_txt, t.acc_db_id from ixn_actor ia, load.term t where ia.object_type_id = t.object_type_id and ia.acc_txt = t.acc_txt and ia.acc_db_id <> t.acc_db_id -- should be zero rows returned;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jun 26 14 3 23s556ms 7s852ms -
select ia.object_nm, ia.acc_txt, ia.acc_db_id, t.nm, t.acc_txt, t.acc_db_id from ixn_actor ia, load.term t where ia.object_type_id = t.object_type_id and ia.acc_txt = t.acc_txt and ia.acc_db_id <> t.acc_db_id -- Update SQL is here: https://my.ctdbase.org/download/attachments/34275461/updateIxnActorJoin.sql -- Should be zero rows returned;
Date: 2026-06-26 14:27:01 Duration: 7s877ms
-
select ia.object_nm, ia.acc_txt, ia.acc_db_id, t.nm, t.acc_txt, t.acc_db_id from ixn_actor ia, load.term t where ia.object_type_id = t.object_type_id and ia.acc_txt = t.acc_txt and ia.acc_db_id <> t.acc_db_id -- Update SQL is here: https://my.ctdbase.org/download/attachments/34275461/updateIxnActorJoin.sql -- Should be zero rows returned;
Date: 2026-06-26 14:28:44 Duration: 7s866ms
-
select ia.object_nm, ia.acc_txt, ia.acc_db_id, t.nm, t.acc_txt, t.acc_db_id from ixn_actor ia, load.term t where ia.object_type_id = t.object_type_id and ia.acc_txt = t.acc_txt and ia.acc_db_id <> t.acc_db_id -- Update SQL is here: https://my.ctdbase.org/download/attachments/34275461/updateIxnActorJoin.sql -- Should be zero rows returned;
Date: 2026-06-26 14:33:23 Duration: 7s812ms
16 2 54s996ms 26s917ms 28s79ms 27s498ms vacuum full analyze ixn_actor;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jun 26 14 2 54s996ms 27s498ms -
vacuum FULL analyze ixn_actor;
Date: 2026-06-26 14:49:36 Duration: 28s79ms
-
vacuum FULL analyze ixn_actor;
Date: 2026-06-26 14:50:28 Duration: 26s917ms
-
vacuum FULL analyze ixn_actor;
Date: 2026-06-26 14:49:15 Duration: 0ms
17 2 20s740ms 10s314ms 10s426ms 10s370ms 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 #17
Day Hour Count Duration Avg duration Jun 26 02 2 20s740ms 10s370ms [ User: pubeu - Total duration: 10s426ms - Times executed: 1 ]
-
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 = '2195410') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 50;
Date: 2026-06-26 02:58:59 Duration: 10s426ms 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 = '2195410') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 350;
Date: 2026-06-26 02:59:23 Duration: 10s314ms Bind query: yes
18 2 11s162ms 5s534ms 5s628ms 5s581ms select d.abbr dagabbr, d.nm dagnm, gt.level_min_no daglevelmin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pvalcorrected, te.raw_p_val pvalraw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, count(*) over () fullrowcount from term_enrichment te inner join dag_node gt on te.enriched_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where te.term_id = ? and te.enriched_object_type_id = ? order by te.corrected_p_val, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jun 26 01 1 5s628ms 5s628ms 02 1 5s534ms 5s534ms [ User: pubeu - Total duration: 11s162ms - Times executed: 2 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1500714' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-06-26 01:51:20 Duration: 5s628ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1410231' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-06-26 02:39:31 Duration: 5s534ms Database: ctdprd51 User: pubeu Bind query: yes
19 1 51m46s 51m46s 51m46s 51m46s 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 #19
Day Hour Count Duration Avg duration Jun 26 20 1 51m46s 51m46s -
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-06-26 20:11:12 Duration: 51m46s Bind query: yes
20 1 35m8s 35m8s 35m8s 35m8s 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 #20
Day Hour Count Duration Avg duration Jun 26 18 1 35m8s 35m8s [ User: load - Total duration: 35m8s - 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-06-26 18:18:52 Duration: 35m8s Database: ctdprd51 User: load Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 51m46s 51m46s 51m46s 1 51m46s 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 Jun 26 20 1 51m46s 51m46s -
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-06-26 20:11:12 Duration: 51m46s Bind query: yes
2 35m8s 35m8s 35m8s 1 35m8s 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 Jun 26 18 1 35m8s 35m8s [ User: load - Total duration: 35m8s - 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-06-26 18:18:52 Duration: 35m8s Database: ctdprd51 User: load Bind query: yes
3 29m19s 29m19s 29m19s 1 29m19s 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 Jun 26 17 1 29m19s 29m19s -
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-06-26 17:34:09 Duration: 29m19s Bind query: yes
4 16m47s 16m47s 16m47s 1 16m47s 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 Jun 26 16 1 16m47s 16m47s [ User: pub2 - Total duration: 16m47s - 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-06-26 16:58:19 Duration: 16m47s Database: ctdprd51 User: pub2 Bind query: yes
5 9m17s 9m17s 9m17s 1 9m17s select maint_query_logs_archive ();Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jun 26 00 1 9m17s 9m17s [ User: pubc - Total duration: 9m17s - Times executed: 1 ]
[ Application: psql - Total duration: 9m17s - 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-06-26 00:09:19 Duration: 9m17s Database: ctdprd51 User: pubc Application: psql
6 16s461ms 1h8m14s 6m50s 12 1h22m8s 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 Jun 26 21 12 1h22m8s 6m50s -
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-06-26 21:19:33 Duration: 1h8m14s 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-06-26 21:32:33 Duration: 7m4s 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-06-26 21:25:29 Duration: 1m40s Bind query: yes
7 5m18s 5m18s 5m18s 1 5m18s 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 Jun 26 17 1 5m18s 5m18s -
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-06-26 17:39:28 Duration: 5m18s Bind query: yes
8 4m47s 4m47s 4m47s 1 4m47s 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 #8
Day Hour Count Duration Avg duration Jun 26 21 1 4m47s 4m47s -
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-06-26 21:52:55 Duration: 4m47s 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-06-26 21:52:54 Duration: 0ms Database: ctdprd51 User: pub2
9 4m15s 4m15s 4m15s 1 4m15s 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 #9
Day Hour Count Duration Avg duration Jun 26 17 1 4m15s 4m15s -
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-06-26 17:04:49 Duration: 4m15s Bind query: yes
10 3m1s 3m1s 3m1s 1 3m1s 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 Jun 26 22 1 3m1s 3m1s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-06-26 22:10:29 Duration: 3m1s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2026-06-26 22:10:29 Duration: 0ms
11 5s8ms 12m45s 2m48s 6 16m51s 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 #11
Day Hour Count Duration Avg duration Jun 26 18 4 14m54s 3m43s 19 2 1m56s 58s252ms [ User: load - Total duration: 12m45s - 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-06-26 18:34:31 Duration: 12m45s 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-06-26 18:52:55 Duration: 1m2s Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from 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-06-26 18:59:24 Duration: 1m1s Bind query: yes
12 2m27s 2m27s 2m27s 1 2m27s create index ix_gene_disease_ref_dis_gene on pub2.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jun 26 22 1 2m27s 2m27s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-06-26 22:03:48 Duration: 2m27s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2026-06-26 22:03:47 Duration: 0ms
13 2m26s 2m26s 2m26s 1 2m26s vacuum full analyze db_link;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jun 26 14 1 2m26s 2m26s -
vacuum FULL analyze db_link;
Date: 2026-06-26 14:53:05 Duration: 2m26s
-
vacuum FULL analyze db_link;
Date: 2026-06-26 14:51:05 Duration: 0ms
14 2m16s 2m16s 2m16s 1 2m16s 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 Jun 26 21 1 2m16s 2m16s -
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-06-26 21:36:03 Duration: 2m16s Bind query: yes
15 2m8s 2m8s 2m8s 1 2m8s lock table edit.db_link in access share mode;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jun 26 14 1 2m8s 2m8s -
LOCK TABLE edit.db_link IN ACCESS SHARE MODE;
Date: 2026-06-26 14:02:10 Duration: 2m8s
-
LOCK TABLE edit.db_link IN ACCESS SHARE MODE;
Date: 2026-06-26 14:02:10 Duration: 0ms
16 2m2s 2m2s 2m2s 1 2m2s 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 #16
Day Hour Count Duration Avg duration Jun 26 17 1 2m2s 2m2s -
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-06-26 17:00:34 Duration: 2m2s Bind query: yes
17 1m55s 1m55s 1m55s 1 1m55s create index ix_gene_disease_ref_chem on pub2.gene_disease_reference using btree (via_chem_id);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jun 26 21 1 1m55s 1m55s -
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2026-06-26 21:57:42 Duration: 1m55s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2026-06-26 21:57:41 Duration: 0ms
18 1m52s 1m52s 1m52s 1 1m52s create index ix_gene_disease_ref_disease on pub2.gene_disease_reference using btree (disease_id);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jun 26 21 1 1m52s 1m52s -
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2026-06-26 21:59:35 Duration: 1m52s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2026-06-26 21:59:34 Duration: 0ms
19 1m52s 1m53s 1m52s 4 7m31s 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 #19
Day Hour Count Duration Avg duration Jun 26 06 1 1m52s 1m52s 10 1 1m52s 1m52s 14 1 1m53s 1m53s 18 1 1m52s 1m52s [ User: postgres - Total duration: 7m31s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m31s - 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-06-26 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-06-26 06:06:54 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-06-26 10:06:54 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
20 5s306ms 7m37s 58s976ms 59 57m59s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jun 26 12 10 1m26s 8s620ms 13 28 27m20s 58s589ms 14 3 7m32s 2m30s 18 3 1m10s 23s558ms 21 3 9m2s 3m 22 4 7m14s 1m48s 23 8 4m13s 31s694ms [ User: load - Total duration: 11m36s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m36s - 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-06-26 21:46:42 Duration: 7m37s 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-06-26 13:18:56 Duration: 5m34s 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-06-26 22:24:22 Duration: 5m24s 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
- 13,053 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
- 10 ERROR entries
- 0 WARNING entries
- 3 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 2 Max number of times the same event was reported
- 15 Total events found
Rank Times reported Error 1 2 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #1
Day Hour Count Jun 26 21 1 22 1 - ERROR: canceling statement due to user request
Statement: SELECT pg_database_size(datname::text) FROM pg_catalog.pg_database WHERE datistemplate = false AND datname = $1;
Date: 2026-06-26 21:49:08
2 2 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #2
Day Hour Count Jun 26 21 1 22 1 3 2 LOG: could not send data to client: Broken pipe
Times Reported Most Frequent Error / Event #3
Day Hour Count Jun 26 21 1 22 1 4 2 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #4
Day Hour Count Jun 26 12 1 14 1 - ERROR: syntax error at or near "select" at character 74
- ERROR: syntax error at or near "," at character 39
Statement: SELECT * FROM edit.reference_ixn WHERE taxon_acc_txt = '134510' 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-06-26 12:51:20
Statement: select * from load.img where url in ( ,'/0/80294-22-0.png' ,'/3/112444-82-3.png' ,'/3/79620-36-3.png' ,'/9/72779-40-9.png' )
Date: 2026-06-26 14:17:30
5 2 ERROR: unterminated quoted identifier at or near ""..."
Times Reported Most Frequent Error / Event #5
Day Hour Count Jun 26 09 1 12 1 - ERROR: unterminated quoted identifier at or near "" -- This provides discrepancies introduced this month - it is NOT aggregate select nm as Underlying_Term_Name ,acc_txt as Underlying_Term_Accession ,synonym ,reference_acc_txt ,notes ,create_by ,create_tm from edit.term_label where ( acc_txt, object_type_id ) not in ( -- Current Month's PUB select acc_txt, object_type_id from pub1.term ) order by acc_txt " at character 1
- ERROR: unterminated quoted identifier at or near "" " at character 524
Statement: " -- This provides discrepancies introduced this month - it is NOT aggregate select nm as Underlying_Term_Name ,acc_txt as Underlying_Term_Accession ,synonym ,reference_acc_txt ,notes ,create_by ,create_tm from edit.term_label where ( acc_txt, object_type_id ) not in ( -- Current Month's PUB select acc_txt, object_type_id from pub1.term ) order by acc_txt
Date: 2026-06-26 09:59:00 Database: ctdprd51 Application: pgAdmin 4 - CONN:5620981 User: edit Remote:
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-06-26 12:45:16 Database: ctdprd51 Application: pgAdmin 4 - CONN:4766381 User: load Remote:
6 1 ERROR: relation "..." does not exist
Times Reported Most Frequent Error / Event #6
Day Hour Count Jun 26 11 1 - ERROR: relation "ixn_qualifer2" does not exist
Statement: GRANT SELECT ON ixn_qualifer2 TO pub1, pub2;
Date: 2026-06-26 11:01:26 Database: ctdprd51 Application: pgAdmin 4 - CONN:4047355 User: edit Remote:
7 1 ERROR: function get_ixn_prose(...) does not exist
Times Reported Most Frequent Error / Event #7
Day Hour Count Jun 26 12 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-06-26 12:45:22
8 1 LOG: process ... still waiting for AccessShareLock on relation ... of database ... after ... ms
Times Reported Most Frequent Error / Event #8
Day Hour Count Jun 26 14 1 - LOG: process 793118 still waiting for AccessShareLock on relation 2633821 of database 484829 after 1000.051 ms
Detail: Process holding the lock: 792812. Wait queue: 793118.
Statement: LOCK TABLE edit.db_link IN ACCESS SHARE MODEDate: 2026-06-26 14:00:03 Database: ctdprd51 Application: pg_dump User: postgres Remote:
9 1 ERROR: column "..." does not exist
Times Reported Most Frequent Error / Event #9
Day Hour Count Jun 26 14 1 - ERROR: column ia.id does not exist at character 8
Hint: Perhaps you meant to reference the column "t.id".
Statement: select ia.id ,ia.object_nm ,ia.acc_txt ,ia.acc_db_id ,t.nm ,t.acc_txt ,t.acc_db_id from ixn_actor ia ,load.term t where ia.object_type_id = t.object_type_id and ia.acc_txt = t.acc_txt and ia.acc_db_id <> t.acc_db_idDate: 2026-06-26 14:27:44
10 1 ERROR: column "..." must appear in the GROUP BY clause or be used in an aggregate function
Times Reported Most Frequent Error / Event #10
Day Hour Count Jun 26 09 1 - ERROR: column "log_query.remote_addr" must appear in the GROUP BY clause or be used in an aggregate function at character 8
Statement: select remote_addr, min(query_tm), max(query_tm) --select distinct(basic_query_txt) --select * --select max(query_tm) from log_query -- from log_query_archive where query_tm >= '20260515' -- and query_tm < '20251129' --and remote_addr = '83.249.73.193' and remote_addr = '120.238.227.252'
Date: 2026-06-26 09:23:15