pgBadger-
Global information
- Generated on Wed Aug 27 04:15:05 2025
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20250826
- Parsed 30,186 log entries in 3s
- Log start from 2025-08-26 00:00:01 to 2025-08-26 23:58:54
-
Overview
Global Stats
- 124 Number of unique normalized queries
- 234 Number of queries
- 5h25m4s Total query duration
- 2025-08-26 00:02:52 First query
- 2025-08-26 23:35:07 Last query
- 1 queries/s at 2025-08-26 23:32:05 Query peak
- 5h25m4s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 5h25m4s Execute total duration
- 16 Number of events
- 8 Number of unique normalized events
- 6 Max number of times the same event was reported
- 0 Number of cancellation
- 60 Total number of automatic vacuums
- 98 Total number of automatic analyzes
- 2,118 Number temporary file
- 27.14 GiB Max size of temporary file
- 195.69 MiB Average size of temporary file
- 2,223 Total number of sessions
- 149 sessions at 2025-08-26 23:32:05 Session peak
- 41d1h37m19s Total duration of sessions
- 26m36s Average duration of sessions
- 0 Average queries per session
- 8s773ms Average queries duration per session
- 26m27s Average idle time per session
- 2,224 Total number of connections
- 11 connections/s at 2025-08-26 09:48:09 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 1 queries/s Query Peak
- 2025-08-26 23:32:05 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2025-08-26 06:02:52 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2025-08-26 23:32:05 Date
Queries duration
Key values
- 5h25m4s Total query duration
Prepared queries ratio
Key values
- 0.00 Ratio of bind vs prepare
- 0.00 % Ratio between prepared and "usual" statements
General Activity
↑ Back to the top of the General Activity tableDay Hour Count Min duration Max duration Avg duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 26 00 4 0ms 9m 2m20s 0ms 7s169ms 9m6s 01 2 0ms 7s242ms 7s124ms 0ms 0ms 7s242ms 02 3 0ms 7s139ms 6s394ms 0ms 0ms 19s184ms 03 4 0ms 9s142ms 7s570ms 6s935ms 7s149ms 9s142ms 04 2 0ms 7s4ms 6s986ms 0ms 0ms 13s972ms 05 9 0ms 7s503ms 7s145ms 13s996ms 14s147ms 14s951ms 06 2 0ms 7s227ms 7s94ms 0ms 0ms 14s188ms 07 4 0ms 7s102ms 6s114ms 0ms 5s185ms 12s190ms 08 4 0ms 9s107ms 8s66ms 0ms 7s126ms 18s89ms 09 5 0ms 7s63ms 5s998ms 0ms 6s938ms 10s215ms 10 2 0ms 6s962ms 6s946ms 0ms 0ms 13s893ms 11 2 0ms 7s151ms 7s3ms 0ms 6s856ms 7s151ms 12 3 0ms 7s76ms 6s416ms 0ms 0ms 12s196ms 13 5 0ms 7s138ms 6s65ms 5s326ms 7s138ms 10s756ms 14 18 0ms 4m22s 24s93ms 23s452ms 35s826ms 4m37s 15 31 0ms 2m37s 46s261ms 1m34s 1m44s 2m37s 16 15 0ms 14m33s 1m33s 34s909ms 1m58s 14m43s 17 13 0ms 33m51s 5m23s 1m27s 4m27s 33m59s 18 8 0ms 10m7s 1m42s 39s522ms 42s142ms 10m15s 19 4 0ms 42m47s 10m46s 0ms 12s567ms 42m47s 20 39 0ms 50m27s 2m11s 2m 3m21s 50m27s 21 7 0ms 5m16s 1m3s 6s716ms 31s706ms 5m16s 22 19 0ms 3m27s 26s957ms 41s36ms 51s79ms 3m27s 23 29 0ms 14m17s 56s464ms 1m27s 2m8s 14m23s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 26 00 3 0 3m4s 0ms 0ms 9m 01 2 0 7s124ms 0ms 0ms 7s242ms 02 3 0 6s394ms 0ms 0ms 19s184ms 03 4 0 7s570ms 0ms 6s935ms 9s142ms 04 2 0 6s986ms 0ms 0ms 13s972ms 05 9 0 7s145ms 0ms 13s996ms 14s951ms 06 2 0 7s94ms 0ms 0ms 0ms 07 4 0 6s114ms 0ms 0ms 12s190ms 08 4 0 8s66ms 0ms 0ms 18s89ms 09 5 0 5s998ms 0ms 0ms 10s215ms 10 2 0 6s946ms 0ms 0ms 13s893ms 11 2 0 7s3ms 0ms 0ms 7s151ms 12 3 0 6s416ms 0ms 0ms 12s196ms 13 5 0 6s65ms 0ms 5s326ms 10s756ms 14 17 0 24s978ms 7s169ms 23s452ms 4m37s 15 31 0 46s261ms 1m27s 1m34s 2m37s 16 7 0 6s194ms 0ms 5s321ms 13s305ms 17 7 0 5m2s 0ms 6s956ms 33m59s 18 8 0 1m42s 0ms 39s522ms 10m15s 19 4 0 10m46s 0ms 0ms 42m47s 20 14 0 4m37s 5s159ms 48s863ms 7m18s 21 7 0 1m3s 0ms 6s716ms 5m16s 22 18 0 28s144ms 9s89ms 41s36ms 3m27s 23 12 0 8s22ms 5s114ms 7s279ms 27s378ms Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 26 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 0 0 0 0 0ms 0ms 0ms 0ms 12 0 0 0 0 0ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 1 0 0 0 9s37ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 4 0 0 0 4m59s 0ms 0ms 3m18s 17 6 0 0 0 5m46s 0ms 0ms 4m27s 18 0 0 0 0 0ms 0ms 0ms 0ms 19 0 0 0 0 0ms 0ms 0ms 0ms 20 0 0 0 0 0ms 0ms 0ms 0ms 21 0 0 0 0 0ms 0ms 0ms 0ms 22 0 0 0 0 0ms 0ms 0ms 0ms 23 3 5 0 0 40s76ms 0ms 0ms 1m27s Day Hour Prepare Bind Bind/Prepare Percentage of prepare Aug 26 00 0 2 2.00 0.00% 01 0 2 2.00 0.00% 02 0 3 3.00 0.00% 03 0 4 4.00 0.00% 04 0 2 2.00 0.00% 05 0 9 9.00 0.00% 06 0 2 2.00 0.00% 07 0 4 4.00 0.00% 08 0 4 4.00 0.00% 09 0 5 5.00 0.00% 10 0 2 2.00 0.00% 11 0 2 2.00 0.00% 12 0 3 3.00 0.00% 13 0 5 5.00 0.00% 14 0 17 17.00 0.00% 15 0 30 30.00 0.00% 16 0 7 7.00 0.00% 17 0 13 13.00 0.00% 18 0 8 8.00 0.00% 19 0 4 4.00 0.00% 20 0 39 39.00 0.00% 21 0 7 7.00 0.00% 22 0 19 19.00 0.00% 23 0 29 29.00 0.00% Day Hour Count Average / Second Aug 26 00 80 0.02/s 01 81 0.02/s 02 80 0.02/s 03 82 0.02/s 04 81 0.02/s 05 103 0.03/s 06 86 0.02/s 07 102 0.03/s 08 80 0.02/s 09 121 0.03/s 10 79 0.02/s 11 80 0.02/s 12 84 0.02/s 13 97 0.03/s 14 138 0.04/s 15 131 0.04/s 16 79 0.02/s 17 97 0.03/s 18 81 0.02/s 19 78 0.02/s 20 89 0.02/s 21 83 0.02/s 22 120 0.03/s 23 92 0.03/s Day Hour Count Average Duration Average idle time Aug 26 00 80 29m47s 29m40s 01 81 30m8s 30m8s 02 80 30m3s 30m3s 03 82 29m35s 29m35s 04 81 29m56s 29m56s 05 103 24m4s 24m3s 06 86 27m48s 27m48s 07 102 23m56s 23m56s 08 80 30m21s 30m20s 09 121 20m26s 20m26s 10 79 29m41s 29m41s 11 80 30m6s 30m6s 12 84 29m11s 29m11s 13 95 25m17s 25m17s 14 137 17m52s 17m49s 15 132 18m27s 18m16s 16 77 29m35s 29m17s 17 97 26m42s 25m59s 18 81 29m48s 29m38s 19 78 29m21s 28m48s 20 92 40m18s 39m22s 21 83 26m42s 26m37s 22 120 20m48s 20m44s 23 92 25m31s 25m13s -
Connections
Established Connections
Key values
- 11 connections Connection Peak
- 2025-08-26 09:48:09 Date
Connections per database
Key values
- ctdprd51 Main Database
- 2,224 connections Total
Connections per user
Key values
- pubeu Main User
- 2,224 connections Total
-
Sessions
Simultaneous sessions
Key values
- 149 sessions Session Peak
- 2025-08-26 23:32:05 Date
Histogram of session times
Key values
- 1,800 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,223 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,223 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 2,223 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 5,284,902 buffers Checkpoint Peak
- 2025-08-26 23:34:15 Date
- 1619.668 seconds Highest write time
- 0.780 seconds Sync time
Checkpoints Wal files
Key values
- 551 files Wal files usage Peak
- 2025-08-26 15:13:16 Date
Checkpoints distance
Key values
- 19,088.51 Mo Distance Peak
- 2025-08-26 23:11:24 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Aug 26 00 2,932 293.8s 0.004s 293.877s 01 80 8.171s 0.002s 8.201s 02 5,545 555.186s 0.002s 555.267s 03 146 14.789s 0.002s 14.817s 04 144 14.484s 0.002s 14.513s 05 106 10.781s 0.002s 10.81s 06 304 30.641s 0.002s 30.67s 07 408 41.055s 0.002s 41.085s 08 53,196 1,626.311s 0.003s 1,626.491s 09 5,695 570.194s 0.003s 570.276s 10 57,142 1,621.264s 0.003s 1,621.439s 11 1,571 157.486s 0.003s 157.517s 12 2,739 274.494s 0.004s 274.617s 13 56,027 1,619.47s 0.152s 1,619.79s 14 22,599 1,619.432s 0.104s 1,619.73s 15 278,507 2,176.928s 0.881s 2,213.255s 16 2,252,131 3,520.823s 1.546s 3,564.178s 17 1,701,073 3,009.662s 0.295s 3,023.334s 18 262,764 1,619.402s 0.015s 1,621.75s 19 34,512 1,630.735s 0.003s 1,630.824s 20 90,065 69.441s 1.732s 108.942s 21 750,284 3,238.799s 0.016s 3,248.342s 22 405,845 2,079.934s 0.01s 2,086.733s 23 10,408,434 2,912.628s 0.157s 2,953.878s Day Hour Added Removed Recycled Synced files Longest sync Average sync Aug 26 00 0 1 0 71 0.001s 0.002s 01 0 0 0 19 0.001s 0.002s 02 0 4 0 38 0.001s 0.002s 03 0 0 0 27 0.001s 0.002s 04 0 0 0 27 0.001s 0.002s 05 0 0 0 22 0.001s 0.002s 06 0 0 0 76 0.001s 0.002s 07 0 0 0 123 0.001s 0.002s 08 0 36 0 86 0.001s 0.002s 09 0 4 0 38 0.001s 0.002s 10 0 38 0 40 0.001s 0.002s 11 0 0 0 27 0.001s 0.002s 12 0 2 0 44 0.001s 0.002s 13 0 37 0 585 0.001s 0.001s 14 0 18 0 145 0.033s 0.001s 15 0 159 2,667 413 0.443s 0.013s 16 0 28 3,222 477 0.725s 0.023s 17 0 0 1,076 168 0.113s 0.014s 18 0 0 183 246 0.001s 0.001s 19 0 0 0 80 0.001s 0.002s 20 0 33 2,694 148 0.779s 0.076s 21 0 0 775 151 0.001s 0.002s 22 0 24 550 82 0.001s 0.002s 23 0 256 2,859 644 0.040s 0.006s Day Hour Count Avg time (sec) Aug 26 00 0 0s 01 0 0s 02 0 0s 03 0 0s 04 0 0s 05 0 0s 06 0 0s 07 0 0s 08 0 0s 09 0 0s 10 0 0s 11 0 0s 12 0 0s 13 0 0s 14 0 0s 15 0 0s 16 0 0s 17 0 0s 18 0 0s 19 0 0s 20 0 0s 21 0 0s 22 0 0s 23 0 0s Day Hour Mean distance Mean estimate Aug 26 00 7,131.00 kB 11,690.50 kB 01 197.00 kB 9,598.00 kB 02 29,397.50 kB 33,311.50 kB 03 392.00 kB 49,989.00 kB 04 367.00 kB 40,571.00 kB 05 312.00 kB 32,921.50 kB 06 964.50 kB 26,808.00 kB 07 1,186.00 kB 21,947.50 kB 08 291,567.00 kB 553,554.00 kB 09 29,625.50 kB 454,005.00 kB 10 313,295.50 kB 595,149.00 kB 11 4,600.50 kB 482,550.50 kB 12 9,206.50 kB 392,511.50 kB 13 602,378.00 kB 602,378.00 kB 14 309,070.00 kB 573,047.00 kB 15 7,687,815.33 kB 7,691,574.00 kB 16 8,815,869.67 kB 8,826,652.67 kB 17 8,812,462.00 kB 8,822,245.50 kB 18 3,523,826.00 kB 8,291,825.00 kB 19 953.00 kB 7,089,665.50 kB 20 7,390,815.17 kB 8,494,236.00 kB 21 6,519,375.00 kB 8,734,395.00 kB 22 4,433,333.00 kB 8,236,858.00 kB 23 8,506,156.33 kB 9,364,468.67 kB -
Temporary Files
Size of temporary files
Key values
- 20.00 GiB Temp Files size Peak
- 2025-08-26 20:20:26 Date
Number of temporary files
Key values
- 20 per second Temp Files Peak
- 2025-08-26 20:20:26 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Aug 26 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 0 0 0 07 0 0 0 08 0 0 0 09 0 0 0 10 0 0 0 11 0 0 0 12 0 0 0 13 0 0 0 14 428 8.11 GiB 19.41 MiB 15 800 55.30 GiB 70.79 MiB 16 115 6.23 GiB 55.51 MiB 17 0 0 0 18 28 27.14 GiB 992.57 MiB 19 55 54.64 GiB 1017.35 MiB 20 341 194.31 GiB 583.51 MiB 21 10 7.96 GiB 815.04 MiB 22 210 18.11 GiB 88.33 MiB 23 131 32.93 GiB 257.42 MiB Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 1,413 88.40 GiB 8.00 KiB 1.00 GiB 64.06 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-08-26 20:32:46 Duration: 7m18s 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-08-26 21:04:39 Duration: 5m16s
-
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-08-26 14:58:44 Duration: 4m22s
2 85 84.26 GiB 263.49 MiB 1.00 GiB 1015.05 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-08-26 20:20:24 Duration: 0ms
3 55 54.64 GiB 658.40 MiB 1.00 GiB 1017.35 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-08-26 19:29:51 Duration: 0ms
4 35 1.20 GiB 21.12 MiB 52.61 MiB 34.97 MiB vacuum full analyze ixn_actor;-
vacuum FULL analyze ixn_actor;
Date: 2025-08-26 16:01:53 Duration: 25s55ms
-
vacuum FULL analyze ixn_actor;
Date: 2025-08-26 16:01:34 Duration: 0ms
5 35 4.44 GiB 71.73 MiB 171.87 MiB 129.94 MiB vacuum full analyze db_link;-
vacuum FULL analyze db_link;
Date: 2025-08-26 16:04:24 Duration: 1m58s
-
vacuum FULL analyze db_link;
Date: 2025-08-26 16:02:48 Duration: 0ms
6 25 17.43 GiB 8.00 KiB 1.00 GiB 714.03 MiB alter table pub2.term_enrichment_agent add constraint term_enrichment_agent_pk primary key (term_id, enriched_term_id, agent_term_id);-
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2025-08-26 23:11:25 Duration: 3m12s
-
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2025-08-26 23:11:24 Duration: 0ms
7 25 393.71 MiB 9.94 MiB 20.22 MiB 15.75 MiB vacuum full analyze ixn;-
vacuum FULL analyze ixn;
Date: 2025-08-26 16:02:09 Duration: 7s926ms
-
vacuum FULL analyze ixn;
Date: 2025-08-26 16:02:04 Duration: 0ms
8 20 218.59 MiB 6.66 MiB 16.98 MiB 10.93 MiB vacuum full analyze term;-
vacuum FULL analyze TERM;
Date: 2025-08-26 16:01:56 Duration: 9s854ms
-
vacuum FULL analyze TERM;
Date: 2025-08-26 16:01:49 Duration: 0ms
9 20 13.37 GiB 8.00 KiB 1.00 GiB 684.53 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-08-26 20:38:52 Duration: 3m21s
-
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-08-26 20:38:52 Duration: 0ms
10 15 7.43 GiB 8.00 KiB 1.00 GiB 507.06 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-08-26 20:35:31 Duration: 1m23s
-
ALTER TABLE pub2.gene_disease_reference ADD CONSTRAINT gene_disease_reference_pk PRIMARY KEY (id);
Date: 2025-08-26 20:35:30 Duration: 0ms Database: ctdprd51 User: pub2
11 15 12.45 GiB 420.05 MiB 1.00 GiB 850.02 MiB create index ix_term_enrich_agent_enr_term on pub2.term_enrichment_agent using btree (enriched_term_id);-
CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);
Date: 2025-08-26 23:13:16 Duration: 1m51s
-
CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);
Date: 2025-08-26 23:13:16 Duration: 0ms
12 10 7.43 GiB 422.51 MiB 1.00 GiB 760.59 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-08-26 20:47:13 Duration: 1m57s
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-08-26 20:47:13 Duration: 0ms
13 10 7.43 GiB 422.51 MiB 1.00 GiB 760.59 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-08-26 20:45:15 Duration: 1m25s
-
CREATE INDEX ix_gene_disease_ref_reference ON pub2.gene_disease_reference USING btree (reference_id);
Date: 2025-08-26 20:45:15 Duration: 0ms
14 10 7.43 GiB 435.43 MiB 1.00 GiB 760.59 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-08-26 20:50:11 Duration: 1m30s
-
CREATE INDEX ix_gene_disease_ref_mod_tm ON pub2.gene_disease_reference USING btree (mod_tm);
Date: 2025-08-26 20:50:10 Duration: 0ms
15 10 164.82 MiB 8.00 KiB 37.12 MiB 16.48 MiB alter table pub2.term_enrichment add constraint term_enrichment_pk primary key (term_id, enriched_term_id);-
ALTER TABLE pub2.term_enrichment ADD CONSTRAINT term_enrichment_pk PRIMARY KEY (term_id, enriched_term_id);
Date: 2025-08-26 22:53:40 Duration: 0ms Database: ctdprd51 User: pub2
16 10 7.43 GiB 437.14 MiB 1.00 GiB 760.59 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-08-26 20:43:50 Duration: 1m30s
-
CREATE INDEX ix_gene_disease_ref_disease ON pub2.gene_disease_reference USING btree (disease_id);
Date: 2025-08-26 20:43:50 Duration: 0ms
17 10 7.43 GiB 420.61 MiB 1.00 GiB 760.59 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-08-26 20:39:43 Duration: 50s596ms
-
CREATE INDEX ix_gene_disease_ref_src_db ON pub2.gene_disease_reference USING btree (source_acc_db_id);
Date: 2025-08-26 20:39:43 Duration: 0ms
18 10 7.43 GiB 422.51 MiB 1.00 GiB 760.59 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-08-26 20:42:19 Duration: 1m35s
-
CREATE INDEX ix_gene_disease_ref_chem ON pub2.gene_disease_reference USING btree (via_chem_id);
Date: 2025-08-26 20:42:19 Duration: 0ms
19 10 254.85 MiB 8.00 KiB 60.67 MiB 25.49 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-08-26 20:55:29 Duration: 0ms
20 10 7.43 GiB 422.66 MiB 1.00 GiB 760.59 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-08-26 20:52:47 Duration: 2m36s
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-08-26 20:52:47 Duration: 0ms
21 10 1.11 GiB 8.00 KiB 235.86 MiB 113.91 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-08-26 20:53:00 Duration: 12s223ms
-
ALTER TABLE pub2.phenotype_term_reference ADD CONSTRAINT phenotype_term_reference_pk PRIMARY KEY (id);
Date: 2025-08-26 20:52:58 Duration: 0ms
22 10 7.43 GiB 448.05 MiB 1.00 GiB 760.59 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-08-26 20:48:40 Duration: 1m26s
-
CREATE INDEX ix_gene_disease_reference_ixn ON pub2.gene_disease_reference USING btree (ixn_id);
Date: 2025-08-26 20:48:40 Duration: 0ms
23 10 7.43 GiB 276.49 MiB 1.00 GiB 760.59 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-08-26 20:40:44 Duration: 1m1s
-
CREATE INDEX ix_gene_disease_ref_source_cd ON pub2.gene_disease_reference USING btree (source_cd);
Date: 2025-08-26 20:40:44 Duration: 0ms
24 10 464.38 MiB 8.00 KiB 94.95 MiB 46.44 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-08-26 20:55:03 Duration: 6s247ms
-
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-08-26 20:55:03 Duration: 0ms
25 10 66.43 MiB 8.00 KiB 13.38 MiB 6.64 MiB alter table pub2.phenotype_term add constraint phenotype_term_pk primary key (phenotype_id, term_id);-
ALTER TABLE pub2.phenotype_term ADD CONSTRAINT phenotype_term_pk PRIMARY KEY (phenotype_id, term_id);
Date: 2025-08-26 23:29:09 Duration: 0ms
26 10 656.62 MiB 8.00 KiB 132.51 MiB 65.66 MiB alter table pub2.gene_disease add constraint gene_disease_pk primary key (gene_id, disease_id);-
ALTER TABLE pub2.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);
Date: 2025-08-26 23:28:40 Duration: 5s796ms
-
ALTER TABLE pub2.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);
Date: 2025-08-26 23:28:40 Duration: 0ms Database: ctdprd51 User: pub2
27 8 66.49 MiB 8.00 KiB 18.15 MiB 8.31 MiB alter table pub2.chem_disease add constraint chem_disease_pk primary key (chem_id, disease_id);-
ALTER TABLE pub2.chem_disease ADD CONSTRAINT chem_disease_pk PRIMARY KEY (chem_id, disease_id);
Date: 2025-08-26 23:29:15 Duration: 0ms
28 6 5.92 GiB 940.61 MiB 1.00 GiB 1010.10 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-08-26 20:24:32 Duration: 0ms
29 5 688.00 KiB 120.00 KiB 160.00 KiB 137.60 KiB create index ix_gene_disease_cur_ref_qty on pub2.gene_disease using btree (curated_reference_qty) where (curated_reference_qty > ?);-
CREATE INDEX ix_gene_disease_cur_ref_qty ON pub2.gene_disease USING btree (curated_reference_qty) WHERE (curated_reference_qty > 0);
Date: 2025-08-26 23:29:01 Duration: 0ms
30 5 230.04 MiB 42.45 MiB 48.58 MiB 46.01 MiB create index ix_term_enrich_corr_p_val on pub2.term_enrichment using btree (corrected_p_val);-
CREATE INDEX ix_term_enrich_corr_p_val ON pub2.term_enrichment USING btree (corrected_p_val);
Date: 2025-08-26 22:53:49 Duration: 0ms
31 5 254.82 MiB 50.50 MiB 51.63 MiB 50.96 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-08-26 20:55:06 Duration: 0ms
32 5 66.39 MiB 12.52 MiB 13.69 MiB 13.28 MiB create index ix_phenotype_term_term_id on pub2.phenotype_term using btree (term_id);-
CREATE INDEX ix_phenotype_term_term_id ON pub2.phenotype_term USING btree (term_id);
Date: 2025-08-26 23:29:10 Duration: 0ms
33 5 230.03 MiB 43.98 MiB 49.05 MiB 46.01 MiB create index ix_term_enrich_raw_p_val on pub2.term_enrichment using btree (raw_p_val);-
CREATE INDEX ix_term_enrich_raw_p_val ON pub2.term_enrichment USING btree (raw_p_val);
Date: 2025-08-26 22:53:55 Duration: 5s582ms
-
CREATE INDEX ix_term_enrich_raw_p_val ON pub2.term_enrichment USING btree (raw_p_val);
Date: 2025-08-26 22:53:55 Duration: 0ms
34 5 254.81 MiB 50.13 MiB 51.41 MiB 50.96 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-08-26 20:55:16 Duration: 0ms
35 5 1.11 GiB 213.32 MiB 248.07 MiB 227.80 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-08-26 20:53:30 Duration: 9s280ms
-
CREATE INDEX ix_phenotype_term_ref_object_type_id ON pub2.phenotype_term_reference USING btree (term_object_type_id);
Date: 2025-08-26 20:53:29 Duration: 0ms
36 5 1.11 GiB 215.73 MiB 237.15 MiB 227.80 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-08-26 20:53:10 Duration: 9s972ms
-
CREATE INDEX ix_phenotype_term_ref_phenotype_id ON pub2.phenotype_term_reference USING btree (phenotype_id);
Date: 2025-08-26 20:53:10 Duration: 0ms
37 5 254.81 MiB 49.41 MiB 52.80 MiB 50.96 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-08-26 20:55:19 Duration: 0ms
38 5 1.11 GiB 212.09 MiB 245.02 MiB 227.80 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-08-26 20:53:49 Duration: 8s22ms
-
CREATE INDEX ix_phenotype_term_ref_taxon_id ON pub2.phenotype_term_reference USING btree (taxon_id);
Date: 2025-08-26 20:53:49 Duration: 0ms
39 5 254.82 MiB 50.42 MiB 51.46 MiB 50.96 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-08-26 20:55:10 Duration: 0ms
40 5 1.56 GiB 317.71 MiB 324.93 MiB 319.69 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-08-26 20:54:57 Duration: 15s663ms
-
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-08-26 20:54:57 Duration: 0ms
41 5 656.58 MiB 127.69 MiB 135.74 MiB 131.32 MiB create index ix_gene_disease_disease on pub2.gene_disease using btree (disease_id);-
CREATE INDEX ix_gene_disease_disease ON pub2.gene_disease USING btree (disease_id);
Date: 2025-08-26 23:28:47 Duration: 6s646ms
-
CREATE INDEX ix_gene_disease_disease ON pub2.gene_disease USING btree (disease_id);
Date: 2025-08-26 23:28:46 Duration: 0ms
42 5 1.11 GiB 216.26 MiB 242.95 MiB 227.80 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-08-26 20:54:06 Duration: 8s369ms
-
CREATE INDEX ix_phenotype_term_reference_source_acc_db_id ON pub2.phenotype_term_reference USING btree (source_acc_db_id);
Date: 2025-08-26 20:54:06 Duration: 0ms
43 5 40.00 KiB 8.00 KiB 8.00 KiB 8.00 KiB create index ix_gene_disease_exp_ref_qty on pub2.gene_disease using btree (exposure_reference_qty) where (exposure_reference_qty > ?);-
CREATE INDEX ix_gene_disease_exp_ref_qty ON pub2.gene_disease USING btree (exposure_reference_qty) WHERE (exposure_reference_qty > 0);
Date: 2025-08-26 23:29:08 Duration: 0ms
44 5 656.44 MiB 126.66 MiB 140.04 MiB 131.29 MiB create index ix_gene_disease_ind_chem_qty on pub2.gene_disease using btree (indirect_chem_qty) where (indirect_chem_qty > ?);-
CREATE INDEX ix_gene_disease_ind_chem_qty ON pub2.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);
Date: 2025-08-26 23:29:08 Duration: 6s661ms
-
CREATE INDEX ix_gene_disease_ind_chem_qty ON pub2.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);
Date: 2025-08-26 23:29:08 Duration: 0ms
45 5 164.78 MiB 30.91 MiB 36.55 MiB 32.96 MiB create index ix_term_enrich_tgt_match on pub2.term_enrichment using btree (target_match_qty);-
CREATE INDEX ix_term_enrich_tgt_match ON pub2.term_enrichment USING btree (target_match_qty);
Date: 2025-08-26 22:53:43 Duration: 0ms
46 5 66.39 MiB 12.69 MiB 13.70 MiB 13.28 MiB create index ix_phenotype_term_phenotype_id on pub2.phenotype_term using btree (phenotype_id);-
CREATE INDEX ix_phenotype_term_phenotype_id ON pub2.phenotype_term USING btree (phenotype_id);
Date: 2025-08-26 23:29:10 Duration: 0ms
47 5 1.11 GiB 223.52 MiB 240.66 MiB 227.80 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-08-26 20:54:41 Duration: 11s773ms
-
CREATE INDEX ix_phenotype_term_ref_via_term_id ON pub2.phenotype_term_reference USING btree (via_term_id);
Date: 2025-08-26 20:54:41 Duration: 0ms
48 5 1.11 GiB 213.58 MiB 232.34 MiB 227.80 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-08-26 20:54:18 Duration: 12s330ms
-
CREATE INDEX ix_phenotype_term_reference_term_reference_id ON pub2.phenotype_term_reference USING btree (term_reference_id);
Date: 2025-08-26 20:54:18 Duration: 0ms
49 5 254.82 MiB 48.67 MiB 53.02 MiB 50.96 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-08-26 20:55:27 Duration: 5s340ms
-
CREATE INDEX ix_chem_disease_ref_net_sc ON pub2.chem_disease_reference USING btree (network_score);
Date: 2025-08-26 20:55:27 Duration: 0ms
50 5 1.11 GiB 216.66 MiB 235.00 MiB 227.80 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-08-26 20:54:30 Duration: 11s529ms
-
CREATE INDEX ix_phenotype_term_reference_ixn_id ON pub2.phenotype_term_reference USING btree (ixn_id);
Date: 2025-08-26 20:54:30 Duration: 0ms
51 5 254.81 MiB 50.30 MiB 51.62 MiB 50.96 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-08-26 20:55:13 Duration: 0ms
52 5 254.82 MiB 47.67 MiB 53.61 MiB 50.96 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-08-26 20:55:11 Duration: 0ms
53 5 656.58 MiB 129.62 MiB 132.13 MiB 131.32 MiB create index ix_gene_disease_network_score on pub2.gene_disease using btree (network_score);-
CREATE INDEX ix_gene_disease_network_score ON pub2.gene_disease USING btree (network_score);
Date: 2025-08-26 23:29:00 Duration: 13s852ms
-
CREATE INDEX ix_gene_disease_network_score ON pub2.gene_disease USING btree (network_score);
Date: 2025-08-26 23:29:00 Duration: 0ms
54 5 1.11 GiB 221.15 MiB 231.10 MiB 227.80 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-08-26 20:53:41 Duration: 11s847ms
-
CREATE INDEX ix_phenotype_term_ref_reference_id ON pub2.phenotype_term_reference USING btree (reference_id);
Date: 2025-08-26 20:53:41 Duration: 0ms
55 5 1.11 GiB 201.58 MiB 241.29 MiB 227.80 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-08-26 20:53:57 Duration: 7s932ms
-
CREATE INDEX ix_phenotype_term_ref_evidence_cd ON pub2.phenotype_term_reference USING btree (evidence_cd);
Date: 2025-08-26 20:53:57 Duration: 0ms
56 5 164.78 MiB 27.88 MiB 37.45 MiB 32.96 MiB create index ix_term_enrich_obj_type on pub2.term_enrichment using btree (object_type_id);-
CREATE INDEX ix_term_enrich_obj_type ON pub2.term_enrichment USING btree (object_type_id);
Date: 2025-08-26 22:53:41 Duration: 0ms
57 5 1.11 GiB 224.53 MiB 232.12 MiB 227.80 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-08-26 20:53:20 Duration: 10s676ms
-
CREATE INDEX ix_phenotype_term_ref_term_id ON pub2.phenotype_term_reference USING btree (term_id);
Date: 2025-08-26 20:53:20 Duration: 0ms
58 5 254.81 MiB 50.21 MiB 51.39 MiB 50.96 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-08-26 20:55:22 Duration: 0ms
59 5 164.79 MiB 29.45 MiB 37.12 MiB 32.96 MiB create index ix_term_enrich_enr_obj_type on pub2.term_enrichment using btree (enriched_object_type_id);-
CREATE INDEX ix_term_enrich_enr_obj_type ON pub2.term_enrichment USING btree (enriched_object_type_id);
Date: 2025-08-26 22:53:44 Duration: 0ms
60 4 66.46 MiB 15.49 MiB 17.14 MiB 16.62 MiB create index ix_chem_disease_disease on pub2.chem_disease using btree (disease_id);-
CREATE INDEX ix_chem_disease_disease ON pub2.chem_disease USING btree (disease_id);
Date: 2025-08-26 23:29:14 Duration: 0ms
61 4 32.00 KiB 8.00 KiB 8.00 KiB 8.00 KiB create index ix_chem_disease_exp_ref_qty on pub2.chem_disease using btree (exposure_reference_qty) where (exposure_reference_qty > ?);-
CREATE INDEX ix_chem_disease_exp_ref_qty ON pub2.chem_disease USING btree (exposure_reference_qty) WHERE (exposure_reference_qty > 0);
Date: 2025-08-26 23:29:15 Duration: 0ms
62 4 65.57 MiB 16.00 MiB 16.77 MiB 16.39 MiB create index ix_chem_disease_ind_gene_qty on pub2.chem_disease using btree (indirect_gene_qty) where (indirect_gene_qty > ?);-
CREATE INDEX ix_chem_disease_ind_gene_qty ON pub2.chem_disease USING btree (indirect_gene_qty) WHERE (indirect_gene_qty > 0);
Date: 2025-08-26 23:29:14 Duration: 0ms
63 4 66.45 MiB 16.48 MiB 16.78 MiB 16.61 MiB create index ix_chem_disease_network_score on pub2.chem_disease using btree (network_score);-
CREATE INDEX ix_chem_disease_network_score ON pub2.chem_disease USING btree (network_score);
Date: 2025-08-26 23:29:13 Duration: 0ms
64 4 2.01 MiB 504.00 KiB 520.00 KiB 514.00 KiB create index ix_chem_disease_cur_ref_qty on pub2.chem_disease using btree (curated_reference_qty) where (curated_reference_qty > ?);-
CREATE INDEX ix_chem_disease_cur_ref_qty ON pub2.chem_disease USING btree (curated_reference_qty) WHERE (curated_reference_qty > 0);
Date: 2025-08-26 23:29:14 Duration: 0ms
65 4 14.48 MiB 8.00 KiB 7.29 MiB 3.62 MiB alter table pub2.phenotype_term_axn add constraint phenotype_term_axn_pk primary key (phenotype_id, term_id, action_type_nm, action_degree_type_nm);-
ALTER TABLE pub2.phenotype_term_axn ADD CONSTRAINT phenotype_term_axn_pk PRIMARY KEY (phenotype_id, term_id, action_type_nm, action_degree_type_nm);
Date: 2025-08-26 23:29:11 Duration: 0ms
66 2 6.56 MiB 3.01 MiB 3.55 MiB 3.28 MiB create index ix_phenotype_term_axn_term_id on pub2.phenotype_term_axn using btree (term_id);-
CREATE INDEX ix_phenotype_term_axn_term_id ON pub2.phenotype_term_axn USING btree (term_id);
Date: 2025-08-26 23:29:11 Duration: 0ms
67 2 6.56 MiB 2.16 MiB 4.40 MiB 3.28 MiB create index ix_phenotype_term_axn_phenotype_id on pub2.phenotype_term_axn using btree (phenotype_id);-
CREATE INDEX ix_phenotype_term_axn_phenotype_id ON pub2.phenotype_term_axn USING btree (phenotype_id);
Date: 2025-08-26 23:29:11 Duration: 0ms
68 1 27.14 GiB 27.14 GiB 27.14 GiB 27.14 GiB select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);-
select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');
Date: 2025-08-26 18:42:47 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 27.14 GiB select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = 'does not affect');[ Date: 2025-08-26 18:42:47 ]
2 1.00 GiB SELECT * FROM pgbulkload.pg_bulkload ($1);[ Date: 2025-08-26 15:23:30 - Database: ctdprd51 - User: load - Application: pg_bulkload ]
3 1.00 GiB 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-08-26 19:29:51 ]
4 1.00 GiB 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-08-26 19:29:51 ]
5 1.00 GiB 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-08-26 19:29:51 ]
6 1.00 GiB 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-08-26 19:29:51 ]
7 1.00 GiB 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-08-26 19:29:51 ]
8 1.00 GiB 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-08-26 19:29:51 ]
9 1.00 GiB 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-08-26 19:29:51 ]
10 1.00 GiB 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-08-26 19:29:51 ]
11 1.00 GiB 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-08-26 19:29:51 ]
12 1.00 GiB 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-08-26 19:29:51 ]
13 1.00 GiB 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-08-26 19:29:51 ]
14 1.00 GiB 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-08-26 19:29:51 ]
15 1.00 GiB 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-08-26 19:29:51 ]
16 1.00 GiB 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-08-26 19:29:51 ]
17 1.00 GiB 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-08-26 19:29:51 ]
18 1.00 GiB 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-08-26 19:29:51 ]
19 1.00 GiB 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-08-26 19:29:51 ]
20 1.00 GiB 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-08-26 19:29:52 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 60.78 sec Highest CPU-cost vacuum
Table pub2.gene_go_annot
Database ctdprd51 - 2025-08-26 17:08:49 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 60.78 sec Highest CPU-cost vacuum
Table pub2.gene_go_annot
Database ctdprd51 - 2025-08-26 17:08:49 Date
Analyzes per table
Key values
- pubc.log_query (21) Main table analyzed (database ctdprd51)
- 98 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 21 ctdprd51.pub2.term 3 ctdprd51.pg_catalog.pg_class 3 ctdprd51.pg_catalog.pg_attribute 2 ctdprd51.pub2.db 2 ctdprd51.pub2.dag_node 2 ctdprd51.pg_catalog.pg_index 2 ctdprd51.pub2.reference 2 postgres.pg_catalog.pg_shdepend 2 ctdprd51.pub2.exp_event 1 ctdprd51.pub2.chem_disease 1 ctdprd51.pub2.reference_party_role 1 ctdprd51.pg_catalog.pg_description 1 ctdprd51.pub2.db_report 1 ctdprd51.pg_catalog.pg_shdepend 1 ctdprd51.pub2.exp_event_location 1 ctdprd51.edit.db_link 1 ctdprd51.edit.slim_term 1 ctdprd51.pub2.dag_edge 1 ctdprd51.pub2.country 1 ctdprd51.edit.race 1 ctdprd51.pub2.exp_study_factor 1 ctdprd51.pub2.exp_event_project 1 ctdprd51.pub2.db_report_site 1 ctdprd51.edit.evidence 1 ctdprd51.pub2.action_type 1 ctdprd51.edit.object_note 1 ctdprd51.pub2.exp_outcome 1 ctdprd51.pg_catalog.pg_attrdef 1 ctdprd51.pub2.exp_stressor 1 ctdprd51.pub2.exp_stressor_stressor_src 1 ctdprd51.pub2.exp_event_assay_method 1 ctdprd51.edit.db 1 ctdprd51.edit.action_type 1 ctdprd51.load.data_load 1 ctdprd51.pub2.medium 1 ctdprd51.pg_catalog.pg_depend 1 ctdprd51.pub2.gene_taxon 1 ctdprd51.pub2.exposure 1 ctdprd51.pub2.term_label 1 ctdprd51.pub2.exp_receptor_gender 1 ctdprd51.edit.db_report_site 1 ctdprd51.edit.list_db_report 1 ctdprd51.pub2.reference_party 1 ctdprd51.edit.term_label_type 1 ctdprd51.edit.geographic_region 1 ctdprd51.edit.db_report 1 ctdprd51.pub2.reference_exp 1 ctdprd51.pg_catalog.pg_trigger 1 ctdprd51.pub2.exp_receptor_tobacco_use 1 ctdprd51.edit.reference_db_link 1 ctdprd51.edit.age_uom 1 ctdprd51.pub2.exp_receptor_race 1 ctdprd51.edit.country 1 ctdprd51.pub2.exp_anatomy 1 ctdprd51.pub2.exp_receptor 1 ctdprd51.edit.action_degree 1 ctdprd51.pub2.list_db_report 1 ctdprd51.edit.actor_form_type 1 ctdprd51.pg_catalog.pg_type 1 ctdprd51.edit.action_type_path 1 ctdprd51.pub1.term_set_enrichment_agent 1 ctdprd51.pub2.term_pathway 1 ctdprd51.pub2.db_link 1 ctdprd51.pub2.gene_go_annot 1 ctdprd51.pub2.geographic_region 1 ctdprd51.pg_catalog.pg_constraint 1 ctdprd51.pub2.img 1 Total 98 Vacuums per table
Key values
- pub2.term (3) Main table vacuumed on database ctdprd51
- 60 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pub2.term 3 1 558,146 0 2,547 0 0 393,908 13,707 138,644,807 ctdprd51.pubc.log_query 3 3 1,120 0 221 0 0 365 144 1,051,671 ctdprd51.pg_catalog.pg_statistic 2 2 1,552 0 193 0 258 1,061 177 715,915 ctdprd51.pub2.dag_node 2 1 406,962 0 853 0 0 318,868 565 96,072,512 ctdprd51.pg_catalog.pg_class 2 2 696 0 74 0 28 329 70 277,038 ctdprd51.pub2.reference 2 1 309,761 0 2,317 0 0 201,192 9 15,308,717 ctdprd51.pg_catalog.pg_description 1 1 202 0 44 0 42 128 23 95,771 ctdprd51.pg_catalog.pg_depend 1 1 639 0 81 0 65 293 83 353,092 ctdprd51.pub2.gene_taxon 1 0 163,194 0 7 0 0 81,538 4 4,840,371 ctdprd51.pub2.exposure 1 0 3,955 0 1,927 0 0 1,924 1 121,935 ctdprd51.edit.db_link 1 0 7,601 0 3 0 0 3,682 2 225,957 ctdprd51.pub2.exp_event_location 1 0 3,696 0 1,799 0 0 1,796 1 114,383 ctdprd51.pub2.exp_receptor_gender 1 0 2,840 0 1,408 0 0 1,405 1 91,314 ctdprd51.pub2.term_label 1 0 190,237 0 6 0 0 95,050 4 5,636,215 ctdprd51.pg_catalog.pg_attribute 1 1 723 0 28 0 37 324 27 154,444 ctdprd51.pub2.reference_party 1 0 5,137 0 4 0 0 2,535 2 163,816 ctdprd51.pub2.exp_event 1 0 13,334 0 4 0 0 6,588 2 402,019 ctdprd51.edit.action_type 1 0 140 0 2 0 0 7 1 9,121 ctdprd51.pub2.reference_party_role 1 0 13,602 0 5 0 0 6,774 2 411,537 ctdprd51.pub2.chem_disease 1 1 274,900 0 48,874 0 0 167,968 48,489 183,386,917 ctdprd51.pub2.exp_receptor_race 1 0 1,390 0 663 0 0 660 1 47,359 ctdprd51.pub2.dag_edge 1 0 1,024 0 5 0 0 482 2 39,509 ctdprd51.edit.country 1 0 66 0 1 0 0 8 1 9,627 ctdprd51.pub2.exp_anatomy 1 0 127 0 39 0 0 35 2 16,544 ctdprd51.pub2.exp_study_factor 1 0 114 0 14 0 0 11 1 9,068 ctdprd51.pub2.db 1 1 116 0 3 0 0 20 1 11,531 ctdprd51.edit.geographic_region 1 0 64 0 5 0 0 3 2 16,773 ctdprd51.pub2.reference_exp 1 0 336 0 4 0 0 131 2 21,980 ctdprd51.edit.age_uom 1 0 78 0 5 0 0 3 2 14,041 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 1,237 0 586 0 0 583 1 42,816 ctdprd51.pg_catalog.pg_trigger 1 1 343 0 31 0 0 130 32 142,196 ctdprd51.edit.reference_db_link 1 0 7,389 0 4 0 0 3,682 2 232,685 ctdprd51.pub1.term_set_enrichment 1 0 7,906 0 3,650 0 0 3,900 2 241,971 ctdprd51.edit.object_note 1 1 175 0 5 0 0 16 3 16,024 ctdprd51.pub2.exp_outcome 1 0 839 0 366 0 0 362 2 35,689 ctdprd51.pub2.exp_stressor 1 0 6,621 0 3,285 0 0 3,281 2 207,494 ctdprd51.pg_catalog.pg_attrdef 1 1 85 0 4 0 0 23 3 12,758 postgres.pg_catalog.pg_shdepend 1 1 190 0 49 0 0 105 42 125,352 ctdprd51.pg_catalog.pg_index 1 1 177 0 17 0 0 90 12 54,140 ctdprd51.pg_toast.pg_toast_2619 1 1 4,231 0 443 0 10,441 3,024 390 353,971 ctdprd51.pg_toast.pg_toast_9054452 1 0 89,433 0 4 0 0 44,709 2 2,654,354 ctdprd51.pub2.exp_event_project 1 0 2,280 0 1,121 0 0 1,118 1 74,381 ctdprd51.pub2.exp_receptor 1 0 7,723 0 3,836 0 0 3,833 1 234,566 ctdprd51.edit.action_degree 1 0 46 0 3 0 0 13 2 15,358 ctdprd51.pg_catalog.pg_constraint 1 1 282 0 21 0 0 109 22 101,526 ctdprd51.pub2.img 1 0 1,108 0 5 0 0 524 2 44,871 ctdprd51.pub2.exp_event_assay_method 1 0 5,277 0 4 0 0 2,593 2 166,314 ctdprd51.edit.action_type_path 1 0 51 0 0 0 0 4 1 9,059 ctdprd51.pub2.term_pathway 1 0 3,332 0 3 0 0 1,614 1 103,645 ctdprd51.pub2.exp_stressor_stressor_src 1 0 2,879 0 1,414 0 0 1,411 1 91,668 ctdprd51.pub2.db_link 1 0 295,120 0 109,351 0 0 147,436 6 8,744,839 ctdprd51.pub2.gene_go_annot 1 0 623,077 0 251,784 0 0 311,420 11 18,460,788 Total 60 22 3,021,553 13,974 437,122 0 10,871 1,817,068 63,871 480,430,449 Tuples removed per table
Key values
- pub2.chem_disease (3476631) Main table with removed tuples on database ctdprd51
- 7414225 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pub2.chem_disease 1 1 3,476,631 3,476,631 0 0 51,078 ctdprd51.pub2.term 3 1 2,129,522 4,292,412 0 0 170,151 ctdprd51.pub2.dag_node 2 1 1,742,999 3,470,966 0 0 125,259 ctdprd51.pub2.reference 2 1 53,114 401,522 0 0 117,096 ctdprd51.pg_toast.pg_toast_2619 1 1 3,765 18,124 0 0 12,592 ctdprd51.pg_catalog.pg_statistic 2 2 1,423 6,352 0 0 820 ctdprd51.pg_catalog.pg_depend 1 1 1,360 14,070 0 0 139 ctdprd51.pg_catalog.pg_description 1 1 1,285 5,325 0 0 90 ctdprd51.pg_catalog.pg_attribute 1 1 1,252 8,740 0 0 230 postgres.pg_catalog.pg_shdepend 1 1 626 1,570 0 0 25 ctdprd51.pg_catalog.pg_class 2 2 465 4,130 0 0 188 ctdprd51.pg_catalog.pg_trigger 1 1 452 1,797 0 0 52 ctdprd51.pg_catalog.pg_index 1 1 285 1,167 0 0 38 ctdprd51.edit.country 1 0 163 249 0 0 4 ctdprd51.pg_catalog.pg_constraint 1 1 162 879 0 0 40 ctdprd51.pub2.db 1 1 134 134 0 0 7 ctdprd51.edit.action_degree 1 0 109 219 0 0 6 ctdprd51.edit.action_type_path 1 0 106 106 0 0 2 ctdprd51.pg_catalog.pg_attrdef 1 1 92 234 0 0 12 ctdprd51.edit.geographic_region 1 0 67 51 0 0 1 ctdprd51.edit.object_note 1 1 66 33 0 1 2 ctdprd51.edit.action_type 1 0 64 60 0 0 3 ctdprd51.edit.age_uom 1 0 63 9 0 0 1 ctdprd51.pubc.log_query 3 3 20 8,042 3 0 239 ctdprd51.pub2.gene_taxon 1 0 0 12,801,236 0 0 81,537 ctdprd51.pub2.exposure 1 0 0 233,322 0 0 1,923 ctdprd51.edit.db_link 1 0 0 330,331 0 0 3,681 ctdprd51.pub2.exp_event_location 1 0 0 269,944 0 0 1,795 ctdprd51.pub2.exp_receptor_gender 1 0 0 201,624 0 0 1,404 ctdprd51.pub2.term_label 1 0 0 6,546,266 0 0 95,049 ctdprd51.pub2.reference_party 1 0 0 453,652 0 0 2,534 ctdprd51.pub2.exp_event 1 0 0 223,894 0 0 6,587 ctdprd51.pub2.reference_party_role 1 0 0 1,252,902 0 0 6,773 ctdprd51.pub2.exp_receptor_race 1 0 0 101,622 0 0 659 ctdprd51.pub2.dag_edge 1 0 0 88,931 0 0 481 ctdprd51.pub2.exp_anatomy 1 0 0 4,025 0 0 34 ctdprd51.pub2.exp_study_factor 1 0 0 1,696 0 0 10 ctdprd51.pub2.reference_exp 1 0 0 3,586 0 0 130 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 0 82,439 0 0 582 ctdprd51.edit.reference_db_link 1 0 0 330,331 0 0 3,681 ctdprd51.pub1.term_set_enrichment 1 0 0 1,223,990 0 0 20,239 ctdprd51.pub2.exp_outcome 1 0 0 36,349 0 0 361 ctdprd51.pub2.exp_stressor 1 0 0 226,513 0 0 3,280 ctdprd51.pg_toast.pg_toast_9054452 1 0 0 241,679 0 0 44,708 ctdprd51.pub2.exp_event_project 1 0 0 106,629 0 0 1,117 ctdprd51.pub2.exp_receptor 1 0 0 206,105 0 0 3,832 ctdprd51.pub2.img 1 0 0 50,671 0 0 523 ctdprd51.pub2.exp_event_assay_method 1 0 0 258,619 0 0 2,592 ctdprd51.pub2.term_pathway 1 0 0 135,792 0 0 1,613 ctdprd51.pub2.exp_stressor_stressor_src 1 0 0 318,579 0 0 1,410 ctdprd51.pub2.db_link 1 0 0 20,357,616 0 0 147,435 ctdprd51.pub2.gene_go_annot 1 0 0 48,894,240 0 0 311,419 Total 60 22 7,414,225 106,695,405 3 1 1,223,464 Pages removed per table
Key values
- edit.object_note (1) Main table with removed pages on database ctdprd51
- 1 pages Total removed
Table Number of vacuums Index scans Tuples removed Pages removed ctdprd51.edit.object_note 1 1 66 1 ctdprd51.pg_catalog.pg_description 1 1 1285 0 ctdprd51.pg_catalog.pg_depend 1 1 1360 0 ctdprd51.pub2.gene_taxon 1 0 0 0 ctdprd51.pub2.exposure 1 0 0 0 ctdprd51.edit.db_link 1 0 0 0 ctdprd51.pub2.exp_event_location 1 0 0 0 ctdprd51.pub2.exp_receptor_gender 1 0 0 0 ctdprd51.pub2.term_label 1 0 0 0 ctdprd51.pg_catalog.pg_attribute 1 1 1252 0 ctdprd51.pub2.reference_party 1 0 0 0 ctdprd51.pub2.exp_event 1 0 0 0 ctdprd51.edit.action_type 1 0 64 0 ctdprd51.pub2.reference_party_role 1 0 0 0 ctdprd51.pub2.term 3 1 2129522 0 ctdprd51.pub2.chem_disease 1 1 3476631 0 ctdprd51.pub2.exp_receptor_race 1 0 0 0 ctdprd51.pub2.dag_edge 1 0 0 0 ctdprd51.pubc.log_query 3 3 20 0 ctdprd51.edit.country 1 0 163 0 ctdprd51.pub2.exp_anatomy 1 0 0 0 ctdprd51.pub2.exp_study_factor 1 0 0 0 ctdprd51.pg_catalog.pg_statistic 2 2 1423 0 ctdprd51.pub2.db 1 1 134 0 ctdprd51.edit.geographic_region 1 0 67 0 ctdprd51.pub2.dag_node 2 1 1742999 0 ctdprd51.pg_catalog.pg_class 2 2 465 0 ctdprd51.pub2.reference_exp 1 0 0 0 ctdprd51.edit.age_uom 1 0 63 0 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 0 0 ctdprd51.pg_catalog.pg_trigger 1 1 452 0 ctdprd51.edit.reference_db_link 1 0 0 0 ctdprd51.pub1.term_set_enrichment 1 0 0 0 ctdprd51.pub2.reference 2 1 53114 0 ctdprd51.pub2.exp_outcome 1 0 0 0 ctdprd51.pub2.exp_stressor 1 0 0 0 ctdprd51.pg_catalog.pg_attrdef 1 1 92 0 postgres.pg_catalog.pg_shdepend 1 1 626 0 ctdprd51.pg_catalog.pg_index 1 1 285 0 ctdprd51.pg_toast.pg_toast_2619 1 1 3765 0 ctdprd51.pg_toast.pg_toast_9054452 1 0 0 0 ctdprd51.pub2.exp_event_project 1 0 0 0 ctdprd51.pub2.exp_receptor 1 0 0 0 ctdprd51.edit.action_degree 1 0 109 0 ctdprd51.pg_catalog.pg_constraint 1 1 162 0 ctdprd51.pub2.img 1 0 0 0 ctdprd51.pub2.exp_event_assay_method 1 0 0 0 ctdprd51.edit.action_type_path 1 0 106 0 ctdprd51.pub2.term_pathway 1 0 0 0 ctdprd51.pub2.exp_stressor_stressor_src 1 0 0 0 ctdprd51.pub2.db_link 1 0 0 0 ctdprd51.pub2.gene_go_annot 1 0 0 0 Total 60 22 7,414,225 1 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Aug 26 00 1 1 01 0 1 02 0 2 03 1 2 04 0 1 05 0 3 06 0 1 07 0 1 08 1 1 09 0 1 10 0 1 11 0 0 12 0 1 13 11 12 14 8 19 15 2 1 16 6 13 17 10 8 18 0 0 19 0 1 20 0 1 21 1 2 22 0 2 23 19 23 - 60.78 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
- 175 Total read queries
- 52 Total write queries
Queries by database
Key values
- unknown Main database
- 148 Requests
- 4h2m56s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 180 Requests
User Request type Count Duration edit Total 1 9s37ms insert 1 9s37ms load Total 24 1h59s select 24 1h59s pub2 Total 3 14m48s insert 2 14m42s select 1 6s38ms pubeu Total 122 16m18s select 122 16m18s qaeu Total 2 14s67ms select 2 14s67ms unknown Total 180 4h7m17s ddl 33 41m1s insert 11 40m26s others 7 3m13s select 124 2h37m42s update 5 4m52s Duration by user
Key values
- 4h7m17s (unknown) Main time consuming user
User Request type Count Duration edit Total 1 9s37ms insert 1 9s37ms load Total 24 1h59s select 24 1h59s pub2 Total 3 14m48s insert 2 14m42s select 1 6s38ms pubeu Total 122 16m18s select 122 16m18s qaeu Total 2 14s67ms select 2 14s67ms unknown Total 180 4h7m17s ddl 33 41m1s insert 11 40m26s others 7 3m13s select 124 2h37m42s update 5 4m52s Queries by host
Key values
- unknown Main host
- 332 Requests
- 5h39m47s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 217 Requests
- 5h9m39s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2025-08-26 00:43:44 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 132 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 50m27s 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-08-26 20:20:22 - Bind query: yes ]
2 42m47s 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-08-26 19:29:48 - Bind query: yes ]
3 33m51s 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-08-26 17:49:39 - Database: ctdprd51 - User: load - Bind query: yes ]
4 26m46s 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-08-26 17:07:17 - Bind query: yes ]
5 14m33s 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-08-26 16:35:03 - Database: ctdprd51 - User: pub2 - Bind query: yes ]
6 14m17s ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enr_agent_term_enr_fk FOREIGN KEY (term_id, enriched_term_id) REFERENCES term_enrichment (term_id, enriched_term_id);[ Date: 2025-08-26 23:08:13 - Bind query: yes ]
7 10m7s 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-08-26 18:02:38 - Database: ctdprd51 - User: load - Bind query: yes ]
8 9m /* * 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-08-26 00:09:01 ]
9 7m18s 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-08-26 20:32:46 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
10 5m16s 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-08-26 21:04:39 - Bind query: yes ]
11 4m27s 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-08-26 17:11:44 - Bind query: yes ]
12 4m22s 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-08-26 14:58:44 - Bind query: yes ]
13 3m27s SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.TERM_ENRICHMENT_AGENT,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.log,parse-badfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/goEnrichment/enrichedTermAgent.txt.DUPE}');[ Date: 2025-08-26 22:51:02 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
14 3m21s 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-08-26 20:38:52 - Bind query: yes ]
15 3m18s 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-08-26 16:40:30 - Bind query: yes ]
16 3m12s ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2025-08-26 23:11:25 - Bind query: yes ]
17 2m37s 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-08-26 15:36:28 - Bind query: yes ]
18 2m36s CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);[ Date: 2025-08-26 20:52:47 - Bind query: yes ]
19 2m2s 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-08-26 20:22:31 - Bind query: yes ]
20 1m58s vacuum FULL analyze db_link;[ Date: 2025-08-26 16:04:24 ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 54m1s 60 5s483ms 7m18s 54s33ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 26 14 14 6m45s 28s954ms 15 24 22m17s 55s736ms 17 3 1m6s 22s316ms 20 4 8m47s 2m11s 21 3 6m59s 2m19s 22 11 7m44s 42s191ms 23 1 20s815ms 20s815ms [ User: load - Total duration: 15m15s - Times executed: 16 ]
[ Application: pg_bulkload - Total duration: 15m15s - Times executed: 16 ]
-
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-08-26 20:32:46 Duration: 7m18s 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-08-26 21:04:39 Duration: 5m16s 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-08-26 14:58:44 Duration: 4m22s Bind query: yes
2 50m27s 1 50m27s 50m27s 50m27s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Aug 26 20 1 50m27s 50m27s -
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-08-26 20:20:22 Duration: 50m27s Bind query: yes
3 42m47s 1 42m47s 42m47s 42m47s select distinct ptr.phenotype_id, gcr.gene_id, ( select id from pub2.object_type where cd = ?), ptr.term_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.phenotype_term_reference ptr, pub2.phenotype_term_reference ptr2 where gcr.chem_id = ptr.term_id and ptr.phenotype_id = ptr2.phenotype_id and gcr.gene_id = ptr2.term_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 26 19 1 42m47s 42m47s -
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-08-26 19:29:48 Duration: 42m47s Bind query: yes
4 33m51s 1 33m51s 33m51s 33m51s select i.id, edit.get_ixn_xml (i.id), edit.get_ixn_prose (i.id), edit.get_ixn_delimited_actions (i.id), i.ixn_type_id, r.reference_acc_txt, r.taxon_acc_txt, r.create_by, common.break_html_words (edit.get_ixn_prose_html (i.id), false) from edit.ixn i, edit.reference_ixn r where i.id = i.root_id and i.id = r.ixn_id and r.create_by not in (...) order by i.id asc;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 26 17 1 33m51s 33m51s [ User: load - Total duration: 33m51s - 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-08-26 17:49:39 Duration: 33m51s Database: ctdprd51 User: load Bind query: yes
5 26m46s 1 26m46s 26m46s 26m46s insert into pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) select gene_id, go_term_id, taxon_id, evidence_cd, is_not from load.gene_go_annot;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 26 17 1 26m46s 26m46s -
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-08-26 17:07:17 Duration: 26m46s Bind query: yes
6 14m33s 1 14m33s 14m33s 14m33s 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 #6
Day Hour Count Duration Avg duration Aug 26 16 1 14m33s 14m33s [ User: pub2 - Total duration: 14m33s - 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-08-26 16:35:03 Duration: 14m33s Database: ctdprd51 User: pub2 Bind query: yes
7 14m17s 1 14m17s 14m17s 14m17s alter table pub2.term_enrichment_agent add constraint term_enr_agent_term_enr_fk foreign key (term_id, enriched_term_id) references term_enrichment (term_id, enriched_term_id);Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Aug 26 23 1 14m17s 14m17s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enr_agent_term_enr_fk FOREIGN KEY (term_id, enriched_term_id) REFERENCES term_enrichment (term_id, enriched_term_id);
Date: 2025-08-26 23:08:13 Duration: 14m17s Bind query: yes
8 12m45s 5 38s941ms 10m7s 2m33s 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 Aug 26 18 5 12m45s 2m33s [ User: load - Total duration: 10m7s - 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-08-26 18:02:38 Duration: 10m7s 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-08-26 18:22:34 Duration: 40s38ms 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-08-26 18:33:26 Duration: 39s522ms Bind query: yes
9 9m 1 9m 9m 9m select maint_query_logs_archive ();Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 26 00 1 9m 9m -
/* * 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-08-26 00:09:01 Duration: 9m
10 4m27s 1 4m27s 4m27s 4m27s 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 Aug 26 17 1 4m27s 4m27s -
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-08-26 17:11:44 Duration: 4m27s Bind query: yes
11 3m21s 1 3m21s 3m21s 3m21s create unique index gene_disease_reference_ak1 on pub2.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 26 20 1 3m21s 3m21s -
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-08-26 20:38:52 Duration: 3m21s 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-08-26 20:38:52 Duration: 0ms
12 3m18s 1 3m18s 3m18s 3m18s insert into pub2.term_label (id, object_type_id, term_id, term_label_type_id, nm) select l.id, t.object_type_id, l.term_id, l.term_label_type_id, l.nm from load.term t, load.term_label l where t.id = l.term_id and t.id in ( select id from pub2.term);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 26 16 1 3m18s 3m18s -
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-08-26 16:40:30 Duration: 3m18s Bind query: yes
13 3m12s 27 6s856ms 8s167ms 7s133ms 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 #13
Day Hour Count Duration Avg duration Aug 26 00 1 7s169ms 7s169ms 01 1 7s7ms 7s7ms 02 1 7s24ms 7s24ms 03 2 14s85ms 7s42ms 04 1 7s4ms 7s4ms 05 3 20s961ms 6s987ms 06 1 6s960ms 6s960ms 07 1 7s102ms 7s102ms 08 1 7s126ms 7s126ms 09 1 7s63ms 7s63ms 10 1 6s962ms 6s962ms 11 1 6s856ms 6s856ms 12 1 7s76ms 7s76ms 13 1 7s105ms 7s105ms 14 1 7s70ms 7s70ms 15 1 7s87ms 7s87ms 16 1 7s110ms 7s110ms 17 1 6s977ms 6s977ms 18 1 7s190ms 7s190ms 19 1 7s310ms 7s310ms 20 1 7s96ms 7s96ms 21 1 7s989ms 7s989ms 22 1 8s167ms 8s167ms 23 1 7s105ms 7s105ms [ User: pubeu - Total duration: 2m57s - Times executed: 25 ]
[ User: qaeu - Total duration: 6s984ms - 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-08-26 22:03:12 Duration: 8s167ms 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-08-26 21:03:00 Duration: 7s989ms 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-08-26 19:02:59 Duration: 7s310ms Database: ctdprd51 User: pubeu Bind query: yes
14 3m12s 1 3m12s 3m12s 3m12s alter table pub2.term_enrichment_agent add constraint term_enrichment_agent_pk primary key (term_id, enriched_term_id, agent_term_id);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Aug 26 23 1 3m12s 3m12s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2025-08-26 23:11:25 Duration: 3m12s Bind query: yes
-
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2025-08-26 23:11:24 Duration: 0ms
15 3m5s 26 6s931ms 7s497ms 7s119ms 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 #15
Day Hour Count Duration Avg duration Aug 26 00 1 7s46ms 7s46ms 01 1 7s242ms 7s242ms 02 1 7s139ms 7s139ms 03 1 7s55ms 7s55ms 04 1 6s967ms 6s967ms 05 3 21s259ms 7s86ms 06 1 7s227ms 7s227ms 07 1 7s81ms 7s81ms 08 1 7s48ms 7s48ms 09 1 6s938ms 6s938ms 10 1 6s931ms 6s931ms 11 1 7s151ms 7s151ms 12 1 7s52ms 7s52ms 13 1 7s138ms 7s138ms 14 1 7s169ms 7s169ms 15 1 7s274ms 7s274ms 16 1 7s111ms 7s111ms 17 1 6s956ms 6s956ms 18 1 7s60ms 7s60ms 19 1 7s480ms 7s480ms 20 1 6s939ms 6s939ms 21 1 7s497ms 7s497ms 22 1 7s49ms 7s49ms 23 1 7s279ms 7s279ms [ User: pubeu - Total duration: 7s49ms - Times executed: 1 ]
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-08-26 21:03:08 Duration: 7s497ms 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-08-26 19:03:07 Duration: 7s480ms 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-08-26 23:03:05 Duration: 7s279ms Bind query: yes
16 2m36s 1 2m36s 2m36s 2m36s create index ix_gene_disease_ref_net_sc on pub2.gene_disease_reference using btree (network_score);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Aug 26 20 1 2m36s 2m36s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-08-26 20:52:47 Duration: 2m36s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-08-26 20:52:47 Duration: 0ms
17 2m2s 1 2m2s 2m2s 2m2s 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 #17
Day Hour Count Duration Avg duration Aug 26 20 1 2m2s 2m2s -
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-08-26 20:22:31 Duration: 2m2s Bind query: yes
18 1m58s 1 1m58s 1m58s 1m58s vacuum full analyze db_link;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 26 16 1 1m58s 1m58s -
vacuum FULL analyze db_link;
Date: 2025-08-26 16:04:24 Duration: 1m58s
-
vacuum FULL analyze db_link;
Date: 2025-08-26 16:02:48 Duration: 0ms
19 1m57s 1 1m57s 1m57s 1m57s 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 Aug 26 20 1 1m57s 1m57s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-08-26 20:47:13 Duration: 1m57s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-08-26 20:47:13 Duration: 0ms
20 1m57s 1 1m57s 1m57s 1m57s insert into pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, secondary_nm, description, note, is_leaf, nm_html, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_marrays, nm_fts) select t.id, t.object_type_id, t.acc_txt, get_db_cd (t.acc_db_id) as db_cd, t.nm, common.search_str (t.nm_sort), t.secondary_nm, t.description, t.note, is_leaf, break_html_words (t.nm), ?, ?, ?, ?, ?, ?, ? from load.term t where object_type_id not in (...);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 26 16 1 1m57s 1m57s -
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-08-26 16:37:12 Duration: 1m57s Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 60 54m1s 5s483ms 7m18s 54s33ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 26 14 14 6m45s 28s954ms 15 24 22m17s 55s736ms 17 3 1m6s 22s316ms 20 4 8m47s 2m11s 21 3 6m59s 2m19s 22 11 7m44s 42s191ms 23 1 20s815ms 20s815ms [ User: load - Total duration: 15m15s - Times executed: 16 ]
[ Application: pg_bulkload - Total duration: 15m15s - Times executed: 16 ]
-
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-08-26 20:32:46 Duration: 7m18s 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-08-26 21:04:39 Duration: 5m16s 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-08-26 14:58:44 Duration: 4m22s Bind query: yes
2 27 3m12s 6s856ms 8s167ms 7s133ms 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 #2
Day Hour Count Duration Avg duration Aug 26 00 1 7s169ms 7s169ms 01 1 7s7ms 7s7ms 02 1 7s24ms 7s24ms 03 2 14s85ms 7s42ms 04 1 7s4ms 7s4ms 05 3 20s961ms 6s987ms 06 1 6s960ms 6s960ms 07 1 7s102ms 7s102ms 08 1 7s126ms 7s126ms 09 1 7s63ms 7s63ms 10 1 6s962ms 6s962ms 11 1 6s856ms 6s856ms 12 1 7s76ms 7s76ms 13 1 7s105ms 7s105ms 14 1 7s70ms 7s70ms 15 1 7s87ms 7s87ms 16 1 7s110ms 7s110ms 17 1 6s977ms 6s977ms 18 1 7s190ms 7s190ms 19 1 7s310ms 7s310ms 20 1 7s96ms 7s96ms 21 1 7s989ms 7s989ms 22 1 8s167ms 8s167ms 23 1 7s105ms 7s105ms [ User: pubeu - Total duration: 2m57s - Times executed: 25 ]
[ User: qaeu - Total duration: 6s984ms - 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-08-26 22:03:12 Duration: 8s167ms 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-08-26 21:03:00 Duration: 7s989ms 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-08-26 19:02:59 Duration: 7s310ms Database: ctdprd51 User: pubeu Bind query: yes
3 26 3m5s 6s931ms 7s497ms 7s119ms 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 #3
Day Hour Count Duration Avg duration Aug 26 00 1 7s46ms 7s46ms 01 1 7s242ms 7s242ms 02 1 7s139ms 7s139ms 03 1 7s55ms 7s55ms 04 1 6s967ms 6s967ms 05 3 21s259ms 7s86ms 06 1 7s227ms 7s227ms 07 1 7s81ms 7s81ms 08 1 7s48ms 7s48ms 09 1 6s938ms 6s938ms 10 1 6s931ms 6s931ms 11 1 7s151ms 7s151ms 12 1 7s52ms 7s52ms 13 1 7s138ms 7s138ms 14 1 7s169ms 7s169ms 15 1 7s274ms 7s274ms 16 1 7s111ms 7s111ms 17 1 6s956ms 6s956ms 18 1 7s60ms 7s60ms 19 1 7s480ms 7s480ms 20 1 6s939ms 6s939ms 21 1 7s497ms 7s497ms 22 1 7s49ms 7s49ms 23 1 7s279ms 7s279ms [ User: pubeu - Total duration: 7s49ms - Times executed: 1 ]
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-08-26 21:03:08 Duration: 7s497ms 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-08-26 19:03:07 Duration: 7s480ms 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-08-26 23:03:05 Duration: 7s279ms Bind query: yes
4 10 52s151ms 5s24ms 5s732ms 5s215ms 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 #4
Day Hour Count Duration Avg duration Aug 26 20 2 10s183ms 5s91ms 22 2 10s104ms 5s52ms 23 6 31s863ms 5s310ms [ User: pubeu - Total duration: 46s779ms - Times executed: 9 ]
-
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 = '1306384') 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 = '1306384') 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-08-26 23:08:48 Duration: 5s732ms 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 = '1321214') 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 = '1321214') 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-08-26 23:27:01 Duration: 5s372ms 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 = '1422452') 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 = '1422452') 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-08-26 23:32:47 Duration: 5s352ms Database: ctdprd51 User: pubeu Bind query: yes
5 9 46s458ms 5s21ms 5s273ms 5s162ms 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 #5
Day Hour Count Duration Avg duration Aug 26 02 1 5s21ms 5s21ms 07 1 5s87ms 5s87ms 12 1 5s120ms 5s120ms 16 1 5s192ms 5s192ms 19 1 5s256ms 5s256ms 20 1 5s234ms 5s234ms 21 1 5s273ms 5s273ms 22 1 5s33ms 5s33ms 23 1 5s238ms 5s238ms [ User: pubeu - Total duration: 41s424ms - Times executed: 8 ]
-
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 = 1319292)) 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-08-26 21:02:33 Duration: 5s273ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1319292)) 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-08-26 19:02:34 Duration: 5s256ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1319292)) 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-08-26 23:02:33 Duration: 5s238ms Database: ctdprd51 User: pubeu Bind query: yes
6 6 33s609ms 5s36ms 6s716ms 5s601ms 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 #6
Day Hour Count Duration Avg duration Aug 26 09 1 5s772ms 5s772ms 13 3 16s83ms 5s361ms 14 1 5s36ms 5s36ms 21 1 6s716ms 6s716ms [ User: pubeu - Total duration: 33s609ms - Times executed: 6 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1371691' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-08-26 21:44:26 Duration: 6s716ms 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 = '1460250' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-08-26 09:47:14 Duration: 5s772ms 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 = '1300004' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-08-26 13:48:03 Duration: 5s382ms Database: ctdprd51 User: pubeu Bind query: yes
7 5 12m45s 38s941ms 10m7s 2m33s 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 #7
Day Hour Count Duration Avg duration Aug 26 18 5 12m45s 2m33s [ User: load - Total duration: 10m7s - 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-08-26 18:02:38 Duration: 10m7s 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-08-26 18:22:34 Duration: 40s38ms 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-08-26 18:33:26 Duration: 39s522ms Bind query: yes
8 3 27s232ms 8s982ms 9s142ms 9s77ms select ii.cd, count(ii.id) cnt from ( select ot.cd, tl.term_id id from object_type ot inner join term_label tl on ot.id = tl.object_type_id where tl.nm_fts @@ to_tsquery(?, ?) union select ?, r.id from reference r where r.title_abstract_fts @@ to_tsquery(?, ?) or r.id in ( select rpr.reference_id from reference_party_role rpr inner join reference_party rp on rpr.reference_party_id = rp.id where (substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ?)) union select ot.cd, l.object_id from db_link l inner join object_type ot on l.object_type_id = ot.id where l.type_cd = ? and (upper(l.acc_txt) like ? or upper(l.acc_txt) like ?)) ii group by ii.cd;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Aug 26 03 1 9s142ms 9s142ms 08 2 18s89ms 9s44ms [ User: pubeu - Total duration: 27s232ms - Times executed: 3 ]
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'PORIFERASTEROL & MONOGLUCOSIDE_QT') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'PORIFERASTEROL & MONOGLUCOSIDE_QT') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'PORIFERASTEROL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'MONOGLUCOSIDE_QT')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'PORIFERASTEROL' OR upper(l.acc_txt) LIKE 'MONOGLUCOSIDE_QT')) ii GROUP BY ii.cd;
Date: 2025-08-26 03:47:35 Duration: 9s142ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'JUJUBOSIDE & A_QT') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'JUJUBOSIDE & A_QT') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'JUJUBOSIDE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'A_QT')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'JUJUBOSIDE' OR upper(l.acc_txt) LIKE 'A_QT')) ii GROUP BY ii.cd;
Date: 2025-08-26 08:23:21 Duration: 9s107ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'JUJUBOSIDE & A_QT') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'JUJUBOSIDE & A_QT') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'JUJUBOSIDE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'A_QT')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'JUJUBOSIDE' OR upper(l.acc_txt) LIKE 'A_QT')) ii GROUP BY ii.cd;
Date: 2025-08-26 08:23:26 Duration: 8s982ms Database: ctdprd51 User: pubeu Bind query: yes
9 2 1m2s 31s129ms 31s439ms 31s284ms 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 #9
Day Hour Count Duration Avg duration Aug 26 15 2 1m2s 31s284ms [ User: load - Total duration: 1m2s - Times executed: 2 ]
-
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-08-26 15:43:28 Duration: 31s439ms 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-08-26 15:37:03 Duration: 31s129ms Database: ctdprd51 User: load Bind query: yes
10 2 14s951ms 7s448ms 7s503ms 7s475ms 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 baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.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 #10
Day Hour Count Duration Avg duration Aug 26 05 2 14s951ms 7s475ms [ User: pubeu - Total duration: 14s951ms - Times executed: 2 ]
-
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 baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'MOUPINAMIDE' AND tl.object_type_id = 2))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases', 'decreases', 'affects')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-08-26 05:19:45 Duration: 7s503ms 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 baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'MOUPINAMIDE' AND tl.object_type_id = 2))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases', 'decreases', 'affects')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-08-26 05:19:15 Duration: 7s448ms Database: ctdprd51 User: pubeu Bind query: yes
11 2 10s215ms 5s101ms 5s114ms 5s107ms 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 desc, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 26 09 2 10s215ms 5s107ms [ User: pubeu - Total duration: 10s215ms - Times executed: 2 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1460250' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val DESC, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-08-26 09:49:02 Duration: 5s114ms 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 = '1460250' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val DESC, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-08-26 09:49:00 Duration: 5s101ms Database: ctdprd51 User: pubeu Bind query: yes
12 1 50m27s 50m27s 50m27s 50m27s 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 #12
Day Hour Count Duration Avg duration Aug 26 20 1 50m27s 50m27s -
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-08-26 20:20:22 Duration: 50m27s Bind query: yes
13 1 42m47s 42m47s 42m47s 42m47s 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 #13
Day Hour Count Duration Avg duration Aug 26 19 1 42m47s 42m47s -
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-08-26 19:29:48 Duration: 42m47s Bind query: yes
14 1 33m51s 33m51s 33m51s 33m51s 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 #14
Day Hour Count Duration Avg duration Aug 26 17 1 33m51s 33m51s [ User: load - Total duration: 33m51s - 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-08-26 17:49:39 Duration: 33m51s Database: ctdprd51 User: load Bind query: yes
15 1 26m46s 26m46s 26m46s 26m46s 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 #15
Day Hour Count Duration Avg duration Aug 26 17 1 26m46s 26m46s -
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-08-26 17:07:17 Duration: 26m46s Bind query: yes
16 1 14m33s 14m33s 14m33s 14m33s 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 #16
Day Hour Count Duration Avg duration Aug 26 16 1 14m33s 14m33s [ User: pub2 - Total duration: 14m33s - 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-08-26 16:35:03 Duration: 14m33s Database: ctdprd51 User: pub2 Bind query: yes
17 1 14m17s 14m17s 14m17s 14m17s alter table pub2.term_enrichment_agent add constraint term_enr_agent_term_enr_fk foreign key (term_id, enriched_term_id) references term_enrichment (term_id, enriched_term_id);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Aug 26 23 1 14m17s 14m17s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enr_agent_term_enr_fk FOREIGN KEY (term_id, enriched_term_id) REFERENCES term_enrichment (term_id, enriched_term_id);
Date: 2025-08-26 23:08:13 Duration: 14m17s Bind query: yes
18 1 9m 9m 9m 9m select maint_query_logs_archive ();Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 26 00 1 9m 9m -
/* * 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-08-26 00:09:01 Duration: 9m
19 1 4m27s 4m27s 4m27s 4m27s 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 #19
Day Hour Count Duration Avg duration Aug 26 17 1 4m27s 4m27s -
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-08-26 17:11:44 Duration: 4m27s Bind query: yes
20 1 3m21s 3m21s 3m21s 3m21s 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 #20
Day Hour Count Duration Avg duration Aug 26 20 1 3m21s 3m21s -
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-08-26 20:38:52 Duration: 3m21s 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-08-26 20:38:52 Duration: 0ms
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 50m27s 50m27s 50m27s 1 50m27s select distinct gga.go_term_id, gcr.chem_id, ptr.term_object_type_id, gga.gene_id, ( select id from pub2.object_type where cd = ?), ( select current_date) from pub2.gene_chem_reference gcr, pub2.gene_go_annot gga, pub2.phenotype_term_reference ptr where gcr.gene_id = gga.gene_id and gcr.chem_id = ptr.term_id and gga.go_term_id = ptr.phenotype_id and gcr.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 26 20 1 50m27s 50m27s -
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-08-26 20:20:22 Duration: 50m27s Bind query: yes
2 42m47s 42m47s 42m47s 1 42m47s 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 Aug 26 19 1 42m47s 42m47s -
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-08-26 19:29:48 Duration: 42m47s Bind query: yes
3 33m51s 33m51s 33m51s 1 33m51s 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 Aug 26 17 1 33m51s 33m51s [ User: load - Total duration: 33m51s - 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-08-26 17:49:39 Duration: 33m51s Database: ctdprd51 User: load Bind query: yes
4 26m46s 26m46s 26m46s 1 26m46s 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 Aug 26 17 1 26m46s 26m46s -
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-08-26 17:07:17 Duration: 26m46s Bind query: yes
5 14m33s 14m33s 14m33s 1 14m33s 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 Aug 26 16 1 14m33s 14m33s [ User: pub2 - Total duration: 14m33s - 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-08-26 16:35:03 Duration: 14m33s Database: ctdprd51 User: pub2 Bind query: yes
6 14m17s 14m17s 14m17s 1 14m17s alter table pub2.term_enrichment_agent add constraint term_enr_agent_term_enr_fk foreign key (term_id, enriched_term_id) references term_enrichment (term_id, enriched_term_id);Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 26 23 1 14m17s 14m17s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enr_agent_term_enr_fk FOREIGN KEY (term_id, enriched_term_id) REFERENCES term_enrichment (term_id, enriched_term_id);
Date: 2025-08-26 23:08:13 Duration: 14m17s Bind query: yes
7 9m 9m 9m 1 9m select maint_query_logs_archive ();Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Aug 26 00 1 9m 9m -
/* * 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-08-26 00:09:01 Duration: 9m
8 4m27s 4m27s 4m27s 1 4m27s 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 #8
Day Hour Count Duration Avg duration Aug 26 17 1 4m27s 4m27s -
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-08-26 17:11:44 Duration: 4m27s Bind query: yes
9 3m21s 3m21s 3m21s 1 3m21s create unique index gene_disease_reference_ak1 on pub2.gene_disease_reference using btree (gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 26 20 1 3m21s 3m21s -
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-08-26 20:38:52 Duration: 3m21s 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-08-26 20:38:52 Duration: 0ms
10 3m18s 3m18s 3m18s 1 3m18s 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 Aug 26 16 1 3m18s 3m18s -
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-08-26 16:40:30 Duration: 3m18s Bind query: yes
11 3m12s 3m12s 3m12s 1 3m12s alter table pub2.term_enrichment_agent add constraint term_enrichment_agent_pk primary key (term_id, enriched_term_id, agent_term_id);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 26 23 1 3m12s 3m12s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2025-08-26 23:11:25 Duration: 3m12s Bind query: yes
-
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2025-08-26 23:11:24 Duration: 0ms
12 2m36s 2m36s 2m36s 1 2m36s 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 Aug 26 20 1 2m36s 2m36s -
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-08-26 20:52:47 Duration: 2m36s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_net_sc ON pub2.gene_disease_reference USING btree (network_score);
Date: 2025-08-26 20:52:47 Duration: 0ms
13 38s941ms 10m7s 2m33s 5 12m45s select distinct gcr.gene_id, cdr.disease_id, cdr.reference_id, cdr.chem_id as via_chem_id, cdr.ixn_id, to_char(cdr.mod_tm, ?) 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 #13
Day Hour Count Duration Avg duration Aug 26 18 5 12m45s 2m33s [ User: load - Total duration: 10m7s - 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-08-26 18:02:38 Duration: 10m7s 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-08-26 18:22:34 Duration: 40s38ms 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-08-26 18:33:26 Duration: 39s522ms Bind query: yes
14 2m2s 2m2s 2m2s 1 2m2s select distinct ptr.phenotype_id, cdr.disease_id, ( select id from pub2.object_type where cd = ?), cdr.reference_id, ptr.reference_id, cdr.ixn_id, cdr.chem_id, ( select id from pub2.object_type where cd = ?), cdr.mod_tm from pub2.chem_disease_reference cdr, pub2.phenotype_term_reference ptr where cdr.chem_id = ptr.term_id and ptr.source_cd = ? and cdr.source_cd = ? and ptr.ixn_id not in ( select ixn_id from pub2.ixn_axn where action_degree_type_nm = ?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Aug 26 20 1 2m2s 2m2s -
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-08-26 20:22:31 Duration: 2m2s Bind query: yes
15 1m58s 1m58s 1m58s 1 1m58s vacuum full analyze db_link;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Aug 26 16 1 1m58s 1m58s -
vacuum FULL analyze db_link;
Date: 2025-08-26 16:04:24 Duration: 1m58s
-
vacuum FULL analyze db_link;
Date: 2025-08-26 16:02:48 Duration: 0ms
16 1m57s 1m57s 1m57s 1 1m57s 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 Aug 26 20 1 1m57s 1m57s -
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-08-26 20:47:13 Duration: 1m57s Bind query: yes
-
CREATE INDEX ix_gene_disease_ref_dis_gene ON pub2.gene_disease_reference USING btree (disease_id, gene_id);
Date: 2025-08-26 20:47:13 Duration: 0ms
17 1m57s 1m57s 1m57s 1 1m57s 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 #17
Day Hour Count Duration Avg duration Aug 26 16 1 1m57s 1m57s -
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-08-26 16:37:12 Duration: 1m57s Bind query: yes
18 5s483ms 7m18s 54s33ms 60 54m1s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 26 14 14 6m45s 28s954ms 15 24 22m17s 55s736ms 17 3 1m6s 22s316ms 20 4 8m47s 2m11s 21 3 6m59s 2m19s 22 11 7m44s 42s191ms 23 1 20s815ms 20s815ms [ User: load - Total duration: 15m15s - Times executed: 16 ]
[ Application: pg_bulkload - Total duration: 15m15s - Times executed: 16 ]
-
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-08-26 20:32:46 Duration: 7m18s 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-08-26 21:04:39 Duration: 5m16s 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-08-26 14:58:44 Duration: 4m22s Bind query: yes
19 31s129ms 31s439ms 31s284ms 2 1m2s 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 Aug 26 15 2 1m2s 31s284ms [ User: load - Total duration: 1m2s - Times executed: 2 ]
-
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-08-26 15:43:28 Duration: 31s439ms 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-08-26 15:37:03 Duration: 31s129ms Database: ctdprd51 User: load Bind query: yes
20 8s982ms 9s142ms 9s77ms 3 27s232ms select ii.cd, count(ii.id) cnt from ( select ot.cd, tl.term_id id from object_type ot inner join term_label tl on ot.id = tl.object_type_id where tl.nm_fts @@ to_tsquery(?, ?) union select ?, r.id from reference r where r.title_abstract_fts @@ to_tsquery(?, ?) or r.id in ( select rpr.reference_id from reference_party_role rpr inner join reference_party rp on rpr.reference_party_id = rp.id where (substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ?)) union select ot.cd, l.object_id from db_link l inner join object_type ot on l.object_type_id = ot.id where l.type_cd = ? and (upper(l.acc_txt) like ? or upper(l.acc_txt) like ?)) ii group by ii.cd;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 26 03 1 9s142ms 9s142ms 08 2 18s89ms 9s44ms [ User: pubeu - Total duration: 27s232ms - Times executed: 3 ]
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'PORIFERASTEROL & MONOGLUCOSIDE_QT') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'PORIFERASTEROL & MONOGLUCOSIDE_QT') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'PORIFERASTEROL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'MONOGLUCOSIDE_QT')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'PORIFERASTEROL' OR upper(l.acc_txt) LIKE 'MONOGLUCOSIDE_QT')) ii GROUP BY ii.cd;
Date: 2025-08-26 03:47:35 Duration: 9s142ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'JUJUBOSIDE & A_QT') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'JUJUBOSIDE & A_QT') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'JUJUBOSIDE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'A_QT')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'JUJUBOSIDE' OR upper(l.acc_txt) LIKE 'A_QT')) ii GROUP BY ii.cd;
Date: 2025-08-26 08:23:21 Duration: 9s107ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'JUJUBOSIDE & A_QT') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'JUJUBOSIDE & A_QT') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'JUJUBOSIDE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'A_QT')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'JUJUBOSIDE' OR upper(l.acc_txt) LIKE 'A_QT')) ii GROUP BY ii.cd;
Date: 2025-08-26 08:23:26 Duration: 8s982ms Database: ctdprd51 User: pubeu Bind query: yes
Time consuming prepare
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
Time consuming bind
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
-
Events
Log levels
Key values
- 16,148 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 0 FATAL entries
- 13 ERROR entries
- 0 WARNING entries
- 3 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 6 Max number of times the same event was reported
- 16 Total events found
Rank Times reported Error 1 6 ERROR: invalid byte sequence for encoding
Times Reported Most Frequent Error / Event #1
Day Hour Count Aug 26 12 6 - ERROR: invalid byte sequence for encoding "UTF8": 0x00
Context: unnamed portal parameter $1
Statement: SELECT /* ObjectIdDAOImpl.LabelsAndAccs */ t.id ,t.nm ,t.nm_sort nmSort ,t.acc_txt acc ,t.acc_db_cd accDbCd FROM term t ,(SELECT li.term_id FROM term_label li WHERE UPPER(li.nm) = $1 AND li.object_type_id = 2 UNION SELECT l.object_id FROM db_link l WHERE upper( l.acc_txt ) = $2 AND l.object_type_id = 2 AND l.type_cd = 'A') ids WHERE t.id = ids.term_id ORDER BY CASE WHEN UPPER(t.nm) = $3 THEN 1 ELSE 2 END ,t.nm_sortDate: 2025-08-26 12:10:49 Database: ctdprd51 Application: User: pubeu Remote:
2 3 LOG: could not receive data from client: Connection timed out
Times Reported Most Frequent Error / Event #2
Day Hour Count Aug 26 20 3 3 2 ERROR: relation "..." does not exist
Times Reported Most Frequent Error / Event #3
Day Hour Count Aug 26 16 2 - ERROR: relation "ixn_actor" does not exist
- ERROR: relation "db_link" does not exist at character 15
Statement: vacuum FULL analyze ixn_actor
Date: 2025-08-26 16:01:15
Statement: select * from db_link limit 100
Date: 2025-08-26 16:08:07
4 1 ERROR: function count(...) does not exist
Times Reported Most Frequent Error / Event #4
Day Hour Count Aug 26 16 1 - ERROR: function count(character varying, character varying) does not exist at character 8
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Statement: select count( distinct t.nm, tl.nm ) from pub1.term_label tl ,pub1.term t where ( t.nm, tl.nm ) not in ( select lt.nm, ltl.nm from load.term lt ,load.term_label ltl where lt.id = ltl.term_id ) and t.id = tl.term_id limit 100Date: 2025-08-26 16:13:59
5 1 ERROR: function get_ixn_prose(...) does not exist
Times Reported Most Frequent Error / Event #5
Day Hour Count Aug 26 14 1 - ERROR: function get_ixn_prose(integer) does not exist at character 66
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Statement: select reference_acc_txt ,taxon_acc_txt ,pubTerm.nm ,get_ixn_prose( ixn_id ) ,create_by ,create_tm from edit.reference_ixn ri ,pub1.term pubTerm -- set to CURRENT PRODUCTION PUB!!!!! where taxon_acc_txt not in ( select acc_txt from load.term where object_type_id = ( select id from edit.object_type where cd = 'taxon' ) ) and pubTerm.acc_txt = ri.taxon_acc_txt and object_type_id = ( select id from edit.object_type where cd = 'taxon' ) and taxon_acc_txt is not null and taxon_acc_txt <> ''Date: 2025-08-26 14:35:37 Database: ctdprd51 Application: pgAdmin 4 - CONN:3657194 User: load Remote:
6 1 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #6
Day Hour Count Aug 26 16 1 - ERROR: syntax error at or near "and" at character 168
Statement: select distinct t.nm, tl.nm from pub1.term_label tl ,pub1.term t where ( t.nm, tl.nm ) not in ( select lt.nm from load.term lt ,load.term_label ltl and lt.id = ltl.term_id ) and t.id = tl.term_id limit 100
Date: 2025-08-26 16:12:06
7 1 ERROR: subquery has too few columns
Times Reported Most Frequent Error / Event #7
Day Hour Count Aug 26 16 1 - ERROR: subquery has too few columns at character 92
Statement: select distinct t.nm, tl.nm from pub1.term_label tl ,pub1.term t where ( t.nm, tl.nm ) not in ( select lt.nm from load.term lt ,load.term_label ltl where lt.id = ltl.term_id ) and t.id = tl.term_id limit 100
Date: 2025-08-26 16:12:13
8 1 ERROR: column "..." does not exist
Times Reported Most Frequent Error / Event #8
Day Hour Count Aug 26 16 1 - ERROR: column "acc_txt" does not exist at character 32
Statement: select * from term_label where acc_txt = '461054-93-3'
Date: 2025-08-26 16:16:54 Database: ctdprd51 Application: pgAdmin 4 - CONN:1161282 User: pub1 Remote: