pgBadger-
Global information
- Generated on Thu Oct 30 04:15:04 2025
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20251029
- Parsed 36,751 log entries in 3s
- Log start from 2025-10-29 00:00:01 to 2025-10-29 23:59:23
-
Overview
Global Stats
- 96 Number of unique normalized queries
- 275 Number of queries
- 5h19m8s Total query duration
- 2025-10-29 00:10:06 First query
- 2025-10-29 23:29:33 Last query
- 2 queries/s at 2025-10-29 20:48:57 Query peak
- 5h19m8s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 5h19m8s Execute total duration
- 21 Number of events
- 11 Number of unique normalized events
- 6 Max number of times the same event was reported
- 0 Number of cancellation
- 39 Total number of automatic vacuums
- 64 Total number of automatic analyzes
- 1,780 Number temporary file
- 1.00 GiB Max size of temporary file
- 205.95 MiB Average size of temporary file
- 3,532 Total number of sessions
- 144 sessions at 2025-10-29 23:36:38 Session peak
- 270d2h2m17s Total duration of sessions
- 1h50m6s Average duration of sessions
- 0 Average queries per session
- 5s421ms Average queries duration per session
- 1h50m1s Average idle time per session
- 3,542 Total number of connections
- 67 connections/s at 2025-10-29 20:47:37 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 2 queries/s Query Peak
- 2025-10-29 20:48:57 Date
SELECT Traffic
Key values
- 2 queries/s Query Peak
- 2025-10-29 20:48:57 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2025-10-29 23:09:23 Date
Queries duration
Key values
- 5h19m8s 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) Oct 29 00 4 0ms 10m5s 2m35s 0ms 10s762ms 10m12s 01 1 0ms 3m33s 3m33s 0ms 0ms 3m33s 02 10 0ms 29s322ms 11s444ms 14s175ms 17s104ms 29s322ms 03 21 0ms 28s724ms 13s716ms 17s480ms 19s760ms 32s538ms 04 15 0ms 17s310ms 11s536ms 16s828ms 21s210ms 28s768ms 05 9 0ms 7s447ms 6s179ms 5s524ms 14s354ms 14s527ms 06 8 0ms 24s295ms 20s255ms 0ms 41s475ms 1m45s 07 1 0ms 7s489ms 7s489ms 0ms 0ms 7s489ms 08 0 0ms 0ms 0ms 0ms 0ms 0ms 09 2 0ms 11s20ms 8s264ms 0ms 5s509ms 11s20ms 10 0 0ms 0ms 0ms 0ms 0ms 0ms 11 18 0ms 21s202ms 9s358ms 17s574ms 25s659ms 37s732ms 12 2 0ms 7s901ms 6s512ms 0ms 5s124ms 7s901ms 13 39 0ms 25s852ms 8s732ms 24s670ms 25s553ms 48s992ms 14 37 0ms 4m39s 35s229ms 1m32s 1m45s 4m39s 15 15 0ms 2m39s 54s88ms 1m20s 1m46s 2m39s 16 0 0ms 0ms 0ms 0ms 0ms 0ms 17 0 0ms 0ms 0ms 0ms 0ms 0ms 18 4 0ms 14m45s 5m3s 0ms 3m15s 14m55s 19 6 0ms 26m37s 5m45s 1m26s 4m29s 26m37s 20 31 0ms 33m52s 1m42s 54s628ms 1m7s 34m1s 21 11 0ms 44m31s 4m10s 12s924ms 25s266ms 44m31s 22 16 0ms 51m16s 4m34s 2m7s 7m35s 51m16s 23 25 0ms 5m14s 49s70ms 1m27s 1m55s 5m14s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Oct 29 00 3 0 3m25s 0ms 0ms 10m5s 01 1 0 3m33s 0ms 0ms 3m33s 02 10 0 11s444ms 0ms 14s175ms 29s322ms 03 21 0 13s716ms 16s614ms 17s480ms 32s538ms 04 15 0 11s536ms 12s29ms 16s828ms 28s768ms 05 9 0 6s179ms 0ms 5s524ms 14s527ms 06 8 0 20s255ms 0ms 0ms 1m45s 07 1 0 7s489ms 0ms 0ms 7s489ms 08 0 0 0ms 0ms 0ms 0ms 09 2 0 8s264ms 0ms 0ms 11s20ms 10 0 0 0ms 0ms 0ms 0ms 11 18 0 9s358ms 12s484ms 17s574ms 37s732ms 12 1 0 7s901ms 0ms 0ms 7s901ms 13 39 0 8s732ms 16s69ms 24s670ms 48s992ms 14 36 0 35s962ms 1m20s 1m32s 4m39s 15 11 0 59s42ms 6s633ms 1m20s 2m39s 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 0 0 0ms 0ms 0ms 0ms 19 0 0 0ms 0ms 0ms 0ms 20 31 0 1m42s 35s876ms 54s628ms 34m1s 21 11 0 4m10s 0ms 12s924ms 44m31s 22 10 0 6m22s 11s932ms 1m7s 51m16s 23 6 0 1m32s 0ms 13s937ms 5m14s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Oct 29 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 0 0 0 0 0ms 0ms 0ms 0ms 12 0 0 0 0 0ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 1 0 0 0 8s836ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 0 0 0 0 0ms 0ms 0ms 0ms 17 0 0 0 0 0ms 0ms 0ms 0ms 18 4 0 0 0 5m3s 0ms 0ms 3m15s 19 6 0 0 0 5m45s 0ms 0ms 4m29s 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 Oct 29 00 0 2 2.00 0.00% 01 0 1 1.00 0.00% 02 0 10 10.00 0.00% 03 0 21 21.00 0.00% 04 0 15 15.00 0.00% 05 0 9 9.00 0.00% 06 0 8 8.00 0.00% 07 0 1 1.00 0.00% 08 0 0 0.00 0.00% 09 0 2 2.00 0.00% 10 0 0 0.00 0.00% 11 0 18 18.00 0.00% 12 0 1 1.00 0.00% 13 0 39 39.00 0.00% 14 0 36 36.00 0.00% 15 0 10 10.00 0.00% 16 0 0 0.00 0.00% 17 0 0 0.00 0.00% 18 0 4 4.00 0.00% 19 0 6 6.00 0.00% 20 0 31 31.00 0.00% 21 0 11 11.00 0.00% 22 0 16 16.00 0.00% 23 0 25 25.00 0.00% Day Hour Count Average / Second Oct 29 00 81 0.02/s 01 79 0.02/s 02 100 0.03/s 03 296 0.08/s 04 292 0.08/s 05 98 0.03/s 06 80 0.02/s 07 77 0.02/s 08 74 0.02/s 09 76 0.02/s 10 83 0.02/s 11 104 0.03/s 12 95 0.03/s 13 599 0.17/s 14 163 0.05/s 15 101 0.03/s 16 78 0.02/s 17 81 0.02/s 18 77 0.02/s 19 82 0.02/s 20 422 0.12/s 21 227 0.06/s 22 86 0.02/s 23 91 0.03/s Day Hour Count Average Duration Average idle time Oct 29 00 81 30m33s 30m25s 01 79 30m8s 30m5s 02 100 25m11s 25m10s 03 296 7m49s 7m48s 04 292 8m1s 8m 05 98 25m12s 25m12s 06 80 29m27s 29m24s 07 77 29m37s 29m37s 08 74 30m19s 30m19s 09 76 32m11s 32m11s 10 78 31m53s 31m53s 11 100 2d6h25m59s 2d6h25m57s 12 95 25m44s 25m43s 13 599 3m57s 3m56s 14 163 14m58s 14m50s 15 101 24m36s 24m28s 16 78 30m49s 30m49s 17 81 30m 30m 18 76 31m24s 31m8s 19 82 30m41s 30m16s 20 422 6m10s 6m2s 21 227 9m44s 9m32s 22 86 1h47m18s 1h46m27s 23 91 27m15s 27m1s -
Connections
Established Connections
Key values
- 67 connections Connection Peak
- 2025-10-29 20:47:37 Date
Connections per database
Key values
- ctdprd51 Main Database
- 3,542 connections Total
Connections per user
Key values
- pubeu Main User
- 3,542 connections Total
-
Sessions
Simultaneous sessions
Key values
- 144 sessions Session Peak
- 2025-10-29 23:36:38 Date
Histogram of session times
Key values
- 1,765 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 3,532 sessions Total
Sessions per user
Key values
- pubeu Main User
- 3,532 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 3,532 sessions Total
Host Count Total Duration Average Duration 10.12.5.122 12 225d2h10m50s 18d18h10m54s 10.12.5.45 382 7d23h55m12s 30m8s 10.12.5.46 376 8d10m21s 30m39s 10.12.5.52 15 28s318ms 1s887ms 10.12.5.53 1,860 8d2h55m26s 6m17s 10.12.5.54 375 8d1m56s 30m43s 10.12.5.55 371 7d23h57m24s 31m2s 10.12.5.56 140 6h5m36s 2m36s ::1 1 4d14h45m 4d14h45m -
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 2,783,395 buffers Checkpoint Peak
- 2025-10-29 22:54:30 Date
- 1620.010 seconds Highest write time
- 0.811 seconds Sync time
Checkpoints Wal files
Key values
- 552 files Wal files usage Peak
- 2025-10-29 23:36:38 Date
Checkpoints distance
Key values
- 17,740.52 Mo Distance Peak
- 2025-10-29 22:54:39 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Oct 29 00 1,755 175.999s 0.003s 176.071s 01 89 9.088s 0.002s 9.167s 02 75 7.692s 0.002s 7.723s 03 187 18.935s 0.002s 18.965s 04 1,216 122.029s 0.004s 122.06s 05 146 14.712s 0.002s 14.742s 06 135 13.703s 0.003s 13.735s 07 202 20.413s 0.002s 20.444s 08 404 40.648s 0.002s 40.678s 09 220 22.212s 0.002s 22.242s 10 185 18.703s 0.002s 18.782s 11 333 33.537s 0.002s 33.566s 12 2,856 286.048s 0.151s 286.37s 13 43,775 1,646.059s 0.003s 1,646.46s 14 286,138 1,992.916s 1.179s 2,022.669s 15 42,028 1,656.423s 0.815s 1,671.406s 16 35,109 1,633.617s 0.004s 1,642.68s 17 37 3.876s 0.002s 3.907s 18 2,427,189 1,223.154s 0.706s 1,251.774s 19 580,979 1,619.831s 0.025s 1,626.343s 20 824,686 3,240.626s 0.017s 3,245.968s 21 273 27.543s 0.002s 27.573s 22 2,783,784 160.109s 0.758s 183.815s 23 616,007 1,623.312s 1.451s 1,646.527s Day Hour Added Removed Recycled Synced files Longest sync Average sync Oct 29 00 0 0 0 63 0.001s 0.002s 01 0 0 1 28 0.001s 0.002s 02 0 0 0 25 0.001s 0.002s 03 0 0 0 36 0.001s 0.002s 04 0 0 0 48 0.001s 0.002s 05 0 0 0 27 0.001s 0.002s 06 0 0 0 32 0.001s 0.002s 07 0 0 0 29 0.001s 0.002s 08 0 0 0 119 0.001s 0.002s 09 0 0 0 71 0.001s 0.002s 10 0 0 1 62 0.001s 0.002s 11 0 0 0 77 0.001s 0.002s 12 0 0 1 658 0.002s 0.002s 13 0 0 28 205 0.001s 0.002s 14 0 177 2,151 467 0.576s 0.016s 15 0 6 1,071 122 0.799s 0.013s 16 0 0 742 170 0.001s 0.003s 17 0 0 0 17 0.001s 0.002s 18 0 31 2,153 279 0.132s 0.014s 19 0 0 538 134 0.001s 0.001s 20 0 0 432 267 0.001s 0.003s 21 0 0 0 27 0.001s 0.002s 22 0 32 1,607 116 0.741s 0.046s 23 0 0 1,638 123 0.774s 0.076s Day Hour Count Avg time (sec) Oct 29 00 0 0s 01 0 0s 02 0 0s 03 0 0s 04 0 0s 05 0 0s 06 0 0s 07 0 0s 08 0 0s 09 0 0s 10 0 0s 11 0 0s 12 0 0s 13 0 0s 14 0 0s 15 0 0s 16 0 0s 17 0 0s 18 0 0s 19 0 0s 20 0 0s 21 0 0s 22 0 0s 23 0 0s Day Hour Mean distance Mean estimate Oct 29 00 6,462.50 kB 12,240.00 kB 01 228.00 kB 9,958.00 kB 02 183.50 kB 8,088.00 kB 03 518.00 kB 6,640.00 kB 04 4,177.00 kB 6,052.00 kB 05 373.50 kB 5,089.00 kB 06 318.50 kB 4,206.00 kB 07 457.00 kB 3,476.50 kB 08 1,170.00 kB 2,988.50 kB 09 614.50 kB 2,603.50 kB 10 549.50 kB 2,175.50 kB 11 865.00 kB 1,936.50 kB 12 7,505.50 kB 8,303.50 kB 13 236,237.00 kB 447,695.50 kB 14 7,523,863.20 kB 7,527,997.80 kB 15 8,817,524.50 kB 8,828,721.00 kB 16 4,229,788.00 kB 8,219,332.33 kB 17 69.50 kB 6,411,174.50 kB 18 7,050,458.40 kB 8,143,721.00 kB 19 8,817,114.00 kB 8,819,504.00 kB 20 2,533,216.00 kB 7,856,723.00 kB 21 659.50 kB 6,023,574.00 kB 22 5,340,800.20 kB 7,341,924.20 kB 23 8,823,021.33 kB 9,011,832.33 kB -
Temporary Files
Size of temporary files
Key values
- 18.00 GiB Temp Files size Peak
- 2025-10-29 22:36:42 Date
Number of temporary files
Key values
- 18 per second Temp Files Peak
- 2025-10-29 22:36:42 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Oct 29 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 0 0 0 07 0 0 0 08 0 0 0 09 0 0 0 10 0 0 0 11 0 0 0 12 0 0 0 13 0 0 0 14 1,066 45.31 GiB 43.52 MiB 15 277 24.81 GiB 91.70 MiB 16 0 0 0 17 0 0 0 18 0 0 0 19 0 0 0 20 28 27.54 GiB 1007.08 MiB 21 56 55.68 GiB 1018.07 MiB 22 168 142.51 GiB 868.63 MiB 23 185 62.16 GiB 344.08 MiB Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 1,238 71.83 GiB 8.00 KiB 1.00 GiB 59.41 MiB select * from pgbulkload.pg_bulkload (?);-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2025-10-29 22:49:03 Duration: 7m20s Database: ctdprd51 User: load Application: pg_bulkload
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2025-10-29 23:20:40 Duration: 5m14s
-
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-10-29 14:41:47 Duration: 4m39s
2 86 85.56 GiB 568.92 MiB 1.00 GiB 1018.71 MiB select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in;-
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in;
Date: 2025-10-29 22:36:38 Duration: 0ms
3 56 55.68 GiB 692.13 MiB 1.00 GiB 1018.07 MiB select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.object_type where cd = ?), ptr.term_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.phenotype_term_reference ptr, pub2.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in;-
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in;
Date: 2025-10-29 21:45:14 Duration: 0ms
4 35 4.45 GiB 72.54 MiB 172.51 MiB 130.19 MiB vacuum full analyze db_link;-
vacuum FULL analyze db_link;
Date: 2025-10-29 15:57:10 Duration: 2m
-
vacuum FULL analyze db_link;
Date: 2025-10-29 15:55:31 Duration: 0ms
5 35 1.20 GiB 24.15 MiB 48.97 MiB 35.17 MiB vacuum full analyze ixn_actor;-
vacuum FULL analyze ixn_actor;
Date: 2025-10-29 15:54:43 Duration: 24s47ms
-
vacuum FULL analyze ixn_actor;
Date: 2025-10-29 15:54:26 Duration: 0ms
6 28 27.54 GiB 550.16 MiB 1.00 GiB 1007.08 MiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;
Date: 2025-10-29 20:56:48 Duration: 0ms
7 25 396.21 MiB 10.88 MiB 21.77 MiB 15.85 MiB vacuum full analyze ixn;-
vacuum FULL analyze ixn;
Date: 2025-10-29 15:55:00 Duration: 7s675ms
-
vacuum FULL analyze ixn;
Date: 2025-10-29 15:54:55 Duration: 0ms
8 20 13.49 GiB 8.00 KiB 1.00 GiB 690.52 MiB create unique index gene_disease_reference_ak1 on pub2.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-10-29 22:55:07 Duration: 3m16s
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-10-29 22:55:06 Duration: 0ms
9 20 219.05 MiB 5.09 MiB 16.95 MiB 10.95 MiB vacuum full analyze term;-
vacuum FULL analyze TERM;
Date: 2025-10-29 15:54:13 Duration: 9s530ms
-
vacuum FULL analyze TERM;
Date: 2025-10-29 15:54:06 Duration: 0ms
10 15 7.49 GiB 8.00 KiB 1.00 GiB 511.50 MiB alter table pub2.gene_disease_reference add constraint gene_disease_reference_pk primary key (id);-
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2025-10-29 22:51:50 Duration: 1m23s
-
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2025-10-29 22:51:49 Duration: 0ms Database: ctdprd51 User: pub2
11 10 7.49 GiB 435.53 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_ref_dis_gene on pub2.gene_disease_reference using btree (disease_id, gene_id);-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-10-29 23:03:18 Duration: 1m55s
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-10-29 23:03:18 Duration: 0ms
12 10 7.49 GiB 439.95 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_ref_mod_tm on pub2.gene_disease_reference using btree (mod_tm);-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub2.gene_disease_reference USING btree (mod_tm);
Date: 2025-10-29 23:06:14 Duration: 1m27s
-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub2.gene_disease_reference USING btree (mod_tm);
Date: 2025-10-29 23:06:13 Duration: 0ms
13 10 7.49 GiB 422.51 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_ref_reference on pub2.gene_disease_reference using btree (reference_id);-
CREATE INDEX ix_gene_disease_ref_reference ON pub2.gene_disease_reference USING btree (reference_id);
Date: 2025-10-29 23:01:23 Duration: 1m24s
-
CREATE INDEX ix_gene_disease_ref_reference ON pub2.gene_disease_reference USING btree (reference_id);
Date: 2025-10-29 23:01:22 Duration: 0ms
14 10 258.40 MiB 8.00 KiB 52.09 MiB 25.84 MiB alter table pub2.chem_disease_reference add constraint chem_disease_reference_pk primary key (id);-
ALTER TABLE pub2.chem_disease_reference ADD CONSTRAINT chem_disease_reference_pk PRIMARY KEY (id);
Date: 2025-10-29 23:11:03 Duration: 0ms
15 10 1.13 GiB 8.00 KiB 232.98 MiB 115.57 MiB alter table pub2.phenotype_term_reference add constraint phenotype_term_reference_pk primary key (id);-
ALTER TABLE pub2.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2025-10-29 23:08:59 Duration: 12s350ms
-
ALTER TABLE pub2.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2025-10-29 23:08:58 Duration: 0ms
16 10 471.83 MiB 8.00 KiB 99.70 MiB 47.18 MiB create unique index chem_disease_reference_ak1 on pub2.chem_disease_reference using btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);-
CREATE UNIQUE INDEX chem_disease_reference_ak1 ON pub2.chem_disease_reference USING btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);
Date: 2025-10-29 23:11:10 Duration: 6s393ms
-
CREATE UNIQUE INDEX chem_disease_reference_ak1 ON pub2.chem_disease_reference USING btree (chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id);
Date: 2025-10-29 23:11:10 Duration: 0ms
17 10 7.49 GiB 497.75 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_ref_src_db on pub2.gene_disease_reference using btree (source_acc_db_id);-
CREATE INDEX ix_gene_disease_ref_src_db ON pub2.gene_disease_reference USING btree (source_acc_db_id);
Date: 2025-10-29 22:55:57 Duration: 50s395ms
-
CREATE INDEX ix_gene_disease_ref_src_db ON pub2.gene_disease_reference USING btree (source_acc_db_id);
Date: 2025-10-29 22:55:56 Duration: 0ms
18 10 7.49 GiB 466.77 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_reference_ixn on pub2.gene_disease_reference using btree (ixn_id);-
CREATE INDEX ix_gene_disease_reference_ixn ON pub2.gene_disease_reference USING btree (ixn_id);
Date: 2025-10-29 23:04:46 Duration: 1m27s
-
CREATE INDEX ix_gene_disease_reference_ixn ON pub2.gene_disease_reference USING btree (ixn_id);
Date: 2025-10-29 23:04:45 Duration: 0ms
19 10 7.49 GiB 422.51 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_ref_chem on pub2.gene_disease_reference using btree (via_chem_id);-
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2025-10-29 22:58:31 Duration: 1m34s
-
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2025-10-29 22:58:30 Duration: 0ms
20 10 7.49 GiB 296.76 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_ref_source_cd on pub2.gene_disease_reference using btree (source_cd);-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub2.gene_disease_reference USING btree (source_cd);
Date: 2025-10-29 22:56:56 Duration: 59s393ms
-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub2.gene_disease_reference USING btree (source_cd);
Date: 2025-10-29 22:56:56 Duration: 0ms
21 10 7.49 GiB 444.20 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_ref_net_sc on pub2.gene_disease_reference using btree (network_score);-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-10-29 23:08:47 Duration: 2m33s
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-10-29 23:08:47 Duration: 0ms
22 10 7.49 GiB 422.66 MiB 1.00 GiB 767.25 MiB create index ix_gene_disease_ref_disease on pub2.gene_disease_reference using btree (disease_id);-
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2025-10-29 22:59:59 Duration: 1m28s
-
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2025-10-29 22:59:58 Duration: 0ms
23 7 6.00 GiB 4.34 MiB 1.00 GiB 878.33 MiB select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub2.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.object_type where cd = ?), cdr.mod_tm from pub2.chem_disease_reference cdr, pub2.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id;-
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub2.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub2.CHEM_DISEASE_REFERENCE cdr, pub2.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id;
Date: 2025-10-29 22:40:46 Duration: 0ms
24 5 1.13 GiB 229.53 MiB 232.77 MiB 231.12 MiB create index ix_phenotype_term_reference_ixn_id on pub2.phenotype_term_reference using btree (ixn_id);-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub2.phenotype_term_reference USING btree (ixn_id);
Date: 2025-10-29 23:10:33 Duration: 13s13ms
-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub2.phenotype_term_reference USING btree (ixn_id);
Date: 2025-10-29 23:10:33 Duration: 0ms
25 5 258.38 MiB 49.62 MiB 53.52 MiB 51.67 MiB create index ix_chem_disease_ref_src_db on pub2.chem_disease_reference using btree (source_acc_db_id);-
CREATE INDEX ix_chem_disease_ref_src_db ON pub2.chem_disease_reference USING btree (source_acc_db_id);
Date: 2025-10-29 23:11:20 Duration: 0ms
26 5 1.58 GiB 317.48 MiB 350.52 MiB 324.35 MiB create index ix_phenotype_term_ref_ids on pub2.phenotype_term_reference using btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);-
CREATE INDEX ix_phenotype_term_ref_ids ON pub2.phenotype_term_reference USING btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);
Date: 2025-10-29 23:11:00 Duration: 14s810ms
-
CREATE INDEX ix_phenotype_term_ref_ids ON pub2.phenotype_term_reference USING btree (phenotype_id, term_id, via_term_object_type_id, term_object_type_id);
Date: 2025-10-29 23:11:00 Duration: 0ms
27 5 1.13 GiB 221.71 MiB 239.09 MiB 231.13 MiB create index ix_phenotype_term_ref_reference_id on pub2.phenotype_term_reference using btree (reference_id);-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub2.phenotype_term_reference USING btree (reference_id);
Date: 2025-10-29 23:09:44 Duration: 11s834ms
-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub2.phenotype_term_reference USING btree (reference_id);
Date: 2025-10-29 23:09:44 Duration: 0ms
28 5 1.13 GiB 225.62 MiB 233.96 MiB 231.13 MiB create index ix_phenotype_term_ref_taxon_id on pub2.phenotype_term_reference using btree (taxon_id);-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub2.phenotype_term_reference USING btree (taxon_id);
Date: 2025-10-29 23:09:52 Duration: 8s32ms
-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub2.phenotype_term_reference USING btree (taxon_id);
Date: 2025-10-29 23:09:52 Duration: 0ms
29 5 1.13 GiB 221.63 MiB 242.94 MiB 231.13 MiB create index ix_phenotype_term_reference_term_reference_id on pub2.phenotype_term_reference using btree (term_reference_id);-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub2.phenotype_term_reference USING btree (term_reference_id);
Date: 2025-10-29 23:10:20 Duration: 11s895ms
-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub2.phenotype_term_reference USING btree (term_reference_id);
Date: 2025-10-29 23:10:20 Duration: 0ms
30 5 1.13 GiB 221.12 MiB 244.89 MiB 231.13 MiB create index ix_phenotype_term_ref_via_term_id on pub2.phenotype_term_reference using btree (via_term_id);-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub2.phenotype_term_reference USING btree (via_term_id);
Date: 2025-10-29 23:10:45 Duration: 11s970ms
-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub2.phenotype_term_reference USING btree (via_term_id);
Date: 2025-10-29 23:10:45 Duration: 0ms
31 5 1.13 GiB 228.16 MiB 239.79 MiB 231.13 MiB create index ix_phenotype_term_ref_object_type_id on pub2.phenotype_term_reference using btree (term_object_type_id);-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub2.phenotype_term_reference USING btree (term_object_type_id);
Date: 2025-10-29 23:09:32 Duration: 9s178ms
-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub2.phenotype_term_reference USING btree (term_object_type_id);
Date: 2025-10-29 23:09:32 Duration: 0ms
32 5 1.13 GiB 229.29 MiB 233.05 MiB 231.13 MiB create index ix_phenotype_term_ref_term_id on pub2.phenotype_term_reference using btree (term_id);-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub2.phenotype_term_reference USING btree (term_id);
Date: 2025-10-29 23:09:23 Duration: 12s177ms
-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub2.phenotype_term_reference USING btree (term_id);
Date: 2025-10-29 23:09:23 Duration: 0ms
33 5 258.38 MiB 48.23 MiB 53.90 MiB 51.67 MiB create index ix_chem_disease_reference_gene on pub2.chem_disease_reference using btree (via_gene_id);-
CREATE INDEX ix_chem_disease_reference_gene ON pub2.chem_disease_reference USING btree (via_gene_id);
Date: 2025-10-29 23:11:23 Duration: 0ms
34 5 1.13 GiB 229.61 MiB 232.73 MiB 231.13 MiB create index ix_phenotype_term_reference_source_acc_db_id on pub2.phenotype_term_reference using btree (source_acc_db_id);-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub2.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2025-10-29 23:10:09 Duration: 8s374ms
-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub2.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2025-10-29 23:10:08 Duration: 0ms
35 5 258.37 MiB 46.93 MiB 54.88 MiB 51.67 MiB create index ix_chem_disease_ref_net_sc on pub2.chem_disease_reference using btree (network_score);-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub2.chem_disease_reference USING btree (network_score);
Date: 2025-10-29 23:11:34 Duration: 5s286ms
-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub2.chem_disease_reference USING btree (network_score);
Date: 2025-10-29 23:11:34 Duration: 0ms
36 5 258.38 MiB 49.24 MiB 53.39 MiB 51.67 MiB create index ix_chem_disease_reference_ixn on pub2.chem_disease_reference using btree (ixn_id);-
CREATE INDEX ix_chem_disease_reference_ixn ON pub2.chem_disease_reference USING btree (ixn_id);
Date: 2025-10-29 23:11:26 Duration: 0ms
37 5 258.37 MiB 50.10 MiB 53.38 MiB 51.67 MiB create index ix_chem_disease_ref_source_cd on pub2.chem_disease_reference using btree (source_cd);-
CREATE INDEX ix_chem_disease_ref_source_cd ON pub2.chem_disease_reference USING btree (source_cd);
Date: 2025-10-29 23:11:18 Duration: 0ms
38 5 258.38 MiB 51.47 MiB 51.95 MiB 51.67 MiB create index ix_chem_disease_reference_ref on pub2.chem_disease_reference using btree (reference_id);-
CREATE INDEX ix_chem_disease_reference_ref ON pub2.chem_disease_reference USING btree (reference_id);
Date: 2025-10-29 23:11:16 Duration: 0ms
39 5 258.38 MiB 51.33 MiB 51.93 MiB 51.67 MiB create index ix_chem_disease_reference_dis on pub2.chem_disease_reference using btree (disease_id);-
CREATE INDEX ix_chem_disease_reference_dis ON pub2.chem_disease_reference USING btree (disease_id);
Date: 2025-10-29 23:11:13 Duration: 0ms
40 5 258.38 MiB 49.73 MiB 53.20 MiB 51.67 MiB create index ix_chem_disease_ref_mod_tm on pub2.chem_disease_reference using btree (mod_tm);-
CREATE INDEX ix_chem_disease_ref_mod_tm ON pub2.chem_disease_reference USING btree (mod_tm);
Date: 2025-10-29 23:11:29 Duration: 0ms
41 5 1.13 GiB 227.30 MiB 235.58 MiB 231.13 MiB create index ix_phenotype_term_ref_phenotype_id on pub2.phenotype_term_reference using btree (phenotype_id);-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub2.phenotype_term_reference USING btree (phenotype_id);
Date: 2025-10-29 23:09:11 Duration: 11s539ms
-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub2.phenotype_term_reference USING btree (phenotype_id);
Date: 2025-10-29 23:09:11 Duration: 0ms
42 5 1.13 GiB 225.97 MiB 234.55 MiB 231.13 MiB create index ix_phenotype_term_ref_evidence_cd on pub2.phenotype_term_reference using btree (evidence_cd);-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub2.phenotype_term_reference USING btree (evidence_cd);
Date: 2025-10-29 23:10:00 Duration: 7s918ms
-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub2.phenotype_term_reference USING btree (evidence_cd);
Date: 2025-10-29 23:10:00 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB SELECT * FROM pgbulkload.pg_bulkload ($1);[ Date: 2025-10-29 15:14:19 - Database: ctdprd51 - User: load - Application: pg_bulkload ]
2 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:48 ]
3 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:48 ]
4 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:48 ]
5 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
6 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
7 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
8 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
9 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
10 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
11 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
12 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
13 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
14 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
15 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
16 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
17 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
18 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
19 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:49 ]
20 1.00 GiB select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn;[ Date: 2025-10-29 20:56:50 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 73.88 sec Highest CPU-cost vacuum
Table pub2.gene_go_annot
Database ctdprd51 - 2025-10-29 19:21:24 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 73.88 sec Highest CPU-cost vacuum
Table pub2.gene_go_annot
Database ctdprd51 - 2025-10-29 19:21:24 Date
Analyzes per table
Key values
- pubc.log_query (18) Main table analyzed (database ctdprd51)
- 64 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 18 ctdprd51.pg_catalog.pg_class 3 ctdprd51.pub2.db 2 postgres.pg_catalog.pg_shdepend 2 ctdprd51.pub2.term 2 ctdprd51.edit.action_type 1 ctdprd51.edit.db_link 1 ctdprd51.pub2.action_type 1 ctdprd51.pub1.term_set_enrichment 1 ctdprd51.pg_catalog.pg_attrdef 1 ctdprd51.pub2.reference_party_role 1 ctdprd51.pub2.db_link 1 ctdprd51.edit.db_report 1 ctdprd51.pub2.term_pathway 1 ctdprd51.pub2.list_db_report 1 ctdprd51.pub2.gene_go_annot 1 ctdprd51.pg_catalog.pg_type 1 ctdprd51.edit.reference_db_link 1 ctdprd51.pg_catalog.pg_constraint 1 ctdprd51.pg_catalog.pg_trigger 1 ctdprd51.pub2.db_report_site 1 ctdprd51.edit.action_degree 1 ctdprd51.pub2.dag_edge 1 ctdprd51.pub2.reference 1 ctdprd51.edit.action_type_path 1 ctdprd51.load.data_load 1 ctdprd51.pub2.term_label 1 ctdprd51.pub2.reference_party 1 ctdprd51.edit.object_note 1 ctdprd51.pg_catalog.pg_index 1 ctdprd51.pub2.dag_node 1 ctdprd51.pg_catalog.pg_attribute 1 ctdprd51.edit.list_db_report 1 ctdprd51.edit.db 1 ctdprd51.pg_catalog.pg_depend 1 ctdprd51.pub2.gene_taxon 1 ctdprd51.edit.db_report_site 1 ctdprd51.pub2.img 1 ctdprd51.pg_catalog.pg_description 1 ctdprd51.pg_catalog.pg_shdepend 1 ctdprd51.pub2.db_report 1 ctdprd51.edit.country 1 Total 64 Vacuums per table
Key values
- pubc.log_query (3) Main table vacuumed on database ctdprd51
- 39 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pubc.log_query 3 2 866 0 160 0 0 287 106 746,954 ctdprd51.pg_catalog.pg_class 2 2 714 0 80 0 20 370 75 313,044 ctdprd51.pub2.term 2 0 137,671 0 4 0 0 46,589 2 2,765,589 ctdprd51.edit.reference_db_link 1 0 7,409 0 4 0 0 3,691 1 226,095 ctdprd51.pg_catalog.pg_trigger 1 1 327 0 30 0 0 125 32 162,245 ctdprd51.pg_catalog.pg_constraint 1 1 269 0 21 0 0 103 20 103,831 ctdprd51.edit.action_degree 1 0 45 0 0 0 0 12 1 9,451 ctdprd51.edit.db_link 1 0 7,619 0 3 0 0 3,691 1 226,164 ctdprd51.edit.action_type 1 0 174 0 1 0 0 7 1 9,121 ctdprd51.pg_toast.pg_toast_9596218 1 0 89,670 0 4 0 0 44,827 2 2,661,316 ctdprd51.pub2.db 1 1 148 0 13 0 0 20 10 33,371 ctdprd51.pub2.term_pathway 1 0 3,365 0 3 0 0 1,614 1 103,645 ctdprd51.edit.db_report 1 0 96 0 2 0 0 9 1 9,434 ctdprd51.pub2.db_link 1 0 295,688 0 131,079 0 0 147,720 5 8,756,687 ctdprd51.pub2.reference_party_role 1 0 13,634 0 5 0 0 6,790 2 411,121 ctdprd51.pg_catalog.pg_attrdef 1 1 105 0 4 0 0 24 3 16,864 ctdprd51.pub2.gene_go_annot 1 0 628,053 0 313,922 0 0 313,909 11 18,605,735 ctdprd51.pg_catalog.pg_depend 1 1 610 0 70 0 65 281 79 326,919 ctdprd51.pg_catalog.pg_attribute 1 1 834 0 62 0 37 422 61 321,991 ctdprd51.edit.db_report_site 1 0 60 0 3 0 0 11 2 15,267 ctdprd51.pg_catalog.pg_description 1 1 207 0 41 0 42 127 22 99,819 ctdprd51.pub2.img 1 0 1,109 0 5 0 0 524 2 42,247 ctdprd51.pub2.gene_taxon 1 0 165,051 0 6 0 0 82,466 3 4,890,307 ctdprd51.pg_catalog.pg_shdepend 1 1 211 0 21 0 0 108 20 73,435 ctdprd51.edit.country 1 0 63 0 0 0 0 8 1 9,627 ctdprd51.pub2.reference 1 0 78,337 0 5 0 0 39,059 3 2,324,013 ctdprd51.pub2.dag_edge 1 0 1,024 0 5 0 0 482 2 39,769 ctdprd51.pub2.reference_party 1 0 5,142 0 3 0 0 2,537 1 158,102 ctdprd51.pub2.term_label 1 0 190,421 0 6 0 0 95,159 4 5,642,106 ctdprd51.edit.action_type_path 1 0 48 0 0 0 0 4 1 9,059 ctdprd51.edit.object_note 1 1 168 0 1 0 0 12 1 9,817 ctdprd51.pg_catalog.pg_statistic 1 1 860 0 95 0 117 639 84 270,287 ctdprd51.pg_toast.pg_toast_2619 1 1 5,690 0 967 0 10,025 4,547 913 536,139 ctdprd51.pub2.dag_node 1 0 83,916 0 5 0 0 41,817 3 2,486,463 ctdprd51.pg_catalog.pg_index 1 1 195 0 23 0 0 99 18 91,211 Total 39 16 1,719,799 773 446,653 0 10,306 838,090 1,494 52,507,245 Tuples removed per table
Key values
- pg_toast.pg_toast_2619 (5763) Main table with removed tuples on database ctdprd51
- 14411 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pg_toast.pg_toast_2619 1 1 5,763 19,755 0 0 12,592 ctdprd51.pg_catalog.pg_attribute 1 1 1,736 8,740 0 0 230 ctdprd51.pg_catalog.pg_depend 1 1 1,654 14,068 0 0 139 ctdprd51.pg_catalog.pg_description 1 1 1,317 5,251 0 0 90 ctdprd51.pg_catalog.pg_statistic 1 1 949 2,702 0 0 410 ctdprd51.pg_catalog.pg_shdepend 1 1 659 2,075 0 0 24 ctdprd51.pg_catalog.pg_class 2 2 578 4,026 0 0 188 ctdprd51.pg_catalog.pg_trigger 1 1 453 1,797 0 0 49 ctdprd51.pg_catalog.pg_index 1 1 227 1,167 0 0 38 ctdprd51.edit.country 1 0 163 249 0 0 4 ctdprd51.pub2.db 1 1 134 134 0 0 7 ctdprd51.pg_catalog.pg_constraint 1 1 113 879 0 0 35 ctdprd51.pg_catalog.pg_attrdef 1 1 112 234 0 0 12 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.db_report 1 0 94 162 0 0 4 ctdprd51.edit.db_report_site 1 0 94 164 0 0 5 ctdprd51.edit.action_type 1 0 64 60 0 0 3 ctdprd51.edit.object_note 1 1 61 33 0 0 3 ctdprd51.pubc.log_query 3 2 38 4,467 1 0 159 ctdprd51.edit.reference_db_link 1 0 0 331,169 0 0 3,690 ctdprd51.edit.db_link 1 0 0 331,169 0 0 3,690 ctdprd51.pg_toast.pg_toast_9596218 1 0 0 242,315 0 0 44,826 ctdprd51.pub2.term_pathway 1 0 0 135,792 0 0 1,613 ctdprd51.pub2.db_link 1 0 0 20,395,792 0 0 147,719 ctdprd51.pub2.reference_party_role 1 0 0 1,255,850 0 0 6,789 ctdprd51.pub2.gene_go_annot 1 0 0 49,284,996 0 0 313,908 ctdprd51.pub2.img 1 0 0 50,671 0 0 523 ctdprd51.pub2.gene_taxon 1 0 0 12,947,050 0 0 82,465 ctdprd51.pub2.term 2 0 0 2,178,116 0 0 73,114 ctdprd51.pub2.reference 1 0 0 200,977 0 0 39,058 ctdprd51.pub2.dag_edge 1 0 0 88,931 0 0 481 ctdprd51.pub2.reference_party 1 0 0 454,035 0 0 2,536 ctdprd51.pub2.term_label 1 0 0 6,552,163 0 0 95,158 ctdprd51.pub2.dag_node 1 0 0 1,739,883 0 0 41,816 Total 39 16 14,411 96,255,197 1 0 871,386 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.edit.reference_db_link 1 0 0 0 ctdprd51.pg_catalog.pg_trigger 1 1 453 0 ctdprd51.pg_catalog.pg_constraint 1 1 113 0 ctdprd51.pg_catalog.pg_class 2 2 578 0 ctdprd51.edit.action_degree 1 0 96 0 ctdprd51.edit.db_link 1 0 0 0 ctdprd51.edit.action_type 1 0 64 0 ctdprd51.pg_toast.pg_toast_9596218 1 0 0 0 ctdprd51.pub2.db 1 1 134 0 ctdprd51.pub2.term_pathway 1 0 0 0 ctdprd51.edit.db_report 1 0 94 0 ctdprd51.pub2.db_link 1 0 0 0 ctdprd51.pub2.reference_party_role 1 0 0 0 ctdprd51.pg_catalog.pg_attrdef 1 1 112 0 ctdprd51.pub2.gene_go_annot 1 0 0 0 ctdprd51.pg_catalog.pg_depend 1 1 1654 0 ctdprd51.pg_catalog.pg_attribute 1 1 1736 0 ctdprd51.edit.db_report_site 1 0 94 0 ctdprd51.pg_catalog.pg_description 1 1 1317 0 ctdprd51.pub2.img 1 0 0 0 ctdprd51.pub2.gene_taxon 1 0 0 0 ctdprd51.pub2.term 2 0 0 0 ctdprd51.pubc.log_query 3 2 38 0 ctdprd51.pg_catalog.pg_shdepend 1 1 659 0 ctdprd51.edit.country 1 0 163 0 ctdprd51.pub2.reference 1 0 0 0 ctdprd51.pub2.dag_edge 1 0 0 0 ctdprd51.pub2.reference_party 1 0 0 0 ctdprd51.pub2.term_label 1 0 0 0 ctdprd51.edit.action_type_path 1 0 106 0 ctdprd51.edit.object_note 1 1 61 0 ctdprd51.pg_catalog.pg_statistic 1 1 949 0 ctdprd51.pg_toast.pg_toast_2619 1 1 5763 0 ctdprd51.pub2.dag_node 1 0 0 0 ctdprd51.pg_catalog.pg_index 1 1 227 0 Total 39 16 14,411 0 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Oct 29 00 1 0 01 0 1 02 0 3 03 0 2 04 0 1 05 0 3 06 0 0 07 1 1 08 0 1 09 0 0 10 0 0 11 0 3 12 11 12 13 0 1 14 8 11 15 1 3 16 0 0 17 0 0 18 6 11 19 9 9 20 1 1 21 1 1 22 0 0 23 0 0 - 73.88 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
- 233 Total read queries
- 37 Total write queries
Queries by database
Key values
- ctdprd51 Main database
- 150 Requests
- 3h31m4s (unknown)
- Main time consuming database
Queries by user
Key values
- pubeu Main user
- 288 Requests
User Request type Count Duration edit Total 1 8s836ms insert 1 8s836ms load Total 20 58m10s select 20 58m10s pub2 Total 2 14m50s ddl 1 5s124ms insert 1 14m45s pubc Total 1 10m5s select 1 10m5s pubeu Total 288 1h14m33s select 288 1h14m33s qaeu Total 4 24s706ms select 4 24s706ms unknown Total 235 3h58m10s ddl 25 20m45s insert 9 40m2s others 5 2m48s select 196 2h54m32s Duration by user
Key values
- 3h58m10s (unknown) Main time consuming user
User Request type Count Duration edit Total 1 8s836ms insert 1 8s836ms load Total 20 58m10s select 20 58m10s pub2 Total 2 14m50s ddl 1 5s124ms insert 1 14m45s pubc Total 1 10m5s select 1 10m5s pubeu Total 288 1h14m33s select 288 1h14m33s qaeu Total 4 24s706ms select 4 24s706ms unknown Total 235 3h58m10s ddl 25 20m45s insert 9 40m2s others 5 2m48s select 196 2h54m32s Queries by host
Key values
- unknown Main host
- 551 Requests
- 6h36m23s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 260 Requests
- 4h57m39s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2025-10-29 14:54:21 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 142 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 51m16s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2025-10-29 22:36:35 - Bind query: yes ]
2 44m31s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');[ Date: 2025-10-29 21:45:13 - Bind query: yes ]
3 33m52s 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-10-29 20:02:29 - Database: ctdprd51 - User: load - Bind query: yes ]
4 26m37s insert into pub2.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;[ Date: 2025-10-29 19:20:07 - Bind query: yes ]
5 14m45s insert into pub2.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;[ Date: 2025-10-29 18:48:00 - Database: ctdprd51 - User: pub2 - Bind query: yes ]
6 11m5s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');[ Date: 2025-10-29 20:16:25 - Database: ctdprd51 - User: load - Bind query: yes ]
7 10m5s /* * 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-10-29 00:10:06 - Database: ctdprd51 - User: pubc - Application: psql ]
8 7m20s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');[ Date: 2025-10-29 22:49:03 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
9 5m14s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');[ Date: 2025-10-29 23:20:40 - Bind query: yes ]
10 4m39s 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-10-29 14:41:47 - Bind query: yes ]
11 4m29s insert into pub2.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;[ Date: 2025-10-29 19:24:37 - Bind query: yes ]
12 3m33s SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.gene_id = ANY (ARRAY (( SELECT /* CIQH.getIxnGeneWhereEquals.Name */ gi.id gene_id FROM term gi WHERE gi.object_type_id = 4 AND UPPER(gi.nm) LIKE 'NFKBIA')))) ORDER BY g.nm_sort, g.id LIMIT 50;[ Date: 2025-10-29 01:31:59 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
13 3m16s CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);[ Date: 2025-10-29 22:55:07 - Bind query: yes ]
14 3m15s INSERT INTO pub2.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub2.TERM);[ Date: 2025-10-29 18:53:29 - 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-10-29 15:25:47 - Bind query: yes ]
16 2m33s CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);[ Date: 2025-10-29 23:08:47 - Bind query: yes ]
17 2m7s SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub2.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub2.CHEM_DISEASE_REFERENCE cdr, pub2.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id AND ptr.source_cd = 'C' AND cdr.source_cd = 'C' AND ptr.ixn_id NOT IN ( SELECT ixn_id FROM pub2.IXN_AXN WHERE action_degree_type_nm = 'does not affect');[ Date: 2025-10-29 22:38:51 - Bind query: yes ]
18 2m2s INSERT INTO pub2.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);[ Date: 2025-10-29 18:50:14 - Bind query: yes ]
19 2m vacuum FULL analyze db_link;[ Date: 2025-10-29 15:57:10 ]
20 1m55s CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);[ Date: 2025-10-29 23:03:18 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 51m16s 1 51m16s 51m16s 51m16s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Oct 29 22 1 51m16s 51m16s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 22:36:35 Duration: 51m16s Bind query: yes
2 47m24s 51 5s38ms 7m20s 55s765ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Oct 29 14 36 21m34s 35s962ms 15 5 8m54s 1m46s 20 3 1m7s 22s544ms 22 3 8m42s 2m54s 23 4 7m4s 1m46s [ User: load - Total duration: 11m9s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m9s - Times executed: 12 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2025-10-29 22:49:03 Duration: 7m20s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2025-10-29 23:20:40 Duration: 5m14s 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-10-29 14:41:47 Duration: 4m39s Bind query: yes
3 44m31s 1 44m31s 44m31s 44m31s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.object_type where cd = ?), ptr.term_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.phenotype_term_reference ptr, pub2.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Oct 29 21 1 44m31s 44m31s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 21:45:13 Duration: 44m31s Bind query: yes
4 33m52s 1 33m52s 33m52s 33m52s 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 Oct 29 20 1 33m52s 33m52s [ User: load - Total duration: 33m52s - 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-10-29 20:02:29 Duration: 33m52s Database: ctdprd51 User: load Bind query: yes
5 26m37s 1 26m37s 26m37s 26m37s insert into pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Oct 29 19 1 26m37s 26m37s -
insert into pub2.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;
Date: 2025-10-29 19:20:07 Duration: 26m37s Bind query: yes
6 15m17s 77 7s672ms 29s322ms 11s916ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Oct 29 02 4 1m19s 19s759ms 03 13 3m50s 17s726ms 04 8 2m13s 16s677ms 13 34 5m5s 8s976ms 20 12 1m50s 9s235ms 21 6 58s604ms 9s767ms [ User: pubeu - Total duration: 12m29s - Times executed: 63 ]
-
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 = '2108479') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2025-10-29 02:48:54 Duration: 29s322ms 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 = '2108479') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2025-10-29 03:20:24 Duration: 28s724ms 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 = '2118653') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2025-10-29 13:24:51 Duration: 25s852ms Database: ctdprd51 User: pubeu Bind query: yes
7 14m45s 1 14m45s 14m45s 14m45s insert into pub2.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Oct 29 18 1 14m45s 14m45s [ User: pub2 - Total duration: 14m45s - Times executed: 1 ]
-
insert into pub2.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;
Date: 2025-10-29 18:48:00 Duration: 14m45s Database: ctdprd51 User: pub2 Bind query: yes
8 14m34s 5 48s231ms 11m5s 2m54s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Oct 29 20 5 14m34s 2m54s [ User: load - Total duration: 11m5s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:16:25 Duration: 11m5s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:46:59 Duration: 56s416ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:52:26 Duration: 54s628ms Bind query: yes
9 10m5s 1 10m5s 10m5s 10m5s select maint_query_logs_archive ();Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Oct 29 00 1 10m5s 10m5s [ User: pubc - Total duration: 10m5s - Times executed: 1 ]
[ Application: psql - Total duration: 10m5s - 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-10-29 00:10:06 Duration: 10m5s Database: ctdprd51 User: pubc Application: psql
10 4m29s 1 4m29s 4m29s 4m29s insert into pub2.gene_taxon (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.gene_taxon;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Oct 29 19 1 4m29s 4m29s -
insert into pub2.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;
Date: 2025-10-29 19:24:37 Duration: 4m29s Bind query: yes
11 3m33s 1 3m33s 3m33s 3m33s select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where exists ( select ? from gene_chem_ref_gene_form gf where gf.gene_chem_reference_id = gcr.id and gf.gene_id = gcr.gene_id and gf.actor_form_type_nm in ( select tc.nm from actor_form_type tp, actor_form_type tc where tc.subset_left_no between tp.subset_left_no and tp.subset_right_no and (tp.nm = ?))) and gcr.gene_id = any (array (( select gi.id gene_id from term gi where gi.object_type_id = ? and upper(gi.nm) like ?)))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Oct 29 01 1 3m33s 3m33s [ User: pubeu - Total duration: 3m33s - Times executed: 1 ]
-
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.gene_id = ANY (ARRAY (( SELECT /* CIQH.getIxnGeneWhereEquals.Name */ gi.id gene_id FROM term gi WHERE gi.object_type_id = 4 AND UPPER(gi.nm) LIKE 'NFKBIA')))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2025-10-29 01:31:59 Duration: 3m33s Database: ctdprd51 User: pubeu Bind query: yes
12 3m16s 1 3m16s 3m16s 3m16s create unique index gene_disease_reference_ak1 on pub2.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Oct 29 22 1 3m16s 3m16s -
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-10-29 22:55:07 Duration: 3m16s Bind query: yes
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-10-29 22:55:06 Duration: 0ms
13 3m15s 1 3m15s 3m15s 3m15s insert into pub2.term_label (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.term t, load.term_label l where t.id = l.term_id and t.id in ( select id from pub2.term);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Oct 29 18 1 3m15s 3m15s -
INSERT INTO pub2.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub2.TERM);
Date: 2025-10-29 18:53:29 Duration: 3m15s Bind query: yes
14 2m33s 1 2m33s 2m33s 2m33s create index ix_gene_disease_ref_net_sc on pub2.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Oct 29 23 1 2m33s 2m33s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-10-29 23:08:47 Duration: 2m33s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-10-29 23:08:47 Duration: 0ms
15 2m7s 1 2m7s 2m7s 2m7s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub2.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.object_type where cd = ?), cdr.mod_tm from pub2.chem_disease_reference cdr, pub2.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id and ptr.source_cd = ? and cdr.source_cd = ? and ptr.ixn_id not in ( select ixn_id from pub2.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Oct 29 22 1 2m7s 2m7s -
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub2.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub2.CHEM_DISEASE_REFERENCE cdr, pub2.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id AND ptr.source_cd = 'C' AND cdr.source_cd = 'C' AND ptr.ixn_id NOT IN ( SELECT ixn_id FROM pub2.IXN_AXN WHERE action_degree_type_nm = 'does not affect');
Date: 2025-10-29 22:38:51 Duration: 2m7s Bind query: yes
16 2m2s 1 2m2s 2m2s 2m2s insert into pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) select t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) as db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), ?, ?, ?, ?, ?, ?, ? from load.term t where object_type_id not in (...);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Oct 29 18 1 2m2s 2m2s -
INSERT INTO pub2.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2025-10-29 18:50:14 Duration: 2m2s Bind query: yes
17 2m 1 2m 2m 2m vacuum full analyze db_link;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Oct 29 15 1 2m 2m -
vacuum FULL analyze db_link;
Date: 2025-10-29 15:57:10 Duration: 2m
-
vacuum FULL analyze db_link;
Date: 2025-10-29 15:55:31 Duration: 0ms
18 1m59s 10 5s40ms 21s202ms 11s960ms select ? AS "Input", phenotypeterm.nm AS "PhenotypeName", phenotypeterm.acc_txt AS "PhenotypeID", diseaseterm.nm AS "DiseaseName", diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt "DiseaseID", ( select string_agg(distinct geneterm.nm, ?) from term geneterm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = geneterm.id and ptr.via_term_object_type_id = ?) AS "GeneInferenceNetwork", ( select string_agg(distinct chemterm.nm, ?) from term chemterm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = chemterm.id and ptr.via_term_object_type_id = ?) AS "ChemInferenceNetwork", ( select string_agg(distinct r.acc_txt, ?) from reference r, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and (r.id = ptr.reference_id or r.id = ptr.term_reference_id)) AS "PubMedIDs" 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 = ?) and diseaseterm.object_type_id = ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Oct 29 11 10 1m59s 11s960ms [ User: pubeu - Total duration: 1m21s - Times executed: 7 ]
-
SELECT /* BatchDiseasePhenotypeAssnsDAO */ 'go:0006915' AS "Input", phenotypeTerm.nm as "PhenotypeName", phenotypeTerm.acc_txt as "PhenotypeID", diseaseTerm.nm as "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", ( SELECT STRING_AGG(distinct geneTerm.nm, '|') from term geneTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = geneTerm.id and ptr.via_term_object_type_id = 4) AS "GeneInferenceNetwork", ( SELECT STRING_AGG(distinct chemTerm.nm, '|') from term chemTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = chemTerm.id and ptr.via_term_object_type_id = 2) AS "ChemInferenceNetwork", ( SELECT STRING_AGG(distinct r.acc_txt, '|') from reference r, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and (r.id = ptr.reference_id or r.id = ptr.term_reference_id)) AS "PubMedIDs" 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 = 1270787) and diseaseTerm.object_type_id = 3;
Date: 2025-10-29 11:22:04 Duration: 21s202ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseasePhenotypeAssnsDAO */ 'go:0008283' AS "Input", phenotypeTerm.nm as "PhenotypeName", phenotypeTerm.acc_txt as "PhenotypeID", diseaseTerm.nm as "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", ( SELECT STRING_AGG(distinct geneTerm.nm, '|') from term geneTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = geneTerm.id and ptr.via_term_object_type_id = 4) AS "GeneInferenceNetwork", ( SELECT STRING_AGG(distinct chemTerm.nm, '|') from term chemTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = chemTerm.id and ptr.via_term_object_type_id = 2) AS "ChemInferenceNetwork", ( SELECT STRING_AGG(distinct r.acc_txt, '|') from reference r, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and (r.id = ptr.reference_id or r.id = ptr.term_reference_id)) AS "PubMedIDs" 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 = 1244263) and diseaseTerm.object_type_id = 3;
Date: 2025-10-29 11:15:08 Duration: 12s862ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseasePhenotypeAssnsDAO */ 'go:0008283' AS "Input", phenotypeTerm.nm as "PhenotypeName", phenotypeTerm.acc_txt as "PhenotypeID", diseaseTerm.nm as "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", ( SELECT STRING_AGG(distinct geneTerm.nm, '|') from term geneTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = geneTerm.id and ptr.via_term_object_type_id = 4) AS "GeneInferenceNetwork", ( SELECT STRING_AGG(distinct chemTerm.nm, '|') from term chemTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = chemTerm.id and ptr.via_term_object_type_id = 2) AS "ChemInferenceNetwork", ( SELECT STRING_AGG(distinct r.acc_txt, '|') from reference r, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and (r.id = ptr.reference_id or r.id = ptr.term_reference_id)) AS "PubMedIDs" 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 = 1244263) and diseaseTerm.object_type_id = 3;
Date: 2025-10-29 11:15:43 Duration: 12s796ms Bind query: yes
19 1m55s 1 1m55s 1m55s 1m55s create index ix_gene_disease_ref_dis_gene on pub2.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Oct 29 23 1 1m55s 1m55s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-10-29 23:03:18 Duration: 1m55s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-10-29 23:03:18 Duration: 0ms
20 1m54s 1 1m54s 1m54s 1m54s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub2.phenotype_term_reference viachemptr, pub2.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 #20
Day Hour Count Duration Avg duration Oct 29 23 1 1m54s 1m54s -
SELECT distinct viaChemPTR.via_term_id, -- chem viaGenePTR.via_term_id, -- gene viaChemPTR.phenotype_id, -- phenotype viaChemPTR.term_id -- disease FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR, pub2.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-10-29 23:13:51 Duration: 1m54s Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 77 15m17s 7s672ms 29s322ms 11s916ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Oct 29 02 4 1m19s 19s759ms 03 13 3m50s 17s726ms 04 8 2m13s 16s677ms 13 34 5m5s 8s976ms 20 12 1m50s 9s235ms 21 6 58s604ms 9s767ms [ User: pubeu - Total duration: 12m29s - Times executed: 63 ]
-
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 = '2108479') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2025-10-29 02:48:54 Duration: 29s322ms 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 = '2108479') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2025-10-29 03:20:24 Duration: 28s724ms 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 = '2118653') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2025-10-29 13:24:51 Duration: 25s852ms Database: ctdprd51 User: pubeu Bind query: yes
2 51 47m24s 5s38ms 7m20s 55s765ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Oct 29 14 36 21m34s 35s962ms 15 5 8m54s 1m46s 20 3 1m7s 22s544ms 22 3 8m42s 2m54s 23 4 7m4s 1m46s [ User: load - Total duration: 11m9s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m9s - Times executed: 12 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2025-10-29 22:49:03 Duration: 7m20s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2025-10-29 23:20:40 Duration: 5m14s 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-10-29 14:41:47 Duration: 4m39s Bind query: yes
3 12 1m9s 5s314ms 6s867ms 5s786ms 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 #3
Day Hour Count Duration Avg duration Oct 29 00 2 10s762ms 5s381ms 02 2 11s141ms 5s570ms 09 1 5s509ms 5s509ms 11 7 42s28ms 6s4ms [ User: pubeu - Total duration: 51s756ms - Times executed: 9 ]
-
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 = '1448872' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-10-29 11:52:10 Duration: 6s867ms 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 = '1448872' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-10-29 11:52:13 Duration: 6s291ms 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 = '1448872' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-10-29 11:52:12 Duration: 6s146ms Database: ctdprd51 User: pubeu Bind query: yes
4 10 1m59s 5s40ms 21s202ms 11s960ms select ? AS "Input", phenotypeterm.nm AS "PhenotypeName", phenotypeterm.acc_txt AS "PhenotypeID", diseaseterm.nm AS "DiseaseName", diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt "DiseaseID", ( select string_agg(distinct geneterm.nm, ?) from term geneterm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = geneterm.id and ptr.via_term_object_type_id = ?) AS "GeneInferenceNetwork", ( select string_agg(distinct chemterm.nm, ?) from term chemterm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = chemterm.id and ptr.via_term_object_type_id = ?) AS "ChemInferenceNetwork", ( select string_agg(distinct r.acc_txt, ?) from reference r, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and (r.id = ptr.reference_id or r.id = ptr.term_reference_id)) AS "PubMedIDs" 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 = ?) and diseaseterm.object_type_id = ?;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Oct 29 11 10 1m59s 11s960ms [ User: pubeu - Total duration: 1m21s - Times executed: 7 ]
-
SELECT /* BatchDiseasePhenotypeAssnsDAO */ 'go:0006915' AS "Input", phenotypeTerm.nm as "PhenotypeName", phenotypeTerm.acc_txt as "PhenotypeID", diseaseTerm.nm as "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", ( SELECT STRING_AGG(distinct geneTerm.nm, '|') from term geneTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = geneTerm.id and ptr.via_term_object_type_id = 4) AS "GeneInferenceNetwork", ( SELECT STRING_AGG(distinct chemTerm.nm, '|') from term chemTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = chemTerm.id and ptr.via_term_object_type_id = 2) AS "ChemInferenceNetwork", ( SELECT STRING_AGG(distinct r.acc_txt, '|') from reference r, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and (r.id = ptr.reference_id or r.id = ptr.term_reference_id)) AS "PubMedIDs" 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 = 1270787) and diseaseTerm.object_type_id = 3;
Date: 2025-10-29 11:22:04 Duration: 21s202ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseasePhenotypeAssnsDAO */ 'go:0008283' AS "Input", phenotypeTerm.nm as "PhenotypeName", phenotypeTerm.acc_txt as "PhenotypeID", diseaseTerm.nm as "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", ( SELECT STRING_AGG(distinct geneTerm.nm, '|') from term geneTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = geneTerm.id and ptr.via_term_object_type_id = 4) AS "GeneInferenceNetwork", ( SELECT STRING_AGG(distinct chemTerm.nm, '|') from term chemTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = chemTerm.id and ptr.via_term_object_type_id = 2) AS "ChemInferenceNetwork", ( SELECT STRING_AGG(distinct r.acc_txt, '|') from reference r, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and (r.id = ptr.reference_id or r.id = ptr.term_reference_id)) AS "PubMedIDs" 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 = 1244263) and diseaseTerm.object_type_id = 3;
Date: 2025-10-29 11:15:08 Duration: 12s862ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseasePhenotypeAssnsDAO */ 'go:0008283' AS "Input", phenotypeTerm.nm as "PhenotypeName", phenotypeTerm.acc_txt as "PhenotypeID", diseaseTerm.nm as "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", ( SELECT STRING_AGG(distinct geneTerm.nm, '|') from term geneTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = geneTerm.id and ptr.via_term_object_type_id = 4) AS "GeneInferenceNetwork", ( SELECT STRING_AGG(distinct chemTerm.nm, '|') from term chemTerm, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and ptr.via_term_id = chemTerm.id and ptr.via_term_object_type_id = 2) AS "ChemInferenceNetwork", ( SELECT STRING_AGG(distinct r.acc_txt, '|') from reference r, phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and (r.id = ptr.reference_id or r.id = ptr.term_reference_id)) AS "PubMedIDs" 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 = 1244263) and diseaseTerm.object_type_id = 3;
Date: 2025-10-29 11:15:43 Duration: 12s796ms Bind query: yes
5 8 48s432ms 5s30ms 11s20ms 6s54ms 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 #5
Day Hour Count Duration Avg duration Oct 29 02 1 5s48ms 5s48ms 03 1 5s508ms 5s508ms 04 2 10s441ms 5s220ms 05 2 10s995ms 5s497ms 09 1 11s20ms 11s20ms 21 1 5s417ms 5s417ms [ User: pubeu - Total duration: 37s506ms - Times executed: 6 ]
-
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 = '2120701') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-10-29 09:49:34 Duration: 11s20ms 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 = '2108479') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-10-29 05:54:57 Duration: 5s583ms 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 = '2108479') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-10-29 03:16:26 Duration: 5s508ms Bind query: yes
6 7 47s73ms 5s381ms 8s36ms 6s724ms 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 (g.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 g.nm, d.nm_sort, "DirectEvidence", c.nm;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Oct 29 20 7 47s73ms 6s724ms [ User: pubeu - Total duration: 47s73ms - Times executed: 7 ]
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'cyp1a1' "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 (g.id = 1495314) 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 g.nm, d.nm_sort, "DirectEvidence", c.nm;
Date: 2025-10-29 20:48:26 Duration: 8s36ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'cyp1a1' "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 (g.id = 1495314) 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 g.nm, d.nm_sort, "DirectEvidence", c.nm;
Date: 2025-10-29 20:48:57 Duration: 7s705ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'cyp1a2' "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 (g.id = 1495313) 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 g.nm, d.nm_sort, "DirectEvidence", c.nm;
Date: 2025-10-29 20:48:57 Duration: 7s626ms Database: ctdprd51 User: pubeu Bind query: yes
7 6 44s686ms 7s40ms 8s214ms 7s447ms 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 limit ?;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Oct 29 02 1 7s40ms 7s40ms 03 1 7s422ms 7s422ms 05 2 14s524ms 7s262ms 13 1 8s214ms 8s214ms 21 1 7s485ms 7s485ms [ User: pubeu - Total duration: 23s121ms - Times executed: 3 ]
-
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 LIMIT 50;
Date: 2025-10-29 13:36:09 Duration: 8s214ms 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 '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 LIMIT 50;
Date: 2025-10-29 21:01:46 Duration: 7s485ms 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 '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 LIMIT 50;
Date: 2025-10-29 05:49:13 Duration: 7s447ms Bind query: yes
8 6 44s523ms 7s79ms 8s413ms 7s420ms 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 limit ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Oct 29 02 1 7s134ms 7s134ms 03 1 7s290ms 7s290ms 05 2 14s357ms 7s178ms 13 1 8s413ms 8s413ms 21 1 7s326ms 7s326ms [ User: pubeu - Total duration: 21s540ms - Times executed: 3 ]
[ User: qaeu - Total duration: 7s278ms - 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 'ZINC')) 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:0006915' 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')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-10-29 13:36:01 Duration: 8s413ms 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 'ZINC')) 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:0006915' 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')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-10-29 21:01:38 Duration: 7s326ms 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 'ZINC')) 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:0006915' 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')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-10-29 03:43:29 Duration: 7s290ms Bind query: yes
9 6 31s142ms 5s43ms 5s749ms 5s190ms 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 #9
Day Hour Count Duration Avg duration Oct 29 02 1 5s43ms 5s43ms 03 1 5s47ms 5s47ms 05 2 10s210ms 5s105ms 13 1 5s749ms 5s749ms 21 1 5s90ms 5s90ms [ User: pubeu - Total duration: 20s239ms - Times executed: 4 ]
[ User: qaeu - Total duration: 5s152ms - 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 = 1324741)) 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-10-29 13:35:26 Duration: 5s749ms 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 = 1324741)) 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-10-29 05:43:38 Duration: 5s152ms 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 = 1324741)) 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-10-29 21:01:11 Duration: 5s90ms Database: ctdprd51 User: pubeu Bind query: yes
10 5 14m34s 48s231ms 11m5s 2m54s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Oct 29 20 5 14m34s 2m54s [ User: load - Total duration: 11m5s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:16:25 Duration: 11m5s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:46:59 Duration: 56s416ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:52:26 Duration: 54s628ms Bind query: yes
11 5 1m38s 17s386ms 21s133ms 19s634ms 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 phenotypeterm.id = any (array (( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?))) and associatedterm.object_type_id = ? group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Oct 29 06 5 1m38s 19s634ms [ User: pubeu - Total duration: 1m1s - Times executed: 3 ]
-
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 phenotypeTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1245038'))) and associatedTerm.object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-10-29 06:21:08 Duration: 21s133ms 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 phenotypeTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1245038'))) and associatedTerm.object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-10-29 06:21:10 Duration: 20s466ms 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 phenotypeTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1245038'))) and associatedTerm.object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-10-29 06:21:09 Duration: 20s258ms Database: ctdprd51 User: pubeu Bind query: yes
12 3 1m37s 32s353ms 32s575ms 32s457ms select t.id, t.object_type_id, t.acc_txt, t.acc_db_id, t.nm, t.nm_sort, t.secondary_nm, t.description, t.note, l.acc_txt from load.term t, edit.db_link l where t.object_type_id = ? and t.object_type_id = l.object_type_id and t.id = l.object_id;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Oct 29 15 3 1m37s 32s457ms [ User: load - Total duration: 1m37s - Times executed: 3 ]
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_ID, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.ACC_TXT from load.TERM t, edit.DB_LINK l where t.object_type_id = 4 and t.object_type_id = l.object_type_id and t.id = l.object_id;
Date: 2025-10-29 15:26:23 Duration: 32s575ms Database: ctdprd51 User: load Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_ID, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.ACC_TXT from load.TERM t, edit.DB_LINK l where t.object_type_id = 4 and t.object_type_id = l.object_type_id and t.id = l.object_id;
Date: 2025-10-29 15:34:35 Duration: 32s443ms Database: ctdprd51 User: load Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_ID, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.ACC_TXT from load.TERM t, edit.DB_LINK l where t.object_type_id = 4 and t.object_type_id = l.object_type_id and t.id = l.object_id;
Date: 2025-10-29 15:31:47 Duration: 32s353ms Database: ctdprd51 User: load Bind query: yes
13 3 30s750ms 7s293ms 15s796ms 10s250ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ? offset ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Oct 29 03 1 15s796ms 15s796ms 13 1 7s660ms 7s660ms 20 1 7s293ms 7s293ms [ User: pubeu - Total duration: 23s456ms - 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 = '2108479') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 1971250;
Date: 2025-10-29 03:15:41 Duration: 15s796ms 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 = '2118653') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 988100;
Date: 2025-10-29 13:33:04 Duration: 7s660ms 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 = '2118653') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50 OFFSET 988100;
Date: 2025-10-29 20:43:08 Duration: 7s293ms Bind query: yes
14 3 25s852ms 5s63ms 15s486ms 8s617ms 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 #14
Day Hour Count Duration Avg duration Oct 29 03 1 5s63ms 5s63ms 06 1 15s486ms 15s486ms 13 1 5s301ms 5s301ms [ User: pubeu - Total duration: 25s852ms - 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 = '1462096') 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 = '1462096') 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-10-29 06:17:56 Duration: 15s486ms 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 = '1425057') 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 = '1425057') 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-10-29 13:37:11 Duration: 5s301ms 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 = '1289484') 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 = '1289484') 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-10-29 03:17:42 Duration: 5s63ms Database: ctdprd51 User: pubeu Bind query: yes
15 2 48s384ms 24s89ms 24s295ms 24s192ms 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 #15
Day Hour Count Duration Avg duration Oct 29 06 2 48s384ms 24s192ms [ User: pubeu - Total duration: 24s89ms - Times executed: 1 ]
-
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 = '1245038') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-10-29 06:21:23 Duration: 24s295ms 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 = '1245038') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-10-29 06:20:58 Duration: 24s89ms Database: ctdprd51 User: pubeu Bind query: yes
16 2 11s676ms 5s790ms 5s886ms 5s838ms 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 = ?) and gd.indirect_chem_qty > ? and gd.curated_reference_qty = ? order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Oct 29 03 1 5s886ms 5s886ms 04 1 5s790ms 5s790ms [ User: pubeu - Total duration: 5s886ms - Times executed: 1 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2108479') AND gd.indirect_chem_qty > 0 AND gd.curated_reference_qty = 0 ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-10-29 03:16:10 Duration: 5s886ms 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 = '2108479') AND gd.indirect_chem_qty > 0 AND gd.curated_reference_qty = 0 ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-10-29 04:47:51 Duration: 5s790ms Bind query: yes
17 2 11s331ms 5s576ms 5s754ms 5s665ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Oct 29 03 1 5s576ms 5s576ms 04 1 5s754ms 5s754ms [ User: pubeu - Total duration: 11s331ms - 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 = '2108479') ORDER BY gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-10-29 04:46:29 Duration: 5s754ms 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 = '2108479') ORDER BY gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-10-29 03:17:23 Duration: 5s576ms Database: ctdprd51 User: pubeu Bind query: yes
18 1 51m16s 51m16s 51m16s 51m16s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Oct 29 22 1 51m16s 51m16s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 22:36:35 Duration: 51m16s Bind query: yes
19 1 44m31s 44m31s 44m31s 44m31s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.object_type where cd = ?), ptr.term_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.phenotype_term_reference ptr, pub2.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Oct 29 21 1 44m31s 44m31s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 21:45:13 Duration: 44m31s Bind query: yes
20 1 33m52s 33m52s 33m52s 33m52s select i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) from edit.ixn i, edit.reference_ixn r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in (...) order by i.id asc;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Oct 29 20 1 33m52s 33m52s [ User: load - Total duration: 33m52s - 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-10-29 20:02:29 Duration: 33m52s Database: ctdprd51 User: load Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 51m16s 51m16s 51m16s 1 51m16s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Oct 29 22 1 51m16s 51m16s -
select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.GENE_GO_ANNOT gga, pub2.PHENOTYPE_TERM_REFERENCE ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 22:36:35 Duration: 51m16s Bind query: yes
2 44m31s 44m31s 44m31s 1 44m31s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.object_type where cd = ?), ptr.term_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.phenotype_term_reference ptr, pub2.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Oct 29 21 1 44m31s 44m31s -
select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.OBJECT_TYPE where cd = 'gene'), ptr.term_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), ( select current_date) from pub2.GENE_CHEM_REFERENCE gcr, pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.PHENOTYPE_TERM_REFERENCE ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub2.GENE_CHEM_REFERENCE_AXN where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 21:45:13 Duration: 44m31s Bind query: yes
3 33m52s 33m52s 33m52s 1 33m52s 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 Oct 29 20 1 33m52s 33m52s [ User: load - Total duration: 33m52s - 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-10-29 20:02:29 Duration: 33m52s Database: ctdprd51 User: load Bind query: yes
4 26m37s 26m37s 26m37s 1 26m37s insert into pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Oct 29 19 1 26m37s 26m37s -
insert into pub2.GENE_GO_ANNOT (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.GENE_GO_ANNOT;
Date: 2025-10-29 19:20:07 Duration: 26m37s Bind query: yes
5 14m45s 14m45s 14m45s 1 14m45s insert into pub2.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.db_link;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Oct 29 18 1 14m45s 14m45s [ User: pub2 - Total duration: 14m45s - Times executed: 1 ]
-
insert into pub2.DB_LINK (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary) select object_id, object_type_id, acc_txt, db_id, type_cd, is_primary from edit.DB_LINK;
Date: 2025-10-29 18:48:00 Duration: 14m45s Database: ctdprd51 User: pub2 Bind query: yes
6 10m5s 10m5s 10m5s 1 10m5s select maint_query_logs_archive ();Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Oct 29 00 1 10m5s 10m5s [ User: pubc - Total duration: 10m5s - Times executed: 1 ]
[ Application: psql - Total duration: 10m5s - 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-10-29 00:10:06 Duration: 10m5s Database: ctdprd51 User: pubc Application: psql
7 4m29s 4m29s 4m29s 1 4m29s insert into pub2.gene_taxon (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.gene_taxon;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Oct 29 19 1 4m29s 4m29s -
insert into pub2.GENE_TAXON (gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd) select gene_id, taxon_id, gene_acc_txt, gene_acc_db_cd from load.GENE_TAXON;
Date: 2025-10-29 19:24:37 Duration: 4m29s Bind query: yes
8 3m33s 3m33s 3m33s 1 3m33s select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where exists ( select ? from gene_chem_ref_gene_form gf where gf.gene_chem_reference_id = gcr.id and gf.gene_id = gcr.gene_id and gf.actor_form_type_nm in ( select tc.nm from actor_form_type tp, actor_form_type tc where tc.subset_left_no between tp.subset_left_no and tp.subset_right_no and (tp.nm = ?))) and gcr.gene_id = any (array (( select gi.id gene_id from term gi where gi.object_type_id = ? and upper(gi.nm) like ?)))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Oct 29 01 1 3m33s 3m33s [ User: pubeu - Total duration: 3m33s - Times executed: 1 ]
-
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ EXISTS ( SELECT /* CIQH.getIxnGeneFormTypeWhere */ 1 FROM gene_chem_ref_gene_form gf WHERE gf.gene_chem_reference_id = gcr.id AND gf.gene_id = gcr.gene_id AND gf.actor_form_type_nm IN ( SELECT tc.nm FROM actor_form_type tp, actor_form_type tc WHERE tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no AND (tp.nm = 'protein'))) AND gcr.gene_id = ANY (ARRAY (( SELECT /* CIQH.getIxnGeneWhereEquals.Name */ gi.id gene_id FROM term gi WHERE gi.object_type_id = 4 AND UPPER(gi.nm) LIKE 'NFKBIA')))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2025-10-29 01:31:59 Duration: 3m33s Database: ctdprd51 User: pubeu Bind query: yes
9 3m16s 3m16s 3m16s 1 3m16s create unique index gene_disease_reference_ak1 on pub2.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Oct 29 22 1 3m16s 3m16s -
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-10-29 22:55:07 Duration: 3m16s Bind query: yes
-
CREATE UNIQUE INDEX gene_disease_reference_ak1 ON pub2.gene_disease_reference USING btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);
Date: 2025-10-29 22:55:06 Duration: 0ms
10 3m15s 3m15s 3m15s 1 3m15s insert into pub2.term_label (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.term t, load.term_label l where t.id = l.term_id and t.id in ( select id from pub2.term);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Oct 29 18 1 3m15s 3m15s -
INSERT INTO pub2.TERM_LABEL (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.TERM t, load.TERM_LABEL l where t.id = l.term_id and t.id in ( select id from pub2.TERM);
Date: 2025-10-29 18:53:29 Duration: 3m15s Bind query: yes
11 48s231ms 11m5s 2m54s 5 14m34s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Oct 29 20 5 14m34s 2m54s [ User: load - Total duration: 11m5s - Times executed: 1 ]
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:16:25 Duration: 11m5s Database: ctdprd51 User: load Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:46:59 Duration: 56s416ms Bind query: yes
-
select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, TO_CHAR(cdr.mod_tm, 'YYYY-MM-DD') from pub2.GENE_CHEM_REFERENCE gcr, pub2.CHEM_DISEASE_REFERENCE cdr where gcr.chem_id = cdr.chem_id and cdr.source_cd = 'C' and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-10-29 20:52:26 Duration: 54s628ms Bind query: yes
12 2m33s 2m33s 2m33s 1 2m33s create index ix_gene_disease_ref_net_sc on pub2.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Oct 29 23 1 2m33s 2m33s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-10-29 23:08:47 Duration: 2m33s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-10-29 23:08:47 Duration: 0ms
13 2m7s 2m7s 2m7s 1 2m7s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub2.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.object_type where cd = ?), cdr.mod_tm from pub2.chem_disease_reference cdr, pub2.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id and ptr.source_cd = ? and cdr.source_cd = ? and ptr.ixn_id not in ( select ixn_id from pub2.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Oct 29 22 1 2m7s 2m7s -
SELECT DISTINCT ptr.phenotype_id, cdr.disease_id, ( select id from pub2.OBJECT_TYPE where cd = 'disease'), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.OBJECT_TYPE where cd = 'chem'), cdr.mod_tm FROM pub2.CHEM_DISEASE_REFERENCE cdr, pub2.PHENOTYPE_TERM_REFERENCE ptr WHERE cdr.chem_id = ptr.term_id AND ptr.source_cd = 'C' AND cdr.source_cd = 'C' AND ptr.ixn_id NOT IN ( SELECT ixn_id FROM pub2.IXN_AXN WHERE action_degree_type_nm = 'does not affect');
Date: 2025-10-29 22:38:51 Duration: 2m7s Bind query: yes
14 2m2s 2m2s 2m2s 1 2m2s insert into pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) select t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) as db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), ?, ?, ?, ?, ?, ?, ? from load.term t where object_type_id not in (...);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Oct 29 18 1 2m2s 2m2s -
INSERT INTO pub2.TERM (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) SELECT t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) AS db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), 0, 0, 'f', 'f', 'f', 'f', 'dummy' FROM load.TERM t where object_type_id NOT in (2, 3, 6);
Date: 2025-10-29 18:50:14 Duration: 2m2s Bind query: yes
15 2m 2m 2m 1 2m vacuum full analyze db_link;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Oct 29 15 1 2m 2m -
vacuum FULL analyze db_link;
Date: 2025-10-29 15:57:10 Duration: 2m
-
vacuum FULL analyze db_link;
Date: 2025-10-29 15:55:31 Duration: 0ms
16 1m55s 1m55s 1m55s 1 1m55s create index ix_gene_disease_ref_dis_gene on pub2.gene_disease_reference using btree (disease_id, gene_id);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Oct 29 23 1 1m55s 1m55s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-10-29 23:03:18 Duration: 1m55s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-10-29 23:03:18 Duration: 0ms
17 1m54s 1m54s 1m54s 1 1m54s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub2.phenotype_term_reference viachemptr, pub2.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 #17
Day Hour Count Duration Avg duration Oct 29 23 1 1m54s 1m54s -
SELECT distinct viaChemPTR.via_term_id, -- chem viaGenePTR.via_term_id, -- gene viaChemPTR.phenotype_id, -- phenotype viaChemPTR.term_id -- disease FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR, pub2.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-10-29 23:13:51 Duration: 1m54s Bind query: yes
18 5s38ms 7m20s 55s765ms 51 47m24s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Oct 29 14 36 21m34s 35s962ms 15 5 8m54s 1m46s 20 3 1m7s 22s544ms 22 3 8m42s 2m54s 23 4 7m4s 1m46s [ User: load - Total duration: 11m9s - Times executed: 12 ]
[ Application: pg_bulkload - Total duration: 11m9s - Times executed: 12 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.GENE_DISEASE_REFERENCE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.log,parse-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/indirectAssociation/geneDiseaseRef.txt.DUPE}');
Date: 2025-10-29 22:49:03 Duration: 7m20s Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.DAG_PATH,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.log,parse-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/dag/dagPath.txt.DUPE}');
Date: 2025-10-29 23:20:40 Duration: 5m14s 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-10-29 14:41:47 Duration: 4m39s Bind query: yes
19 32s353ms 32s575ms 32s457ms 3 1m37s select t.id, t.object_type_id, t.acc_txt, t.acc_db_id, t.nm, t.nm_sort, t.secondary_nm, t.description, t.note, l.acc_txt from load.term t, edit.db_link l where t.object_type_id = ? and t.object_type_id = l.object_type_id and t.id = l.object_id;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Oct 29 15 3 1m37s 32s457ms [ User: load - Total duration: 1m37s - Times executed: 3 ]
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_ID, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.ACC_TXT from load.TERM t, edit.DB_LINK l where t.object_type_id = 4 and t.object_type_id = l.object_type_id and t.id = l.object_id;
Date: 2025-10-29 15:26:23 Duration: 32s575ms Database: ctdprd51 User: load Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_ID, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.ACC_TXT from load.TERM t, edit.DB_LINK l where t.object_type_id = 4 and t.object_type_id = l.object_type_id and t.id = l.object_id;
Date: 2025-10-29 15:34:35 Duration: 32s443ms Database: ctdprd51 User: load Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_ID, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.ACC_TXT from load.TERM t, edit.DB_LINK l where t.object_type_id = 4 and t.object_type_id = l.object_type_id and t.id = l.object_id;
Date: 2025-10-29 15:31:47 Duration: 32s353ms Database: ctdprd51 User: load Bind query: yes
20 24s89ms 24s295ms 24s192ms 2 48s384ms 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 #20
Day Hour Count Duration Avg duration Oct 29 06 2 48s384ms 24s192ms [ User: pubeu - Total duration: 24s89ms - Times executed: 1 ]
-
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 = '1245038') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-10-29 06:21:23 Duration: 24s295ms 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 = '1245038') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-10-29 06:20:58 Duration: 24s89ms 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 1 0ms 6 0ms 0ms 0ms ;Times Reported Time consuming bind #1
Day Hour Count Duration Avg duration Oct 28 04 5 0ms 0ms Oct 29 04 1 0ms 0ms [ User: pubeu - Total duration: 14m29s - Times executed: 4 ]
-
;
Date: Duration: 0ms Database: postgres
-
Events
Log levels
Key values
- 18,357 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 5 FATAL entries
- 7 ERROR entries
- 0 WARNING entries
- 9 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 6 Max number of times the same event was reported
- 21 Total events found
Rank Times reported Error 1 6 LOG: could not receive data from client: Connection reset by peer
Times Reported Most Frequent Error / Event #1
Day Hour Count Oct 29 02 2 03 3 20 1 2 3 ERROR: column "..." must appear in the GROUP BY clause or be used in an aggregate function
Times Reported Most Frequent Error / Event #2
Day Hour Count Oct 29 11 3 - ERROR: column "log_query_archive.id" must appear in the GROUP BY clause or be used in an aggregate function at character 21
- ERROR: column "log_query_archive.id" must appear in the GROUP BY clause or be used in an aggregate function at character 8
- ERROR: column "log_query.id" must appear in the GROUP BY clause or be used in an aggregate function at character 8
Statement: select remote_addr, * from log_query_archive where remote_addr = '129.81.255.94' and query_tm > '2025-10-28' group by remote_addr order by query_tm desc
Date: 2025-10-29 11:04:11
Statement: select * --remote_addr from log_query_archive where remote_addr = '129.81.255.94' and query_tm > '2025-10-28' group by remote_addr order by query_tm desc
Date: 2025-10-29 11:04:21
Statement: select * --remote_addr from log_query where query_tm > '2025-10-28' group by remote_addr order by count(*) desc
Date: 2025-10-29 11:21:06
3 2 LOG: could not send data to client: Connection reset by peer
Times Reported Most Frequent Error / Event #3
Day Hour Count Oct 29 20 2 - LOG: could not send data to client: Connection reset by peer
- LOG: could not send data to client: Connection reset by peer
Statement: 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
Date: 2025-10-29 20:57:19 Database: ctdprd51 Application: User: pubeu Remote:
Statement: 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)
Date: 2025-10-29 20:57:19 Database: ctdprd51 Application: User: pubeu Remote:
4 2 FATAL: canceling authentication due to timeout
Times Reported Most Frequent Error / Event #4
Day Hour Count Oct 29 13 1 20 1 5 2 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #5
Day Hour Count Oct 29 11 1 14 1 - ERROR: syntax error at or near "where" at character 82
- ERROR: syntax error at or near "from" at character 33
Statement: select remote_addr, * from log_query_archive where remote_addr = '129.81.255.94' where query_tm > '2025-10-28'
Date: 2025-10-29 11:03:44 Database: ctdprd51 Application: pgAdmin 4 - CONN:2409479 User: pubc Remote:
Statement: select count(*) * --remote_addr from log_query_archive where remote_addr = '129.81.255.94'
Date: 2025-10-29 14:56:56
6 1 FATAL: connection to client lost 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
Times Reported Most Frequent Error / Event #6
Day Hour Count Oct 29 20 1 - FATAL: connection to client lost 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
Statement: SELECT /* DiseaseGeneAssnsDAO */
Date: 2025-10-29 20:57:19
7 1 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #7
Day Hour Count Oct 29 22 1 8 1 ERROR: function get_ixn_prose(...) does not exist
Times Reported Most Frequent Error / Event #8
Day Hour Count Oct 29 15 1 - ERROR: function get_ixn_prose(integer) does not exist at character 66
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Statement: select reference_acc_txt ,taxon_acc_txt ,pubTerm.nm ,get_ixn_prose( ixn_id ) ,create_by ,create_tm from edit.reference_ixn ri ,pub1.term pubTerm -- set to CURRENT PRODUCTION PUB!!!!! where taxon_acc_txt not in ( select acc_txt from load.term where object_type_id = ( select id from edit.object_type where cd = 'taxon' ) ) and pubTerm.acc_txt = ri.taxon_acc_txt and object_type_id = ( select id from edit.object_type where cd = 'taxon' ) and taxon_acc_txt is not null and taxon_acc_txt <> ''Date: 2025-10-29 15:04:53 Database: ctdprd51 Application: pgAdmin 4 - CONN:7531889 User: load Remote:
9 1 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #9
Day Hour Count Oct 29 22 1 - ERROR: canceling statement due to user request
Statement: SELECT pg_database_size(datname::text) FROM pg_catalog.pg_database WHERE datistemplate = false AND datname = $1;
Date: 2025-10-29 22:54:08
10 1 FATAL: connection to client lost d.nm diseaseNm ,d.acc_txt diseaseAcc ,d.acc_db_cd diseaseAccDbCd
Times Reported Most Frequent Error / Event #10
Day Hour Count Oct 29 20 1 - FATAL: connection to client lost d.nm diseaseNm ,d.acc_txt diseaseAcc ,d.acc_db_cd diseaseAccDbCd
Statement: SELECT /* DiseaseGeneAssnsDAO */
Date: 2025-10-29 20:57:19
11 1 LOG: could not send data to client: Broken pipe
Times Reported Most Frequent Error / Event #11
Day Hour Count Oct 29 22 1