pgBadger-
Global information
- Generated on Sat Aug 29 04:15:04 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260828
- Parsed 25,612 log entries in 3s
- Log start from 2026-08-28 00:00:01 to 2026-08-28 23:59:17
-
Overview
Global Stats
- 125 Number of unique normalized queries
- 215 Number of queries
- 11h54m38s Total query duration
- 2026-08-28 00:09:29 First query
- 2026-08-28 20:58:58 Last query
- 2 queries/s at 2026-08-28 10:38:40 Query peak
- 11h54m38s Total query duration
- 12s604ms Prepare/parse total duration
- 0ms Bind total duration
- 11h54m26s Execute total duration
- 1,382 Number of events
- 11 Number of unique normalized events
- 1,069 Max number of times the same event was reported
- 0 Number of cancellation
- 43 Total number of automatic vacuums
- 58 Total number of automatic analyzes
- 1,312 Number temporary file
- 47.30 GiB Max size of temporary file
- 210.38 MiB Average size of temporary file
- 2,050 Total number of sessions
- 167 sessions at 2026-08-28 01:13:50 Session peak
- 58d6h50m27s Total duration of sessions
- 40m56s Average duration of sessions
- 0 Average queries per session
- 20s916ms Average queries duration per session
- 40m35s Average idle time per session
- 2,042 Total number of connections
- 10 connections/s at 2026-08-28 09:50:10 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 2 queries/s Query Peak
- 2026-08-28 10:38:40 Date
SELECT Traffic
Key values
- 2 queries/s Query Peak
- 2026-08-28 10:38:40 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-08-28 14:00:32 Date
Queries duration
Key values
- 11h54m38s 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 28 00 56 0ms 13m19s 47s918ms 1m22s 3m28s 13m19s 01 25 0ms 27m19s 1m38s 1m29s 2m22s 27m19s 02 1 0ms 17s896ms 17s896ms 0ms 0ms 17s896ms 03 3 0ms 1h58m38s 40m33s 0ms 0ms 1h58m38s 04 4 0ms 10s643ms 6s496ms 0ms 5s56ms 10s643ms 05 6 0ms 40s735ms 13s55ms 6s616ms 7s708ms 40s735ms 06 20 0ms 2h47m20s 8m54s 1m11s 1m53s 2h47m30s 07 6 0ms 58m58s 9m58s 0ms 0ms 59m21s 08 5 0ms 5s830ms 5s470ms 0ms 5s198ms 22s155ms 09 0 0ms 0ms 0ms 0ms 0ms 0ms 10 20 0ms 2h26m30s 8m4s 49s87ms 1m52s 2h26m51s 11 1 0ms 39m36s 39m36s 0ms 0ms 39m36s 12 9 0ms 19s405ms 10s325ms 0ms 19s537ms 37s705ms 13 15 0ms 1m29s 23s248ms 26s295ms 55s931ms 1m29s 14 17 0ms 40m25s 2m40s 39s759ms 1m53s 40m25s 15 13 0ms 2m37s 31s166ms 21s216ms 2m10s 2m45s 16 4 0ms 1m24s 25s293ms 0ms 16s967ms 1m24s 17 0 0ms 0ms 0ms 0ms 0ms 0ms 18 9 0ms 1m55s 25s399ms 0ms 40s606ms 1m55s 19 0 0ms 0ms 0ms 0ms 0ms 0ms 20 1 0ms 7s189ms 7s189ms 0ms 0ms 7s189ms 21 0 0ms 0ms 0ms 0ms 0ms 0ms 22 0 0ms 0ms 0ms 0ms 0ms 0ms 23 0 0ms 0ms 0ms 0ms 0ms 0ms Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 28 00 51 0 28s635ms 52s593ms 1m3s 3m 01 2 0 13s837ms 0ms 0ms 27s674ms 02 1 0 17s896ms 0ms 0ms 17s896ms 03 1 0 1h58m38s 0ms 0ms 1h58m38s 04 4 0 6s496ms 0ms 0ms 10s643ms 05 6 0 13s55ms 0ms 6s616ms 40s735ms 06 2 9 15m51s 0ms 39s985ms 2h47m20s 07 2 0 15s601ms 0ms 0ms 0ms 08 5 0 5s470ms 0ms 0ms 22s155ms 09 0 0 0ms 0ms 0ms 0ms 10 11 9 8m4s 5s509ms 49s87ms 2h26m51s 11 1 0 39m36s 0ms 0ms 39m36s 12 9 0 10s325ms 0ms 0ms 37s705ms 13 14 0 24s545ms 6s322ms 26s295ms 1m29s 14 8 9 2m40s 11s621ms 39s759ms 40m25s 15 13 0 31s166ms 5s858ms 21s216ms 2m45s 16 3 0 5s655ms 0ms 0ms 16s967ms 17 0 0 0ms 0ms 0ms 0ms 18 0 9 25s399ms 0ms 0ms 1m55s 19 0 0 0ms 0ms 0ms 0ms 20 1 0 7s189ms 0ms 0ms 7s189ms 21 0 0 0ms 0ms 0ms 0ms 22 0 0 0ms 0ms 0ms 0ms 23 0 0 0ms 0ms 0ms 0ms Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 28 00 0 0 0 0 0ms 0ms 0ms 0ms 01 9 9 0 0 2m11s 0ms 29s987ms 2m32s 02 0 0 0 0 0ms 0ms 0ms 0ms 03 1 0 0 0 2m53s 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 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 0 0 0 0 0ms 0ms 0ms 0ms 17 0 0 0 0 0ms 0ms 0ms 0ms 18 0 0 0 0 0ms 0ms 0ms 0ms 19 0 0 0 0 0ms 0ms 0ms 0ms 20 0 0 0 0 0ms 0ms 0ms 0ms 21 0 0 0 0 0ms 0ms 0ms 0ms 22 0 0 0 0 0ms 0ms 0ms 0ms 23 0 0 0 0 0ms 0ms 0ms 0ms Day Hour Prepare Bind Bind/Prepare Percentage of prepare Aug 28 00 0 54 54.00 0.00% 01 0 25 25.00 0.00% 02 0 1 1.00 0.00% 03 0 3 3.00 0.00% 04 0 4 4.00 0.00% 05 0 6 6.00 0.00% 06 0 11 11.00 0.00% 07 0 6 6.00 0.00% 08 0 5 5.00 0.00% 09 0 0 0.00 0.00% 10 0 11 11.00 0.00% 11 0 1 1.00 0.00% 12 0 0 0.00 0.00% 13 0 15 15.00 0.00% 14 0 7 7.00 0.00% 15 0 13 13.00 0.00% 16 1 3 3.00 33.33% 17 0 0 0.00 0.00% 18 0 0 0.00 0.00% 19 0 0 0.00 0.00% 20 0 1 1.00 0.00% 21 0 0 0.00 0.00% 22 0 0 0.00 0.00% 23 0 0 0.00 0.00% Day Hour Count Average / Second Aug 28 00 120 0.03/s 01 87 0.02/s 02 80 0.02/s 03 82 0.02/s 04 84 0.02/s 05 99 0.03/s 06 77 0.02/s 07 72 0.02/s 08 76 0.02/s 09 117 0.03/s 10 76 0.02/s 11 68 0.02/s 12 82 0.02/s 13 124 0.03/s 14 77 0.02/s 15 92 0.03/s 16 83 0.02/s 17 72 0.02/s 18 78 0.02/s 19 80 0.02/s 20 76 0.02/s 21 78 0.02/s 22 86 0.02/s 23 76 0.02/s Day Hour Count Average Duration Average idle time Aug 28 00 120 22m21s 21m59s 01 87 27m49s 27m21s 02 80 28m49s 28m49s 03 82 28m52s 27m23s 04 84 29m12s 29m12s 05 99 25m3s 25m2s 06 77 30m26s 28m7s 07 72 32m36s 31m47s 08 76 33m7s 33m7s 09 117 19m50s 19m50s 10 76 31m28s 29m21s 11 69 40m19s 39m44s 12 82 29m47s 29m46s 13 123 22m32s 22m29s 14 77 30m35s 30m 15 94 1h28m8s 1h28m4s 16 81 1h40m32s 1h40m31s 17 73 1h53m24s 1h53m24s 18 81 1h36m57s 1h36m55s 19 84 56m2s 56m2s 20 76 31m10s 31m10s 21 78 31m34s 31m34s 22 86 28m48s 28m48s 23 76 31m3s 31m3s -
Connections
Established Connections
Key values
- 10 connections Connection Peak
- 2026-08-28 09:50:10 Date
Connections per database
Key values
- ctdprd51 Main Database
- 2,042 connections Total
Connections per user
Key values
- pubeu Main User
- 2,042 connections Total
-
Sessions
Simultaneous sessions
Key values
- 167 sessions Session Peak
- 2026-08-28 01:13:50 Date
Histogram of session times
Key values
- 1,745 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,050 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,050 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 2,050 sessions Total
Host Count Total Duration Average Duration 10.12.5.45 383 7d23h42m21s 30m1s 10.12.5.46 378 7d23h1m32s 30m19s 10.12.5.52 26 2h19m50s 5m22s 10.12.5.53 484 7d23h43m24s 23m46s 10.12.5.54 376 8d25m53s 30m42s 10.12.5.55 365 7d23h34m53s 31m29s 10.12.5.56 18 11h13m19s 37m24s 192.168.201.10 4 12d7h19m6s 3d1h49m46s 192.168.201.6 7 5d8h31m9s 18h21m35s ::1 9 2h58m55s 19m52s -
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 2,350,129 buffers Checkpoint Peak
- 2026-08-28 00:40:41 Date
- 1620.075 seconds Highest write time
- 0.755 seconds Sync time
Checkpoints Wal files
Key values
- 879 files Wal files usage Peak
- 2026-08-28 07:42:46 Date
Checkpoints distance
Key values
- 17,252.03 Mo Distance Peak
- 2026-08-28 06:51:12 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Aug 28 00 2,892,660 2,780.452s 0.041s 2,785.973s 01 2,737,313 2,366.736s 0.361s 2,372.901s 02 1,137,299 1,619.85s 0.002s 1,620.906s 03 74,060 1,620.784s 0.002s 1,621.056s 04 311,629 3,238.968s 0.015s 3,239.912s 05 259,300 3,239.091s 0.022s 3,239.696s 06 889,960 3,356.813s 1.346s 3,363.573s 07 126,501 224.714s 2.194s 256.271s 08 1,096,675 3,302.141s 0.107s 3,305.496s 09 453,225 3,239.97s 0.006s 3,241.086s 10 1,443,218 3,176.109s 0.321s 3,178.596s 11 481,894 1,659.678s 0.004s 1,659.75s 12 99 10.01s 0.001s 10.014s 13 46,903 3,239.817s 0.004s 3,239.91s 14 1,250 125.513s 0.004s 125.53s 15 269 27.173s 0.002s 27.183s 16 21 2.186s 0.001s 2.21s 17 63,251 1,619.532s 0.003s 1,620.046s 18 51 5.28s 0.002s 5.289s 19 16 1.772s 0.002s 1.78s 20 279 28.114s 0.002s 28.124s 21 84 8.588s 0.002s 8.598s 22 36 3.791s 0.002s 3.8s 23 37 3.893s 0.002s 3.903s Day Hour Added Removed Recycled Synced files Longest sync Average sync Aug 28 00 0 1 1,680 186 0.029s 0.003s 01 0 79 2,691 748 0.052s 0.006s 02 0 0 538 82 0.001s 0.001s 03 0 0 63 42 0.001s 0.002s 04 0 216 85 98 0.007s 0.002s 05 0 121 41 72 0.008s 0.002s 06 0 105 2,204 613 0.577s 0.018s 07 0 327 10,440 1,062 0.740s 0.041s 08 0 0 1,614 239 0.017s 0.003s 09 0 0 607 101 0.001s 0.002s 10 0 33 1,076 311 0.113s 0.005s 11 0 0 26 216 0.001s 0.003s 12 0 0 0 52 0.001s 0.001s 13 0 19 0 156 0.001s 0.002s 14 0 0 0 192 0.001s 0.003s 15 0 0 0 113 0.001s 0.002s 16 0 0 0 10 0.001s 0.001s 17 0 160 0 55 0.001s 0.001s 18 0 0 0 26 0.001s 0.002s 19 0 0 0 12 0.001s 0.002s 20 0 0 0 27 0.001s 0.002s 21 0 0 0 15 0.001s 0.002s 22 0 0 0 17 0.001s 0.002s 23 0 0 0 21 0.001s 0.002s Day Hour Count Avg time (sec) Aug 28 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 28 00 8,118,378.50 kB 8,747,757.50 kB 01 7,983,237.80 kB 8,732,242.60 kB 02 8,811,368.00 kB 8,817,900.00 kB 03 784,160.00 kB 7,688,293.00 kB 04 2,204,621.50 kB 6,561,474.50 kB 05 1,327,295.50 kB 5,590,737.00 kB 06 6,302,721.67 kB 7,480,245.50 kB 07 8,821,075.40 kB 8,827,974.30 kB 08 8,810,511.00 kB 8,825,042.00 kB 09 5,097,378.50 kB 8,453,889.00 kB 10 5,972,042.67 kB 8,313,391.67 kB 11 317,392.33 kB 7,255,450.67 kB 12 457.00 kB 5,855,394.00 kB 13 154,372.50 kB 5,028,987.50 kB 14 1,274.00 kB 3,880,018.67 kB 15 772.00 kB 2,974,848.50 kB 16 49.00 kB 2,536,497.00 kB 17 2,626,597.00 kB 2,626,597.00 kB 18 65.50 kB 2,245,752.00 kB 19 33.00 kB 1,819,064.50 kB 20 768.50 kB 1,473,522.00 kB 21 224.50 kB 1,193,645.00 kB 22 79.00 kB 966,883.00 kB 23 52.50 kB 783,187.00 kB -
Temporary Files
Size of temporary files
Key values
- 13.00 GiB Temp Files size Peak
- 2026-08-28 15:44:27 Date
Number of temporary files
Key values
- 24 per second Temp Files Peak
- 2026-08-28 06:59:13 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Aug 28 00 75 28.83 GiB 393.68 MiB 01 91 3.16 GiB 35.57 MiB 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 456 21.27 GiB 47.76 MiB 07 566 153.26 GiB 277.27 MiB 08 0 0 0 09 0 0 0 10 0 0 0 11 10 9.28 GiB 950.18 MiB 12 0 0 0 13 0 0 0 14 0 0 0 15 54 52.99 GiB 1004.84 MiB 16 60 775.82 MiB 12.93 MiB 17 0 0 0 18 0 0 0 19 0 0 0 20 0 0 0 21 0 0 0 22 0 0 0 23 0 0 0 Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 942 172.82 GiB 128.00 KiB 1.00 GiB 187.87 MiB vacuum full analyze;-
VACUUM FULL ANALYZE;
Date: 2026-08-28 07:49:05 Duration: 58m58s
-
VACUUM FULL ANALYZE;
Date: 2026-08-28 06:50:10 Duration: 0ms
2 60 775.83 MiB 7.43 MiB 29.12 MiB 12.93 MiB cluster pub2.term;-
CLUSTER pub2.TERM;
Date: 2026-08-28 06:49:15 Duration: 1m11s
-
CLUSTER pub2.TERM;
Date: 2026-08-28 06:48:16 Duration: 0ms
3 60 775.82 MiB 7.84 MiB 27.46 MiB 12.93 MiB vacuum full analyze term;-
vacuum FULL analyze TERM;
Date: 2026-08-28 16:53:21 Duration: 1m24s
-
vacuum FULL analyze TERM;
Date: 2026-08-28 16:52:08 Duration: 0ms Database: ctdprd51 User: pub2 Application: pgAdmin 4 - CONN:2371216
4 25 16.21 GiB 8.00 KiB 1.00 GiB 664.10 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: 2026-08-28 00:40:51 Duration: 3m28s
-
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-08-28 00:40:50 Duration: 0ms
5 20 969.77 MiB 26.48 MiB 83.20 MiB 48.49 MiB cluster pub2.term_label;-
CLUSTER pub2.TERM_LABEL;
Date: 2026-08-28 06:50:05 Duration: 50s516ms
-
CLUSTER pub2.TERM_LABEL;
Date: 2026-08-28 06:49:26 Duration: 0ms
6 15 11.58 GiB 261.86 MiB 1.00 GiB 790.58 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: 2026-08-28 00:42:58 Duration: 2m6s
-
CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);
Date: 2026-08-28 00:42:57 Duration: 0ms
7 10 9.28 GiB 285.81 MiB 1.00 GiB 950.18 MiB select pub2.maint_cached_value_refresh_data_metrics ();-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-08-28 11:06:17 Duration: 39m36s
-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-08-28 11:01:20 Duration: 0ms
8 10 156.84 MiB 8.00 KiB 32.14 MiB 15.68 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: 2026-08-28 00:23:50 Duration: 0ms Database: ctdprd51 User: pub2
9 10 681.92 MiB 8.00 KiB 139.27 MiB 68.19 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: 2026-08-28 01:13:08 Duration: 6s530ms
-
ALTER TABLE pub2.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);
Date: 2026-08-28 01:13:08 Duration: 0ms
10 10 68.40 MiB 8.00 KiB 13.74 MiB 6.84 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: 2026-08-28 01:13:35 Duration: 0ms
11 8 68.22 MiB 8.00 KiB 17.24 MiB 8.53 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: 2026-08-28 01:13:41 Duration: 0ms
12 6 5.69 GiB 701.71 MiB 1.00 GiB 970.29 MiB select phenotypeterm.nm "GOName", phenotypeterm.acc_txt "GOID", diseaseterm.nm "DiseaseName", diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( select string_agg(distinct chemtermnetwork.nm, ?)) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( select string_agg(distinct genetermnetwork.nm, ?)) "InferenceGeneSymbols" from phenotype_term_reference ptr inner join phenotype_term pt on ptr.phenotype_id = pt.phenotype_id and ptr.term_id = pt.term_id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id;-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id;
Date: 2026-08-28 15:52:24 Duration: 0ms
13 5 68.34 MiB 12.73 MiB 14.18 MiB 13.67 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: 2026-08-28 01:13:36 Duration: 0ms
14 5 218.91 MiB 42.52 MiB 44.40 MiB 43.78 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: 2026-08-28 00:23:59 Duration: 0ms
15 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: 2026-08-28 01:13:02 Duration: 0ms
16 5 156.80 MiB 30.58 MiB 32.08 MiB 31.36 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: 2026-08-28 00:23:54 Duration: 0ms
17 5 681.75 MiB 134.48 MiB 138.62 MiB 136.35 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: 2026-08-28 01:13:01 Duration: 7s496ms
-
CREATE INDEX ix_gene_disease_ind_chem_qty ON pub2.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);
Date: 2026-08-28 01:13:01 Duration: 0ms
18 5 68.35 MiB 12.70 MiB 14.04 MiB 13.67 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: 2026-08-28 01:13:36 Duration: 0ms
19 5 156.80 MiB 29.90 MiB 32.16 MiB 31.36 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: 2026-08-28 00:23:53 Duration: 0ms
20 5 218.89 MiB 42.09 MiB 45.20 MiB 43.78 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: 2026-08-28 00:24:04 Duration: 0ms
21 5 156.80 MiB 30.21 MiB 33.15 MiB 31.36 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: 2026-08-28 00:23:51 Duration: 0ms
22 5 681.88 MiB 134.62 MiB 137.63 MiB 136.38 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: 2026-08-28 01:13:31 Duration: 15s12ms
-
CREATE INDEX ix_gene_disease_network_score ON pub2.gene_disease USING btree (network_score);
Date: 2026-08-28 01:13:31 Duration: 0ms
23 5 681.88 MiB 132.52 MiB 140.26 MiB 136.38 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: 2026-08-28 01:13:16 Duration: 7s635ms
-
CREATE INDEX ix_gene_disease_disease ON pub2.gene_disease USING btree (disease_id);
Date: 2026-08-28 01:13:16 Duration: 0ms
24 5 696.00 KiB 128.00 KiB 152.00 KiB 139.20 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: 2026-08-28 01:12:54 Duration: 0ms Database: ctdprd51 User: pub2
25 4 67.30 MiB 16.77 MiB 16.85 MiB 16.82 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: 2026-08-28 01:13:40 Duration: 0ms
26 4 68.18 MiB 16.95 MiB 17.10 MiB 17.04 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: 2026-08-28 01:13:42 Duration: 0ms
27 4 2.03 MiB 512.00 KiB 536.00 KiB 520.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: 2026-08-28 01:13:39 Duration: 0ms
28 4 15.54 MiB 8.00 KiB 7.96 MiB 3.88 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: 2026-08-28 01:13:38 Duration: 0ms
29 4 68.19 MiB 14.83 MiB 19.45 MiB 17.05 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: 2026-08-28 01:13:43 Duration: 0ms
30 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: 2026-08-28 01:13:40 Duration: 0ms
31 2 7.04 MiB 2.98 MiB 4.06 MiB 3.52 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: 2026-08-28 01:13:38 Duration: 0ms
32 2 7.05 MiB 3.01 MiB 4.04 MiB 3.52 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: 2026-08-28 01:13:38 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:50 ]
2 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:50 ]
3 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:50 ]
4 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:50 ]
5 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:50 ]
6 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
7 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
8 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
9 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
10 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
11 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
12 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
13 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
14 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
15 1.00 GiB ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 ]
16 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-08-28 00:42:57 ]
17 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-08-28 00:42:57 ]
18 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-08-28 00:42:57 ]
19 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-08-28 00:42:57 ]
20 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-08-28 00:42:57 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 278.01 sec Highest CPU-cost vacuum
Table pub2.gene_disease
Database ctdprd51 - 2026-08-28 01:52:29 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 278.01 sec Highest CPU-cost vacuum
Table pub2.gene_disease
Database ctdprd51 - 2026-08-28 01:52:29 Date
Analyzes per table
Key values
- pubc.log_query (12) Main table analyzed (database ctdprd51)
- 58 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 12 ctdprd51.pub2.reference 2 ctdprd51.pub2.term 2 ctdprd51.pg_catalog.pg_class 2 ctdprd51.pub2.term_set_enrichment_agent 2 ctdprd51.pub2.phenotype_term 2 ctdprd51.pub2.term_set_enrichment 2 ctdprd51.pub2.term_comp_agent 2 ctdprd51.pub2.exp_receptor 1 ctdprd51.pg_catalog.pg_type 1 ctdprd51.pub2.gene_disease 1 ctdprd51.pub2.exp_receptor_race 1 ctdprd51.pub2.dag_node 1 ctdprd51.pub2.exposure 1 ctdprd51.pub2.term_reference 1 ctdprd51.pub2.chem_disease 1 ctdprd51.pub2.slim_term_mapping 1 ctdprd51.pub2.gene_gene_reference 1 ctdprd51.pub2.exp_receptor_gender 1 ctdprd51.pub2.exp_event_project 1 ctdprd51.pub2.medium 1 ctdprd51.pub2.exp_study_factor 1 ctdprd51.pub2.exp_event_location 1 ctdprd51.pub2.ixn 1 ctdprd51.pg_catalog.pg_depend 1 ctdprd51.pub2.exp_stressor 1 ctdprd51.pub2.geographic_region 1 ctdprd51.pub2.gene_chem_ref_gene_form 1 ctdprd51.pg_catalog.pg_attribute 1 ctdprd51.pub2.exp_outcome 1 ctdprd51.pub2.exp_event 1 ctdprd51.pub2.exp_stressor_stressor_src 1 ctdprd51.pub2.exp_receptor_tobacco_use 1 ctdprd51.pub2.term_comp 1 ctdprd51.pub2.exp_event_assay_method 1 ctdprd51.pub2.country 1 ctdprd51.pub2.gene_gene 1 ctdprd51.pub2.reference_exp 1 ctdprd51.pub2.exp_anatomy 1 ctdprd51.pub2.gene_gene_ref_throughput 1 Total 58 Vacuums per table
Key values
- pub2.phenotype_term (2) Main table vacuumed on database ctdprd51
- 43 vacuums Total
Index Buffer usage Skipped WAL usage Frozen Table Vacuums scans hits misses dirtied pins frozen records full page bytes pages tuples ctdprd51.pub2.phenotype_term 2 2 1,025,685 0 1,384 0 0 821,241 54,078 265,502,769 0 0 ctdprd51.pubc.log_query 2 1 407 0 67 0 0 112 52 348,741 0 0 ctdprd51.pub2.reference 2 2 614,236 0 68,727 1 0 385,055 9,044 92,655,559 0 0 ctdprd51.pg_toast.pg_toast_2619 2 2 9,239 0 3,914 0 19,725 7,908 2,108 1,235,106 0 0 ctdprd51.pg_catalog.pg_statistic 2 2 1,296 0 368 0 244 732 258 1,148,721 0 0 ctdprd51.pub2.term 2 2 2,304,511 0 730,617 0 0 1,438,056 706,928 1,601,713,943 0 0 ctdprd51.pub2.exp_study_factor 1 0 116 0 3 0 0 12 1 9,127 0 0 ctdprd51.pub2.ixn 1 1 1,660,424 0 99 0 0 1,103,275 47,796 249,128,136 0 0 ctdprd51.pub2.exp_event_location 1 0 3,894 0 3 0 0 1,896 1 120,283 0 0 ctdprd51.pg_catalog.pg_class 1 1 362 0 43 0 0 174 44 199,944 0 0 ctdprd51.pg_toast.pg_toast_12200649 1 1 92 0 3 0 0 50 7 11,869 0 0 ctdprd51.pub2.gene_chem_ref_gene_form 1 0 36,542 0 4 0 0 18,221 2 1,086,646 0 0 ctdprd51.pub2.exp_stressor 1 0 7,087 0 3 0 0 3,514 1 215,745 0 0 ctdprd51.pub2.exp_stressor_stressor_src 1 0 3,051 0 4 0 0 1,497 1 96,742 0 0 ctdprd51.pub2.exp_outcome 1 0 1,043 0 4 0 0 447 2 37,428 0 0 ctdprd51.pub2.exp_event 1 0 14,118 0 3 0 0 6,981 1 420,298 0 0 ctdprd51.pub2.term_set_enrichment_agent 1 0 10,455 0 3 0 0 5,197 1 315,042 0 0 ctdprd51.pg_catalog.pg_attribute 1 1 620 0 116 0 37 250 94 508,074 0 0 ctdprd51.pub2.exp_event_assay_method 1 0 5,607 0 4 0 0 2,775 2 175,528 0 0 ctdprd51.pub2.term_set_enrichment 1 0 554 0 3 0 0 239 1 22,520 0 0 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 1,322 0 3 0 0 626 1 45,353 0 0 ctdprd51.pub2.term_comp_agent 1 0 163 0 4 0 0 38 2 14,413 0 0 ctdprd51.pub2.gene_gene_ref_throughput 1 0 16,045 0 3 0 0 7,983 1 479,416 0 0 ctdprd51.pub2.exp_anatomy 1 0 133 0 3 0 0 38 1 10,661 0 0 ctdprd51.pub2.reference_exp 1 0 347 0 4 0 0 136 2 18,511 0 0 ctdprd51.pub2.gene_gene 1 0 13,337 0 5 0 0 6,617 2 401,738 0 0 ctdprd51.pub2.exp_receptor 1 0 8,197 0 3 0 0 4,070 1 248,549 0 0 ctdprd51.pub2.exp_receptor_race 1 0 1,439 0 3 0 0 684 1 48,775 0 0 ctdprd51.pub2.gene_disease 1 1 3,062,071 0 900,630 0 0 1,724,687 774,844 2,105,594,565 0 0 ctdprd51.pub2.exposure 1 0 4,190 0 3 0 0 2,042 1 128,897 0 0 ctdprd51.pub2.dag_node 1 1 338,273 0 48,077 0 0 289,825 53,447 199,328,397 0 0 ctdprd51.pub2.chem_disease 1 1 282,085 0 10,432 0 0 172,352 10,420 125,673,244 0 0 ctdprd51.pub2.term_reference 1 0 41,037 0 4 0 0 20,463 1 1,215,736 0 0 ctdprd51.pub2.gene_gene_reference 1 0 33,431 0 3 0 0 16,639 1 990,120 0 0 ctdprd51.pub2.slim_term_mapping 1 0 606 0 4 0 0 265 2 26,686 0 0 ctdprd51.pub2.exp_event_project 1 0 2,436 0 3 0 0 1,196 1 78,983 0 0 ctdprd51.pub2.exp_receptor_gender 1 0 3,017 0 3 0 0 1,493 1 96,506 0 0 Total 43 18 9,507,468 187,288 1,764,559 1 20,006 6,046,786 1,659,151 4,649,352,771 0 0 Vacuum throughput per table
Key values
- pub2.gene_disease (278.01) Max CPU elapsed for vacuum on database ctdprd51
- unknown (0 ms) Max I/O read time for vacuum on database ctdprd51
- unknown (0 ms) Max I/O write time for vacuum on database ctdprd51
I/O timing (ms) CPU (s) Table read write elapsed ctdprd51.pub2.phenotype_term 0 0 27.24 ctdprd51.pubc.log_query 0 0 0.01 ctdprd51.pub2.reference 0 0 42.47 ctdprd51.pg_toast.pg_toast_2619 0 0 1.09 ctdprd51.pg_catalog.pg_statistic 0 0 0.1 ctdprd51.pub2.term 0 0 245.55 ctdprd51.pub2.exp_study_factor 0 0 0 ctdprd51.pub2.ixn 0 0 21.01 ctdprd51.pub2.exp_event_location 0 0 0.05 ctdprd51.pg_catalog.pg_class 0 0 0.02 ctdprd51.pg_toast.pg_toast_12200649 0 0 0 ctdprd51.pub2.gene_chem_ref_gene_form 0 0 0.52 ctdprd51.pub2.exp_stressor 0 0 0.08 ctdprd51.pub2.exp_stressor_stressor_src 0 0 0.04 ctdprd51.pub2.exp_outcome 0 0 0.01 ctdprd51.pub2.exp_event 0 0 0.16 ctdprd51.pub2.term_set_enrichment_agent 0 0 0.13 ctdprd51.pg_catalog.pg_attribute 0 0 0.04 ctdprd51.pub2.exp_event_assay_method 0 0 0.07 ctdprd51.pub2.term_set_enrichment 0 0 0 ctdprd51.pub2.exp_receptor_tobacco_use 0 0 0.02 ctdprd51.pub2.term_comp_agent 0 0 0 ctdprd51.pub2.gene_gene_ref_throughput 0 0 0.27 ctdprd51.pub2.exp_anatomy 0 0 0 ctdprd51.pub2.reference_exp 0 0 0 ctdprd51.pub2.gene_gene 0 0 0.41 ctdprd51.pub2.exp_receptor 0 0 0.12 ctdprd51.pub2.exp_receptor_race 0 0 0.02 ctdprd51.pub2.gene_disease 0 0 278.01 ctdprd51.pub2.exposure 0 0 0.06 ctdprd51.pub2.dag_node 0 0 20.3 ctdprd51.pub2.chem_disease 0 0 8.78 ctdprd51.pub2.term_reference 0 0 0.59 ctdprd51.pub2.gene_gene_reference 0 0 0.82 ctdprd51.pub2.slim_term_mapping 0 0 0.02 ctdprd51.pub2.exp_event_project 0 0 0.03 ctdprd51.pub2.exp_receptor_gender 0 0 0.05 Total 0 0 648.09 Tuples removed per table
Key values
- pub2.gene_disease (35679358) Main table with removed tuples on database ctdprd51
- 64871157 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pub2.gene_disease 1 1 35,679,358 35,679,358 0 0 524,697 ctdprd51.pub2.phenotype_term 2 2 21,460,323 7,150,562 0 0 267,392 ctdprd51.pub2.chem_disease 1 1 3,567,189 3,567,189 0 0 52,410 ctdprd51.pub2.term 2 2 2,217,115 4,420,900 0 0 559,668 ctdprd51.pub2.dag_node 1 1 1,826,660 1,818,807 0 0 87,402 ctdprd51.pub2.ixn 1 1 57,963 2,555,308 0 0 608,894 ctdprd51.pub2.reference 2 2 52,171 407,241 0 0 168,757 ctdprd51.pg_toast.pg_toast_2619 2 2 8,787 43,997 52 0 25,184 ctdprd51.pg_catalog.pg_statistic 2 2 807 6,576 86 0 820 ctdprd51.pg_catalog.pg_attribute 1 1 514 9,001 0 0 236 ctdprd51.pg_catalog.pg_class 1 1 189 1,835 0 0 94 ctdprd51.pg_toast.pg_toast_12200649 1 1 69 72 0 0 22 ctdprd51.pubc.log_query 2 1 12 1,307 0 0 47 ctdprd51.pub2.exp_study_factor 1 0 0 1,803 0 0 11 ctdprd51.pub2.exp_event_location 1 0 0 283,643 0 0 1,895 ctdprd51.pub2.gene_chem_ref_gene_form 1 0 0 3,363,695 0 0 18,220 ctdprd51.pub2.exp_stressor 1 0 0 239,904 0 0 3,513 ctdprd51.pub2.exp_stressor_stressor_src 1 0 0 338,030 0 0 1,496 ctdprd51.pub2.exp_outcome 1 0 0 49,031 0 0 446 ctdprd51.pub2.exp_event 1 0 0 236,284 0 0 6,980 ctdprd51.pub2.term_set_enrichment_agent 1 0 0 457,238 0 0 5,196 ctdprd51.pub2.exp_event_assay_method 1 0 0 274,901 0 0 2,774 ctdprd51.pub2.term_set_enrichment 1 0 0 14,344 0 0 238 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 0 88,494 0 0 625 ctdprd51.pub2.term_comp_agent 1 0 0 3,776 0 0 37 ctdprd51.pub2.gene_gene_ref_throughput 1 0 0 1,533,211 0 0 7,982 ctdprd51.pub2.exp_anatomy 1 0 0 4,409 0 0 37 ctdprd51.pub2.reference_exp 1 0 0 3,756 0 0 135 ctdprd51.pub2.gene_gene 1 0 0 1,223,789 0 0 6,616 ctdprd51.pub2.exp_receptor 1 0 0 218,612 0 0 4,069 ctdprd51.pub2.exp_receptor_race 1 0 0 105,333 0 0 683 ctdprd51.pub2.exposure 1 0 0 247,485 0 0 2,041 ctdprd51.pub2.term_reference 1 0 0 3,785,323 0 0 20,462 ctdprd51.pub2.gene_gene_reference 1 0 0 1,525,549 0 0 16,638 ctdprd51.pub2.slim_term_mapping 1 0 0 33,517 0 0 264 ctdprd51.pub2.exp_event_project 1 0 0 114,290 0 0 1,195 ctdprd51.pub2.exp_receptor_gender 1 0 0 215,413 0 0 1,492 Total 43 18 64,871,157 70,023,983 138 0 2,398,668 Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Pages removed per tables
NO DATASET
Table Number of vacuums Index scans Tuples removed Pages removed ctdprd51.pub2.exp_study_factor 1 0 0 0 ctdprd51.pub2.ixn 1 1 57963 0 ctdprd51.pub2.exp_event_location 1 0 0 0 ctdprd51.pg_catalog.pg_class 1 1 189 0 ctdprd51.pg_toast.pg_toast_12200649 1 1 69 0 ctdprd51.pub2.gene_chem_ref_gene_form 1 0 0 0 ctdprd51.pub2.exp_stressor 1 0 0 0 ctdprd51.pub2.exp_stressor_stressor_src 1 0 0 0 ctdprd51.pub2.exp_outcome 1 0 0 0 ctdprd51.pub2.exp_event 1 0 0 0 ctdprd51.pub2.phenotype_term 2 2 21460323 0 ctdprd51.pub2.term_set_enrichment_agent 1 0 0 0 ctdprd51.pg_catalog.pg_attribute 1 1 514 0 ctdprd51.pub2.exp_event_assay_method 1 0 0 0 ctdprd51.pub2.term_set_enrichment 1 0 0 0 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 0 0 ctdprd51.pubc.log_query 2 1 12 0 ctdprd51.pub2.term_comp_agent 1 0 0 0 ctdprd51.pub2.gene_gene_ref_throughput 1 0 0 0 ctdprd51.pub2.exp_anatomy 1 0 0 0 ctdprd51.pub2.reference_exp 1 0 0 0 ctdprd51.pub2.gene_gene 1 0 0 0 ctdprd51.pub2.reference 2 2 52171 0 ctdprd51.pub2.exp_receptor 1 0 0 0 ctdprd51.pg_toast.pg_toast_2619 2 2 8787 0 ctdprd51.pg_catalog.pg_statistic 2 2 807 0 ctdprd51.pub2.exp_receptor_race 1 0 0 0 ctdprd51.pub2.gene_disease 1 1 35679358 0 ctdprd51.pub2.exposure 1 0 0 0 ctdprd51.pub2.dag_node 1 1 1826660 0 ctdprd51.pub2.chem_disease 1 1 3567189 0 ctdprd51.pub2.term_reference 1 0 0 0 ctdprd51.pub2.gene_gene_reference 1 0 0 0 ctdprd51.pub2.slim_term_mapping 1 0 0 0 ctdprd51.pub2.exp_event_project 1 0 0 0 ctdprd51.pub2.exp_receptor_gender 1 0 0 0 ctdprd51.pub2.term 2 2 2217115 0 Total 43 18 64,871,157 0 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Aug 28 00 1 1 01 28 30 02 0 0 03 2 3 04 1 2 05 0 4 06 0 0 07 3 4 08 0 1 09 0 0 10 3 4 11 0 0 12 0 0 13 4 9 14 0 0 15 0 0 16 0 0 17 0 0 18 0 0 19 0 0 20 0 0 21 1 0 22 0 0 23 0 0 - 278.01 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- AccessShareLock Main Lock Type
- 1 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query 1 1 12s603ms 12s603ms 12s603ms 12s603ms select sq.*, count(*) over () fullrowcount from ( select t.acc_txt acc, ? || t.nm accquerystr, t.nm, t.nm_html nmhtml, t.secondary_nm casrn, l.nm matchednm, lt.nm_display matchedtype, case when lt.nm_display = ? then true else false end isnamematch, t.has_genes hasgenes, t.has_chems haschems, t.has_diseases hasdiseases, t.has_phenotypes hasphenotypes, case when upper(l.nm) = ? then ? else ? end relevance, t.nm_sort, t.id, t.acc_db_cd accdbcd, t.has_exposures hasexposures from term t inner join term_label l on l.term_id = t.id inner join term_label_type lt on l.term_label_type_id = lt.id where t.object_type_id = ? and l.object_type_id = ? and l.id in ( select first_value(i.id) over (partition by i.term_id order by it.priority_seq, i.nm) from term_label i inner join term_label_type it on i.term_label_type_id = it.id where i.object_type_id = ? and i.nm_fts @@ to_tsquery(?, ?)) union all select t.acc_txt acc, ? || t.nm accquerystr, t.nm, t.nm_html nmhtml, t.secondary_nm casrn, l.acc_txt matchednm, ? matchedtype, false isnamematch, t.has_genes hasgenes, t.has_chems haschems, t.has_diseases hasdiseases, t.has_phenotypes hasphenotypes, ? relevance, t.nm_sort, t.id, t.acc_db_cd accdbcd, t.has_exposures hasexposures from db_link l inner join term t on l.object_id = t.id where l.type_cd = ? and l.object_type_id = ? and (upper(l.acc_txt) = ?) order by ?, ?) sq limit ?;-
SELECT /* MeshBasicQueryDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT /* label */ t.acc_txt acc, 'name:' || t.nm accQueryStr, t.nm, t.nm_html nmHtml, t.secondary_nm casRN, l.nm matchedNm, lt.nm_display matchedType, CASE WHEN lt.nm_display = 'Name' THEN true ELSE false END isNameMatch, t.has_genes hasGenes, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_phenotypes hasPhenotypes, CASE WHEN UPPER(l.nm) = $1 THEN 1 ELSE 2 END relevance, t.nm_sort, t.id, t.acc_db_cd accdbcd, t.has_exposures hasExposures FROM term t INNER JOIN term_label l ON l.term_id = t.id INNER JOIN term_label_type lt ON l.term_label_type_id = lt.id WHERE t.object_type_id = 2 AND l.object_type_id = 2 AND l.id IN ( SELECT FIRST_VALUE(i.id) OVER (PARTITION BY i.term_id ORDER BY it.priority_seq, i.nm) FROM term_label i INNER JOIN term_label_type it ON i.term_label_type_id = it.id WHERE i.object_type_id = 2 AND i.nm_fts @@ to_tsquery('common.english_nostops', $2)) UNION ALL SELECT /* term acc */ t.acc_txt acc, 'name:' || t.nm accQueryStr, t.nm, t.nm_html nmHtml, t.secondary_nm casRN, l.acc_txt matchednm, 'Accession' matchedtype, false isNameMatch, t.has_genes hasgenes, t.has_chems haschems, t.has_diseases hasdiseases, t.has_phenotypes hasPhenotypes, 1 relevance, t.nm_sort, t.id, t.acc_db_cd accdbcd, t.has_exposures hasexposures FROM db_link l INNER JOIN term t ON l.object_id = t.id WHERE l.type_cd = 'A' AND l.object_type_id = 2 AND (upper(l.acc_txt) = $3) ORDER BY 13, 14) sq LIMIT 50;
Date: 2026-08-28 16:53:12
Queries that waited the most
Rank Wait time Query 1 12s603ms SELECT /* MeshBasicQueryDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT /* label */ t.acc_txt acc, 'name:' || t.nm accQueryStr, t.nm, t.nm_html nmHtml, t.secondary_nm casRN, l.nm matchedNm, lt.nm_display matchedType, CASE WHEN lt.nm_display = 'Name' THEN true ELSE false END isNameMatch, t.has_genes hasGenes, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_phenotypes hasPhenotypes, CASE WHEN UPPER(l.nm) = $1 THEN 1 ELSE 2 END relevance, t.nm_sort, t.id, t.acc_db_cd accdbcd, t.has_exposures hasExposures FROM term t INNER JOIN term_label l ON l.term_id = t.id INNER JOIN term_label_type lt ON l.term_label_type_id = lt.id WHERE t.object_type_id = 2 AND l.object_type_id = 2 AND l.id IN ( SELECT FIRST_VALUE(i.id) OVER (PARTITION BY i.term_id ORDER BY it.priority_seq, i.nm) FROM term_label i INNER JOIN term_label_type it ON i.term_label_type_id = it.id WHERE i.object_type_id = 2 AND i.nm_fts @@ to_tsquery('common.english_nostops', $2)) UNION ALL SELECT /* term acc */ t.acc_txt acc, 'name:' || t.nm accQueryStr, t.nm, t.nm_html nmHtml, t.secondary_nm casRN, l.acc_txt matchednm, 'Accession' matchedtype, false isNameMatch, t.has_genes hasgenes, t.has_chems haschems, t.has_diseases hasdiseases, t.has_phenotypes hasPhenotypes, 1 relevance, t.nm_sort, t.id, t.acc_db_cd accdbcd, t.has_exposures hasexposures FROM db_link l INNER JOIN term t ON l.object_id = t.id WHERE l.type_cd = 'A' AND l.object_type_id = 2 AND (upper(l.acc_txt) = $3) ORDER BY 13, 14) sq LIMIT 50;[ Date: 2026-08-28 16:53:12 ]
-
Queries
Queries by type
Key values
- 134 Total read queries
- 66 Total write queries
Queries by database
Key values
- unknown Main database
- 152 Requests
- 10h34m59s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 303 Requests
User Request type Count Duration edit Total 1 9s108ms insert 1 9s108ms load Total 25 1h6m23s select 25 1h6m23s postgres Total 16 17m49s copy to 16 17m49s pub1 Total 1 16s282ms select 1 16s282ms pub2 Total 5 18m10s insert 3 17m53s select 2 16s762ms pubc Total 1 9m27s select 1 9m27s pubeu Total 68 21m16s select 68 21m16s qaeu Total 22 52m18s cte 2 10s597ms select 20 52m7s unknown Total 303 15h23m12s copy to 56 12m3s ddl 35 47m59s insert 17 52m3s others 19 1h8m45s select 167 11h45m28s update 9 36m52s Duration by user
Key values
- 15h23m12s (unknown) Main time consuming user
User Request type Count Duration edit Total 1 9s108ms insert 1 9s108ms load Total 25 1h6m23s select 25 1h6m23s postgres Total 16 17m49s copy to 16 17m49s pub1 Total 1 16s282ms select 1 16s282ms pub2 Total 5 18m10s insert 3 17m53s select 2 16s762ms pubc Total 1 9m27s select 1 9m27s pubeu Total 68 21m16s select 68 21m16s qaeu Total 22 52m18s cte 2 10s597ms select 20 52m7s unknown Total 303 15h23m12s copy to 56 12m3s ddl 35 47m59s insert 17 52m3s others 19 1h8m45s select 167 11h45m28s update 9 36m52s Queries by host
Key values
- unknown Main host
- 442 Requests
- 18h29m4s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 199 Requests
- 11h31m43s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-08-28 00:56:04 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 119 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 2h47m20s SELECT maint_term_derive_nm_fts ();[ Date: 2026-08-28 06:42:47 - Bind query: yes ]
2 2h26m30s select pub2.maint_term_derive_data ();[ Date: 2026-08-28 10:16:00 - Bind query: yes ]
3 1h58m38s select pub2.maint_gene_chem_ref_gene_form_refresh ();[ Date: 2026-08-28 03:52:24 - Bind query: yes ]
4 58m58s VACUUM FULL ANALYZE;[ Date: 2026-08-28 07:49:05 - Bind query: yes ]
5 40m25s SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;[ Date: 2026-08-28 14:33:49 - Database: ctdprd51 - User: qaeu - Bind query: yes ]
6 39m36s select pub2.maint_cached_value_refresh_data_metrics ();[ Date: 2026-08-28 11:06:17 - Bind query: yes ]
7 27m19s update pub2.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub2.GENE_DISEASE_REFERENCE gdr where gd.gene_id = gdr.gene_id and gd.disease_id = gdr.disease_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));[ Date: 2026-08-28 01:47:46 - Bind query: yes ]
8 13m19s 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: 2026-08-28 00:37:23 - Bind query: yes ]
9 9m27s /* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();[ Date: 2026-08-28 00:09:29 - Database: ctdprd51 - User: pubc - Application: psql ]
10 9m23s select pub2.maint_phenotype_term_derive_data ();[ Date: 2026-08-28 10:26:41 - Bind query: yes ]
11 3m28s ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-08-28 00:40:51 - Bind query: yes ]
12 3m23s SELECT maint_term_label_derive_nm_fts ();[ Date: 2026-08-28 06:46:22 - Bind query: yes ]
13 3m 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: 2026-08-28 00:21:11 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
14 2m53s INSERT INTO pub2.TERM_REFERENCE (term_id, object_type_id, reference_id, ixn_type_id) SELECT DISTINCT term_id, object_type_id, reference_id, ixn_type_id FROM ( SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT ee.exp_marker_term_id as term_id, ( SELECT object_type_id FROM term WHERE id = exp_marker_term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_EVENT ee WHERE e.exp_event_id = ee.id AND exp_marker_term_id IS NOT NULL UNION SELECT er.term_id, ( SELECT object_type_id FROM term WHERE id = er.term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_RECEPTOR er WHERE e.exp_receptor_id = er.id AND er.term_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM term WHERE id = chem_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_STRESSOR es WHERE e.exp_stressor_id = es.id AND chem_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM term WHERE id = phenotype_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND phenotype_id IS NOT NULL UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM term WHERE id = disease_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND disease_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' AND taxon_id IS NOT NULL UNION SELECT from_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT DISTINCT t.id as taxon_id, t.object_type_id as taxon_object_id, r.id as reference_id, i.ixn_type_id as ixn_type_id FROM edit.REFERENCE_IXN ri, pub2.TERM t, edit.IXN i, pub2.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub2.OBJECT_TYPE WHERE cd = 'taxon') AND ri.ixn_id = i.root_id AND i.ixn_type_id in ( SELECT id FROM edit.IXN_TYPE WHERE nm in ('CHEMICAL-DISEASE', 'GENE-DISEASE')) AND ri.reference_acc_txt = r.acc_txt AND ri.taxon_acc_txt IS NOT NULL AND ri.taxon_acc_txt <> '') as test UNION select ea.anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_ANATOMY ea, pub2.EXP_OUTCOME eo, pub2.EXPOSURE e, pub2.REFERENCE r where ea.exp_outcome_id = eo.id and eo.id = e.exp_outcome_id and e.reference_acc_txt = r.acc_txt UNION select anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'PHENOTYPE') as ixn_type_id from pub2.IXN i, pub2.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub2.REFERENCE r where i.id = ri.ixn_id and ri.reference_acc_txt = r.acc_txt and i.id = ia.ixn_id UNION select medium_term_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_EVENT ee, pub2.EXPOSURE e, pub2.REFERENCE r where ee.id = e.exp_event_id and e.reference_acc_txt = r.acc_txt and medium_term_id is not null;[ Date: 2026-08-28 03:55:18 - Bind query: yes ]
15 2m37s SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;[ Date: 2026-08-28 15:50:38 - Database: ctdprd51 - User: qaeu - Bind query: yes ]
16 2m32s update pub2.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub2.PHENOTYPE_TERM_REFERENCE ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));[ Date: 2026-08-28 01:50:19 - Bind query: yes ]
17 2m13s update pub2.TERM set has_exposures = false;[ Date: 2026-08-28 01:17:02 - Bind query: yes ]
18 2m10s SELECT /* AllCuratedChemPhenoIxnsDAO */ chemTerm.nm ChemicalName, chemTerm.acc_txt ChemicalID, chemTerm.secondary_nm CasRN, phenoTerm.nm PhenotypeName, phenoTerm.acc_txt PhenotypeID, ( SELECT STRING_AGG(distinct coMentionTerm.nm || '^' || coMentionTerm.acc_txt || '^' || coMentionTerm.acc_db_cd, '|')) as coMentionedTerms, taxonTerm.nm Organism, taxonTerm.acc_txt OrganismID, i.ixn_prose_txt Interaction, i.actions_txt InteractionActions, ( SELECT STRING_AGG(distinct ia.level_seq + 1 || '^' || anatomyTerm.nm || '^' || anatomyTerm.acc_txt, '|' ORDER BY ia.level_seq + 1 || '^' || anatomyTerm.nm || '^' || anatomyTerm.acc_txt)) as anatomyTerms, STRING_AGG(distinct inferredTerm.nm || '^' || inferredTerm.acc_txt || '^' || inferredTerm.acc_db_cd, '|' ORDER BY inferredTerm.nm || '^' || inferredTerm.acc_txt || '^' || inferredTerm.acc_db_cd) InferenceGeneSymbols, STRING_AGG(distinct r.acc_txt, '|' ORDER BY r.acc_txt) PubMedIDs, ptr.ixn_id ignorecolumnIxnId FROM phenotype_term_reference ptr LEFT OUTER JOIN term taxonTerm ON ptr.taxon_id = taxonTerm.id INNER JOIN ixn i ON ptr.ixn_id = i.id INNER JOIN term phenoTerm ON ptr.phenotype_id = phenoTerm.id INNER JOIN reference r ON ptr.reference_id = r.id INNER JOIN term chemTerm ON ptr.term_id = chemTerm.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 LEFT OUTER JOIN phenotype_term_reference ptr2 ON ptr.ixn_id = ptr2.ixn_id and (ptr.term_id <> ptr2.term_id or ptr.phenotype_id <> ptr2.phenotype_id) LEFT OUTER JOIN phenotype_term_reference ptr3 ON ptr.term_id = ptr3.term_id and (ptr.phenotype_id = ptr3.phenotype_id and ptr3.source_cd = 'I') LEFT OUTER JOIN term coMentionTerm ON coMentionTerm.id = ptr2.term_id LEFT OUTER JOIN term inferredTerm ON inferredTerm.id = ptr3.via_term_id WHERE ptr.source_cd = 'C' AND ptr.term_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'chem') GROUP BY chemTerm.nm, chemTerm.acc_txt, chemTerm.secondary_nm, phenoTerm.nm, phenoTerm.acc_txt, taxonTerm.nm, taxonTerm.acc_txt, i.ixn_prose_txt, i.actions_txt, ptr.ixn_id ORDER BY chemTerm.nm, phenoTerm.nm;[ Date: 2026-08-28 15:47:19 - Database: ctdprd51 - User: qaeu - Bind query: yes ]
19 2m6s CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-08-28 00:42:58 - Bind query: yes ]
20 1m55s COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;[ Date: 2026-08-28 18:06:57 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 2h47m20s 1 2h47m20s 2h47m20s 2h47m20s select maint_term_derive_nm_fts ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 28 06 1 2h47m20s 2h47m20s -
SELECT maint_term_derive_nm_fts ();
Date: 2026-08-28 06:42:47 Duration: 2h47m20s Bind query: yes
2 2h26m30s 1 2h26m30s 2h26m30s 2h26m30s select pub2.maint_term_derive_data ();Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Aug 28 10 1 2h26m30s 2h26m30s -
select pub2.maint_term_derive_data ();
Date: 2026-08-28 10:16:00 Duration: 2h26m30s Bind query: yes
3 1h58m38s 1 1h58m38s 1h58m38s 1h58m38s select pub2.maint_gene_chem_ref_gene_form_refresh ();Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 28 03 1 1h58m38s 1h58m38s -
select pub2.maint_gene_chem_ref_gene_form_refresh ();
Date: 2026-08-28 03:52:24 Duration: 1h58m38s Bind query: yes
4 58m58s 1 58m58s 58m58s 58m58s vacuum full analyze;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 28 07 1 58m58s 58m58s -
VACUUM FULL ANALYZE;
Date: 2026-08-28 07:49:05 Duration: 58m58s Bind query: yes
-
VACUUM FULL ANALYZE;
Date: 2026-08-28 06:50:10 Duration: 0ms
5 41m18s 10 5s152ms 40m25s 4m7s select g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(r.acc_txt, ? order by r.acc_txt) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id group by g.nm, g.acc_txt, d.nm, d.acc_db_cd || ? || d.acc_txt, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by g.nm, d.nm;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 28 14 6 40m55s 6m49s 15 4 22s760ms 5s690ms [ User: qaeu - Total duration: 40m25s - Times executed: 1 ]
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:33:49 Duration: 40m25s Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:48:45 Duration: 7s277ms Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:56:03 Duration: 6s434ms Bind query: yes
6 39m36s 1 39m36s 39m36s 39m36s select pub2.maint_cached_value_refresh_data_metrics ();Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 28 11 1 39m36s 39m36s -
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-08-28 11:06:17 Duration: 39m36s Bind query: yes
-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-08-28 11:01:20 Duration: 0ms
7 27m19s 1 27m19s 27m19s 27m19s update pub2.gene_disease gd set exposure_reference_qty = ( select count(distinct reference_id) from pub2.gene_disease_reference gdr where gd.gene_id = gdr.gene_id and gd.disease_id = gdr.disease_id and reference_id in ( select id from pub2.reference r where has_exposures = true));Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Aug 28 01 1 27m19s 27m19s -
update pub2.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub2.GENE_DISEASE_REFERENCE gdr where gd.gene_id = gdr.gene_id and gd.disease_id = gdr.disease_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));
Date: 2026-08-28 01:47:46 Duration: 27m19s Bind query: yes
8 13m19s 1 13m19s 13m19s 13m19s 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 #8
Day Hour Count Duration Avg duration Aug 28 00 1 13m19s 13m19s -
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: 2026-08-28 00:37:23 Duration: 13m19s Bind query: yes
9 11m9s 46 5s186ms 29s998ms 14s562ms select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub2.gene_disease_reference order by gene_id, disease_id;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 28 00 46 11m9s 14s562ms -
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub2.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-08-28 00:46:36 Duration: 29s998ms Bind query: yes
-
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub2.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-08-28 00:47:33 Duration: 28s783ms Bind query: yes
-
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub2.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-08-28 00:47:04 Duration: 27s936ms Bind query: yes
10 9m27s 1 9m27s 9m27s 9m27s select maint_query_logs_archive ();Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Aug 28 00 1 9m27s 9m27s [ User: pubc - Total duration: 9m27s - Times executed: 1 ]
[ Application: psql - Total duration: 9m27s - Times executed: 1 ]
-
/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();
Date: 2026-08-28 00:09:29 Duration: 9m27s Database: ctdprd51 User: pubc Application: psql
11 9m23s 1 9m23s 9m23s 9m23s select pub2.maint_phenotype_term_derive_data ();Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 28 10 1 9m23s 9m23s -
select pub2.maint_phenotype_term_derive_data ();
Date: 2026-08-28 10:26:41 Duration: 9m23s Bind query: yes
12 7m36s 4 1m52s 1m55s 1m54s copy pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 28 06 1 1m53s 1m53s 10 1 1m52s 1m52s 14 1 1m53s 1m53s 18 1 1m55s 1m55s [ User: postgres - Total duration: 7m36s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m36s - Times executed: 4 ]
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 18:06:57 Duration: 1m55s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 06:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 14:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
13 3m44s 4 8s281ms 3m 56s245ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Aug 28 00 3 3m24s 1m8s 01 1 20s397ms 20s397ms [ User: load - Total duration: 3m44s - Times executed: 4 ]
[ Application: pg_bulkload - Total duration: 3m44s - Times executed: 4 ]
-
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: 2026-08-28 00:21:11 Duration: 3m Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.GENE_DISEASE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.log,parse-badfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.DUPE}');
Date: 2026-08-28 01:12:48 Duration: 20s397ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
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/pathwayEnrichment/enrichedTermAgent.txt.log,parse-badfile=/home/load/ctdLoadData/pub/pathwayEnrichment/enrichedTermAgent.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/pathwayEnrichment/enrichedTermAgent.txt.DUPE}');
Date: 2026-08-28 00:23:46 Duration: 15s616ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
14 3m28s 1 3m28s 3m28s 3m28s 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 28 00 1 3m28s 3m28s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-08-28 00:40:51 Duration: 3m28s 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: 2026-08-28 00:40:50 Duration: 0ms
15 3m23s 1 3m23s 3m23s 3m23s select maint_term_label_derive_nm_fts ();Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Aug 28 06 1 3m23s 3m23s -
SELECT maint_term_label_derive_nm_fts ();
Date: 2026-08-28 06:46:22 Duration: 3m23s Bind query: yes
16 3m18s 6 5s420ms 2m37s 33s54ms select phenotypeterm.nm "GOName", phenotypeterm.acc_txt "GOID", diseaseterm.nm "DiseaseName", diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( select string_agg(distinct chemtermnetwork.nm, ?)) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( select string_agg(distinct genetermnetwork.nm, ?)) "InferenceGeneSymbols" from phenotype_term_reference ptr inner join phenotype_term pt on ptr.phenotype_id = pt.phenotype_id and ptr.term_id = pt.term_id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id inner join term diseaseterm on ptr.term_id = diseaseterm.id and diseaseterm.object_type_id = ( select id from object_type where cd = ?) left outer join term genetermnetwork on ptr.via_term_id = genetermnetwork.id and genetermnetwork.object_type_id = ( select id from object_type where cd = ?) left outer join term chemtermnetwork on ptr.via_term_id = chemtermnetwork.id and chemtermnetwork.object_type_id = ( select id from object_type where cd = ?) where phenotypeterm.id in ( select dp.descendant_dag_node_id from dag_path dp where dp.ancestor_object_id = ( select id from term where nm = ?)) and ptr.source_cd = ? and ptr.phenotype_id = phenotypeterm.id and ptr.term_id = diseaseterm.id group by phenotypeterm.nm, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Aug 28 15 6 3m18s 33s54ms [ User: qaeu - Total duration: 2m51s - Times executed: 2 ]
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:50:38 Duration: 2m37s Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'molecular_function')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:52:46 Duration: 13s532ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:50:53 Duration: 8s419ms Bind query: yes
17 2m53s 1 2m53s 2m53s 2m53s insert into pub2.term_reference (term_id, object_type_id, reference_id, ixn_type_id) select distinct term_id, object_type_id, reference_id, ixn_type_id from ( select gene_id as term_id, ( select object_type_id from pub2.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_chem_reference union select chem_id as term_id, ( select object_type_id from pub2.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_chem_reference union select taxon_id as term_id, ( select object_type_id from pub2.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_chem_reference where taxon_id is not null union select chem_id as term_id, ( select object_type_id from pub2.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.chem_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub2.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.chem_disease_reference where source_cd = ? union select gene_id as term_id, ( select object_type_id from pub2.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub2.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_disease_reference where source_cd = ? union select ee.exp_marker_term_id as term_id, ( select object_type_id from term where id = exp_marker_term_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_event ee where e.exp_event_id = ee.id and exp_marker_term_id is not null union select er.term_id, ( select object_type_id from term where id = er.term_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_receptor er where e.exp_receptor_id = er.id and er.term_id is not null union select chem_id as term_id, ( select object_type_id from term where id = chem_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_stressor es where e.exp_stressor_id = es.id and chem_id is not null union select phenotype_id as term_id, ( select object_type_id from term where id = phenotype_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_outcome eo where e.exp_outcome_id = eo.id and phenotype_id is not null union select disease_id as term_id, ( select object_type_id from term where id = disease_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_outcome eo where e.exp_outcome_id = eo.id and disease_id is not null union select phenotype_id as term_id, ( select object_type_id from pub2.term where id = phenotype_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.phenotype_term_reference where source_cd = ? union select term_id, ( select object_type_id from pub2.term where id = term_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.phenotype_term_reference where source_cd = ? union select taxon_id as term_id, ( select object_type_id from pub2.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.phenotype_term_reference where source_cd = ? and taxon_id is not null union select from_gene_id as term_id, ( select object_type_id from pub2.term where id = from_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_gene_reference union select to_gene_id as term_id, ( select object_type_id from pub2.term where id = to_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_gene_reference union select from_taxon_id as term_id, ( select object_type_id from pub2.term where id = from_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_gene_reference union select to_taxon_id as term_id, ( select object_type_id from pub2.term where id = to_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_gene_reference union select distinct t.id as taxon_id, t.object_type_id as taxon_object_id, r.id as reference_id, i.ixn_type_id as ixn_type_id from edit.reference_ixn ri, pub2.term t, edit.ixn i, pub2.reference r where ri.taxon_acc_txt = t.acc_txt and t.object_type_id = ( select id from pub2.object_type where cd = ?) and ri.ixn_id = i.root_id and i.ixn_type_id in ( select id from edit.ixn_type where nm in (...)) and ri.reference_acc_txt = r.acc_txt and ri.taxon_acc_txt is not null and ri.taxon_acc_txt <> ?) as test union select ea.anatomy_id as term_id, ( select id from object_type where cd = ?) as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = ?) as ixn_type_id from pub2.exp_anatomy ea, pub2.exp_outcome eo, pub2.exposure e, pub2.reference r where ea.exp_outcome_id = eo.id and eo.id = e.exp_outcome_id and e.reference_acc_txt = r.acc_txt union select anatomy_id as term_id, ( select id from object_type where cd = ?) as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = ?) as ixn_type_id from pub2.ixn i, pub2.ixn_anatomy ia, edit.reference_ixn ri, pub2.reference r where i.id = ri.ixn_id and ri.reference_acc_txt = r.acc_txt and i.id = ia.ixn_id union select medium_term_id as term_id, ( select id from object_type where cd = ?) as object_type_id, r.id, ( select id from ixn_type where nm = ?) as ixn_type_id from pub2.exp_event ee, pub2.exposure e, pub2.reference r where ee.id = e.exp_event_id and e.reference_acc_txt = r.acc_txt and medium_term_id is not null;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Aug 28 03 1 2m53s 2m53s -
INSERT INTO pub2.TERM_REFERENCE (term_id, object_type_id, reference_id, ixn_type_id) SELECT DISTINCT term_id, object_type_id, reference_id, ixn_type_id FROM ( SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT ee.exp_marker_term_id as term_id, ( SELECT object_type_id FROM term WHERE id = exp_marker_term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_EVENT ee WHERE e.exp_event_id = ee.id AND exp_marker_term_id IS NOT NULL UNION SELECT er.term_id, ( SELECT object_type_id FROM term WHERE id = er.term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_RECEPTOR er WHERE e.exp_receptor_id = er.id AND er.term_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM term WHERE id = chem_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_STRESSOR es WHERE e.exp_stressor_id = es.id AND chem_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM term WHERE id = phenotype_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND phenotype_id IS NOT NULL UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM term WHERE id = disease_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND disease_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' AND taxon_id IS NOT NULL UNION SELECT from_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT DISTINCT t.id as taxon_id, t.object_type_id as taxon_object_id, r.id as reference_id, i.ixn_type_id as ixn_type_id FROM edit.REFERENCE_IXN ri, pub2.TERM t, edit.IXN i, pub2.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub2.OBJECT_TYPE WHERE cd = 'taxon') AND ri.ixn_id = i.root_id AND i.ixn_type_id in ( SELECT id FROM edit.IXN_TYPE WHERE nm in ('CHEMICAL-DISEASE', 'GENE-DISEASE')) AND ri.reference_acc_txt = r.acc_txt AND ri.taxon_acc_txt IS NOT NULL AND ri.taxon_acc_txt <> '') as test UNION select ea.anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_ANATOMY ea, pub2.EXP_OUTCOME eo, pub2.EXPOSURE e, pub2.REFERENCE r where ea.exp_outcome_id = eo.id and eo.id = e.exp_outcome_id and e.reference_acc_txt = r.acc_txt UNION select anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'PHENOTYPE') as ixn_type_id from pub2.IXN i, pub2.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub2.REFERENCE r where i.id = ri.ixn_id and ri.reference_acc_txt = r.acc_txt and i.id = ia.ixn_id UNION select medium_term_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_EVENT ee, pub2.EXPOSURE e, pub2.REFERENCE r where ee.id = e.exp_event_id and e.reference_acc_txt = r.acc_txt and medium_term_id is not null;
Date: 2026-08-28 03:55:18 Duration: 2m53s Bind query: yes
18 2m32s 1 2m32s 2m32s 2m32s update pub2.phenotype_term pt set exposure_reference_qty = ( select count(distinct reference_id) from pub2.phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and reference_id in ( select id from pub2.reference r where has_exposures = true));Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 28 01 1 2m32s 2m32s -
update pub2.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub2.PHENOTYPE_TERM_REFERENCE ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));
Date: 2026-08-28 01:50:19 Duration: 2m32s Bind query: yes
19 2m13s 1 2m13s 2m13s 2m13s update pub2.term set has_exposures = false;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 28 01 1 2m13s 2m13s -
update pub2.TERM set has_exposures = false;
Date: 2026-08-28 01:17:02 Duration: 2m13s Bind query: yes
20 2m10s 1 2m10s 2m10s 2m10s select chemterm.nm chemicalname, chemterm.acc_txt chemicalid, chemterm.secondary_nm casrn, phenoterm.nm phenotypename, phenoterm.acc_txt phenotypeid, ( select string_agg(distinct comentionterm.nm || ? || comentionterm.acc_txt || ? || comentionterm.acc_db_cd, ?)) as comentionedterms, taxonterm.nm organism, taxonterm.acc_txt organismid, i.ixn_prose_txt interaction, i.actions_txt interactionactions, ( select string_agg(distinct ia.level_seq + ? || ? || anatomyterm.nm || ? || anatomyterm.acc_txt, ? order by ia.level_seq + ? || ? || anatomyterm.nm || ? || anatomyterm.acc_txt)) as anatomyterms, string_agg(distinct inferredterm.nm || ? || inferredterm.acc_txt || ? || inferredterm.acc_db_cd, ? order by inferredterm.nm || ? || inferredterm.acc_txt || ? || inferredterm.acc_db_cd) inferencegenesymbols, string_agg(distinct r.acc_txt, ? order by r.acc_txt) pubmedids, ptr.ixn_id ignorecolumnixnid from phenotype_term_reference ptr left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join ixn i on ptr.ixn_id = i.id inner join term phenoterm on ptr.phenotype_id = phenoterm.id inner join reference r on ptr.reference_id = r.id inner join term chemterm on ptr.term_id = chemterm.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 left outer join phenotype_term_reference ptr2 on ptr.ixn_id = ptr2.ixn_id and (ptr.term_id <> ptr2.term_id or ptr.phenotype_id <> ptr2.phenotype_id) left outer join phenotype_term_reference ptr3 on ptr.term_id = ptr3.term_id and (ptr.phenotype_id = ptr3.phenotype_id and ptr3.source_cd = ?) left outer join term comentionterm on comentionterm.id = ptr2.term_id left outer join term inferredterm on inferredterm.id = ptr3.via_term_id where ptr.source_cd = ? and ptr.term_object_type_id = ( select ot.id from object_type ot where ot.cd = ?) group by chemterm.nm, chemterm.acc_txt, chemterm.secondary_nm, phenoterm.nm, phenoterm.acc_txt, taxonterm.nm, taxonterm.acc_txt, i.ixn_prose_txt, i.actions_txt, ptr.ixn_id order by chemterm.nm, phenoterm.nm;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 28 15 1 2m10s 2m10s [ User: qaeu - Total duration: 2m10s - Times executed: 1 ]
-
SELECT /* AllCuratedChemPhenoIxnsDAO */ chemTerm.nm ChemicalName, chemTerm.acc_txt ChemicalID, chemTerm.secondary_nm CasRN, phenoTerm.nm PhenotypeName, phenoTerm.acc_txt PhenotypeID, ( SELECT STRING_AGG(distinct coMentionTerm.nm || '^' || coMentionTerm.acc_txt || '^' || coMentionTerm.acc_db_cd, '|')) as coMentionedTerms, taxonTerm.nm Organism, taxonTerm.acc_txt OrganismID, i.ixn_prose_txt Interaction, i.actions_txt InteractionActions, ( SELECT STRING_AGG(distinct ia.level_seq + 1 || '^' || anatomyTerm.nm || '^' || anatomyTerm.acc_txt, '|' ORDER BY ia.level_seq + 1 || '^' || anatomyTerm.nm || '^' || anatomyTerm.acc_txt)) as anatomyTerms, STRING_AGG(distinct inferredTerm.nm || '^' || inferredTerm.acc_txt || '^' || inferredTerm.acc_db_cd, '|' ORDER BY inferredTerm.nm || '^' || inferredTerm.acc_txt || '^' || inferredTerm.acc_db_cd) InferenceGeneSymbols, STRING_AGG(distinct r.acc_txt, '|' ORDER BY r.acc_txt) PubMedIDs, ptr.ixn_id ignorecolumnIxnId FROM phenotype_term_reference ptr LEFT OUTER JOIN term taxonTerm ON ptr.taxon_id = taxonTerm.id INNER JOIN ixn i ON ptr.ixn_id = i.id INNER JOIN term phenoTerm ON ptr.phenotype_id = phenoTerm.id INNER JOIN reference r ON ptr.reference_id = r.id INNER JOIN term chemTerm ON ptr.term_id = chemTerm.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 LEFT OUTER JOIN phenotype_term_reference ptr2 ON ptr.ixn_id = ptr2.ixn_id and (ptr.term_id <> ptr2.term_id or ptr.phenotype_id <> ptr2.phenotype_id) LEFT OUTER JOIN phenotype_term_reference ptr3 ON ptr.term_id = ptr3.term_id and (ptr.phenotype_id = ptr3.phenotype_id and ptr3.source_cd = 'I') LEFT OUTER JOIN term coMentionTerm ON coMentionTerm.id = ptr2.term_id LEFT OUTER JOIN term inferredTerm ON inferredTerm.id = ptr3.via_term_id WHERE ptr.source_cd = 'C' AND ptr.term_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'chem') GROUP BY chemTerm.nm, chemTerm.acc_txt, chemTerm.secondary_nm, phenoTerm.nm, phenoTerm.acc_txt, taxonTerm.nm, taxonTerm.acc_txt, i.ixn_prose_txt, i.actions_txt, ptr.ixn_id ORDER BY chemTerm.nm, phenoTerm.nm;
Date: 2026-08-28 15:47:19 Duration: 2m10s Database: ctdprd51 User: qaeu Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 46 11m9s 5s186ms 29s998ms 14s562ms select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub2.gene_disease_reference order by gene_id, disease_id;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 28 00 46 11m9s 14s562ms -
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub2.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-08-28 00:46:36 Duration: 29s998ms Bind query: yes
-
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub2.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-08-28 00:47:33 Duration: 28s783ms Bind query: yes
-
select gene_id, disease_id, reference_id, source_cd, via_chem_id, network_score, source_acc_txt from pub2.GENE_DISEASE_REFERENCE order by gene_id, disease_id;
Date: 2026-08-28 00:47:04 Duration: 27s936ms Bind query: yes
2 14 1m18s 5s267ms 6s91ms 5s641ms 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 #2
Day Hour Count Duration Avg duration Aug 28 08 4 22s155ms 5s538ms 10 7 39s863ms 5s694ms 16 3 16s967ms 5s655ms [ User: pubeu - Total duration: 1m2s - Times executed: 11 ]
-
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 = '1383800' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-08-28 10:38:35 Duration: 6s91ms 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 = '1441660' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-08-28 16:38:19 Duration: 5s984ms 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 = '1383800' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-08-28 10:38:44 Duration: 5s958ms Bind query: yes
3 10 41m18s 5s152ms 40m25s 4m7s select g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(r.acc_txt, ? order by r.acc_txt) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id group by g.nm, g.acc_txt, d.nm, d.acc_db_cd || ? || d.acc_txt, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by g.nm, d.nm;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 28 14 6 40m55s 6m49s 15 4 22s760ms 5s690ms [ User: qaeu - Total duration: 40m25s - Times executed: 1 ]
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:33:49 Duration: 40m25s Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:48:45 Duration: 7s277ms Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:56:03 Duration: 6s434ms Bind query: yes
4 6 3m18s 5s420ms 2m37s 33s54ms select phenotypeterm.nm "GOName", phenotypeterm.acc_txt "GOID", diseaseterm.nm "DiseaseName", diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( select string_agg(distinct chemtermnetwork.nm, ?)) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( select string_agg(distinct genetermnetwork.nm, ?)) "InferenceGeneSymbols" from phenotype_term_reference ptr inner join phenotype_term pt on ptr.phenotype_id = pt.phenotype_id and ptr.term_id = pt.term_id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id inner join term diseaseterm on ptr.term_id = diseaseterm.id and diseaseterm.object_type_id = ( select id from object_type where cd = ?) left outer join term genetermnetwork on ptr.via_term_id = genetermnetwork.id and genetermnetwork.object_type_id = ( select id from object_type where cd = ?) left outer join term chemtermnetwork on ptr.via_term_id = chemtermnetwork.id and chemtermnetwork.object_type_id = ( select id from object_type where cd = ?) where phenotypeterm.id in ( select dp.descendant_dag_node_id from dag_path dp where dp.ancestor_object_id = ( select id from term where nm = ?)) and ptr.source_cd = ? and ptr.phenotype_id = phenotypeterm.id and ptr.term_id = diseaseterm.id group by phenotypeterm.nm, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 28 15 6 3m18s 33s54ms [ User: qaeu - Total duration: 2m51s - Times executed: 2 ]
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:50:38 Duration: 2m37s Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'molecular_function')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:52:46 Duration: 13s532ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:50:53 Duration: 8s419ms Bind query: yes
5 4 7m36s 1m52s 1m55s 1m54s copy pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 28 06 1 1m53s 1m53s 10 1 1m52s 1m52s 14 1 1m53s 1m53s 18 1 1m55s 1m55s [ User: postgres - Total duration: 7m36s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m36s - Times executed: 4 ]
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 18:06:57 Duration: 1m55s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 06:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 14:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
6 4 3m44s 8s281ms 3m 56s245ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 28 00 3 3m24s 1m8s 01 1 20s397ms 20s397ms [ User: load - Total duration: 3m44s - Times executed: 4 ]
[ Application: pg_bulkload - Total duration: 3m44s - Times executed: 4 ]
-
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: 2026-08-28 00:21:11 Duration: 3m Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.GENE_DISEASE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.log,parse-badfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.DUPE}');
Date: 2026-08-28 01:12:48 Duration: 20s397ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
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/pathwayEnrichment/enrichedTermAgent.txt.log,parse-badfile=/home/load/ctdLoadData/pub/pathwayEnrichment/enrichedTermAgent.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/pathwayEnrichment/enrichedTermAgent.txt.DUPE}');
Date: 2026-08-28 00:23:46 Duration: 15s616ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
7 4 1m37s 24s347ms 24s669ms 24s485ms copy pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Aug 28 06 1 24s375ms 24s375ms 10 1 24s549ms 24s549ms 14 1 24s347ms 24s347ms 18 1 24s669ms 24s669ms -
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 18:07:22 Duration: 24s669ms
-
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 10:07:19 Duration: 24s549ms
-
COPY pubc.log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 06:07:20 Duration: 24s375ms
8 4 1m22s 20s303ms 21s108ms 20s517ms copy edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Aug 28 06 1 20s331ms 20s331ms 10 1 20s303ms 20s303ms 14 1 20s325ms 20s325ms 18 1 21s108ms 21s108ms [ User: postgres - Total duration: 1m22s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 1m22s - Times executed: 4 ]
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 18:00:23 Duration: 21s108ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 06:00:22 Duration: 20s331ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 14:00:22 Duration: 20s325ms Database: ctdprd51 User: postgres Application: pg_dump
9 4 1m2s 15s411ms 15s936ms 15s607ms copy pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 28 06 1 15s610ms 15s610ms 10 1 15s470ms 15s470ms 14 1 15s411ms 15s411ms 18 1 15s936ms 15s936ms -
COPY pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) TO stdout;
Date: 2026-08-28 18:07:38 Duration: 15s936ms
-
COPY pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) TO stdout;
Date: 2026-08-28 06:07:35 Duration: 15s610ms
-
COPY pubc.log_query_bots_original (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status, user_agent_pattern) TO stdout;
Date: 2026-08-28 10:07:35 Duration: 15s470ms
10 4 1m 15s16ms 15s417ms 15s130ms copy edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Aug 28 06 1 15s43ms 15s43ms 10 1 15s16ms 15s16ms 14 1 15s45ms 15s45ms 18 1 15s417ms 15s417ms -
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 18:00:54 Duration: 15s417ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 14:00:53 Duration: 15s45ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 06:00:53 Duration: 15s43ms
11 4 58s853ms 14s587ms 14s982ms 14s713ms copy edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 28 06 1 14s663ms 14s663ms 10 1 14s587ms 14s587ms 14 1 14s619ms 14s619ms 18 1 14s982ms 14s982ms -
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 18:01:10 Duration: 14s982ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 06:01:08 Duration: 14s663ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 14:01:08 Duration: 14s619ms
12 4 30s259ms 7s524ms 7s629ms 7s564ms copy edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 28 06 1 7s567ms 7s567ms 10 1 7s538ms 7s538ms 14 1 7s524ms 7s524ms 18 1 7s629ms 7s629ms -
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 18:00:32 Duration: 7s629ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 06:00:31 Duration: 7s567ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 10:00:32 Duration: 7s538ms
13 4 26s279ms 6s528ms 6s658ms 6s569ms copy edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Aug 28 06 1 6s528ms 6s528ms 10 1 6s537ms 6s537ms 14 1 6s555ms 6s555ms 18 1 6s658ms 6s658ms -
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 18:01:18 Duration: 6s658ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 14:01:16 Duration: 6s555ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 10:01:17 Duration: 6s537ms
14 4 25s116ms 6s229ms 6s361ms 6s279ms copy edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Aug 28 06 1 6s260ms 6s260ms 10 1 6s229ms 6s229ms 14 1 6s264ms 6s264ms 18 1 6s361ms 6s361ms -
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 18:00:39 Duration: 6s361ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 14:00:38 Duration: 6s264ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-08-28 06:00:38 Duration: 6s260ms
15 3 27s329ms 7s941ms 10s952ms 9s109ms vacuum analyze pub2.term;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Aug 28 06 2 18s894ms 9s447ms 07 1 8s434ms 8s434ms -
VACUUM ANALYZE pub2.TERM;
Date: 2026-08-28 06:42:58 Duration: 10s952ms Bind query: yes
-
VACUUM ANALYZE pub2.TERM;
Date: 2026-08-28 07:49:14 Duration: 8s434ms Bind query: yes
-
VACUUM ANALYZE pub2.TERM;
Date: 2026-08-28 06:47:20 Duration: 7s941ms Bind query: yes
16 3 24s989ms 8s140ms 8s606ms 8s329ms vacuum analyze pub2.reference;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Aug 28 03 1 8s606ms 8s606ms 06 1 8s242ms 8s242ms 07 1 8s140ms 8s140ms -
VACUUM ANALYZE pub2.REFERENCE;
Date: 2026-08-28 03:55:27 Duration: 8s606ms Bind query: yes
-
VACUUM ANALYZE pub2.REFERENCE;
Date: 2026-08-28 06:48:03 Duration: 8s242ms Bind query: yes
-
VACUUM ANALYZE pub2.REFERENCE;
Date: 2026-08-28 07:49:29 Duration: 8s140ms Bind query: yes
17 3 19s280ms 6s322ms 6s616ms 6s426ms 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 #17
Day Hour Count Duration Avg duration Aug 28 05 2 12s957ms 6s478ms 13 1 6s322ms 6s322ms [ User: qaeu - Total duration: 12s664ms - Times executed: 2 ]
[ User: pubeu - Total duration: 6s616ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1403103)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-08-28 05:48:47 Duration: 6s616ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1403103)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-08-28 05:44:50 Duration: 6s341ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-08-28 13:27:38 Duration: 6s322ms Database: ctdprd51 User: qaeu Bind query: yes
18 2 55s931ms 12s854ms 43s76ms 27s965ms select t.nm "GeneSymbol", t.secondary_nm "GeneName", t.acc_txt "GeneID", ( select string_agg(distinct l.acc_txt, ? order by l.acc_txt) from db_link l where l.object_type_id = t.object_type_id and l.object_id = t.id and l.type_cd = ? and l.is_primary = false) "AltGeneIDs", ( select string_agg(distinct tl.nm, ? order by tl.nm) from term_label tl inner join term_label_type tlt on tl.term_label_type_id = tlt.id where tl.term_id = t.id and tlt.nm = ?) "Synonyms", ( select string_agg(l.acc_txt, ? order by l.acc_txt) from db_link l inner join db d on l.db_id = d.id where l.object_id = t.id and d.cd = ? and l.type_cd = ?) "BioGRIDIDs", ( select string_agg(l.acc_txt, ? order by l.acc_txt) from db_link l inner join db d on l.db_id = d.id where l.object_id = t.id and d.cd = ? and l.type_cd = ?) "PharmGKBIDs", ( select string_agg(l.acc_txt, ? order by l.acc_txt) from db_link l inner join db d on l.db_id = d.id where l.object_id = t.id and d.cd = ? and l.type_cd = ?) "UniProtIDs" from term t where t.object_type_id = ( select ot.id from object_type ot where ot.cd = ?) order by t.nm_sort;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 28 13 2 55s931ms 27s965ms [ User: qaeu - Total duration: 43s76ms - Times executed: 1 ]
-
SELECT /* AllGenesDAO */ t.nm "GeneSymbol", t.secondary_nm "GeneName", t.acc_txt "GeneID", ( SELECT STRING_AGG(DISTINCT l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l WHERE l.object_type_id = t.object_type_id AND l.object_id = t.id AND l.type_cd = 'A' AND l.is_primary = false) "AltGeneIDs", ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = t.id AND tlt.nm = 'SYNONYM') "Synonyms", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'BIOGRID' AND l.type_cd = 'X') "BioGRIDIDs", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'PGKB' AND l.type_cd = 'X') "PharmGKBIDs", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'SPTREM' AND l.type_cd = 'X') "UniProtIDs" FROM term t WHERE t.object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'gene') ORDER BY t.nm_sort;
Date: 2026-08-28 13:36:17 Duration: 43s76ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* AllGenesDAO */ t.nm "GeneSymbol", t.secondary_nm "GeneName", t.acc_txt "GeneID", ( SELECT STRING_AGG(DISTINCT l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l WHERE l.object_type_id = t.object_type_id AND l.object_id = t.id AND l.type_cd = 'A' AND l.is_primary = false) "AltGeneIDs", ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = t.id AND tlt.nm = 'SYNONYM') "Synonyms", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'BIOGRID' AND l.type_cd = 'X') "BioGRIDIDs", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'PGKB' AND l.type_cd = 'X') "PharmGKBIDs", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'SPTREM' AND l.type_cd = 'X') "UniProtIDs" FROM term t WHERE t.object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'gene') ORDER BY t.nm_sort;
Date: 2026-08-28 13:36:44 Duration: 12s854ms Bind query: yes
19 2 17s394ms 6s128ms 11s265ms 8s697ms select g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, i.id ixnid, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct r.acc_txt, ?)) as references, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id inner join reference r on gcr.reference_id = r.id left outer join term taxonterm on gcr.taxon_id = taxonterm.id where gcr.chem_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by g.nm_sort, c.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 28 07 1 11s265ms 11s265ms 14 1 6s128ms 6s128ms [ User: pubeu - Total duration: 17s394ms - Times executed: 2 ]
-
SELECT /* ChemGeneIxnsDAO */ g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, i.id ixnId, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1500966') GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2026-08-28 07:53:04 Duration: 11s265ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGeneIxnsDAO */ g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, i.id ixnId, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1367816') GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2026-08-28 14:28:11 Duration: 6s128ms Database: ctdprd51 User: pubeu Bind query: yes
20 2 12s580ms 5s533ms 7s47ms 6s290ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort asc, pt.indirect_term_qty desc limit ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 28 05 1 7s47ms 7s47ms 13 1 5s533ms 5s533ms [ User: qaeu - Total duration: 7s47ms - 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 asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2026-08-28 05:45:19 Duration: 7s47ms Database: ctdprd51 User: qaeu 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 asc, pt.indirect_term_qty desc LIMIT 50;
Date: 2026-08-28 13:28:12 Duration: 5s533ms Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 2h47m20s 2h47m20s 2h47m20s 1 2h47m20s select maint_term_derive_nm_fts ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 28 06 1 2h47m20s 2h47m20s -
SELECT maint_term_derive_nm_fts ();
Date: 2026-08-28 06:42:47 Duration: 2h47m20s Bind query: yes
2 2h26m30s 2h26m30s 2h26m30s 1 2h26m30s select pub2.maint_term_derive_data ();Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Aug 28 10 1 2h26m30s 2h26m30s -
select pub2.maint_term_derive_data ();
Date: 2026-08-28 10:16:00 Duration: 2h26m30s Bind query: yes
3 1h58m38s 1h58m38s 1h58m38s 1 1h58m38s select pub2.maint_gene_chem_ref_gene_form_refresh ();Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 28 03 1 1h58m38s 1h58m38s -
select pub2.maint_gene_chem_ref_gene_form_refresh ();
Date: 2026-08-28 03:52:24 Duration: 1h58m38s Bind query: yes
4 58m58s 58m58s 58m58s 1 58m58s vacuum full analyze;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 28 07 1 58m58s 58m58s -
VACUUM FULL ANALYZE;
Date: 2026-08-28 07:49:05 Duration: 58m58s Bind query: yes
-
VACUUM FULL ANALYZE;
Date: 2026-08-28 06:50:10 Duration: 0ms
5 39m36s 39m36s 39m36s 1 39m36s select pub2.maint_cached_value_refresh_data_metrics ();Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 28 11 1 39m36s 39m36s -
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-08-28 11:06:17 Duration: 39m36s Bind query: yes
-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-08-28 11:01:20 Duration: 0ms
6 27m19s 27m19s 27m19s 1 27m19s update pub2.gene_disease gd set exposure_reference_qty = ( select count(distinct reference_id) from pub2.gene_disease_reference gdr where gd.gene_id = gdr.gene_id and gd.disease_id = gdr.disease_id and reference_id in ( select id from pub2.reference r where has_exposures = true));Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 28 01 1 27m19s 27m19s -
update pub2.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub2.GENE_DISEASE_REFERENCE gdr where gd.gene_id = gdr.gene_id and gd.disease_id = gdr.disease_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));
Date: 2026-08-28 01:47:46 Duration: 27m19s Bind query: yes
7 13m19s 13m19s 13m19s 1 13m19s 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 28 00 1 13m19s 13m19s -
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: 2026-08-28 00:37:23 Duration: 13m19s Bind query: yes
8 9m27s 9m27s 9m27s 1 9m27s select maint_query_logs_archive ();Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Aug 28 00 1 9m27s 9m27s [ User: pubc - Total duration: 9m27s - Times executed: 1 ]
[ Application: psql - Total duration: 9m27s - Times executed: 1 ]
-
/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();
Date: 2026-08-28 00:09:29 Duration: 9m27s Database: ctdprd51 User: pubc Application: psql
9 9m23s 9m23s 9m23s 1 9m23s select pub2.maint_phenotype_term_derive_data ();Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 28 10 1 9m23s 9m23s -
select pub2.maint_phenotype_term_derive_data ();
Date: 2026-08-28 10:26:41 Duration: 9m23s Bind query: yes
10 5s152ms 40m25s 4m7s 10 41m18s select g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(r.acc_txt, ? order by r.acc_txt) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id group by g.nm, g.acc_txt, d.nm, d.acc_db_cd || ? || d.acc_txt, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by g.nm, d.nm;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Aug 28 14 6 40m55s 6m49s 15 4 22s760ms 5s690ms [ User: qaeu - Total duration: 40m25s - Times executed: 1 ]
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:33:49 Duration: 40m25s Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:48:45 Duration: 7s277ms Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2026-08-28 14:56:03 Duration: 6s434ms Bind query: yes
11 3m28s 3m28s 3m28s 1 3m28s 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 28 00 1 3m28s 3m28s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-08-28 00:40:51 Duration: 3m28s 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: 2026-08-28 00:40:50 Duration: 0ms
12 3m23s 3m23s 3m23s 1 3m23s select maint_term_label_derive_nm_fts ();Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 28 06 1 3m23s 3m23s -
SELECT maint_term_label_derive_nm_fts ();
Date: 2026-08-28 06:46:22 Duration: 3m23s Bind query: yes
13 2m53s 2m53s 2m53s 1 2m53s insert into pub2.term_reference (term_id, object_type_id, reference_id, ixn_type_id) select distinct term_id, object_type_id, reference_id, ixn_type_id from ( select gene_id as term_id, ( select object_type_id from pub2.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_chem_reference union select chem_id as term_id, ( select object_type_id from pub2.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_chem_reference union select taxon_id as term_id, ( select object_type_id from pub2.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_chem_reference where taxon_id is not null union select chem_id as term_id, ( select object_type_id from pub2.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.chem_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub2.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.chem_disease_reference where source_cd = ? union select gene_id as term_id, ( select object_type_id from pub2.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub2.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_disease_reference where source_cd = ? union select ee.exp_marker_term_id as term_id, ( select object_type_id from term where id = exp_marker_term_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_event ee where e.exp_event_id = ee.id and exp_marker_term_id is not null union select er.term_id, ( select object_type_id from term where id = er.term_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_receptor er where e.exp_receptor_id = er.id and er.term_id is not null union select chem_id as term_id, ( select object_type_id from term where id = chem_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_stressor es where e.exp_stressor_id = es.id and chem_id is not null union select phenotype_id as term_id, ( select object_type_id from term where id = phenotype_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_outcome eo where e.exp_outcome_id = eo.id and phenotype_id is not null union select disease_id as term_id, ( select object_type_id from term where id = disease_id), e.reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.exposure e, pub2.exp_outcome eo where e.exp_outcome_id = eo.id and disease_id is not null union select phenotype_id as term_id, ( select object_type_id from pub2.term where id = phenotype_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.phenotype_term_reference where source_cd = ? union select term_id, ( select object_type_id from pub2.term where id = term_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.phenotype_term_reference where source_cd = ? union select taxon_id as term_id, ( select object_type_id from pub2.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.phenotype_term_reference where source_cd = ? and taxon_id is not null union select from_gene_id as term_id, ( select object_type_id from pub2.term where id = from_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_gene_reference union select to_gene_id as term_id, ( select object_type_id from pub2.term where id = to_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_gene_reference union select from_taxon_id as term_id, ( select object_type_id from pub2.term where id = from_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_gene_reference union select to_taxon_id as term_id, ( select object_type_id from pub2.term where id = to_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub2.gene_gene_reference union select distinct t.id as taxon_id, t.object_type_id as taxon_object_id, r.id as reference_id, i.ixn_type_id as ixn_type_id from edit.reference_ixn ri, pub2.term t, edit.ixn i, pub2.reference r where ri.taxon_acc_txt = t.acc_txt and t.object_type_id = ( select id from pub2.object_type where cd = ?) and ri.ixn_id = i.root_id and i.ixn_type_id in ( select id from edit.ixn_type where nm in (...)) and ri.reference_acc_txt = r.acc_txt and ri.taxon_acc_txt is not null and ri.taxon_acc_txt <> ?) as test union select ea.anatomy_id as term_id, ( select id from object_type where cd = ?) as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = ?) as ixn_type_id from pub2.exp_anatomy ea, pub2.exp_outcome eo, pub2.exposure e, pub2.reference r where ea.exp_outcome_id = eo.id and eo.id = e.exp_outcome_id and e.reference_acc_txt = r.acc_txt union select anatomy_id as term_id, ( select id from object_type where cd = ?) as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = ?) as ixn_type_id from pub2.ixn i, pub2.ixn_anatomy ia, edit.reference_ixn ri, pub2.reference r where i.id = ri.ixn_id and ri.reference_acc_txt = r.acc_txt and i.id = ia.ixn_id union select medium_term_id as term_id, ( select id from object_type where cd = ?) as object_type_id, r.id, ( select id from ixn_type where nm = ?) as ixn_type_id from pub2.exp_event ee, pub2.exposure e, pub2.reference r where ee.id = e.exp_event_id and e.reference_acc_txt = r.acc_txt and medium_term_id is not null;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Aug 28 03 1 2m53s 2m53s -
INSERT INTO pub2.TERM_REFERENCE (term_id, object_type_id, reference_id, ixn_type_id) SELECT DISTINCT term_id, object_type_id, reference_id, ixn_type_id FROM ( SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT ee.exp_marker_term_id as term_id, ( SELECT object_type_id FROM term WHERE id = exp_marker_term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_EVENT ee WHERE e.exp_event_id = ee.id AND exp_marker_term_id IS NOT NULL UNION SELECT er.term_id, ( SELECT object_type_id FROM term WHERE id = er.term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_RECEPTOR er WHERE e.exp_receptor_id = er.id AND er.term_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM term WHERE id = chem_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_STRESSOR es WHERE e.exp_stressor_id = es.id AND chem_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM term WHERE id = phenotype_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND phenotype_id IS NOT NULL UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM term WHERE id = disease_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND disease_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' AND taxon_id IS NOT NULL UNION SELECT from_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT DISTINCT t.id as taxon_id, t.object_type_id as taxon_object_id, r.id as reference_id, i.ixn_type_id as ixn_type_id FROM edit.REFERENCE_IXN ri, pub2.TERM t, edit.IXN i, pub2.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub2.OBJECT_TYPE WHERE cd = 'taxon') AND ri.ixn_id = i.root_id AND i.ixn_type_id in ( SELECT id FROM edit.IXN_TYPE WHERE nm in ('CHEMICAL-DISEASE', 'GENE-DISEASE')) AND ri.reference_acc_txt = r.acc_txt AND ri.taxon_acc_txt IS NOT NULL AND ri.taxon_acc_txt <> '') as test UNION select ea.anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_ANATOMY ea, pub2.EXP_OUTCOME eo, pub2.EXPOSURE e, pub2.REFERENCE r where ea.exp_outcome_id = eo.id and eo.id = e.exp_outcome_id and e.reference_acc_txt = r.acc_txt UNION select anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'PHENOTYPE') as ixn_type_id from pub2.IXN i, pub2.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub2.REFERENCE r where i.id = ri.ixn_id and ri.reference_acc_txt = r.acc_txt and i.id = ia.ixn_id UNION select medium_term_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_EVENT ee, pub2.EXPOSURE e, pub2.REFERENCE r where ee.id = e.exp_event_id and e.reference_acc_txt = r.acc_txt and medium_term_id is not null;
Date: 2026-08-28 03:55:18 Duration: 2m53s Bind query: yes
14 2m32s 2m32s 2m32s 1 2m32s update pub2.phenotype_term pt set exposure_reference_qty = ( select count(distinct reference_id) from pub2.phenotype_term_reference ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and reference_id in ( select id from pub2.reference r where has_exposures = true));Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Aug 28 01 1 2m32s 2m32s -
update pub2.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub2.PHENOTYPE_TERM_REFERENCE ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true));
Date: 2026-08-28 01:50:19 Duration: 2m32s Bind query: yes
15 2m13s 2m13s 2m13s 1 2m13s update pub2.term set has_exposures = false;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Aug 28 01 1 2m13s 2m13s -
update pub2.TERM set has_exposures = false;
Date: 2026-08-28 01:17:02 Duration: 2m13s Bind query: yes
16 2m10s 2m10s 2m10s 1 2m10s select chemterm.nm chemicalname, chemterm.acc_txt chemicalid, chemterm.secondary_nm casrn, phenoterm.nm phenotypename, phenoterm.acc_txt phenotypeid, ( select string_agg(distinct comentionterm.nm || ? || comentionterm.acc_txt || ? || comentionterm.acc_db_cd, ?)) as comentionedterms, taxonterm.nm organism, taxonterm.acc_txt organismid, i.ixn_prose_txt interaction, i.actions_txt interactionactions, ( select string_agg(distinct ia.level_seq + ? || ? || anatomyterm.nm || ? || anatomyterm.acc_txt, ? order by ia.level_seq + ? || ? || anatomyterm.nm || ? || anatomyterm.acc_txt)) as anatomyterms, string_agg(distinct inferredterm.nm || ? || inferredterm.acc_txt || ? || inferredterm.acc_db_cd, ? order by inferredterm.nm || ? || inferredterm.acc_txt || ? || inferredterm.acc_db_cd) inferencegenesymbols, string_agg(distinct r.acc_txt, ? order by r.acc_txt) pubmedids, ptr.ixn_id ignorecolumnixnid from phenotype_term_reference ptr left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join ixn i on ptr.ixn_id = i.id inner join term phenoterm on ptr.phenotype_id = phenoterm.id inner join reference r on ptr.reference_id = r.id inner join term chemterm on ptr.term_id = chemterm.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 left outer join phenotype_term_reference ptr2 on ptr.ixn_id = ptr2.ixn_id and (ptr.term_id <> ptr2.term_id or ptr.phenotype_id <> ptr2.phenotype_id) left outer join phenotype_term_reference ptr3 on ptr.term_id = ptr3.term_id and (ptr.phenotype_id = ptr3.phenotype_id and ptr3.source_cd = ?) left outer join term comentionterm on comentionterm.id = ptr2.term_id left outer join term inferredterm on inferredterm.id = ptr3.via_term_id where ptr.source_cd = ? and ptr.term_object_type_id = ( select ot.id from object_type ot where ot.cd = ?) group by chemterm.nm, chemterm.acc_txt, chemterm.secondary_nm, phenoterm.nm, phenoterm.acc_txt, taxonterm.nm, taxonterm.acc_txt, i.ixn_prose_txt, i.actions_txt, ptr.ixn_id order by chemterm.nm, phenoterm.nm;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Aug 28 15 1 2m10s 2m10s [ User: qaeu - Total duration: 2m10s - Times executed: 1 ]
-
SELECT /* AllCuratedChemPhenoIxnsDAO */ chemTerm.nm ChemicalName, chemTerm.acc_txt ChemicalID, chemTerm.secondary_nm CasRN, phenoTerm.nm PhenotypeName, phenoTerm.acc_txt PhenotypeID, ( SELECT STRING_AGG(distinct coMentionTerm.nm || '^' || coMentionTerm.acc_txt || '^' || coMentionTerm.acc_db_cd, '|')) as coMentionedTerms, taxonTerm.nm Organism, taxonTerm.acc_txt OrganismID, i.ixn_prose_txt Interaction, i.actions_txt InteractionActions, ( SELECT STRING_AGG(distinct ia.level_seq + 1 || '^' || anatomyTerm.nm || '^' || anatomyTerm.acc_txt, '|' ORDER BY ia.level_seq + 1 || '^' || anatomyTerm.nm || '^' || anatomyTerm.acc_txt)) as anatomyTerms, STRING_AGG(distinct inferredTerm.nm || '^' || inferredTerm.acc_txt || '^' || inferredTerm.acc_db_cd, '|' ORDER BY inferredTerm.nm || '^' || inferredTerm.acc_txt || '^' || inferredTerm.acc_db_cd) InferenceGeneSymbols, STRING_AGG(distinct r.acc_txt, '|' ORDER BY r.acc_txt) PubMedIDs, ptr.ixn_id ignorecolumnIxnId FROM phenotype_term_reference ptr LEFT OUTER JOIN term taxonTerm ON ptr.taxon_id = taxonTerm.id INNER JOIN ixn i ON ptr.ixn_id = i.id INNER JOIN term phenoTerm ON ptr.phenotype_id = phenoTerm.id INNER JOIN reference r ON ptr.reference_id = r.id INNER JOIN term chemTerm ON ptr.term_id = chemTerm.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 LEFT OUTER JOIN phenotype_term_reference ptr2 ON ptr.ixn_id = ptr2.ixn_id and (ptr.term_id <> ptr2.term_id or ptr.phenotype_id <> ptr2.phenotype_id) LEFT OUTER JOIN phenotype_term_reference ptr3 ON ptr.term_id = ptr3.term_id and (ptr.phenotype_id = ptr3.phenotype_id and ptr3.source_cd = 'I') LEFT OUTER JOIN term coMentionTerm ON coMentionTerm.id = ptr2.term_id LEFT OUTER JOIN term inferredTerm ON inferredTerm.id = ptr3.via_term_id WHERE ptr.source_cd = 'C' AND ptr.term_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'chem') GROUP BY chemTerm.nm, chemTerm.acc_txt, chemTerm.secondary_nm, phenoTerm.nm, phenoTerm.acc_txt, taxonTerm.nm, taxonTerm.acc_txt, i.ixn_prose_txt, i.actions_txt, ptr.ixn_id ORDER BY chemTerm.nm, phenoTerm.nm;
Date: 2026-08-28 15:47:19 Duration: 2m10s Database: ctdprd51 User: qaeu Bind query: yes
17 1m52s 1m55s 1m54s 4 7m36s copy pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) to stdout;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Aug 28 06 1 1m53s 1m53s 10 1 1m52s 1m52s 14 1 1m53s 1m53s 18 1 1m55s 1m55s [ User: postgres - Total duration: 7m36s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m36s - Times executed: 4 ]
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 18:06:57 Duration: 1m55s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 06:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;
Date: 2026-08-28 14:06:55 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
18 8s281ms 3m 56s245ms 4 3m44s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 28 00 3 3m24s 1m8s 01 1 20s397ms 20s397ms [ User: load - Total duration: 3m44s - Times executed: 4 ]
[ Application: pg_bulkload - Total duration: 3m44s - Times executed: 4 ]
-
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: 2026-08-28 00:21:11 Duration: 3m Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub2.GENE_DISEASE,TYPE=CSV,DELIMITER=|,"ESCAPE=\\",PARSE_ERRORS=0,DUPLICATE_ERRORS=0,OFFSET=1,VERBOSE=true,infile=stdin,logfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.log,parse-badfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/aggregator/geneDisease.txt.DUPE}');
Date: 2026-08-28 01:12:48 Duration: 20s397ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
-
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/pathwayEnrichment/enrichedTermAgent.txt.log,parse-badfile=/home/load/ctdLoadData/pub/pathwayEnrichment/enrichedTermAgent.txt.BAD,duplicate-badfile=/home/load/ctdLoadData/pub/pathwayEnrichment/enrichedTermAgent.txt.DUPE}');
Date: 2026-08-28 00:23:46 Duration: 15s616ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
19 5s420ms 2m37s 33s54ms 6 3m18s select phenotypeterm.nm "GOName", phenotypeterm.acc_txt "GOID", diseaseterm.nm "DiseaseName", diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( select string_agg(distinct chemtermnetwork.nm, ?)) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( select string_agg(distinct genetermnetwork.nm, ?)) "InferenceGeneSymbols" from phenotype_term_reference ptr inner join phenotype_term pt on ptr.phenotype_id = pt.phenotype_id and ptr.term_id = pt.term_id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id inner join term diseaseterm on ptr.term_id = diseaseterm.id and diseaseterm.object_type_id = ( select id from object_type where cd = ?) left outer join term genetermnetwork on ptr.via_term_id = genetermnetwork.id and genetermnetwork.object_type_id = ( select id from object_type where cd = ?) left outer join term chemtermnetwork on ptr.via_term_id = chemtermnetwork.id and chemtermnetwork.object_type_id = ( select id from object_type where cd = ?) where phenotypeterm.id in ( select dp.descendant_dag_node_id from dag_path dp where dp.ancestor_object_id = ( select id from term where nm = ?)) and ptr.source_cd = ? and ptr.phenotype_id = phenotypeterm.id and ptr.term_id = diseaseterm.id group by phenotypeterm.nm, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.acc_db_cd || ? || diseaseterm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 28 15 6 3m18s 33s54ms [ User: qaeu - Total duration: 2m51s - Times executed: 2 ]
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:50:38 Duration: 2m37s Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'molecular_function')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:52:46 Duration: 13s532ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* PhenotypeDiseasesDAO */ phenotypeTerm.nm "GOName", phenotypeTerm.acc_txt "GOID", diseaseTerm.nm "DiseaseName", diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt "DiseaseID", pt.via_chem_qty "InferenceChemicalQty", ( SELECT STRING_AGG(DISTINCT chemTermNetwork.nm, '|')) "InferenceChemicalNames", pt.via_gene_qty "InferenceGeneQty", ( SELECT STRING_AGG(DISTINCT geneTermNetwork.nm, '|')) "InferenceGeneSymbols" FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt on ptr.phenotype_id = pt.phenotype_id AND ptr.term_id = pt.term_id INNER JOIN term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id INNER JOIN term diseaseTerm on ptr.term_id = diseaseTerm.id AND diseaseTerm.object_type_id = ( select id from object_type where cd = 'disease') LEFT OUTER JOIN term geneTermNetwork on ptr.via_term_id = geneTermNetwork.id AND geneTermNetwork.object_type_id = ( select id from object_type where cd = 'gene') LEFT OUTER JOIN term chemTermNetwork on ptr.via_term_id = chemTermNetwork.id AND chemTermNetwork.object_type_id = ( select id from object_type where cd = 'chem') WHERE phenotypeTerm.id IN ( SELECT dp.descendant_dag_node_id FROM dag_path dp WHERE dp.ancestor_object_id = ( select id from term where nm = 'biological_process')) AND ptr.source_cd = 'I' AND ptr.phenotype_id = phenotypeTerm.id AND ptr.term_id = diseaseTerm.id GROUP BY phenotypeTerm.nm, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.acc_db_cd || ':' || diseaseTerm.acc_txt, pt.via_chem_qty, pt.via_gene_qty;
Date: 2026-08-28 15:50:53 Duration: 8s419ms Bind query: yes
20 12s854ms 43s76ms 27s965ms 2 55s931ms select t.nm "GeneSymbol", t.secondary_nm "GeneName", t.acc_txt "GeneID", ( select string_agg(distinct l.acc_txt, ? order by l.acc_txt) from db_link l where l.object_type_id = t.object_type_id and l.object_id = t.id and l.type_cd = ? and l.is_primary = false) "AltGeneIDs", ( select string_agg(distinct tl.nm, ? order by tl.nm) from term_label tl inner join term_label_type tlt on tl.term_label_type_id = tlt.id where tl.term_id = t.id and tlt.nm = ?) "Synonyms", ( select string_agg(l.acc_txt, ? order by l.acc_txt) from db_link l inner join db d on l.db_id = d.id where l.object_id = t.id and d.cd = ? and l.type_cd = ?) "BioGRIDIDs", ( select string_agg(l.acc_txt, ? order by l.acc_txt) from db_link l inner join db d on l.db_id = d.id where l.object_id = t.id and d.cd = ? and l.type_cd = ?) "PharmGKBIDs", ( select string_agg(l.acc_txt, ? order by l.acc_txt) from db_link l inner join db d on l.db_id = d.id where l.object_id = t.id and d.cd = ? and l.type_cd = ?) "UniProtIDs" from term t where t.object_type_id = ( select ot.id from object_type ot where ot.cd = ?) order by t.nm_sort;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 28 13 2 55s931ms 27s965ms [ User: qaeu - Total duration: 43s76ms - Times executed: 1 ]
-
SELECT /* AllGenesDAO */ t.nm "GeneSymbol", t.secondary_nm "GeneName", t.acc_txt "GeneID", ( SELECT STRING_AGG(DISTINCT l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l WHERE l.object_type_id = t.object_type_id AND l.object_id = t.id AND l.type_cd = 'A' AND l.is_primary = false) "AltGeneIDs", ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = t.id AND tlt.nm = 'SYNONYM') "Synonyms", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'BIOGRID' AND l.type_cd = 'X') "BioGRIDIDs", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'PGKB' AND l.type_cd = 'X') "PharmGKBIDs", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'SPTREM' AND l.type_cd = 'X') "UniProtIDs" FROM term t WHERE t.object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'gene') ORDER BY t.nm_sort;
Date: 2026-08-28 13:36:17 Duration: 43s76ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* AllGenesDAO */ t.nm "GeneSymbol", t.secondary_nm "GeneName", t.acc_txt "GeneID", ( SELECT STRING_AGG(DISTINCT l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l WHERE l.object_type_id = t.object_type_id AND l.object_id = t.id AND l.type_cd = 'A' AND l.is_primary = false) "AltGeneIDs", ( SELECT STRING_AGG(DISTINCT tl.nm, '|' ORDER BY tl.nm) FROM term_label tl INNER JOIN term_label_type tlt ON tl.term_label_type_id = tlt.id WHERE tl.term_id = t.id AND tlt.nm = 'SYNONYM') "Synonyms", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'BIOGRID' AND l.type_cd = 'X') "BioGRIDIDs", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'PGKB' AND l.type_cd = 'X') "PharmGKBIDs", ( SELECT STRING_AGG(l.acc_txt, '|' ORDER BY l.acc_txt) FROM db_link l INNER JOIN db d on l.db_id = d.id WHERE l.object_id = t.id AND d.cd = 'SPTREM' AND l.type_cd = 'X') "UniProtIDs" FROM term t WHERE t.object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'gene') ORDER BY t.nm_sort;
Date: 2026-08-28 13:36:44 Duration: 12s854ms 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
- 12,669 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 1 FATAL entries
- 2 ERROR entries
- 1342 WARNING entries
- 37 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 1,069 Max number of times the same event was reported
- 1,382 Total events found
Rank Times reported Error 1 1,069 WARNING: skipping "..." --- only table or database owner can vacuum it
Times Reported Most Frequent Error / Event #1
Day Hour Count Aug 28 06 1,069 - WARNING: skipping "pg_toast_12202138" --- only table or database owner can vacuum it
- WARNING: skipping "pg_toast_12202138_index" --- only table or database owner can vacuum it
- WARNING: skipping "age_qualifer_pk" --- only table or database owner can vacuum it
Date: 2026-08-28 06:50:07
Date: 2026-08-28 06:50:07
Date: 2026-08-28 06:50:07
2 224 WARNING: skipping "..." --- only superuser or database owner can vacuum it
Times Reported Most Frequent Error / Event #2
Day Hour Count Aug 28 06 224 - WARNING: skipping "pg_statistic" --- only superuser or database owner can vacuum it
- WARNING: skipping "pg_type" --- only superuser or database owner can vacuum it
- WARNING: skipping "pg_foreign_table" --- only superuser or database owner can vacuum it
Date: 2026-08-28 06:50:07
Date: 2026-08-28 06:50:07
Date: 2026-08-28 06:50:07
3 43 WARNING: skipping "..." --- only superuser can vacuum it
Times Reported Most Frequent Error / Event #3
Day Hour Count Aug 28 06 43 - WARNING: skipping "pg_toast_1262" --- only superuser can vacuum it
- WARNING: skipping "pg_toast_1262_index" --- only superuser can vacuum it
- WARNING: skipping "pg_toast_2964" --- only superuser can vacuum it
Date: 2026-08-28 06:50:07
Date: 2026-08-28 06:50:07
Date: 2026-08-28 06:50:07
4 26 ERROR: unexpected EOF on client connection with an open transaction
Times Reported Most Frequent Error / Event #4
Day Hour Count Aug 28 13 17 15 9 - ERROR: unexpected EOF on client connection with an open transaction
- ERROR: unexpected EOF on client connection with an open transaction
- ERROR: unexpected EOF on client connection with an open transaction
Date: 2026-08-28 13:34:04
Date: 2026-08-28 13:35:04
Date: 2026-08-28 13:35:05 Database: ctdprd51 Application: User: qaeu Remote:
5 9 LOG: could not receive data from client: Connection timed out
Times Reported Most Frequent Error / Event #5
Day Hour Count Aug 28 16 1 17 1 18 3 19 4 - LOG: could not receive data from client: Connection timed out
- LOG: could not receive data from client: Connection timed out
- LOG: could not receive data from client: Connection timed out
Date: 2026-08-28 16:04:30
Date: 2026-08-28 17:31:52
Date: 2026-08-28 18:24:18
6 6 WARNING: there is no transaction in progress
Times Reported Most Frequent Error / Event #6
Day Hour Count Aug 28 06 2 10 4 - WARNING: there is no transaction in progress
- WARNING: there is no transaction in progress
- WARNING: there is no transaction in progress
Date: 2026-08-28 06:42:47
Date: 2026-08-28 06:46:22
Date: 2026-08-28 10:16:00
7 1 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #7
Day Hour Count Aug 28 15 1 - ERROR: syntax error at or near "from" at character 1
Statement: from pubc.log_query where query_tm >= '20260826' --and query_tm <= '20260826 04:00:00' --and http_user_agent not like '%CTD%' and remote_addr not in ('152.7.178.45','152.7.178.53') --order by results_qty desc, query_tm desc, remote_addr order by results_qty desc, remote_addr, query_tm desc limit 100
Date: 2026-08-28 15:15:19
8 1 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #8
Day Hour Count Aug 28 00 1 - ERROR: canceling statement due to user request
Statement: SELECT pg_database_size(datname::text) FROM pg_catalog.pg_database WHERE datistemplate = false AND datname = $1;
Date: 2026-08-28 00:44:08 Database: ctdprd51 Application: User: zbx_monitor Remote:
9 1 LOG: could not send data to client: Broken pipe
Times Reported Most Frequent Error / Event #9
Day Hour Count Aug 28 00 1 - LOG: could not send data to client: Broken pipe
Date: 2026-08-28 00:44:08
10 1 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #10
Day Hour Count Aug 28 00 1 - FATAL: connection to client lost
Date: 2026-08-28 00:44:08
11 1 LOG: process ... still waiting for AccessShareLock on relation ... of database ... after ... ms
Times Reported Most Frequent Error / Event #11
Day Hour Count Aug 28 16 1 - LOG: process 2251468 still waiting for AccessShareLock on relation 12200782 of database 484829 after 1000.074 ms at character 548
Detail: Process holding the lock: 2251724. Wait queue: 2251468.
Statement: SELECT /* MeshBasicQueryDAO */ sq.* ,COUNT(*) OVER() fullRowCount FROM ( SELECT /* label */ t.acc_txt acc ,'name:' || t.nm accQueryStr ,t.nm ,t.nm_html nmHtml ,t.secondary_nm casRN ,l.nm matchedNm ,lt.nm_display matchedType ,CASE WHEN lt.nm_display='Name' THEN true ELSE false END isNameMatch ,t.has_genes hasGenes ,t.has_chems hasChems ,t.has_diseases hasDiseases ,t.has_phenotypes hasPhenotypes ,CASE WHEN UPPER(l.nm) = $1 THEN 1 ELSE 2 END relevance ,t.nm_sort ,t.id ,t.acc_db_cd accdbcd ,t.has_exposures hasExposures FROM term t INNER JOIN term_label l ON l.term_id = t.id INNER JOIN term_label_type lt ON l.term_label_type_id = lt.id WHERE t.object_type_id = 2 AND l.object_type_id = 2 AND l.id IN( SELECT FIRST_VALUE(i.id) OVER(PARTITION BY i.term_id ORDER BY it.priority_seq, i.nm) FROM term_label i INNER JOIN term_label_type it ON i.term_label_type_id = it.id WHERE i.object_type_id = 2 AND i.nm_fts @@ to_tsquery('common.english_nostops', $2) ) UNION ALL SELECT /* term acc */ t.acc_txt acc ,'name:' || t.nm accQueryStr ,t.nm ,t.nm_html nmHtml ,t.secondary_nm casRN ,l.acc_txt matchednm ,'Accession' matchedtype ,false isNameMatch ,t.has_genes hasgenes ,t.has_chems haschems ,t.has_diseases hasdiseases ,t.has_phenotypes hasPhenotypes ,1 relevance ,t.nm_sort ,t.id ,t.acc_db_cd accdbcd ,t.has_exposures hasexposures FROM db_link l INNER JOIN term t ON l.object_id = t.id WHERE l.type_cd = 'A' AND l.object_type_id = 2 AND (upper( l.acc_txt ) = $3 ) ORDER BY 13,14 ) sq LIMIT 50Date: 2026-08-28 16:53:01 Database: ctdprd51 Application: User: qaeu Remote: