pgBadger-
Global information
- Generated on Tue Nov 25 04:15:07 2025
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20251124
- Parsed 300,386 log entries in 6s
- Log start from 2025-11-24 00:00:01 to 2025-11-24 23:59:58
-
Overview
Global Stats
- 97 Number of unique normalized queries
- 182 Number of queries
- 5h6m12s Total query duration
- 2025-11-24 00:09:19 First query
- 2025-11-24 23:56:06 Last query
- 3 queries/s at 2025-11-24 00:50:14 Query peak
- 5h6m12s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 5h6m12s Execute total duration
- 2 Number of events
- 2 Number of unique normalized events
- 1 Max number of times the same event was reported
- 0 Number of cancellation
- 41 Total number of automatic vacuums
- 76 Total number of automatic analyzes
- 1,992 Number temporary file
- 1.00 GiB Max size of temporary file
- 194.84 MiB Average size of temporary file
- 36,426 Total number of sessions
- 146 sessions at 2025-11-24 23:55:55 Session peak
- 40d9h1m34s Total duration of sessions
- 1m35s Average duration of sessions
- 0 Average queries per session
- 504ms Average queries duration per session
- 1m35s Average idle time per session
- 36,429 Total number of connections
- 17 connections/s at 2025-11-24 20:43:11 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 3 queries/s Query Peak
- 2025-11-24 00:50:14 Date
SELECT Traffic
Key values
- 3 queries/s Query Peak
- 2025-11-24 00:50:14 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2025-11-24 21:55:06 Date
Queries duration
Key values
- 5h6m12s 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) Nov 24 00 10 0ms 9m17s 1m2s 0ms 0ms 9m24s 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 8 0ms 23s42ms 13s157ms 5s638ms 8s972ms 45s836ms 03 3 0ms 9s153ms 8s517ms 0ms 7s988ms 9s153ms 04 3 0ms 7s447ms 7s406ms 0ms 7s366ms 7s447ms 05 8 0ms 7s562ms 6s802ms 5s79ms 7s509ms 14s795ms 06 3 0ms 8s359ms 8s260ms 0ms 0ms 16s651ms 07 8 0ms 24s914ms 12s479ms 8s319ms 8s417ms 49s578ms 08 8 0ms 15s763ms 6s735ms 5s364ms 5s662ms 20s858ms 09 2 0ms 26s580ms 26s158ms 0ms 0ms 26s580ms 10 1 0ms 6s591ms 6s591ms 0ms 0ms 6s591ms 11 10 0ms 13s771ms 8s470ms 0ms 23s80ms 29s414ms 12 32 0ms 4m40s 52s382ms 1m32s 1m46s 4m40s 13 9 0ms 2m 32s659ms 6s604ms 34s369ms 2m 14 1 0ms 5s788ms 5s788ms 0ms 0ms 5s788ms 15 0 0ms 0ms 0ms 0ms 0ms 0ms 16 0 0ms 0ms 0ms 0ms 0ms 0ms 17 4 0ms 15m3s 5m6s 0ms 1m56s 15m13s 18 12 0ms 34m26s 6m44s 1m41s 10m18s 34m26s 19 5 0ms 43s750ms 42s978ms 0ms 43s12ms 43s750ms 20 1 0ms 45m58s 45m58s 0ms 0ms 45m58s 21 38 0ms 52m29s 2m19s 2m5s 3m35s 52m29s 22 3 0ms 5m23s 2m22s 0ms 32s375ms 5m23s 23 13 0ms 3m17s 35s523ms 25s985ms 50s876ms 3m17s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Nov 24 00 9 0 1m8s 0ms 0ms 1m3s 01 0 0 0ms 0ms 0ms 0ms 02 8 0 13s157ms 0ms 5s638ms 22s647ms 03 3 0 8s517ms 0ms 0ms 8s411ms 04 3 0 7s406ms 0ms 0ms 7s404ms 05 8 0 6s802ms 0ms 5s79ms 14s658ms 06 3 0 8s260ms 0ms 0ms 8s130ms 07 8 0 12s479ms 0ms 8s319ms 8s517ms 08 8 0 6s735ms 0ms 5s364ms 5s771ms 09 2 0 26s158ms 0ms 0ms 25s735ms 10 0 0 0ms 0ms 0ms 0ms 11 9 0 8s429ms 0ms 0ms 23s372ms 12 32 0 52s382ms 1m21s 1m32s 2m39s 13 5 0 26s314ms 0ms 0ms 13s542ms 14 1 0 5s788ms 0ms 0ms 0ms 15 0 0 0ms 0ms 0ms 0ms 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 6 0 7m40s 0ms 0ms 10m18s 19 5 0 42s978ms 0ms 0ms 43s350ms 20 1 0 45m58s 0ms 0ms 0ms 21 13 0 5m8s 0ms 52s38ms 7m30s 22 3 0 2m22s 0ms 0ms 1m11s 23 13 0 35s523ms 0ms 25s985ms 1m2s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Nov 24 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 1 0 0 0 8s835ms 0ms 0ms 0ms 12 0 0 0 0 0ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 0 0 0 0 0ms 0ms 0ms 0ms 17 4 0 0 0 5m6s 0ms 0ms 1m56s 18 6 0 0 0 5m49s 0ms 0ms 1m47s 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 Nov 24 00 0 8 8.00 0.00% 01 0 0 0.00 0.00% 02 0 8 8.00 0.00% 03 0 3 3.00 0.00% 04 0 3 3.00 0.00% 05 0 8 8.00 0.00% 06 0 3 3.00 0.00% 07 0 8 8.00 0.00% 08 0 8 8.00 0.00% 09 0 2 2.00 0.00% 10 0 0 0.00 0.00% 11 0 9 9.00 0.00% 12 0 32 32.00 0.00% 13 0 4 4.00 0.00% 14 0 1 1.00 0.00% 15 0 0 0.00 0.00% 16 0 0 0.00 0.00% 17 0 4 4.00 0.00% 18 0 12 12.00 0.00% 19 0 5 5.00 0.00% 20 0 1 1.00 0.00% 21 0 38 38.00 0.00% 22 0 3 3.00 0.00% 23 0 13 13.00 0.00% Day Hour Count Average / Second Nov 24 00 1,506 0.42/s 01 1,507 0.42/s 02 1,529 0.42/s 03 1,526 0.42/s 04 1,498 0.42/s 05 1,522 0.42/s 06 1,502 0.42/s 07 1,513 0.42/s 08 1,539 0.43/s 09 1,524 0.42/s 10 1,525 0.42/s 11 1,555 0.43/s 12 1,554 0.43/s 13 1,511 0.42/s 14 1,505 0.42/s 15 1,502 0.42/s 16 1,500 0.42/s 17 1,502 0.42/s 18 1,521 0.42/s 19 1,505 0.42/s 20 1,516 0.42/s 21 1,508 0.42/s 22 1,514 0.42/s 23 1,545 0.43/s Day Hour Count Average Duration Average idle time Nov 24 00 1,506 1m35s 1m35s 01 1,507 1m36s 1m36s 02 1,529 1m34s 1m34s 03 1,526 1m34s 1m34s 04 1,498 1m29s 1m29s 05 1,522 1m37s 1m37s 06 1,502 1m35s 1m35s 07 1,513 1m37s 1m37s 08 1,539 1m34s 1m34s 09 1,524 1m34s 1m34s 10 1,523 1m35s 1m35s 11 1,554 1m32s 1m31s 12 1,554 1m32s 1m31s 13 1,512 1m35s 1m35s 14 1,505 1m37s 1m37s 15 1,502 1m35s 1m35s 16 1,500 1m35s 1m35s 17 1,501 1m34s 1m33s 18 1,521 1m42s 1m39s 19 1,505 1m37s 1m37s 20 1,516 1m31s 1m30s 21 1,508 1m38s 1m35s 22 1,514 1m38s 1m38s 23 1,545 1m39s 1m39s -
Connections
Established Connections
Key values
- 17 connections Connection Peak
- 2025-11-24 20:43:11 Date
Connections per database
Key values
- postgres Main Database
- 36,429 connections Total
Connections per user
Key values
- postgres Main User
- 36,429 connections Total
-
Sessions
Simultaneous sessions
Key values
- 146 sessions Session Peak
- 2025-11-24 23:55:55 Date
Histogram of session times
Key values
- 34,317 0-500ms duration
Sessions per database
Key values
- postgres Main Database
- 36,426 sessions Total
Sessions per user
Key values
- postgres Main User
- 36,426 sessions Total
Sessions per host
Key values
- [local] Main Host
- 36,426 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 2,231,945 buffers Checkpoint Peak
- 2025-11-24 21:39:51 Date
- 1620.056 seconds Highest write time
- 0.826 seconds Sync time
Checkpoints Wal files
Key values
- 571 files Wal files usage Peak
- 2025-11-24 23:47:42 Date
Checkpoints distance
Key values
- 17,367.42 Mo Distance Peak
- 2025-11-24 13:52:46 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Nov 24 00 4,761 484.752s 0.005s 484.859s 01 112 11.412s 0.002s 11.443s 02 799 80.201s 0.004s 80.232s 03 2,529 253.494s 0.003s 253.573s 04 280 28.257s 0.003s 28.288s 05 1,301 130.508s 0.002s 130.552s 06 9,460 947.42s 0.003s 947.544s 07 1,061 106.482s 0.003s 106.511s 08 1,491 149.444s 0.003s 149.476s 09 940 94.395s 0.003s 94.475s 10 2,068 207.067s 0.148s 207.338s 11 31 3.194s 0.001s 3.259s 12 290,291 3,137.624s 1.076s 3,175.002s 13 76,736 3,230.843s 0.64s 3,248.326s 14 21 2.276s 0.002s 2.307s 15 1,742 174.674s 0.003s 174.819s 16 8,711 872.003s 0.001s 872.137s 17 2,524,399 1,229.894s 0.853s 1,258.497s 18 1,655,331 3,239.208s 0.238s 3,251.556s 19 34,696 1,619.312s 0.012s 1,619.359s 20 2,300 230.502s 0.002s 230.65s 21 2,233,980 189.86s 2.751s 230.297s 22 766,491 3,447.91s 0.016s 3,458.004s 23 333,490 1,619.617s 0.008s 1,626.536s Day Hour Added Removed Recycled Synced files Longest sync Average sync Nov 24 00 0 0 3 79 0.001s 0.002s 01 0 0 0 32 0.001s 0.002s 02 0 0 0 41 0.001s 0.002s 03 0 0 1 64 0.001s 0.002s 04 0 0 0 40 0.001s 0.002s 05 0 0 1 36 0.001s 0.002s 06 0 0 5 92 0.001s 0.002s 07 0 0 0 127 0.001s 0.002s 08 0 0 0 98 0.001s 0.002s 09 0 0 1 132 0.001s 0.002s 10 0 0 0 704 0.001s 0.002s 11 0 0 1 10 0.001s 0.001s 12 0 124 2,740 450 0.530s 0.03s 13 0 6 1,300 181 0.627s 0.011s 14 0 0 0 13 0.001s 0.002s 15 0 1 0 137 0.001s 0.002s 16 0 7 0 72 0.001s 0.001s 17 0 31 2,153 460 0.141s 0.01s 18 0 0 987 248 0.081s 0.004s 19 0 0 0 102 0.001s 0.001s 20 0 1 0 27 0.001s 0.002s 21 0 48 2,675 188 0.825s 0.099s 22 0 0 834 192 0.001s 0.003s 23 0 0 571 40 0.001s 0.001s Day Hour Count Avg time (sec) Nov 24 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 Nov 24 00 23,985.00 kB 337,406.00 kB 01 295.50 kB 273,399.00 kB 02 2,332.50 kB 221,716.00 kB 03 7,908.00 kB 181,107.00 kB 04 651.50 kB 146,997.00 kB 05 4,342.50 kB 119,523.50 kB 06 35,963.50 kB 100,822.50 kB 07 6,207.50 kB 85,974.50 kB 08 4,495.00 kB 70,535.50 kB 09 2,606.50 kB 57,612.00 kB 10 7,558.00 kB 47,485.50 kB 11 101.00 kB 41,191.00 kB 12 6,629,386.14 kB 6,636,478.29 kB 13 7,309,501.00 kB 8,714,247.67 kB 14 56.50 kB 7,202,592.50 kB 15 6,659.50 kB 5,834,799.00 kB 16 108,077.00 kB 4,986,340.00 kB 17 8,815,718.00 kB 8,816,530.00 kB 18 8,350,764.00 kB 8,771,154.00 kB 19 864.00 kB 7,852,478.00 kB 20 7,548.00 kB 6,714,629.00 kB 21 7,348,665.17 kB 8,307,994.67 kB 22 4,728,350.00 kB 8,309,660.33 kB 23 8,818,089.00 kB 8,818,089.00 kB -
Temporary Files
Size of temporary files
Key values
- 17.00 GiB Temp Files size Peak
- 2025-11-24 21:21:09 Date
Number of temporary files
Key values
- 17 per second Temp Files Peak
- 2025-11-24 21:21:09 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Nov 24 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 0 0 0 07 0 0 0 08 0 0 0 09 0 0 0 10 0 0 0 11 240 2.20 GiB 9.39 MiB 12 963 58.92 GiB 62.65 MiB 13 140 9.40 GiB 68.73 MiB 14 0 0 0 15 0 0 0 16 0 0 0 17 0 0 0 18 0 0 0 19 28 27.97 GiB 1023.06 MiB 20 57 56.26 GiB 1010.78 MiB 21 344 198.05 GiB 589.54 MiB 22 10 7.97 GiB 816.47 MiB 23 210 18.25 GiB 88.97 MiB Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 1,413 89.37 GiB 8.00 KiB 1.00 GiB 64.76 MiB select * from pgbulkload.pg_bulkload (?);-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2025-11-24 21:33:49 Duration: 7m30s Database: ctdprd51 User: load Application: pg_bulkload
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2025-11-24 22:06:04 Duration: 5m23s
-
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: 2025-11-24 12:18:51 Duration: 4m40s
2 87 86.33 GiB 338.15 MiB 1.00 GiB 1016.12 MiB select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.gene_go_annot gga, pub1.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in;-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in;
Date: 2025-11-24 21:21:04 Duration: 0ms
3 57 56.26 GiB 270.36 MiB 1.00 GiB 1010.78 MiB select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.object_type where cd = ?), ptr.term_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.phenotype_term_reference ptr, pub1.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in;-
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in;
Date: 2025-11-24 20:28:27 Duration: 0ms
4 35 4.48 GiB 74.76 MiB 178.39 MiB 130.94 MiB vacuum full analyze db_link;-
vacuum FULL analyze db_link;
Date: 2025-11-24 13:27:28 Duration: 2m
-
vacuum FULL analyze db_link;
Date: 2025-11-24 13:25:50 Duration: 0ms
5 35 1.21 GiB 25.46 MiB 49.57 MiB 35.27 MiB vacuum full analyze ixn_actor;-
vacuum FULL analyze ixn_actor;
Date: 2025-11-24 13:24:58 Duration: 24s363ms
-
vacuum FULL analyze ixn_actor;
Date: 2025-11-24 13:24:41 Duration: 0ms
6 28 27.97 GiB 997.81 MiB 1.00 GiB 1023.06 MiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub1.gene_chem_reference gcr, pub1.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;
Date: 2025-11-24 19:38:36 Duration: 0ms
7 25 397.41 MiB 13.02 MiB 21.45 MiB 15.90 MiB vacuum full analyze ixn;-
vacuum FULL analyze ixn;
Date: 2025-11-24 13:25:19 Duration: 7s884ms
-
vacuum FULL analyze ixn;
Date: 2025-11-24 13:25:13 Duration: 0ms
8 20 219.19 MiB 5.46 MiB 15.87 MiB 10.96 MiB vacuum full analyze term;-
vacuum FULL analyze TERM;
Date: 2025-11-24 13:24:52 Duration: 10s6ms
-
vacuum FULL analyze TERM;
Date: 2025-11-24 13:24:45 Duration: 0ms
9 20 13.55 GiB 8.00 KiB 1.00 GiB 693.71 MiB create unique index gene_disease_reference_ak1 on pub1.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-11-24 21:38:50 Duration: 3m35s
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-11-24 21:38:49 Duration: 0ms Database: ctdprd51 User: pub1
10 15 7.53 GiB 8.00 KiB 1.00 GiB 513.86 MiB alter table pub1.gene_disease_reference add constraint gene_disease_reference_pk primary key (id);-
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2025-11-24 21:54:03 Duration: 1m7s
-
ALTER TABLE pub1.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2025-11-24 21:54:03 Duration: 0ms
11 10 7.53 GiB 422.51 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_ref_chem on pub1.gene_disease_reference using btree (via_chem_id);-
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2025-11-24 21:42:23 Duration: 1m34s
-
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2025-11-24 21:42:23 Duration: 0ms
12 10 7.53 GiB 332.17 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_ref_source_cd on pub1.gene_disease_reference using btree (source_cd);-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub1.gene_disease_reference USING btree (source_cd);
Date: 2025-11-24 21:40:49 Duration: 1m1s
-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub1.gene_disease_reference USING btree (source_cd);
Date: 2025-11-24 21:40:48 Duration: 0ms
13 10 472.41 MiB 8.00 KiB 98.97 MiB 47.24 MiB create unique index chem_disease_reference_ak1 on pub1.chem_disease_reference using btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);-
CREATE UNIQUE INDEX chem_disease_reference_ak1 ON pub1.chem_disease_reference USING btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);
Date: 2025-11-24 21:56:28 Duration: 6s610ms
-
CREATE UNIQUE INDEX chem_disease_reference_ak1 ON pub1.chem_disease_reference USING btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);
Date: 2025-11-24 21:56:28 Duration: 0ms
14 10 7.53 GiB 481.95 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_ref_mod_tm on pub1.gene_disease_reference using btree (mod_tm);-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub1.gene_disease_reference USING btree (mod_tm);
Date: 2025-11-24 21:50:21 Duration: 1m32s
-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub1.gene_disease_reference USING btree (mod_tm);
Date: 2025-11-24 21:50:20 Duration: 0ms
15 10 258.74 MiB 8.00 KiB 52.55 MiB 25.87 MiB alter table pub1.chem_disease_reference add constraint chem_disease_reference_pk primary key (id);-
ALTER TABLE pub1.chem_disease_reference ADD CONSTRAINT chem_disease_reference_pk PRIMARY KEY (id);
Date: 2025-11-24 21:56:22 Duration: 0ms
16 10 7.53 GiB 429.31 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_ref_disease on pub1.gene_disease_reference using btree (disease_id);-
CREATE INDEX ix_gene_disease_ref_disease ON pub1.gene_disease_reference USING btree (disease_id);
Date: 2025-11-24 21:43:53 Duration: 1m29s
-
CREATE INDEX ix_gene_disease_ref_disease ON pub1.gene_disease_reference USING btree (disease_id);
Date: 2025-11-24 21:43:52 Duration: 0ms
17 10 1.14 GiB 8.00 KiB 238.39 MiB 116.44 MiB alter table pub1.phenotype_term_reference add constraint phenotype_term_reference_pk primary key (id);-
ALTER TABLE pub1.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2025-11-24 21:56:19 Duration: 11s655ms
-
ALTER TABLE pub1.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2025-11-24 21:56:18 Duration: 0ms
18 10 164.34 MiB 8.00 KiB 33.21 MiB 16.43 MiB alter table pub1.term_enrichment add constraint term_enrichment_pk primary key (term_id, enriched_term_id);-
ALTER TABLE pub1.term_enrichment ADD CONSTRAINT term_enrichment_pk PRIMARY KEY (term_id, enriched_term_id);
Date: 2025-11-24 23:56:23 Duration: 0ms
19 10 7.53 GiB 453.20 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_ref_reference on pub1.gene_disease_reference using btree (reference_id);-
CREATE INDEX ix_gene_disease_ref_reference ON pub1.gene_disease_reference USING btree (reference_id);
Date: 2025-11-24 21:45:18 Duration: 1m24s
-
CREATE INDEX ix_gene_disease_ref_reference ON pub1.gene_disease_reference USING btree (reference_id);
Date: 2025-11-24 21:45:17 Duration: 0ms
20 10 7.53 GiB 467.87 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_ref_dis_gene on pub1.gene_disease_reference using btree (disease_id, gene_id);-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-11-24 21:47:19 Duration: 2m1s
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-11-24 21:47:18 Duration: 0ms
21 10 7.53 GiB 473.93 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_ref_net_sc on pub1.gene_disease_reference using btree (network_score);-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2025-11-24 21:52:56 Duration: 2m34s
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2025-11-24 21:52:55 Duration: 0ms
22 10 7.53 GiB 500.28 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_ref_src_db on pub1.gene_disease_reference using btree (source_acc_db_id);-
CREATE INDEX ix_gene_disease_ref_src_db ON pub1.gene_disease_reference USING btree (source_acc_db_id);
Date: 2025-11-24 21:39:48 Duration: 57s397ms
-
CREATE INDEX ix_gene_disease_ref_src_db ON pub1.gene_disease_reference USING btree (source_acc_db_id);
Date: 2025-11-24 21:39:47 Duration: 0ms
23 10 7.53 GiB 437.73 MiB 1.00 GiB 770.79 MiB create index ix_gene_disease_reference_ixn on pub1.gene_disease_reference using btree (ixn_id);-
CREATE INDEX ix_gene_disease_reference_ixn ON pub1.gene_disease_reference USING btree (ixn_id);
Date: 2025-11-24 21:48:48 Duration: 1m29s
-
CREATE INDEX ix_gene_disease_reference_ixn ON pub1.gene_disease_reference USING btree (ixn_id);
Date: 2025-11-24 21:48:48 Duration: 0ms
24 7 6.06 GiB 61.48 MiB 1.00 GiB 886.50 MiB select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub1.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.object_type where cd = ?), cdr.mod_tm from pub1.chem_disease_reference cdr, pub1.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id;-
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub1.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub1.CHEM_DISEASE_REFERENCE cdr, pub1.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id;
Date: 2025-11-24 21:25:20 Duration: 0ms
25 5 258.70 MiB 50.26 MiB 53.06 MiB 51.74 MiB create index ix_chem_disease_ref_mod_tm on pub1.chem_disease_reference using btree (mod_tm);-
CREATE INDEX ix_chem_disease_ref_mod_tm ON pub1.chem_disease_reference USING btree (mod_tm);
Date: 2025-11-24 21:56:47 Duration: 0ms
26 5 1.14 GiB 227.61 MiB 238.08 MiB 232.87 MiB create index ix_phenotype_term_ref_taxon_id on pub1.phenotype_term_reference using btree (taxon_id);-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub1.phenotype_term_reference USING btree (taxon_id);
Date: 2025-11-24 21:54:56 Duration: 9s336ms
-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub1.phenotype_term_reference USING btree (taxon_id);
Date: 2025-11-24 21:54:56 Duration: 0ms
27 5 258.70 MiB 51.07 MiB 52.15 MiB 51.74 MiB create index ix_chem_disease_ref_source_cd on pub1.chem_disease_reference using btree (source_cd);-
CREATE INDEX ix_chem_disease_ref_source_cd ON pub1.chem_disease_reference USING btree (source_cd);
Date: 2025-11-24 21:56:36 Duration: 0ms
28 5 1.14 GiB 226.03 MiB 239.55 MiB 232.87 MiB create index ix_phenotype_term_ref_evidence_cd on pub1.phenotype_term_reference using btree (evidence_cd);-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub1.phenotype_term_reference USING btree (evidence_cd);
Date: 2025-11-24 21:55:06 Duration: 9s996ms
-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub1.phenotype_term_reference USING btree (evidence_cd);
Date: 2025-11-24 21:55:06 Duration: 0ms
29 5 1.14 GiB 219.59 MiB 248.64 MiB 232.87 MiB create index ix_phenotype_term_ref_via_term_id on pub1.phenotype_term_reference using btree (via_term_id);-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub1.phenotype_term_reference USING btree (via_term_id);
Date: 2025-11-24 21:55:52 Duration: 11s863ms
-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub1.phenotype_term_reference USING btree (via_term_id);
Date: 2025-11-24 21:55:52 Duration: 0ms
30 5 258.70 MiB 48.34 MiB 55.56 MiB 51.74 MiB create index ix_chem_disease_reference_ixn on pub1.chem_disease_reference using btree (ixn_id);-
CREATE INDEX ix_chem_disease_reference_ixn ON pub1.chem_disease_reference USING btree (ixn_id);
Date: 2025-11-24 21:56:44 Duration: 0ms
31 5 164.30 MiB 30.32 MiB 35.31 MiB 32.86 MiB create index ix_term_enrich_tgt_match on pub1.term_enrichment using btree (target_match_qty);-
CREATE INDEX ix_term_enrich_tgt_match ON pub1.term_enrichment USING btree (target_match_qty);
Date: 2025-11-24 23:56:10 Duration: 0ms
32 5 1.14 GiB 227.95 MiB 238.84 MiB 232.87 MiB create index ix_phenotype_term_reference_source_acc_db_id on pub1.phenotype_term_reference using btree (source_acc_db_id);-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub1.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2025-11-24 21:55:16 Duration: 9s971ms
-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub1.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2025-11-24 21:55:16 Duration: 0ms
33 5 258.70 MiB 51.41 MiB 52.02 MiB 51.74 MiB create index ix_chem_disease_reference_ref on pub1.chem_disease_reference using btree (reference_id);-
CREATE INDEX ix_chem_disease_reference_ref ON pub1.chem_disease_reference USING btree (reference_id);
Date: 2025-11-24 21:56:34 Duration: 0ms
34 5 1.14 GiB 224.55 MiB 242.38 MiB 232.87 MiB create index ix_phenotype_term_reference_ixn_id on pub1.phenotype_term_reference using btree (ixn_id);-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub1.phenotype_term_reference USING btree (ixn_id);
Date: 2025-11-24 21:55:40 Duration: 11s640ms
-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub1.phenotype_term_reference USING btree (ixn_id);
Date: 2025-11-24 21:55:40 Duration: 0ms
35 5 1.14 GiB 225.91 MiB 240.98 MiB 232.87 MiB create index ix_phenotype_term_ref_reference_id on pub1.phenotype_term_reference using btree (reference_id);-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub1.phenotype_term_reference USING btree (reference_id);
Date: 2025-11-24 21:54:47 Duration: 12s346ms
-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub1.phenotype_term_reference USING btree (reference_id);
Date: 2025-11-24 21:54:47 Duration: 0ms
36 5 258.70 MiB 51.23 MiB 52.40 MiB 51.74 MiB create index ix_chem_disease_reference_dis on pub1.chem_disease_reference using btree (disease_id);-
CREATE INDEX ix_chem_disease_reference_dis ON pub1.chem_disease_reference USING btree (disease_id);
Date: 2025-11-24 21:56:31 Duration: 0ms
37 5 229.35 MiB 44.24 MiB 47.39 MiB 45.87 MiB create index ix_term_enrich_raw_p_val on pub1.term_enrichment using btree (raw_p_val);-
CREATE INDEX ix_term_enrich_raw_p_val ON pub1.term_enrichment USING btree (raw_p_val);
Date: 2025-11-24 23:56:20 Duration: 0ms
38 5 1.14 GiB 222.39 MiB 245.67 MiB 232.87 MiB create index ix_phenotype_term_reference_term_reference_id on pub1.phenotype_term_reference using btree (term_reference_id);-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub1.phenotype_term_reference USING btree (term_reference_id);
Date: 2025-11-24 21:55:29 Duration: 12s522ms
-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub1.phenotype_term_reference USING btree (term_reference_id);
Date: 2025-11-24 21:55:29 Duration: 0ms
39 5 1.14 GiB 224.63 MiB 245.88 MiB 232.87 MiB create index ix_phenotype_term_ref_object_type_id on pub1.phenotype_term_reference using btree (term_object_type_id);-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub1.phenotype_term_reference USING btree (term_object_type_id);
Date: 2025-11-24 21:54:35 Duration: 9s232ms
-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub1.phenotype_term_reference USING btree (term_object_type_id);
Date: 2025-11-24 21:54:34 Duration: 0ms
40 5 229.35 MiB 43.67 MiB 47.29 MiB 45.87 MiB create index ix_term_enrich_corr_p_val on pub1.term_enrichment using btree (corrected_p_val);-
CREATE INDEX ix_term_enrich_corr_p_val ON pub1.term_enrichment USING btree (corrected_p_val);
Date: 2025-11-24 23:56:16 Duration: 0ms
41 5 164.30 MiB 30.91 MiB 34.53 MiB 32.86 MiB create index ix_term_enrich_enr_obj_type on pub1.term_enrichment using btree (enriched_object_type_id);-
CREATE INDEX ix_term_enrich_enr_obj_type ON pub1.term_enrichment USING btree (enriched_object_type_id);
Date: 2025-11-24 23:56:11 Duration: 0ms
42 5 1.60 GiB 318.34 MiB 343.19 MiB 326.80 MiB create index ix_phenotype_term_ref_ids on pub1.phenotype_term_reference using btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);-
CREATE INDEX ix_phenotype_term_ref_ids ON pub1.phenotype_term_reference USING btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);
Date: 2025-11-24 21:56:08 Duration: 15s359ms
-
CREATE INDEX ix_phenotype_term_ref_ids ON pub1.phenotype_term_reference USING btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);
Date: 2025-11-24 21:56:07 Duration: 0ms
43 5 258.70 MiB 49.50 MiB 53.32 MiB 51.74 MiB create index ix_chem_disease_ref_net_sc on pub1.chem_disease_reference using btree (network_score);-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub1.chem_disease_reference USING btree (network_score);
Date: 2025-11-24 21:56:52 Duration: 5s445ms
-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub1.chem_disease_reference USING btree (network_score);
Date: 2025-11-24 21:56:52 Duration: 0ms
44 5 1.14 GiB 219.50 MiB 245.16 MiB 232.87 MiB create index ix_phenotype_term_ref_term_id on pub1.phenotype_term_reference using btree (term_id);-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub1.phenotype_term_reference USING btree (term_id);
Date: 2025-11-24 21:54:25 Duration: 10s753ms
-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub1.phenotype_term_reference USING btree (term_id);
Date: 2025-11-24 21:54:25 Duration: 0ms
45 5 1.14 GiB 225.50 MiB 238.25 MiB 232.87 MiB create index ix_phenotype_term_ref_phenotype_id on pub1.phenotype_term_reference using btree (phenotype_id);-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub1.phenotype_term_reference USING btree (phenotype_id);
Date: 2025-11-24 21:54:15 Duration: 11s24ms
-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub1.phenotype_term_reference USING btree (phenotype_id);
Date: 2025-11-24 21:54:14 Duration: 0ms
46 5 258.70 MiB 49.42 MiB 52.92 MiB 51.74 MiB create index ix_chem_disease_ref_src_db on pub1.chem_disease_reference using btree (source_acc_db_id);-
CREATE INDEX ix_chem_disease_ref_src_db ON pub1.chem_disease_reference USING btree (source_acc_db_id);
Date: 2025-11-24 21:56:38 Duration: 0ms
47 5 164.30 MiB 28.47 MiB 35.65 MiB 32.86 MiB create index ix_term_enrich_obj_type on pub1.term_enrichment using btree (object_type_id);-
CREATE INDEX ix_term_enrich_obj_type ON pub1.term_enrichment USING btree (object_type_id);
Date: 2025-11-24 23:56:08 Duration: 0ms Database: ctdprd51 User: pub1
48 5 258.70 MiB 50.20 MiB 53.07 MiB 51.74 MiB create index ix_chem_disease_reference_gene on pub1.chem_disease_reference using btree (via_gene_id);-
CREATE INDEX ix_chem_disease_reference_gene ON pub1.chem_disease_reference USING btree (via_gene_id);
Date: 2025-11-24 21:56:41 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB SELECT * FROM pgbulkload.pg_bulkload ($1);[ Date: 2025-11-24 12:44:27 - Database: ctdprd51 - User: load - Application: pg_bulkload ]
2 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:36 ]
3 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
4 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
5 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
6 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
7 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
8 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
9 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
10 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
11 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
12 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
13 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
14 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
15 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
16 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:37 ]
17 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:38 ]
18 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:38 ]
19 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:38 ]
20 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn;[ Date: 2025-11-24 19:38:38 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 73.95 sec Highest CPU-cost vacuum
Table pub1.gene_go_annot
Database ctdprd51 - 2025-11-24 18:05:59 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 73.95 sec Highest CPU-cost vacuum
Table pub1.gene_go_annot
Database ctdprd51 - 2025-11-24 18:05:59 Date
Analyzes per table
Key values
- pubc.log_query (17) Main table analyzed (database ctdprd51)
- 76 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 17 ctdprd51.pub2.term_comp_agent 6 ctdprd51.pg_catalog.pg_class 3 ctdprd51.pub1.db 2 ctdprd51.pub1.term 2 ctdprd51.pg_catalog.pg_attribute 2 ctdprd51.pg_catalog.pg_index 2 ctdprd51.edit.db_link 2 postgres.pg_catalog.pg_shdepend 2 ctdprd51.pub1.reference_party_role 1 ctdprd51.edit.action_type 1 ctdprd51.load.data_load 1 ctdprd51.pub1.gene_taxon 1 ctdprd51.pub1.dag_edge 1 ctdprd51.edit.db_report_site 1 ctdprd51.pub1.action_type 1 ctdprd51.edit.object_note 1 ctdprd51.pub1.db_link 1 ctdprd51.pg_catalog.pg_trigger 1 ctdprd51.pub1.img 1 ctdprd51.edit.country 1 ctdprd51.pg_catalog.pg_constraint 1 ctdprd51.edit.db 1 ctdprd51.pub1.term_label 1 ctdprd51.edit.term_label_type 1 ctdprd51.edit.db_report 1 ctdprd51.edit.geographic_region 1 ctdprd51.pub1.term_pathway 1 ctdprd51.pg_catalog.pg_shdepend 1 ctdprd51.pub1.list_db_report 1 ctdprd51.pg_catalog.pg_description 1 ctdprd51.edit.action_degree 1 ctdprd51.pub1.reference_party 1 ctdprd51.edit.reference_db_link 1 ctdprd51.pg_catalog.pg_attrdef 1 ctdprd51.pub1.db_report 1 ctdprd51.pub1.reference 1 ctdprd51.edit.race 1 ctdprd51.pg_catalog.pg_proc 1 ctdprd51.edit.list_db_report 1 ctdprd51.edit.action_type_path 1 ctdprd51.pub1.db_report_site 1 ctdprd51.pub1.gene_go_annot 1 ctdprd51.pub1.dag_node 1 ctdprd51.edit.slim_term 1 ctdprd51.pg_catalog.pg_depend 1 ctdprd51.pg_catalog.pg_type 1 Total 76 Vacuums per table
Key values
- pub2.term_comp_agent (4) Main table vacuumed on database ctdprd51
- 41 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pub2.term_comp_agent 4 0 4,444 0 32 0 0 2,044 3 141,882 ctdprd51.pub1.term 2 0 130,535 0 5 0 0 46,618 3 2,769,940 ctdprd51.pg_catalog.pg_class 2 2 761 0 82 0 17 373 74 277,222 ctdprd51.pubc.log_query 2 2 658 0 114 0 0 239 80 540,402 ctdprd51.edit.geographic_region 1 0 65 0 1 0 0 3 1 8,905 ctdprd51.pub1.term_pathway 1 0 3,336 0 3 0 0 1,614 1 103,645 ctdprd51.pg_catalog.pg_shdepend 1 1 209 0 14 0 0 105 18 55,991 ctdprd51.edit.action_type_path 1 0 48 0 0 0 0 4 1 9,059 ctdprd51.pg_catalog.pg_statistic 1 1 887 0 60 0 113 664 57 201,734 ctdprd51.pg_catalog.pg_proc 1 1 449 0 88 0 79 284 84 138,581 ctdprd51.edit.race 1 0 91 0 5 0 0 3 2 10,699 ctdprd51.edit.reference_db_link 1 0 5,769 0 1,650 0 0 3,695 1 226,331 ctdprd51.pub1.reference 1 0 78,397 0 5 0 0 39,087 3 2,326,753 ctdprd51.edit.action_degree 1 0 45 0 0 0 0 12 1 9,451 ctdprd51.pub1.reference_party 1 0 5,141 0 4 0 0 2,537 2 160,470 ctdprd51.pub1.dag_node 1 0 83,936 0 4 0 0 41,841 2 2,485,235 ctdprd51.pg_catalog.pg_index 1 1 190 0 19 0 0 104 18 87,161 ctdprd51.pg_catalog.pg_attribute 1 1 837 0 74 0 37 439 72 362,835 ctdprd51.pub1.gene_go_annot 1 0 630,670 0 315,163 0 0 315,217 11 18,684,823 ctdprd51.edit.db_link 1 0 7,649 0 3 0 0 3,695 1 226,400 ctdprd51.pg_catalog.pg_type 1 1 152 0 42 0 0 60 38 163,762 ctdprd51.pg_catalog.pg_depend 1 1 668 0 77 0 65 304 80 339,128 ctdprd51.edit.slim_term 1 1 61 0 7 0 0 6 4 13,881 ctdprd51.pg_toast.pg_toast_9848910 1 0 89,670 0 4 0 0 44,827 2 2,661,316 ctdprd51.pub1.reference_party_role 1 0 13,705 0 4 0 0 6,799 1 409,560 ctdprd51.pub1.gene_taxon 1 0 166,092 0 5 0 0 82,987 3 4,921,046 ctdprd51.pub1.dag_edge 1 0 1,053 0 5 0 0 482 2 39,497 ctdprd51.pub1.img 1 0 1,108 0 5 0 0 524 2 44,163 ctdprd51.edit.country 1 0 63 0 0 0 0 8 1 9,627 ctdprd51.pg_catalog.pg_constraint 1 1 252 0 29 0 0 92 23 100,855 ctdprd51.pg_catalog.pg_trigger 1 1 331 0 27 0 0 132 34 174,910 ctdprd51.pub1.db_link 1 0 297,387 0 130,349 0 0 148,569 5 8,806,778 ctdprd51.pub1.db 1 1 149 0 13 0 0 20 10 35,059 ctdprd51.pub1.term_label 1 0 190,519 0 86,180 0 0 95,208 4 5,644,725 ctdprd51.pg_toast.pg_toast_2619 1 1 4,198 0 1,164 0 9,902 3,359 1,105 562,105 Total 41 16 1,719,525 2,316 535,237 0 10,213 841,955 1,749 52,753,931 Tuples removed per table
Key values
- pg_toast.pg_toast_2619 (4174) Main table with removed tuples on database ctdprd51
- 12101 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,174 20,005 0 0 12,592 ctdprd51.pg_catalog.pg_depend 1 1 2,211 14,070 0 0 139 ctdprd51.pg_catalog.pg_attribute 1 1 1,652 8,740 0 0 230 ctdprd51.pg_catalog.pg_statistic 1 1 879 2,693 0 0 410 ctdprd51.pg_catalog.pg_shdepend 1 1 730 2,070 0 0 24 ctdprd51.pg_catalog.pg_class 2 2 699 3,885 0 0 188 ctdprd51.pg_catalog.pg_trigger 1 1 398 1,797 0 0 49 ctdprd51.pg_catalog.pg_index 1 1 280 1,167 0 0 38 ctdprd51.edit.country 1 0 163 249 0 0 4 ctdprd51.pg_catalog.pg_proc 1 1 139 1,588 0 0 205 ctdprd51.pub1.db 1 1 134 134 0 0 7 ctdprd51.pg_catalog.pg_constraint 1 1 114 879 0 0 35 ctdprd51.edit.race 1 0 108 27 0 0 1 ctdprd51.edit.action_type_path 1 0 106 106 0 0 2 ctdprd51.edit.action_degree 1 0 96 219 0 0 6 ctdprd51.edit.geographic_region 1 0 67 51 0 0 1 ctdprd51.edit.slim_term 1 1 61 38 0 0 1 ctdprd51.pg_catalog.pg_type 1 1 57 1,153 0 0 34 ctdprd51.pubc.log_query 2 2 33 3,196 7 0 130 ctdprd51.pub1.term 2 0 0 2,184,483 0 0 69,575 ctdprd51.pub1.term_pathway 1 0 0 135,792 0 0 1,613 ctdprd51.edit.reference_db_link 1 0 0 331,479 0 0 3,694 ctdprd51.pub1.reference 1 0 0 201,133 0 0 39,086 ctdprd51.pub1.reference_party 1 0 0 454,035 0 0 2,536 ctdprd51.pub1.dag_node 1 0 0 1,740,999 0 0 41,840 ctdprd51.pub1.gene_go_annot 1 0 0 49,490,012 0 0 315,216 ctdprd51.edit.db_link 1 0 0 331,479 0 0 3,694 ctdprd51.pg_toast.pg_toast_9848910 1 0 0 242,315 0 0 44,826 ctdprd51.pub1.reference_party_role 1 0 0 1,257,611 0 0 6,798 ctdprd51.pub1.gene_taxon 1 0 0 13,028,719 0 0 82,986 ctdprd51.pub1.dag_edge 1 0 0 88,931 0 0 481 ctdprd51.pub1.img 1 0 0 50,671 0 0 523 ctdprd51.pub2.term_comp_agent 4 0 0 583,629 0 0 4,940 ctdprd51.pub1.db_link 1 0 0 20,511,333 0 0 148,568 ctdprd51.pub1.term_label 1 0 0 6,555,304 0 0 95,207 Total 41 16 12,101 97,249,992 7 0 875,679 Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Pages removed per tables
NO DATASET
Table Number of vacuums Index scans Tuples removed Pages removed ctdprd51.pub1.term 2 0 0 0 ctdprd51.edit.geographic_region 1 0 67 0 ctdprd51.pub1.term_pathway 1 0 0 0 ctdprd51.pg_catalog.pg_shdepend 1 1 730 0 ctdprd51.pg_catalog.pg_class 2 2 699 0 ctdprd51.edit.action_type_path 1 0 106 0 ctdprd51.pg_catalog.pg_statistic 1 1 879 0 ctdprd51.pg_catalog.pg_proc 1 1 139 0 ctdprd51.edit.race 1 0 108 0 ctdprd51.edit.reference_db_link 1 0 0 0 ctdprd51.pub1.reference 1 0 0 0 ctdprd51.edit.action_degree 1 0 96 0 ctdprd51.pub1.reference_party 1 0 0 0 ctdprd51.pub1.dag_node 1 0 0 0 ctdprd51.pg_catalog.pg_index 1 1 280 0 ctdprd51.pg_catalog.pg_attribute 1 1 1652 0 ctdprd51.pub1.gene_go_annot 1 0 0 0 ctdprd51.edit.db_link 1 0 0 0 ctdprd51.pg_catalog.pg_type 1 1 57 0 ctdprd51.pg_catalog.pg_depend 1 1 2211 0 ctdprd51.edit.slim_term 1 1 61 0 ctdprd51.pubc.log_query 2 2 33 0 ctdprd51.pg_toast.pg_toast_9848910 1 0 0 0 ctdprd51.pub1.reference_party_role 1 0 0 0 ctdprd51.pub1.gene_taxon 1 0 0 0 ctdprd51.pub1.dag_edge 1 0 0 0 ctdprd51.pub1.img 1 0 0 0 ctdprd51.edit.country 1 0 163 0 ctdprd51.pg_catalog.pg_constraint 1 1 114 0 ctdprd51.pg_catalog.pg_trigger 1 1 398 0 ctdprd51.pub2.term_comp_agent 4 0 0 0 ctdprd51.pub1.db_link 1 0 0 0 ctdprd51.pub1.db 1 1 134 0 ctdprd51.pub1.term_label 1 0 0 0 ctdprd51.pg_toast.pg_toast_2619 1 1 4174 0 Total 41 16 12,101 0 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Nov 24 00 0 0 01 0 2 02 0 2 03 0 3 04 0 1 05 1 2 06 1 1 07 0 2 08 0 1 09 0 1 10 9 12 11 8 17 12 0 0 13 1 3 14 0 0 15 1 2 16 3 3 17 6 12 18 10 8 19 0 0 20 1 0 21 0 0 22 0 2 23 0 2 - 73.95 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- unknown Main Lock Type
- 0 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query NO DATASET
Queries that waited the most
Rank Wait time Query NO DATASET
-
Queries
Queries by type
Key values
- 140 Total read queries
- 37 Total write queries
Queries by database
Key values
- unknown Main database
- 107 Requests
- 3h32m56s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 110 Requests
User Request type Count Duration edit Total 1 8s835ms insert 1 8s835ms load Total 21 1h45s select 21 1h45s pub1 Total 3 15m15s ddl 1 6s591ms insert 1 15m3s select 1 5s398ms pubc Total 1 9m17s select 1 9m17s pubeu Total 62 9m43s select 62 9m43s qaeu Total 4 27s508ms select 4 27s508ms unknown Total 110 3h33m16s ddl 25 21m16s insert 9 40m20s others 5 2m49s select 71 2h28m50s Duration by user
Key values
- 3h33m16s (unknown) Main time consuming user
User Request type Count Duration edit Total 1 8s835ms insert 1 8s835ms load Total 21 1h45s select 21 1h45s pub1 Total 3 15m15s ddl 1 6s591ms insert 1 15m3s select 1 5s398ms pubc Total 1 9m17s select 1 9m17s pubeu Total 62 9m43s select 62 9m43s qaeu Total 4 27s508ms select 4 27s508ms unknown Total 110 3h33m16s ddl 25 21m16s insert 9 40m20s others 5 2m49s select 71 2h28m50s Queries by host
Key values
- unknown Main host
- 202 Requests
- 5h8m55s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 164 Requests
- 4h41m35s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2025-11-24 02:16:58 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 101 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 52m29s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2025-11-24 21:21:02 - Bind query: yes ]
2 45m58s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2025-11-24 20:28:25 - Bind query: yes ]
3 34m26s 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: 2025-11-24 18:46:48 - Database: ctdprd51 - User: load - Bind query: yes ]
4 26m58s insert into pub1.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;[ Date: 2025-11-24 18:03:48 - Bind query: yes ]
5 15m3s insert into pub1.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;[ Date: 2025-11-24 17:31:25 - Database: ctdprd51 - User: pub1 - Bind query: yes ]
6 10m18s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');[ Date: 2025-11-24 18:59:57 - 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: 2025-11-24 00:09:19 - Database: ctdprd51 - User: pubc - Application: psql ]
8 7m30s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');[ Date: 2025-11-24 21:33:49 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
9 5m23s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');[ Date: 2025-11-24 22:06:04 - Bind query: yes ]
10 4m40s 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: 2025-11-24 12:18:51 - Bind query: yes ]
11 4m31s insert into pub1.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;[ Date: 2025-11-24 18:08:19 - Bind query: yes ]
12 3m35s CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);[ Date: 2025-11-24 21:38:50 - Bind query: yes ]
13 3m17s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.TERM_ENRICHMENT_AGENT,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.log,parse-badfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.DUPE}');[ Date: 2025-11-24 23:53:34 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
14 3m16s INSERT INTO pub1.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub1.TERM);[ Date: 2025-11-24 17:36:49 - Bind query: yes ]
15 2m39s 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: 2025-11-24 12:55:57 - Bind query: yes ]
16 2m34s CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);[ Date: 2025-11-24 21:52:56 - Bind query: yes ]
17 2m5s SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub1.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub1.CHEM_DISEASE_REFERENCE cdr, pub1.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id AND ptr.source_cd = 'C' AND cdr.source_cd = 'C' AND ptr.ixn_id NOT IN ( SELECT ixn_id FROM pub1.IXN_AXN WHERE action_degree_type_nm = 'does not affect');[ Date: 2025-11-24 21:23:15 - Bind query: yes ]
18 2m1s CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);[ Date: 2025-11-24 21:47:19 - Bind query: yes ]
19 2m vacuum FULL analyze db_link;[ Date: 2025-11-24 13:27:28 ]
20 1m56s INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);[ Date: 2025-11-24 17:33:33 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 55m2s 60 5s545ms 7m30s 55s35ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Nov 24 11 9 1m15s 8s429ms 12 29 27m12s 56s292ms 13 1 1m45s 1m45s 18 3 1m7s 22s630ms 21 4 9m2s 2m15s 22 3 7m7s 2m22s 23 11 7m30s 40s929ms [ User: load - Total duration: 15m4s - Times executed: 15 ]
[ Application: pg_bulkload - Total duration: 15m4s - Times executed: 15 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2025-11-24 21:33:49 Duration: 7m30s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2025-11-24 22:06:04 Duration: 5m23s 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: 2025-11-24 12:18:51 Duration: 4m40s Bind query: yes
2 52m29s 1 52m29s 52m29s 52m29s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.gene_go_annot gga, pub1.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Nov 24 21 1 52m29s 52m29s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 21:21:02 Duration: 52m29s Bind query: yes
3 45m58s 1 45m58s 45m58s 45m58s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.object_type where cd = ?), ptr.term_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.phenotype_term_reference ptr, pub1.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Nov 24 20 1 45m58s 45m58s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 20:28:25 Duration: 45m58s Bind query: yes
4 34m26s 1 34m26s 34m26s 34m26s 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 Nov 24 18 1 34m26s 34m26s [ User: load - Total duration: 34m26s - 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: 2025-11-24 18:46:48 Duration: 34m26s Database: ctdprd51 User: load Bind query: yes
5 26m58s 1 26m58s 26m58s 26m58s insert into pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Nov 24 18 1 26m58s 26m58s -
insert into pub1.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;
Date: 2025-11-24 18:03:48 Duration: 26m58s Bind query: yes
6 15m3s 1 15m3s 15m3s 15m3s insert into pub1.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Nov 24 17 1 15m3s 15m3s [ User: pub1 - Total duration: 15m3s - Times executed: 1 ]
-
insert into pub1.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;
Date: 2025-11-24 17:31:25 Duration: 15m3s Database: ctdprd51 User: pub1 Bind query: yes
7 13m10s 5 42s237ms 10m18s 2m38s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub1.gene_chem_reference gcr, pub1.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Nov 24 18 1 10m18s 10m18s 19 4 2m51s 42s969ms [ User: load - Total duration: 10m18s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 18:59:57 Duration: 10m18s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 19:18:15 Duration: 43s750ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 19:29:10 Duration: 43s350ms 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 Nov 24 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: 2025-11-24 00:09:19 Duration: 9m17s Database: ctdprd51 User: pubc Application: psql
9 4m31s 1 4m31s 4m31s 4m31s insert into pub1.gene_taxon (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.gene_taxon;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Nov 24 18 1 4m31s 4m31s -
insert into pub1.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;
Date: 2025-11-24 18:08:19 Duration: 4m31s Bind query: yes
10 3m35s 1 3m35s 3m35s 3m35s create unique index gene_disease_reference_ak1 on pub1.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Nov 24 21 1 3m35s 3m35s -
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-11-24 21:38:50 Duration: 3m35s Bind query: yes
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-11-24 21:38:49 Duration: 0ms Database: ctdprd51 User: pub1
11 3m16s 1 3m16s 3m16s 3m16s insert into pub1.term_label (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.term t, load.term_label l where t.id = l.term_id and t.id in ( select id from pub1.term);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Nov 24 17 1 3m16s 3m16s -
INSERT INTO pub1.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub1.TERM);
Date: 2025-11-24 17:36:49 Duration: 3m16s Bind query: yes
12 2m34s 1 2m34s 2m34s 2m34s create index ix_gene_disease_ref_net_sc on pub1.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Nov 24 21 1 2m34s 2m34s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2025-11-24 21:52:56 Duration: 2m34s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2025-11-24 21:52:55 Duration: 0ms
13 2m15s 17 7s232ms 8s517ms 7s944ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort asc, pt.indirect_term_qty desc limit ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Nov 24 03 1 8s411ms 8s411ms 04 3 22s218ms 7s406ms 05 4 29s384ms 7s346ms 06 3 24s782ms 8s260ms 07 6 50s254ms 8s375ms [ User: pubeu - Total duration: 1m19s - Times executed: 10 ]
[ User: qaeu - Total duration: 7s232ms - Times executed: 1 ]
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'MONTELUKAST')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0008150' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases', 'decreases', 'affects')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2025-11-24 07:47:24 Duration: 8s517ms Database: ctdprd51 User: pubeu Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'NELFINAVIR')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0008150' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases', 'decreases', 'affects')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2025-11-24 07:35:15 Duration: 8s417ms Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'MONTELUKAST')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0008150' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases', 'decreases', 'affects')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2025-11-24 07:27:30 Duration: 8s412ms Database: ctdprd51 User: pubeu Bind query: yes
14 2m5s 1 2m5s 2m5s 2m5s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub1.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.object_type where cd = ?), cdr.mod_tm from pub1.chem_disease_reference cdr, pub1.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id and ptr.source_cd = ? and cdr.source_cd = ? and ptr.ixn_id not in ( select ixn_id from pub1.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Nov 24 21 1 2m5s 2m5s -
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub1.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub1.CHEM_DISEASE_REFERENCE cdr, pub1.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id AND ptr.source_cd = 'C' AND cdr.source_cd = 'C' AND ptr.ixn_id NOT IN ( SELECT ixn_id FROM pub1.IXN_AXN WHERE action_degree_type_nm = 'does not affect');
Date: 2025-11-24 21:23:15 Duration: 2m5s Bind query: yes
15 2m1s 1 2m1s 2m1s 2m1s create index ix_gene_disease_ref_dis_gene on pub1.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Nov 24 21 1 2m1s 2m1s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-11-24 21:47:19 Duration: 2m1s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-11-24 21:47:18 Duration: 0ms
16 2m 1 2m 2m 2m vacuum full analyze db_link;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Nov 24 13 1 2m 2m -
vacuum FULL analyze db_link;
Date: 2025-11-24 13:27:28 Duration: 2m
-
vacuum FULL analyze db_link;
Date: 2025-11-24 13:25:50 Duration: 0ms
17 1m56s 1 1m56s 1m56s 1m56s insert into pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) select t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) as db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), ?, ?, ?, ?, ?, ?, ? from load.term t where object_type_id not in (...);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Nov 24 17 1 1m56s 1m56s -
INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2025-11-24 17:33:33 Duration: 1m56s Bind query: yes
18 1m47s 1 1m47s 1m47s 1m47s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub1.phenotype_term_reference viachemptr, pub1.phenotype_term_reference viageneptr where viachemptr.term_id = viageneptr.term_id and viachemptr.term_object_type_id = ? and viachemptr.phenotype_id = viageneptr.phenotype_id and viachemptr.via_term_object_type_id = ? and viageneptr.via_term_object_type_id = ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Nov 24 21 1 1m47s 1m47s -
SELECT distinct viaChemPTR.via_term_id, -- chem viaGenePTR.via_term_id, -- gene viaChemPTR.phenotype_id, -- phenotype viaChemPTR.term_id -- disease FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 AND viaGenePTR.via_term_object_type_id = 4;
Date: 2025-11-24 21:59:01 Duration: 1m47s Bind query: yes
19 1m41s 1 1m41s 1m41s 1m41s insert into pub1.dag_node (id, dag_id, object_id, parent_id, acc_txt, acc_db_cd, rank_nm, is_leaf, subset_left_no, subset_right_no, nm, nm_sort, secondary_nm, object_acc_txt, nm_html, level_min_no, has_chems, has_diseases, has_genes, has_marrays) select n.id, n.dag_id, n.object_id, ( select p.id from load.dag_node p where p.acc_txt = n.parent_acc_txt and p.acc_db_id = n.parent_acc_db_id and p.dag_id = n.dag_id) as parent_id, n.acc_txt, get_db_cd (n.acc_db_id) as acc_db_cd, n.rank_nm, n.is_leaf, n.subset_left_no, n.subset_right_no, t.nm, t.nm_sort, t.secondary_nm, t.acc_txt, break_html_words (t.nm), n.level_min_no, ?, ?, ?, ? from load.dag_node n, load.term t where t.id = n.object_id;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Nov 24 18 1 1m41s 1m41s -
INSERT INTO pub1.DAG_NODE (id, dag_id, object_id, parent_id, acc_txt, acc_db_cd, rank_nm, is_leaf, subset_left_no, subset_right_no, nm, nm_sort, secondary_nm, object_acc_txt, NM_HTML, LEVEL_MIN_NO, has_chems, has_diseases, has_genes, has_marrays) SELECT n.id, n.dag_id, n.object_id, ( SELECT p.id FROM load.DAG_NODE p WHERE p.acc_txt = n.parent_acc_txt AND p.acc_db_id = n.parent_acc_db_id AND p.dag_id = n.dag_id) AS parent_id, n.acc_txt, get_db_cd (n.acc_db_id) AS acc_db_cd, n.rank_nm, n.is_leaf, n.subset_left_no, n.subset_right_no, t.nm, t.nm_sort, t.secondary_nm, t.acc_txt, break_html_words (t.nm), n.LEVEL_MIN_NO, 'f', 'f', 'f', 'f' FROM load.DAG_NODE n, load.TERM t WHERE t.id = n.object_id;
Date: 2025-11-24 18:10:00 Duration: 1m41s Bind query: yes
20 1m34s 1 1m34s 1m34s 1m34s create index ix_gene_disease_ref_chem on pub1.gene_disease_reference using btree (via_chem_id);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Nov 24 21 1 1m34s 1m34s -
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2025-11-24 21:42:23 Duration: 1m34s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2025-11-24 21:42:23 Duration: 0ms
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 60 55m2s 5s545ms 7m30s 55s35ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Nov 24 11 9 1m15s 8s429ms 12 29 27m12s 56s292ms 13 1 1m45s 1m45s 18 3 1m7s 22s630ms 21 4 9m2s 2m15s 22 3 7m7s 2m22s 23 11 7m30s 40s929ms [ User: load - Total duration: 15m4s - Times executed: 15 ]
[ Application: pg_bulkload - Total duration: 15m4s - Times executed: 15 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2025-11-24 21:33:49 Duration: 7m30s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2025-11-24 22:06:04 Duration: 5m23s 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: 2025-11-24 12:18:51 Duration: 4m40s Bind query: yes
2 17 2m15s 7s232ms 8s517ms 7s944ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort asc, pt.indirect_term_qty desc limit ?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Nov 24 03 1 8s411ms 8s411ms 04 3 22s218ms 7s406ms 05 4 29s384ms 7s346ms 06 3 24s782ms 8s260ms 07 6 50s254ms 8s375ms [ User: pubeu - Total duration: 1m19s - Times executed: 10 ]
[ User: qaeu - Total duration: 7s232ms - Times executed: 1 ]
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'MONTELUKAST')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0008150' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases', 'decreases', 'affects')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2025-11-24 07:47:24 Duration: 8s517ms Database: ctdprd51 User: pubeu Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'NELFINAVIR')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0008150' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases', 'decreases', 'affects')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2025-11-24 07:35:15 Duration: 8s417ms Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'MONTELUKAST')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0008150' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases', 'decreases', 'affects')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2025-11-24 07:27:30 Duration: 8s412ms Database: ctdprd51 User: pubeu Bind query: yes
3 8 1m3s 6s519ms 11s904ms 7s887ms select g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, i.id ixnid, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id where gcr.chem_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by g.nm_sort, c.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Nov 24 00 8 1m3s 7s887ms [ User: pubeu - Total duration: 44s27ms - Times executed: 6 ]
-
SELECT /* ChemGeneIxnsDAO */ g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, i.id ixnId, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1424283') GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2025-11-24 00:50:23 Duration: 11s904ms Bind query: yes
-
SELECT /* ChemGeneIxnsDAO */ g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, i.id ixnId, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1424283') GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2025-11-24 00:50:10 Duration: 8s236ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGeneIxnsDAO */ g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, i.id ixnId, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1424283') GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2025-11-24 00:50:14 Duration: 7s827ms Database: ctdprd51 User: pubeu Bind query: yes
4 6 34s786ms 5s287ms 7s114ms 5s797ms 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 #4
Day Hour Count Duration Avg duration Nov 24 02 2 10s926ms 5s463ms 08 2 11s243ms 5s621ms 13 1 5s501ms 5s501ms 21 1 7s114ms 7s114ms [ User: pubeu - Total duration: 34s786ms - Times executed: 6 ]
-
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 = '1368804' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-11-24 21:55:31 Duration: 7s114ms 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 = '1426923' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-11-24 08:01:29 Duration: 5s662ms 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 = '1428607' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-11-24 02:15:18 Duration: 5s638ms Database: ctdprd51 User: pubeu Bind query: yes
5 5 13m10s 42s237ms 10m18s 2m38s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub1.gene_chem_reference gcr, pub1.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Nov 24 18 1 10m18s 10m18s 19 4 2m51s 42s969ms [ User: load - Total duration: 10m18s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 18:59:57 Duration: 10m18s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 19:18:15 Duration: 43s750ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 19:29:10 Duration: 43s350ms Bind query: yes
6 3 1m8s 22s647ms 23s42ms 22s827ms select ? "Input", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( select string_agg(stm.slim_term_nm, ? order by stm.slim_term_nm) from slim_term_mapping stm where stm.mapped_term_id = d.id) "DiseaseCategories", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(distinct r.acc_txt, ?) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id where d.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) group by g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by d.nm_sort, g.nm, "DirectEvidence", c.nm;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Nov 24 02 3 1m8s 22s827ms [ User: pubeu - Total duration: 1m8s - Times executed: 3 ]
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'male infertility' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2122269) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2025-11-24 02:38:34 Duration: 23s42ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'male infertility' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2122269) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2025-11-24 02:38:46 Duration: 22s793ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'male infertility' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2122269) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2025-11-24 02:39:02 Duration: 22s647ms Database: ctdprd51 User: pubeu Bind query: yes
7 3 18s80ms 5s404ms 6s406ms 6s26ms 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 #7
Day Hour Count Duration Avg duration Nov 24 21 3 18s80ms 6s26ms [ User: pubeu - Total duration: 18s80ms - 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 = '1368804') 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 = '1368804') 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: 2025-11-24 21:47:21 Duration: 6s406ms 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 = '1368804') 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 = '1368804') 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: 2025-11-24 21:47:16 Duration: 6s269ms 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 = '1379008') 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 = '1379008') 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: 2025-11-24 21:24:15 Duration: 5s404ms Database: ctdprd51 User: pubeu Bind query: yes
8 3 15s687ms 5s95ms 5s364ms 5s229ms select ? "Input", sq.gene_symbol "GeneSymbol", sq.gene_acc_txt "GeneID", sq.ontology_nm "Ontology", sq.go_term_nm "GoTermName", sq.go_acc_txt "GoTermID" from ( select distinct g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort, d.nm ontology_nm, gt.nm go_term_nm, gt.acc_txt go_acc_txt, gt.nm_sort from term g inner join gene_go_annot gga on g.id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where (gt.object_id = ?) and gga.is_not = false order by gt.nm_sort, g.nm_sort) sq;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Nov 24 08 3 15s687ms 5s229ms [ User: pubeu - Total duration: 15s687ms - Times executed: 3 ]
-
SELECT /* BatchGeneGOAssnsDAO */ 'go:0005515' "Input", sq.gene_symbol "GeneSymbol", sq.gene_acc_txt "GeneID", sq.ontology_nm "Ontology", sq.go_term_nm "GoTermName", sq.go_acc_txt "GoTermID" FROM ( SELECT DISTINCT g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort, d.nm ontology_nm, gt.nm go_term_nm, gt.acc_txt go_acc_txt, gt.nm_sort FROM term g INNER JOIN gene_go_annot gga ON g.id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE (gt.object_id = 1288643) AND gga.is_not = false ORDER BY gt.nm_sort, g.nm_sort) sq;
Date: 2025-11-24 08:20:46 Duration: 5s364ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchGeneGOAssnsDAO */ 'go:0005515' "Input", sq.gene_symbol "GeneSymbol", sq.gene_acc_txt "GeneID", sq.ontology_nm "Ontology", sq.go_term_nm "GoTermName", sq.go_acc_txt "GoTermID" FROM ( SELECT DISTINCT g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort, d.nm ontology_nm, gt.nm go_term_nm, gt.acc_txt go_acc_txt, gt.nm_sort FROM term g INNER JOIN gene_go_annot gga ON g.id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE (gt.object_id = 1288643) AND gga.is_not = false ORDER BY gt.nm_sort, g.nm_sort) sq;
Date: 2025-11-24 08:17:26 Duration: 5s227ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchGeneGOAssnsDAO */ 'go:0005515' "Input", sq.gene_symbol "GeneSymbol", sq.gene_acc_txt "GeneID", sq.ontology_nm "Ontology", sq.go_term_nm "GoTermName", sq.go_acc_txt "GoTermID" FROM ( SELECT DISTINCT g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort, d.nm ontology_nm, gt.nm go_term_nm, gt.acc_txt go_acc_txt, gt.nm_sort FROM term g INNER JOIN gene_go_annot gga ON g.id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE (gt.object_id = 1288643) AND gga.is_not = false ORDER BY gt.nm_sort, g.nm_sort) sq;
Date: 2025-11-24 08:24:10 Duration: 5s95ms Database: ctdprd51 User: pubeu Bind query: yes
9 2 52s316ms 25s735ms 26s580ms 26s158ms select sq.*, count(*) over () fullrowcount from ( select distinct gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid from dag_node gt inner join gene_go_annot gga on gt.object_id = gga.go_term_id inner join term g on gga.gene_id = g.id where gt.id in ( select p.descendant_dag_node_id from dag_path p where p.ancestor_object_id = ?) and gga.is_not = false) sq order by sq.gonmsort, sq.genesymbolsort;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Nov 24 09 2 52s316ms 26s158ms [ User: pubeu - Total duration: 52s316ms - Times executed: 2 ]
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1246904') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort;
Date: 2025-11-24 09:06:58 Duration: 26s580ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1246904') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort;
Date: 2025-11-24 09:07:24 Duration: 25s735ms Database: ctdprd51 User: pubeu Bind query: yes
10 2 49s578ms 24s663ms 24s914ms 24s789ms select sq.*, count(*) over () fullrowcount from ( select distinct gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid from dag_node gt inner join gene_go_annot gga on gt.object_id = gga.go_term_id inner join term g on gga.gene_id = g.id where gt.id in ( select p.descendant_dag_node_id from dag_path p where p.ancestor_object_id = ?) and gga.is_not = false) sq order by sq.gonmsort, sq.genesymbolsort limit ?;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Nov 24 07 2 49s578ms 24s789ms [ User: pubeu - Total duration: 49s578ms - Times executed: 2 ]
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1246904') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-11-24 07:21:50 Duration: 24s914ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1246904') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-11-24 07:21:56 Duration: 24s663ms Database: ctdprd51 User: pubeu Bind query: yes
11 2 14s871ms 7s308ms 7s562ms 7s435ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort asc, pt.indirect_term_qty desc limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Nov 24 05 2 14s871ms 7s435ms [ User: pubeu - Total duration: 7s308ms - Times executed: 1 ]
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2025-11-24 05:44:14 Duration: 7s562ms Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2025-11-24 05:49:19 Duration: 7s308ms Database: ctdprd51 User: pubeu Bind query: yes
12 2 13s542ms 6s757ms 6s785ms 6s771ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Nov 24 13 2 13s542ms 6s771ms [ User: pubeu - Total duration: 13s542ms - Times executed: 2 ]
-
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 = '2118512') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-11-24 13:45:06 Duration: 6s785ms 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 = '2118512') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-11-24 13:45:00 Duration: 6s757ms Database: ctdprd51 User: pubeu Bind query: yes
13 2 11s189ms 5s418ms 5s771ms 5s594ms select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where phenotypeterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? order by chemnetworkcount desc, genenetworkcount desc;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Nov 24 08 2 11s189ms 5s594ms [ User: pubeu - Total duration: 11s189ms - Times executed: 2 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1246904') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc;
Date: 2025-11-24 08:57:54 Duration: 5s771ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1246904') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc;
Date: 2025-11-24 08:51:40 Duration: 5s418ms Database: ctdprd51 User: pubeu Bind query: yes
14 2 10s163ms 5s79ms 5s84ms 5s81ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Nov 24 05 2 10s163ms 5s81ms [ User: qaeu - Total duration: 5s84ms - Times executed: 1 ]
[ User: pubeu - Total duration: 5s79ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1326607)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2025-11-24 05:43:42 Duration: 5s84ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1326607)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2025-11-24 05:48:45 Duration: 5s79ms Database: ctdprd51 User: pubeu Bind query: yes
15 1 52m29s 52m29s 52m29s 52m29s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.gene_go_annot gga, pub1.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Nov 24 21 1 52m29s 52m29s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 21:21:02 Duration: 52m29s Bind query: yes
16 1 45m58s 45m58s 45m58s 45m58s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.object_type where cd = ?), ptr.term_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.phenotype_term_reference ptr, pub1.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Nov 24 20 1 45m58s 45m58s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 20:28:25 Duration: 45m58s Bind query: yes
17 1 34m26s 34m26s 34m26s 34m26s select i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) from edit.ixn i, edit.reference_ixn r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in (...) order by i.id asc;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Nov 24 18 1 34m26s 34m26s [ User: load - Total duration: 34m26s - 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: 2025-11-24 18:46:48 Duration: 34m26s Database: ctdprd51 User: load Bind query: yes
18 1 26m58s 26m58s 26m58s 26m58s insert into pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Nov 24 18 1 26m58s 26m58s -
insert into pub1.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;
Date: 2025-11-24 18:03:48 Duration: 26m58s Bind query: yes
19 1 15m3s 15m3s 15m3s 15m3s insert into pub1.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Nov 24 17 1 15m3s 15m3s [ User: pub1 - Total duration: 15m3s - Times executed: 1 ]
-
insert into pub1.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;
Date: 2025-11-24 17:31:25 Duration: 15m3s Database: ctdprd51 User: pub1 Bind query: yes
20 1 9m17s 9m17s 9m17s 9m17s select maint_query_logs_archive ();Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Nov 24 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: 2025-11-24 00:09:19 Duration: 9m17s Database: ctdprd51 User: pubc Application: psql
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 52m29s 52m29s 52m29s 1 52m29s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.gene_go_annot gga, pub1.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Nov 24 21 1 52m29s 52m29s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.GENE_GO_ANNOT gga, pub1.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 21:21:02 Duration: 52m29s Bind query: yes
2 45m58s 45m58s 45m58s 1 45m58s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.object_type where cd = ?), ptr.term_id, ( select id from pub1.object_type where cd = ?), ( select current_date) from pub1.gene_chem_reference gcr, pub1.phenotype_term_reference ptr, pub1.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Nov 24 20 1 45m58s 45m58s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub1.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub1.GENE_CHEM_REFERENCE gcr, pub1.PHENOTYPE_TERM_REFERENCE ptr, pub1.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub1.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 20:28:25 Duration: 45m58s Bind query: yes
3 34m26s 34m26s 34m26s 1 34m26s 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 #3
Day Hour Count Duration Avg duration Nov 24 18 1 34m26s 34m26s [ User: load - Total duration: 34m26s - 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: 2025-11-24 18:46:48 Duration: 34m26s Database: ctdprd51 User: load Bind query: yes
4 26m58s 26m58s 26m58s 1 26m58s insert into pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Nov 24 18 1 26m58s 26m58s -
insert into pub1.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;
Date: 2025-11-24 18:03:48 Duration: 26m58s Bind query: yes
5 15m3s 15m3s 15m3s 1 15m3s insert into pub1.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Nov 24 17 1 15m3s 15m3s [ User: pub1 - Total duration: 15m3s - Times executed: 1 ]
-
insert into pub1.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;
Date: 2025-11-24 17:31:25 Duration: 15m3s Database: ctdprd51 User: pub1 Bind query: yes
6 9m17s 9m17s 9m17s 1 9m17s select maint_query_logs_archive ();Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Nov 24 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: 2025-11-24 00:09:19 Duration: 9m17s Database: ctdprd51 User: pubc Application: psql
7 4m31s 4m31s 4m31s 1 4m31s insert into pub1.gene_taxon (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.gene_taxon;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Nov 24 18 1 4m31s 4m31s -
insert into pub1.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;
Date: 2025-11-24 18:08:19 Duration: 4m31s Bind query: yes
8 3m35s 3m35s 3m35s 1 3m35s create unique index gene_disease_reference_ak1 on pub1.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Nov 24 21 1 3m35s 3m35s -
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-11-24 21:38:50 Duration: 3m35s Bind query: yes
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub1.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-11-24 21:38:49 Duration: 0ms Database: ctdprd51 User: pub1
9 3m16s 3m16s 3m16s 1 3m16s insert into pub1.term_label (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.term t, load.term_label l where t.id = l.term_id and t.id in ( select id from pub1.term);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Nov 24 17 1 3m16s 3m16s -
INSERT INTO pub1.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub1.TERM);
Date: 2025-11-24 17:36:49 Duration: 3m16s Bind query: yes
10 42s237ms 10m18s 2m38s 5 13m10s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub1.gene_chem_reference gcr, pub1.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Nov 24 18 1 10m18s 10m18s 19 4 2m51s 42s969ms [ User: load - Total duration: 10m18s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 18:59:57 Duration: 10m18s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 19:18:15 Duration: 43s750ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub1.GENE_CHEM_REFERENCE gcr, pub1.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-11-24 19:29:10 Duration: 43s350ms Bind query: yes
11 2m34s 2m34s 2m34s 1 2m34s create index ix_gene_disease_ref_net_sc on pub1.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Nov 24 21 1 2m34s 2m34s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2025-11-24 21:52:56 Duration: 2m34s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub1.gene_disease_reference USING btree (network_score);
Date: 2025-11-24 21:52:55 Duration: 0ms
12 2m5s 2m5s 2m5s 1 2m5s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub1.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.object_type where cd = ?), cdr.mod_tm from pub1.chem_disease_reference cdr, pub1.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id and ptr.source_cd = ? and cdr.source_cd = ? and ptr.ixn_id not in ( select ixn_id from pub1.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Nov 24 21 1 2m5s 2m5s -
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub1.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub1.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub1.CHEM_DISEASE_REFERENCE cdr, pub1.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id AND ptr.source_cd = 'C' AND cdr.source_cd = 'C' AND ptr.ixn_id NOT IN ( SELECT ixn_id FROM pub1.IXN_AXN WHERE action_degree_type_nm = 'does not affect');
Date: 2025-11-24 21:23:15 Duration: 2m5s Bind query: yes
13 2m1s 2m1s 2m1s 1 2m1s create index ix_gene_disease_ref_dis_gene on pub1.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Nov 24 21 1 2m1s 2m1s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-11-24 21:47:19 Duration: 2m1s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub1.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-11-24 21:47:18 Duration: 0ms
14 2m 2m 2m 1 2m vacuum full analyze db_link;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Nov 24 13 1 2m 2m -
vacuum FULL analyze db_link;
Date: 2025-11-24 13:27:28 Duration: 2m
-
vacuum FULL analyze db_link;
Date: 2025-11-24 13:25:50 Duration: 0ms
15 1m56s 1m56s 1m56s 1 1m56s insert into pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) select t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) as db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), ?, ?, ?, ?, ?, ?, ? from load.term t where object_type_id not in (...);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Nov 24 17 1 1m56s 1m56s -
INSERT INTO pub1.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2025-11-24 17:33:33 Duration: 1m56s Bind query: yes
16 1m47s 1m47s 1m47s 1 1m47s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub1.phenotype_term_reference viachemptr, pub1.phenotype_term_reference viageneptr where viachemptr.term_id = viageneptr.term_id and viachemptr.term_object_type_id = ? and viachemptr.phenotype_id = viageneptr.phenotype_id and viachemptr.via_term_object_type_id = ? and viageneptr.via_term_object_type_id = ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Nov 24 21 1 1m47s 1m47s -
SELECT distinct viaChemPTR.via_term_id, -- chem viaGenePTR.via_term_id, -- gene viaChemPTR.phenotype_id, -- phenotype viaChemPTR.term_id -- disease FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 AND viaGenePTR.via_term_object_type_id = 4;
Date: 2025-11-24 21:59:01 Duration: 1m47s Bind query: yes
17 1m41s 1m41s 1m41s 1 1m41s insert into pub1.dag_node (id, dag_id, object_id, parent_id, acc_txt, acc_db_cd, rank_nm, is_leaf, subset_left_no, subset_right_no, nm, nm_sort, secondary_nm, object_acc_txt, nm_html, level_min_no, has_chems, has_diseases, has_genes, has_marrays) select n.id, n.dag_id, n.object_id, ( select p.id from load.dag_node p where p.acc_txt = n.parent_acc_txt and p.acc_db_id = n.parent_acc_db_id and p.dag_id = n.dag_id) as parent_id, n.acc_txt, get_db_cd (n.acc_db_id) as acc_db_cd, n.rank_nm, n.is_leaf, n.subset_left_no, n.subset_right_no, t.nm, t.nm_sort, t.secondary_nm, t.acc_txt, break_html_words (t.nm), n.level_min_no, ?, ?, ?, ? from load.dag_node n, load.term t where t.id = n.object_id;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Nov 24 18 1 1m41s 1m41s -
INSERT INTO pub1.DAG_NODE (id, dag_id, object_id, parent_id, acc_txt, acc_db_cd, rank_nm, is_leaf, subset_left_no, subset_right_no, nm, nm_sort, secondary_nm, object_acc_txt, NM_HTML, LEVEL_MIN_NO, has_chems, has_diseases, has_genes, has_marrays) SELECT n.id, n.dag_id, n.object_id, ( SELECT p.id FROM load.DAG_NODE p WHERE p.acc_txt = n.parent_acc_txt AND p.acc_db_id = n.parent_acc_db_id AND p.dag_id = n.dag_id) AS parent_id, n.acc_txt, get_db_cd (n.acc_db_id) AS acc_db_cd, n.rank_nm, n.is_leaf, n.subset_left_no, n.subset_right_no, t.nm, t.nm_sort, t.secondary_nm, t.acc_txt, break_html_words (t.nm), n.LEVEL_MIN_NO, 'f', 'f', 'f', 'f' FROM load.DAG_NODE n, load.TERM t WHERE t.id = n.object_id;
Date: 2025-11-24 18:10:00 Duration: 1m41s Bind query: yes
18 1m34s 1m34s 1m34s 1 1m34s create index ix_gene_disease_ref_chem on pub1.gene_disease_reference using btree (via_chem_id);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Nov 24 21 1 1m34s 1m34s -
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2025-11-24 21:42:23 Duration: 1m34s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_chem ON pub1.gene_disease_reference USING btree (via_chem_id);
Date: 2025-11-24 21:42:23 Duration: 0ms
19 5s545ms 7m30s 55s35ms 60 55m2s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Nov 24 11 9 1m15s 8s429ms 12 29 27m12s 56s292ms 13 1 1m45s 1m45s 18 3 1m7s 22s630ms 21 4 9m2s 2m15s 22 3 7m7s 2m22s 23 11 7m30s 40s929ms [ User: load - Total duration: 15m4s - Times executed: 15 ]
[ Application: pg_bulkload - Total duration: 15m4s - Times executed: 15 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2025-11-24 21:33:49 Duration: 7m30s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2025-11-24 22:06:04 Duration: 5m23s 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: 2025-11-24 12:18:51 Duration: 4m40s Bind query: yes
20 25s735ms 26s580ms 26s158ms 2 52s316ms select sq.*, count(*) over () fullrowcount from ( select distinct gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid from dag_node gt inner join gene_go_annot gga on gt.object_id = gga.go_term_id inner join term g on gga.gene_id = g.id where gt.id in ( select p.descendant_dag_node_id from dag_path p where p.ancestor_object_id = ?) and gga.is_not = false) sq order by sq.gonmsort, sq.genesymbolsort;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Nov 24 09 2 52s316ms 26s158ms [ User: pubeu - Total duration: 52s316ms - Times executed: 2 ]
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1246904') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort;
Date: 2025-11-24 09:06:58 Duration: 26s580ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1246904') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort;
Date: 2025-11-24 09:07:24 Duration: 25s735ms Database: ctdprd51 User: pubeu Bind query: yes
Time consuming prepare
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
Time consuming bind
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
-
Events
Log levels
Key values
- 150,137 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 0 FATAL entries
- 2 ERROR entries
- 0 WARNING entries
- 0 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 1 Max number of times the same event was reported
- 2 Total events found
Rank Times reported Error 1 1 ERROR: function get_ixn_prose(...) does not exist
Times Reported Most Frequent Error / Event #1
Day Hour Count Nov 24 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 ,pub2.term pubTerm -- set to CURRENT PRODUCTION PUB!!!!! where taxon_acc_txt not in ( select acc_txt from load.term where object_type_id = ( select id from edit.object_type where cd = 'taxon' ) ) and pubTerm.acc_txt = ri.taxon_acc_txt and object_type_id = ( select id from edit.object_type where cd = 'taxon' ) and taxon_acc_txt is not null and taxon_acc_txt <> ''Date: 2025-11-24 12:34:04
2 1 ERROR: relation "..." does not exist
Times Reported Most Frequent Error / Event #2
Day Hour Count Nov 24 10 1 - ERROR: relation "pub1.term" does not exist at character 24
Statement: select count(*) from pub1.term
Date: 2025-11-24 10:16:53 Database: ctdprd51 Application: pgAdmin 4 - CONN:6472735 User: load Remote: