pgBadger-
Global information
- Generated on Sun Jun 28 04:15:03 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260627
- Parsed 25,609 log entries in 2s
- Log start from 2026-06-27 00:00:01 to 2026-06-27 23:58:41
-
Overview
Global Stats
- 130 Number of unique normalized queries
- 163 Number of queries
- 11h42m27s Total query duration
- 2026-06-27 00:05:40 First query
- 2026-06-27 19:48:12 Last query
- 2 queries/s at 2026-06-27 04:49:59 Query peak
- 11h42m27s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 11h42m27s Execute total duration
- 1,352 Number of events
- 8 Number of unique normalized events
- 1,069 Max number of times the same event was reported
- 0 Number of cancellation
- 38 Total number of automatic vacuums
- 48 Total number of automatic analyzes
- 1,198 Number temporary file
- 1.00 GiB Max size of temporary file
- 182.18 MiB Average size of temporary file
- 2,143 Total number of sessions
- 180 sessions at 2026-06-27 03:17:42 Session peak
- 76d20h40m57s Total duration of sessions
- 51m38s Average duration of sessions
- 0 Average queries per session
- 19s667ms Average queries duration per session
- 51m19s Average idle time per session
- 2,138 Total number of connections
- 28 connections/s at 2026-06-27 03:16:42 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 2 queries/s Query Peak
- 2026-06-27 04:49:59 Date
SELECT Traffic
Key values
- 2 queries/s Query Peak
- 2026-06-27 04:49:59 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-06-27 19:47:57 Date
Queries duration
Key values
- 11h42m27s Total query duration
Prepared queries ratio
Key values
- 0.00 Ratio of bind vs prepare
- 0.00 % Ratio between prepared and "usual" statements
General Activity
↑ Back to the top of the General Activity tableDay Hour Count Min duration Max duration Avg duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jun 27 00 67 0ms 12m59s 40s333ms 2m6s 3m26s 12m59s 01 13 0ms 26m9s 2m53s 1m43s 2m47s 26m9s 02 0 0ms 0ms 0ms 0ms 0ms 0ms 03 3 0ms 1h54m3s 39m2s 0ms 0ms 1h54m3s 04 2 0ms 12s448ms 8s989ms 0ms 0ms 17s978ms 05 3 0ms 15s718ms 10s504ms 0ms 0ms 21s719ms 06 11 0ms 2h33m8s 14m34s 18s269ms 1m9s 2h33m19s 07 4 0ms 57m54s 14m34s 0ms 0ms 58m16s 08 0 0ms 0ms 0ms 0ms 0ms 0ms 09 4 0ms 2h17m26s 36m57s 21s611ms 9m9s 2h17m26s 10 1 0ms 38m59s 38m59s 0ms 0ms 38m59s 11 1 0ms 5s60ms 5s60ms 0ms 0ms 5s60ms 12 0 0ms 0ms 0ms 0ms 0ms 0ms 13 0 0ms 0ms 0ms 0ms 0ms 0ms 14 0 0ms 0ms 0ms 0ms 0ms 0ms 15 0 0ms 0ms 0ms 0ms 0ms 0ms 16 0 0ms 0ms 0ms 0ms 0ms 0ms 17 0 0ms 0ms 0ms 0ms 0ms 0ms 18 31 0ms 27m3s 1m33s 1m23s 7m13s 27m3s 19 23 0ms 27m48s 2m5s 1m30s 2m16s 28m32s 20 0 0ms 0ms 0ms 0ms 0ms 0ms 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) Jun 27 00 54 0 26s701ms 58s323ms 1m5s 9m25s 01 0 0 0ms 0ms 0ms 0ms 02 0 0 0ms 0ms 0ms 0ms 03 1 0 1h54m3s 0ms 0ms 1h54m3s 04 2 0 8s989ms 0ms 0ms 17s978ms 05 3 0 10s504ms 0ms 0ms 21s719ms 06 2 0 1h18m15s 0ms 0ms 3m22s 07 0 0 0ms 0ms 0ms 0ms 08 0 0 0ms 0ms 0ms 0ms 09 4 0 36m57s 0ms 21s611ms 2h17m26s 10 1 0 38m59s 0ms 0ms 38m59s 11 1 0 5s60ms 0ms 0ms 5s60ms 12 0 0 0ms 0ms 0ms 0ms 13 0 0 0ms 0ms 0ms 0ms 14 0 0 0ms 0ms 0ms 0ms 15 0 0 0ms 0ms 0ms 0ms 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 0 31 1m33s 59s539ms 1m23s 27m3s 19 0 23 2m5s 57s745ms 1m30s 28m32s 20 0 0 0ms 0ms 0ms 0ms 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) Jun 27 00 3 0 0 0 7s462ms 0ms 0ms 0ms 01 5 8 0 0 2m53s 0ms 32s227ms 2m26s 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 Jun 27 00 0 65 65.00 0.00% 01 0 13 13.00 0.00% 02 0 0 0.00 0.00% 03 0 3 3.00 0.00% 04 0 2 2.00 0.00% 05 0 3 3.00 0.00% 06 0 11 11.00 0.00% 07 0 4 4.00 0.00% 08 0 0 0.00 0.00% 09 0 4 4.00 0.00% 10 0 1 1.00 0.00% 11 0 1 1.00 0.00% 12 0 0 0.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 0 0.00 0.00% 17 0 0 0.00 0.00% 18 0 0 0.00 0.00% 19 0 0 0.00 0.00% 20 0 0 0.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 Jun 27 00 96 0.03/s 01 79 0.02/s 02 77 0.02/s 03 323 0.09/s 04 74 0.02/s 05 100 0.03/s 06 76 0.02/s 07 78 0.02/s 08 81 0.02/s 09 76 0.02/s 10 80 0.02/s 11 81 0.02/s 12 78 0.02/s 13 78 0.02/s 14 78 0.02/s 15 78 0.02/s 16 76 0.02/s 17 79 0.02/s 18 80 0.02/s 19 77 0.02/s 20 72 0.02/s 21 69 0.02/s 22 75 0.02/s 23 77 0.02/s Day Hour Count Average Duration Average idle time Jun 27 00 96 26m43s 26m15s 01 79 30m39s 30m10s 02 77 31m44s 31m44s 03 323 7m30s 7m9s 04 74 31m9s 31m9s 05 100 24m34s 24m34s 06 76 30m55s 28m48s 07 78 9h49m45s 9h49m 08 81 30m42s 30m42s 09 76 31m30s 29m33s 10 81 36m19s 35m50s 11 81 30m22s 30m22s 12 78 31m13s 31m13s 13 78 30m44s 30m44s 14 78 31m25s 31m25s 15 78 31m44s 31m44s 16 76 31m46s 31m46s 17 79 31m8s 31m8s 18 79 30m19s 29m42s 19 78 32m23s 31m46s 20 73 59m37s 59m37s 21 70 1h2m34s 1h2m34s 22 77 1h26m48s 1h26m48s 23 77 31m48s 31m48s -
Connections
Established Connections
Key values
- 28 connections Connection Peak
- 2026-06-27 03:16:42 Date
Connections per database
Key values
- ctdprd51 Main Database
- 2,138 connections Total
Connections per user
Key values
- pubeu Main User
- 2,138 connections Total
-
Sessions
Simultaneous sessions
Key values
- 180 sessions Session Peak
- 2026-06-27 03:17:42 Date
Histogram of session times
Key values
- 1,792 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,143 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,143 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 2,143 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 2,491,254 buffers Checkpoint Peak
- 2026-06-27 00:29:18 Date
- 1619.987 seconds Highest write time
- 0.921 seconds Sync time
Checkpoints Wal files
Key values
- 1,015 files Wal files usage Peak
- 2026-06-27 06:32:28 Date
Checkpoints distance
Key values
- 17,248.22 Mo Distance Peak
- 2026-06-27 07:02:41 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Jun 27 00 3,053,618 2,864.492s 0.848s 2,869.113s 01 2,270,818 1,980.732s 0.117s 1,985.616s 02 2,057,813 3,239.941s 0.007s 3,241.802s 03 22 2.29s 0.001s 2.295s 04 344,453 3,238.913s 0.008s 3,239.799s 05 273,399 3,239.186s 0.014s 3,239.773s 06 953,135 2,670.835s 4.085s 2,700.26s 07 570,396 308.476s 0.865s 322.005s 08 139,369 1,619.088s 0.001s 1,620.115s 09 623,780 3,411.986s 0.35s 3,413.68s 10 498,262 1,619.516s 0.002s 1,620.621s 11 236,717 1,626.071s 0.003s 1,626.317s 12 1,208 121.175s 0.002s 121.185s 13 42 4.378s 0.002s 4.388s 14 24 2.598s 0.002s 2.607s 15 27 2.872s 0.002s 2.881s 16 17 1.87s 0.002s 1.878s 17 20 2.171s 0.002s 2.18s 18 65 6.682s 0.002s 6.691s 19 67,576 1,619.373s 0.001s 1,619.447s 20 131 13.291s 0.002s 13.299s 21 51 5.283s 0.002s 5.293s 22 39 4.08s 0.002s 4.088s 23 30 3.175s 0.002s 3.183s Day Hour Added Removed Recycled Synced files Longest sync Average sync Jun 27 00 0 1 1,548 199 0.806s 0.015s 01 0 327 2,152 601 0.024s 0.004s 02 0 0 929 164 0.001s 0.002s 03 0 0 0 9 0.001s 0.001s 04 0 166 144 92 0.003s 0.002s 05 0 129 42 63 0.006s 0.002s 06 0 850 8,459 799 0.783s 0.123s 07 0 249 4,057 379 0.784s 0.057s 08 0 0 538 26 0.001s 0.001s 09 0 33 655 165 0.130s 0.008s 10 0 0 538 79 0.001s 0.001s 11 0 0 45 65 0.001s 0.003s 12 0 0 0 37 0.001s 0.002s 13 0 0 0 18 0.001s 0.002s 14 0 0 0 14 0.001s 0.002s 15 0 0 0 16 0.001s 0.002s 16 0 0 0 13 0.001s 0.002s 17 0 0 0 13 0.001s 0.002s 18 0 0 0 27 0.001s 0.002s 19 0 10 0 9 0.001s 0.001s 20 0 0 0 27 0.001s 0.002s 21 0 0 0 20 0.001s 0.002s 22 0 0 0 17 0.001s 0.002s 23 0 0 0 17 0.001s 0.002s Day Hour Count Avg time (sec) Jun 27 00 0 0s 01 0 0s 02 0 0s 03 0 0s 04 0 0s 05 0 0s 06 0 0s 07 0 0s 08 0 0s 09 0 0s 10 0 0s 11 0 0s 12 0 0s 13 0 0s 14 0 0s 15 0 0s 16 0 0s 17 0 0s 18 0 0s 19 0 0s 20 0 0s 21 0 0s 22 0 0s 23 0 0s Day Hour Mean distance Mean estimate Jun 27 00 8,072,265.50 kB 8,748,680.00 kB 01 8,294,564.25 kB 8,761,586.50 kB 02 7,876,506.00 kB 8,723,644.50 kB 03 45.00 kB 7,766,570.00 kB 04 2,275,367.00 kB 7,013,045.50 kB 05 1,400,224.50 kB 5,943,606.50 kB 06 8,027,115.00 kB 8,432,835.32 kB 07 8,819,129.12 kB 8,827,329.75 kB 08 8,812,944.00 kB 8,822,716.00 kB 09 3,759,932.67 kB 8,116,271.67 kB 10 8,805,949.00 kB 8,815,431.00 kB 11 422,770.67 kB 7,281,508.33 kB 12 3,788.00 kB 5,582,835.00 kB 13 73.50 kB 4,522,445.00 kB 14 44.50 kB 3,663,190.00 kB 15 42.50 kB 2,967,192.50 kB 16 32.50 kB 2,403,432.00 kB 17 40.00 kB 1,946,787.50 kB 18 82.50 kB 1,576,914.00 kB 19 153,084.00 kB 1,359,836.00 kB 20 284.00 kB 1,162,697.00 kB 21 61.00 kB 941,810.50 kB 22 67.00 kB 762,878.50 kB 23 65.50 kB 617,944.50 kB -
Temporary Files
Size of temporary files
Key values
- 14.92 GiB Temp Files size Peak
- 2026-06-27 07:11:31 Date
Number of temporary files
Key values
- 24 per second Temp Files Peak
- 2026-06-27 06:23:50 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Jun 27 00 166 31.80 GiB 196.16 MiB 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 795 138.66 GiB 178.60 MiB 07 227 33.54 GiB 151.31 MiB 08 0 0 0 09 0 0 0 10 10 9.13 GiB 935.18 MiB 11 0 0 0 12 0 0 0 13 0 0 0 14 0 0 0 15 0 0 0 16 0 0 0 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 170.50 GiB 120.00 KiB 1.00 GiB 185.34 MiB vacuum full analyze;-
VACUUM FULL ANALYZE;
Date: 2026-06-27 07:13:10 Duration: 57m54s
-
VACUUM FULL ANALYZE;
Date: 2026-06-27 06:15:20 Duration: 0ms
2 60 774.59 MiB 6.38 MiB 31.38 MiB 12.91 MiB cluster pub2.term;-
CLUSTER pub2.TERM;
Date: 2026-06-27 06:14:24 Duration: 1m9s
-
CLUSTER pub2.TERM;
Date: 2026-06-27 06:13:25 Duration: 0ms
3 25 16.12 GiB 8.00 KiB 1.00 GiB 660.13 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-06-27 00:29:54 Duration: 3m20s
-
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-06-27 00:29:53 Duration: 0ms
4 20 969.12 MiB 26.12 MiB 80.01 MiB 48.46 MiB cluster pub2.term_label;-
CLUSTER pub2.TERM_LABEL;
Date: 2026-06-27 06:15:14 Duration: 50s8ms
-
CLUSTER pub2.TERM_LABEL;
Date: 2026-06-27 06:14:34 Duration: 0ms
5 15 11.51 GiB 261.86 MiB 1.00 GiB 785.86 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-06-27 00:26:33 Duration: 2m6s
-
CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);
Date: 2026-06-27 00:26:33 Duration: 0ms
6 10 676.14 MiB 8.00 KiB 138.18 MiB 67.61 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-06-27 00:59:06 Duration: 6s190ms
-
ALTER TABLE pub2.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);
Date: 2026-06-27 00:59:06 Duration: 0ms
7 10 9.13 GiB 135.77 MiB 1.00 GiB 935.18 MiB select pub2.maint_cached_value_refresh_data_metrics ();-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-06-27 10:20:24 Duration: 38m59s
-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-06-27 10:15:36 Duration: 0ms
8 10 67.86 MiB 8.00 KiB 13.88 MiB 6.79 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-06-27 00:59:12 Duration: 0ms
9 10 156.06 MiB 8.00 KiB 32.77 MiB 15.61 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-06-27 00:11:27 Duration: 0ms
10 8 67.99 MiB 8.00 KiB 17.25 MiB 8.50 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-06-27 00:59:17 Duration: 0ms
11 5 156.03 MiB 28.94 MiB 32.31 MiB 31.21 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-06-27 00:11:16 Duration: 0ms
12 5 67.81 MiB 13.31 MiB 13.71 MiB 13.56 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-06-27 00:59:10 Duration: 0ms
13 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-06-27 00:59:00 Duration: 0ms
14 5 676.09 MiB 127.80 MiB 139.41 MiB 135.22 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-06-27 00:58:36 Duration: 8s750ms
-
CREATE INDEX ix_gene_disease_disease ON pub2.gene_disease USING btree (disease_id);
Date: 2026-06-27 00:58:36 Duration: 0ms Database: ctdprd51 User: pub2
15 5 217.81 MiB 41.22 MiB 45.52 MiB 43.56 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-06-27 00:11:20 Duration: 0ms
16 5 156.03 MiB 30.66 MiB 31.64 MiB 31.21 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-06-27 00:11:13 Duration: 0ms Database: ctdprd51 User: pub2
17 5 156.02 MiB 30.57 MiB 31.72 MiB 31.20 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-06-27 00:11:15 Duration: 0ms
18 5 217.81 MiB 42.59 MiB 45.86 MiB 43.56 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-06-27 00:11:25 Duration: 0ms
19 5 676.10 MiB 131.24 MiB 138.08 MiB 135.22 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-06-27 00:58:51 Duration: 14s815ms
-
CREATE INDEX ix_gene_disease_network_score ON pub2.gene_disease USING btree (network_score);
Date: 2026-06-27 00:58:51 Duration: 0ms
20 5 67.81 MiB 12.40 MiB 14.22 MiB 13.56 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-06-27 00:59:11 Duration: 0ms
21 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-06-27 00:58:52 Duration: 0ms
22 5 675.96 MiB 132.38 MiB 136.55 MiB 135.19 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-06-27 00:58:59 Duration: 7s527ms
-
CREATE INDEX ix_gene_disease_ind_chem_qty ON pub2.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);
Date: 2026-06-27 00:58:59 Duration: 0ms
23 4 2.04 MiB 400.00 KiB 640.00 KiB 522.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-06-27 00:59:17 Duration: 0ms
24 4 15.40 MiB 8.00 KiB 8.31 MiB 3.85 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-06-27 00:59:14 Duration: 0ms
25 4 67.07 MiB 16.42 MiB 17.07 MiB 16.77 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-06-27 00:59:18 Duration: 0ms
26 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-06-27 00:59:18 Duration: 0ms
27 4 67.95 MiB 16.57 MiB 17.29 MiB 16.99 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-06-27 00:59:16 Duration: 0ms
28 4 67.95 MiB 16.45 MiB 17.36 MiB 16.99 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-06-27 00:59:16 Duration: 0ms
29 2 6.98 MiB 3.05 MiB 3.92 MiB 3.49 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-06-27 00:59:13 Duration: 0ms
30 2 6.98 MiB 3.35 MiB 3.62 MiB 3.49 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-06-27 00:59:13 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
2 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
3 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
4 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
5 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
6 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
7 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
8 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
9 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
10 1.00 GiB CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 ]
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-06-27 00:29:53 ]
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-06-27 00:29:53 ]
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-06-27 00:29:53 ]
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-06-27 00:29:53 ]
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-06-27 00:29:53 ]
16 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-06-27 00:29:53 ]
17 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-06-27 00:29:53 ]
18 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-06-27 00:29:54 ]
19 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-06-27 00:29:54 ]
20 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-06-27 00:29:54 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 306.80 sec Highest CPU-cost vacuum
Table pub2.gene_disease
Database ctdprd51 - 2026-06-27 01:37:59 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 306.80 sec Highest CPU-cost vacuum
Table pub2.gene_disease
Database ctdprd51 - 2026-06-27 01:37:59 Date
Analyzes per table
Key values
- pubc.log_query (9) Main table analyzed (database ctdprd51)
- 48 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 9 ctdprd51.pub2.term 2 ctdprd51.pg_catalog.pg_class 2 ctdprd51.pub2.phenotype_term 2 ctdprd51.pub2.reference 2 ctdprd51.pub2.gene_chem_ref_gene_form 1 ctdprd51.pub2.term_reference 1 ctdprd51.pg_catalog.pg_type 1 ctdprd51.pub2.ixn 1 ctdprd51.pub2.exp_study_factor 1 ctdprd51.pub2.medium 1 ctdprd51.pub2.exp_event_location 1 ctdprd51.pub2.exp_receptor_race 1 ctdprd51.pub2.exp_receptor 1 ctdprd51.pub2.reference_exp 1 ctdprd51.pub2.exp_outcome 1 ctdprd51.pub2.gene_gene 1 ctdprd51.pub2.dag_node 1 ctdprd51.pub2.exp_event_assay_method 1 ctdprd51.pub2.gene_gene_reference 1 ctdprd51.pub2.gene_disease 1 ctdprd51.pub2.slim_term_mapping 1 ctdprd51.pub2.exp_receptor_gender 1 ctdprd51.pub2.exp_anatomy 1 ctdprd51.pub2.exp_event 1 ctdprd51.pub2.exp_stressor 1 ctdprd51.pub2.gene_gene_ref_throughput 1 ctdprd51.pub2.exp_receptor_tobacco_use 1 ctdprd51.pub2.exp_stressor_stressor_src 1 ctdprd51.pub2.exp_event_project 1 ctdprd51.pub2.country 1 ctdprd51.pub2.chem_disease 1 ctdprd51.pub2.geographic_region 1 ctdprd51.pub2.exposure 1 ctdprd51.pg_catalog.pg_depend 1 ctdprd51.pg_catalog.pg_attribute 1 Total 48 Vacuums per table
Key values
- pub2.reference (2) Main table vacuumed on database ctdprd51
- 38 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pub2.reference 2 2 589,528 0 40,850 0 0 378,751 29,492 125,456,600 ctdprd51.pub2.phenotype_term 2 2 1,017,593 0 1,370 0 0 814,851 1,348 183,401,555 ctdprd51.pub2.term 2 2 1,476,670 0 328,579 0 135 885,779 321,398 1,642,762,219 ctdprd51.pub2.chem_disease 1 1 281,381 0 10,395 0 0 171,827 10,383 125,219,630 ctdprd51.pub2.exp_event_project 1 0 2,411 0 3 0 0 1,183 2 84,732 ctdprd51.pub2.exp_stressor_stressor_src 1 0 3,031 0 4 0 0 1,487 1 96,152 ctdprd51.pub2.exposure 1 0 4,163 0 3 0 0 2,028 1 128,071 ctdprd51.pg_catalog.pg_attribute 1 1 631 0 123 0 37 301 111 585,528 ctdprd51.pub2.exp_receptor_gender 1 0 2,990 0 3 0 0 1,480 1 95,739 ctdprd51.pub2.slim_term_mapping 1 0 606 0 4 0 0 265 2 28,358 ctdprd51.pub2.gene_disease 1 1 3,020,568 0 993,987 0 0 1,710,248 815,475 2,110,933,232 ctdprd51.pub2.exp_anatomy 1 0 167 0 3 0 0 38 1 10,661 ctdprd51.pub2.exp_stressor 1 0 7,038 0 4 0 0 3,489 2 219,830 ctdprd51.pub2.exp_event 1 0 14,070 0 3 0 0 6,940 2 425,755 ctdprd51.pg_catalog.pg_class 1 1 375 0 58 0 0 185 58 294,865 ctdprd51.pubc.log_query 1 1 190 0 8 0 0 23 5 36,092 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 1,318 0 3 0 0 624 1 45,235 ctdprd51.pub2.gene_gene_ref_throughput 1 0 15,857 0 3 0 0 7,906 1 474,873 ctdprd51.pub2.exp_event_location 1 0 3,866 0 3 0 0 1,882 1 119,457 ctdprd51.pub2.exp_receptor 1 0 8,142 0 3 0 0 4,042 1 246,897 ctdprd51.pub2.exp_receptor_race 1 0 1,434 0 3 0 0 682 1 48,657 ctdprd51.pub2.reference_exp 1 0 346 0 4 0 0 136 2 20,991 ctdprd51.pub2.exp_outcome 1 0 988 0 4 0 0 436 2 40,099 ctdprd51.pub2.gene_gene_reference 1 0 33,101 0 4 0 0 16,474 2 986,285 ctdprd51.pub2.dag_node 1 1 336,505 0 97,350 0 0 288,405 154,166 475,786,438 ctdprd51.pub2.exp_event_assay_method 1 0 5,565 0 3 0 0 2,754 1 170,905 ctdprd51.pub2.gene_gene 1 0 13,197 0 5 0 0 6,546 2 400,533 ctdprd51.pub2.gene_chem_ref_gene_form 1 0 36,173 0 4 0 0 18,036 2 1,078,375 ctdprd51.pg_toast.pg_toast_11671944 1 1 92 0 3 0 0 50 1 11,621 ctdprd51.pub2.ixn 1 1 1,643,812 0 99 0 0 1,092,688 22 75,015,856 ctdprd51.pub2.term_reference 1 0 40,626 0 5 0 0 20,258 2 1,207,341 ctdprd51.pg_catalog.pg_statistic 1 1 593 0 188 0 126 351 128 559,966 ctdprd51.pg_toast.pg_toast_2619 1 1 5,291 0 2,099 0 9,646 4,571 1,261 647,690 ctdprd51.pub2.exp_study_factor 1 0 115 0 3 0 0 12 1 9,127 ctdprd51.pg_catalog.pg_shdepend 1 1 124 0 61 0 0 81 37 137,073 Total 38 17 8,568,557 195,848 1,475,244 0 9,944 5,444,809 1,333,916 4,746,786,438 Tuples removed per table
Key values
- pub2.gene_disease (35376607) Main table with removed tuples on database ctdprd51
- 64397339 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pub2.gene_disease 1 1 35,376,607 35,376,607 0 0 520,245 ctdprd51.pub2.phenotype_term 2 2 21,292,813 7,095,170 0 0 265,309 ctdprd51.pub2.chem_disease 1 1 3,555,182 3,555,182 0 0 52,233 ctdprd51.pub2.term 2 2 2,213,553 4,414,349 0 0 355,921 ctdprd51.pub2.dag_node 1 1 1,822,374 1,814,565 0 0 86,796 ctdprd51.pub2.reference 2 2 72,456 406,290 0 0 167,654 ctdprd51.pub2.ixn 1 1 57,808 2,530,791 0 0 602,678 ctdprd51.pg_toast.pg_toast_2619 1 1 5,126 21,623 53 0 12,592 ctdprd51.pg_catalog.pg_attribute 1 1 625 8,994 0 0 236 ctdprd51.pg_catalog.pg_statistic 1 1 413 3,215 86 0 410 ctdprd51.pg_catalog.pg_shdepend 1 1 215 2,123 0 0 22 ctdprd51.pg_catalog.pg_class 1 1 96 1,835 0 0 94 ctdprd51.pg_toast.pg_toast_11671944 1 1 68 71 0 0 22 ctdprd51.pubc.log_query 1 1 3 470 34 0 18 ctdprd51.pub2.exp_event_project 1 0 0 113,176 0 0 1,182 ctdprd51.pub2.exp_stressor_stressor_src 1 0 0 335,640 0 0 1,486 ctdprd51.pub2.exposure 1 0 0 245,709 0 0 2,027 ctdprd51.pub2.exp_receptor_gender 1 0 0 213,433 0 0 1,479 ctdprd51.pub2.slim_term_mapping 1 0 0 33,515 0 0 264 ctdprd51.pub2.exp_anatomy 1 0 0 4,356 0 0 37 ctdprd51.pub2.exp_stressor 1 0 0 238,225 0 0 3,488 ctdprd51.pub2.exp_event 1 0 0 234,786 0 0 6,939 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 0 88,225 0 0 623 ctdprd51.pub2.gene_gene_ref_throughput 1 0 0 1,518,441 0 0 7,905 ctdprd51.pub2.exp_event_location 1 0 0 281,360 0 0 1,881 ctdprd51.pub2.exp_receptor 1 0 0 217,021 0 0 4,041 ctdprd51.pub2.exp_receptor_race 1 0 0 105,026 0 0 681 ctdprd51.pub2.reference_exp 1 0 0 3,733 0 0 135 ctdprd51.pub2.exp_outcome 1 0 0 47,533 0 0 435 ctdprd51.pub2.gene_gene_reference 1 0 0 1,510,800 0 0 16,473 ctdprd51.pub2.exp_event_assay_method 1 0 0 273,014 0 0 2,753 ctdprd51.pub2.gene_gene 1 0 0 1,210,745 0 0 6,545 ctdprd51.pub2.gene_chem_ref_gene_form 1 0 0 3,329,483 0 0 18,035 ctdprd51.pub2.term_reference 1 0 0 3,747,493 0 0 20,257 ctdprd51.pub2.exp_study_factor 1 0 0 1,788 0 0 11 Total 38 17 64,397,339 68,984,787 173 0 2,160,907 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.chem_disease 1 1 3555182 0 ctdprd51.pub2.exp_event_project 1 0 0 0 ctdprd51.pub2.exp_stressor_stressor_src 1 0 0 0 ctdprd51.pub2.exposure 1 0 0 0 ctdprd51.pub2.reference 2 2 72456 0 ctdprd51.pg_catalog.pg_attribute 1 1 625 0 ctdprd51.pub2.exp_receptor_gender 1 0 0 0 ctdprd51.pub2.slim_term_mapping 1 0 0 0 ctdprd51.pub2.gene_disease 1 1 35376607 0 ctdprd51.pub2.exp_anatomy 1 0 0 0 ctdprd51.pub2.exp_stressor 1 0 0 0 ctdprd51.pub2.exp_event 1 0 0 0 ctdprd51.pub2.phenotype_term 2 2 21292813 0 ctdprd51.pg_catalog.pg_class 1 1 96 0 ctdprd51.pub2.term 2 2 2213553 0 ctdprd51.pubc.log_query 1 1 3 0 ctdprd51.pub2.exp_receptor_tobacco_use 1 0 0 0 ctdprd51.pub2.gene_gene_ref_throughput 1 0 0 0 ctdprd51.pub2.exp_event_location 1 0 0 0 ctdprd51.pub2.exp_receptor 1 0 0 0 ctdprd51.pub2.exp_receptor_race 1 0 0 0 ctdprd51.pub2.reference_exp 1 0 0 0 ctdprd51.pub2.exp_outcome 1 0 0 0 ctdprd51.pub2.gene_gene_reference 1 0 0 0 ctdprd51.pub2.dag_node 1 1 1822374 0 ctdprd51.pub2.exp_event_assay_method 1 0 0 0 ctdprd51.pub2.gene_gene 1 0 0 0 ctdprd51.pub2.gene_chem_ref_gene_form 1 0 0 0 ctdprd51.pg_toast.pg_toast_11671944 1 1 68 0 ctdprd51.pub2.ixn 1 1 57808 0 ctdprd51.pub2.term_reference 1 0 0 0 ctdprd51.pg_catalog.pg_statistic 1 1 413 0 ctdprd51.pg_toast.pg_toast_2619 1 1 5126 0 ctdprd51.pub2.exp_study_factor 1 0 0 0 ctdprd51.pg_catalog.pg_shdepend 1 1 215 0 Total 38 17 64,397,339 0 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Jun 27 00 6 10 01 21 21 02 0 0 03 2 3 04 0 0 05 2 3 06 0 0 07 4 5 08 0 0 09 3 4 10 0 0 11 0 0 12 0 1 13 0 0 14 0 0 15 0 0 16 0 0 17 0 0 18 0 0 19 0 0 20 0 1 21 0 0 22 0 0 23 0 0 - 306.80 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- unknown Main Lock Type
- 0 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query NO DATASET
Queries that waited the most
Rank Wait time Query NO DATASET
-
Queries
Queries by type
Key values
- 68 Total read queries
- 81 Total write queries
Queries by database
Key values
- unknown Main database
- 147 Requests
- 11h27m14s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 297 Requests
User Request type Count Duration editeu Total 1 10s643ms select 1 10s643ms load Total 23 1h4m30s select 23 1h4m30s postgres Total 8 8m50s copy to 8 8m50s pub2 Total 4 17m25s insert 3 17m17s select 1 8s671ms pubc Total 1 9m25s select 1 9m25s pubeu Total 13 1m58s select 13 1m58s unknown Total 297 16h2m26s copy to 82 1h42m22s ddl 35 46m57s insert 17 49m44s others 20 1h8m23s select 134 10h59m23s tcl 1 11s486ms update 8 35m22s Duration by user
Key values
- 16h2m26s (unknown) Main time consuming user
User Request type Count Duration editeu Total 1 10s643ms select 1 10s643ms load Total 23 1h4m30s select 23 1h4m30s postgres Total 8 8m50s copy to 8 8m50s pub2 Total 4 17m25s insert 3 17m17s select 1 8s671ms pubc Total 1 9m25s select 1 9m25s pubeu Total 13 1m58s select 13 1m58s unknown Total 297 16h2m26s copy to 82 1h42m22s ddl 35 46m57s insert 17 49m44s others 20 1h8m23s select 134 10h59m23s tcl 1 11s486ms update 8 35m22s Queries by host
Key values
- unknown Main host
- 347 Requests
- 17h44m47s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 157 Requests
- 11h29m3s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-06-27 15:43:10 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 113 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 2h33m8s SELECT maint_term_derive_nm_fts ();[ Date: 2026-06-27 06:08:03 - Bind query: yes ]
2 2h17m26s select pub2.maint_term_derive_data ();[ Date: 2026-06-27 09:30:59 - Bind query: yes ]
3 1h54m3s select pub2.maint_gene_chem_ref_gene_form_refresh ();[ Date: 2026-06-27 03:31:52 - Bind query: yes ]
4 57m54s VACUUM FULL ANALYZE;[ Date: 2026-06-27 07:13:10 - Bind query: yes ]
5 38m59s select pub2.maint_cached_value_refresh_data_metrics ();[ Date: 2026-06-27 10:20:24 - Bind query: yes ]
6 27m48s COPY pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;[ Date: 2026-06-27 19:32:03 ]
7 27m3s COPY pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;[ Date: 2026-06-27 18:44:56 ]
8 26m9s 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-06-27 01:32:03 - Bind query: yes ]
9 12m59s 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-06-27 00:24:27 - Bind query: yes ]
10 9m25s /* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();[ Date: 2026-06-27 00:09:26 - Database: ctdprd51 - User: pubc - Application: psql ]
11 9m9s select pub2.maint_phenotype_term_derive_data ();[ Date: 2026-06-27 09:41:24 - Bind query: yes ]
12 6m52s COPY pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;[ Date: 2026-06-27 19:44:35 ]
13 6m47s COPY pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;[ Date: 2026-06-27 18:57:22 ]
14 3m22s SELECT maint_term_label_derive_nm_fts ();[ Date: 2026-06-27 06:11:36 - Bind query: yes ]
15 3m20s ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);[ Date: 2026-06-27 00:29:54 - Bind query: yes ]
16 2m55s 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-06-27 00:08:35 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
17 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-06-27 03:34:45 - Bind query: yes ]
18 2m26s 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-06-27 01:34:30 - Bind query: yes ]
19 2m13s update pub2.TERM set has_exposures = false;[ Date: 2026-06-27 01:02:34 - Bind query: yes ]
20 2m6s CREATE INDEX ix_term_enrich_agent_enr_term ON pub2.term_enrichment_agent USING btree (enriched_term_id);[ Date: 2026-06-27 00:26:33 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 2h33m8s 1 2h33m8s 2h33m8s 2h33m8s select maint_term_derive_nm_fts ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jun 27 06 1 2h33m8s 2h33m8s -
SELECT maint_term_derive_nm_fts ();
Date: 2026-06-27 06:08:03 Duration: 2h33m8s Bind query: yes
2 2h17m26s 1 2h17m26s 2h17m26s 2h17m26s select pub2.maint_term_derive_data ();Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jun 27 09 1 2h17m26s 2h17m26s -
select pub2.maint_term_derive_data ();
Date: 2026-06-27 09:30:59 Duration: 2h17m26s Bind query: yes
3 1h54m3s 1 1h54m3s 1h54m3s 1h54m3s select pub2.maint_gene_chem_ref_gene_form_refresh ();Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jun 27 03 1 1h54m3s 1h54m3s -
select pub2.maint_gene_chem_ref_gene_form_refresh ();
Date: 2026-06-27 03:31:52 Duration: 1h54m3s Bind query: yes
4 57m54s 1 57m54s 57m54s 57m54s vacuum full analyze;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jun 27 07 1 57m54s 57m54s -
VACUUM FULL ANALYZE;
Date: 2026-06-27 07:13:10 Duration: 57m54s Bind query: yes
-
VACUUM FULL ANALYZE;
Date: 2026-06-27 06:15:20 Duration: 0ms
5 38m59s 1 38m59s 38m59s 38m59s select pub2.maint_cached_value_refresh_data_metrics ();Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jun 27 10 1 38m59s 38m59s -
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-06-27 10:20:24 Duration: 38m59s Bind query: yes
-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-06-27 10:15:36 Duration: 0ms
6 27m48s 1 27m48s 27m48s 27m48s copy pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jun 27 19 1 27m48s 27m48s -
COPY pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-06-27 19:32:03 Duration: 27m48s
7 27m3s 1 27m3s 27m3s 27m3s copy pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jun 27 18 1 27m3s 27m3s -
COPY pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-06-27 18:44:56 Duration: 27m3s
8 26m9s 1 26m9s 26m9s 26m9s 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 #8
Day Hour Count Duration Avg duration Jun 27 01 1 26m9s 26m9s -
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-06-27 01:32:03 Duration: 26m9s Bind query: yes
9 12m59s 1 12m59s 12m59s 12m59s 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 #9
Day Hour Count Duration Avg duration Jun 27 00 1 12m59s 12m59s -
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-06-27 00:24:27 Duration: 12m59s Bind query: yes
10 10m27s 46 5s2ms 23s855ms 13s632ms 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 #10
Day Hour Count Duration Avg duration Jun 27 00 46 10m27s 13s632ms -
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-06-27 00:32:11 Duration: 23s855ms 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-06-27 00:32:34 Duration: 23s589ms 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-06-27 00:32:57 Duration: 22s621ms Bind query: yes
11 9m25s 1 9m25s 9m25s 9m25s select maint_query_logs_archive ();Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jun 27 00 1 9m25s 9m25s [ User: pubc - Total duration: 9m25s - Times executed: 1 ]
[ Application: psql - Total duration: 9m25s - Times executed: 1 ]
-
/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();
Date: 2026-06-27 00:09:26 Duration: 9m25s Database: ctdprd51 User: pubc Application: psql
12 9m9s 1 9m9s 9m9s 9m9s select pub2.maint_phenotype_term_derive_data ();Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jun 27 09 1 9m9s 9m9s -
select pub2.maint_phenotype_term_derive_data ();
Date: 2026-06-27 09:41:24 Duration: 9m9s Bind query: yes
13 6m52s 1 6m52s 6m52s 6m52s copy pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jun 27 19 1 6m52s 6m52s -
COPY pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-06-27 19:44:35 Duration: 6m52s
14 6m47s 1 6m47s 6m47s 6m47s copy pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jun 27 18 1 6m47s 6m47s -
COPY pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-06-27 18:57:22 Duration: 6m47s
15 3m38s 4 7s941ms 2m55s 54s728ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jun 27 00 4 3m38s 54s728ms [ User: load - Total duration: 3m38s - Times executed: 4 ]
[ Application: pg_bulkload - Total duration: 3m38s - 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-06-27 00:08:35 Duration: 2m55s 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-06-27 00:58:25 Duration: 20s24ms 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-06-27 00:11:10 Duration: 15s394ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
16 3m22s 1 3m22s 3m22s 3m22s select maint_term_label_derive_nm_fts ();Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jun 27 06 1 3m22s 3m22s -
SELECT maint_term_label_derive_nm_fts ();
Date: 2026-06-27 06:11:36 Duration: 3m22s Bind query: yes
17 3m20s 1 3m20s 3m20s 3m20s 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 #17
Day Hour Count Duration Avg duration Jun 27 00 1 3m20s 3m20s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-06-27 00:29:54 Duration: 3m20s 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-06-27 00:29:53 Duration: 0ms
18 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 #18
Day Hour Count Duration Avg duration Jun 27 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-06-27 03:34:45 Duration: 2m53s Bind query: yes
19 2m26s 1 2m26s 2m26s 2m26s 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 #19
Day Hour Count Duration Avg duration Jun 27 01 1 2m26s 2m26s -
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-06-27 01:34:30 Duration: 2m26s Bind query: yes
20 2m13s 1 2m13s 2m13s 2m13s update pub2.term set has_exposures = false;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jun 27 01 1 2m13s 2m13s -
update pub2.TERM set has_exposures = false;
Date: 2026-06-27 01:02:34 Duration: 2m13s Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 46 10m27s 5s2ms 23s855ms 13s632ms 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 Jun 27 00 46 10m27s 13s632ms -
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-06-27 00:32:11 Duration: 23s855ms 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-06-27 00:32:34 Duration: 23s589ms 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-06-27 00:32:57 Duration: 22s621ms Bind query: yes
2 4 3m38s 7s941ms 2m55s 54s728ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jun 27 00 4 3m38s 54s728ms [ User: load - Total duration: 3m38s - Times executed: 4 ]
[ Application: pg_bulkload - Total duration: 3m38s - 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-06-27 00:08:35 Duration: 2m55s 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-06-27 00:58:25 Duration: 20s24ms 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-06-27 00:11:10 Duration: 15s394ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
3 3 26s674ms 7s907ms 10s766ms 8s891ms vacuum analyze pub2.term;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jun 27 06 2 18s674ms 9s337ms 07 1 8s 8s -
VACUUM ANALYZE pub2.TERM;
Date: 2026-06-27 06:08:13 Duration: 10s766ms Bind query: yes
-
VACUUM ANALYZE pub2.TERM;
Date: 2026-06-27 07:13:18 Duration: 8s Bind query: yes
-
VACUUM ANALYZE pub2.TERM;
Date: 2026-06-27 06:12:31 Duration: 7s907ms Bind query: yes
4 3 24s477ms 7s733ms 8s655ms 8s159ms vacuum analyze pub2.reference;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jun 27 03 1 8s655ms 8s655ms 06 1 8s88ms 8s88ms 07 1 7s733ms 7s733ms -
VACUUM ANALYZE pub2.REFERENCE;
Date: 2026-06-27 03:34:54 Duration: 8s655ms Bind query: yes
-
VACUUM ANALYZE pub2.REFERENCE;
Date: 2026-06-27 06:13:14 Duration: 8s88ms Bind query: yes
-
VACUUM ANALYZE pub2.REFERENCE;
Date: 2026-06-27 07:13:32 Duration: 7s733ms Bind query: yes
5 2 17s978ms 5s529ms 12s448ms 8s989ms 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 #5
Day Hour Count Duration Avg duration Jun 27 04 2 17s978ms 8s989ms [ User: pubeu - Total duration: 17s978ms - 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 = '1458788') 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-06-27 04:49:59 Duration: 12s448ms 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 = '1458788') 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-06-27 04:49:59 Duration: 5s529ms Database: ctdprd51 User: pubeu Bind query: yes
6 1 2h33m8s 2h33m8s 2h33m8s 2h33m8s select maint_term_derive_nm_fts ();Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jun 27 06 1 2h33m8s 2h33m8s -
SELECT maint_term_derive_nm_fts ();
Date: 2026-06-27 06:08:03 Duration: 2h33m8s Bind query: yes
7 1 2h17m26s 2h17m26s 2h17m26s 2h17m26s select pub2.maint_term_derive_data ();Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jun 27 09 1 2h17m26s 2h17m26s -
select pub2.maint_term_derive_data ();
Date: 2026-06-27 09:30:59 Duration: 2h17m26s Bind query: yes
8 1 1h54m3s 1h54m3s 1h54m3s 1h54m3s select pub2.maint_gene_chem_ref_gene_form_refresh ();Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jun 27 03 1 1h54m3s 1h54m3s -
select pub2.maint_gene_chem_ref_gene_form_refresh ();
Date: 2026-06-27 03:31:52 Duration: 1h54m3s Bind query: yes
9 1 57m54s 57m54s 57m54s 57m54s vacuum full analyze;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jun 27 07 1 57m54s 57m54s -
VACUUM FULL ANALYZE;
Date: 2026-06-27 07:13:10 Duration: 57m54s Bind query: yes
-
VACUUM FULL ANALYZE;
Date: 2026-06-27 06:15:20 Duration: 0ms
10 1 38m59s 38m59s 38m59s 38m59s select pub2.maint_cached_value_refresh_data_metrics ();Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jun 27 10 1 38m59s 38m59s -
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-06-27 10:20:24 Duration: 38m59s Bind query: yes
-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-06-27 10:15:36 Duration: 0ms
11 1 27m48s 27m48s 27m48s 27m48s copy pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jun 27 19 1 27m48s 27m48s -
COPY pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-06-27 19:32:03 Duration: 27m48s
12 1 27m3s 27m3s 27m3s 27m3s copy pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jun 27 18 1 27m3s 27m3s -
COPY pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-06-27 18:44:56 Duration: 27m3s
13 1 26m9s 26m9s 26m9s 26m9s 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 #13
Day Hour Count Duration Avg duration Jun 27 01 1 26m9s 26m9s -
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-06-27 01:32:03 Duration: 26m9s Bind query: yes
14 1 12m59s 12m59s 12m59s 12m59s 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 #14
Day Hour Count Duration Avg duration Jun 27 00 1 12m59s 12m59s -
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-06-27 00:24:27 Duration: 12m59s Bind query: yes
15 1 9m25s 9m25s 9m25s 9m25s select maint_query_logs_archive ();Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jun 27 00 1 9m25s 9m25s [ User: pubc - Total duration: 9m25s - Times executed: 1 ]
[ Application: psql - Total duration: 9m25s - Times executed: 1 ]
-
/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();
Date: 2026-06-27 00:09:26 Duration: 9m25s Database: ctdprd51 User: pubc Application: psql
16 1 9m9s 9m9s 9m9s 9m9s select pub2.maint_phenotype_term_derive_data ();Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jun 27 09 1 9m9s 9m9s -
select pub2.maint_phenotype_term_derive_data ();
Date: 2026-06-27 09:41:24 Duration: 9m9s Bind query: yes
17 1 6m52s 6m52s 6m52s 6m52s copy pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jun 27 19 1 6m52s 6m52s -
COPY pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-06-27 19:44:35 Duration: 6m52s
18 1 6m47s 6m47s 6m47s 6m47s copy pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jun 27 18 1 6m47s 6m47s -
COPY pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-06-27 18:57:22 Duration: 6m47s
19 1 3m22s 3m22s 3m22s 3m22s select maint_term_label_derive_nm_fts ();Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jun 27 06 1 3m22s 3m22s -
SELECT maint_term_label_derive_nm_fts ();
Date: 2026-06-27 06:11:36 Duration: 3m22s Bind query: yes
20 1 3m20s 3m20s 3m20s 3m20s 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 #20
Day Hour Count Duration Avg duration Jun 27 00 1 3m20s 3m20s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-06-27 00:29:54 Duration: 3m20s 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-06-27 00:29:53 Duration: 0ms
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 2h33m8s 2h33m8s 2h33m8s 1 2h33m8s select maint_term_derive_nm_fts ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jun 27 06 1 2h33m8s 2h33m8s -
SELECT maint_term_derive_nm_fts ();
Date: 2026-06-27 06:08:03 Duration: 2h33m8s Bind query: yes
2 2h17m26s 2h17m26s 2h17m26s 1 2h17m26s select pub2.maint_term_derive_data ();Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jun 27 09 1 2h17m26s 2h17m26s -
select pub2.maint_term_derive_data ();
Date: 2026-06-27 09:30:59 Duration: 2h17m26s Bind query: yes
3 1h54m3s 1h54m3s 1h54m3s 1 1h54m3s select pub2.maint_gene_chem_ref_gene_form_refresh ();Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jun 27 03 1 1h54m3s 1h54m3s -
select pub2.maint_gene_chem_ref_gene_form_refresh ();
Date: 2026-06-27 03:31:52 Duration: 1h54m3s Bind query: yes
4 57m54s 57m54s 57m54s 1 57m54s vacuum full analyze;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jun 27 07 1 57m54s 57m54s -
VACUUM FULL ANALYZE;
Date: 2026-06-27 07:13:10 Duration: 57m54s Bind query: yes
-
VACUUM FULL ANALYZE;
Date: 2026-06-27 06:15:20 Duration: 0ms
5 38m59s 38m59s 38m59s 1 38m59s select pub2.maint_cached_value_refresh_data_metrics ();Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jun 27 10 1 38m59s 38m59s -
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-06-27 10:20:24 Duration: 38m59s Bind query: yes
-
select pub2.maint_cached_value_refresh_data_metrics ();
Date: 2026-06-27 10:15:36 Duration: 0ms
6 27m48s 27m48s 27m48s 1 27m48s copy pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jun 27 19 1 27m48s 27m48s -
COPY pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-06-27 19:32:03 Duration: 27m48s
7 27m3s 27m3s 27m3s 1 27m3s copy pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jun 27 18 1 27m3s 27m3s -
COPY pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-06-27 18:44:56 Duration: 27m3s
8 26m9s 26m9s 26m9s 1 26m9s 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 #8
Day Hour Count Duration Avg duration Jun 27 01 1 26m9s 26m9s -
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-06-27 01:32:03 Duration: 26m9s Bind query: yes
9 12m59s 12m59s 12m59s 1 12m59s 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 #9
Day Hour Count Duration Avg duration Jun 27 00 1 12m59s 12m59s -
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-06-27 00:24:27 Duration: 12m59s Bind query: yes
10 9m25s 9m25s 9m25s 1 9m25s select maint_query_logs_archive ();Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jun 27 00 1 9m25s 9m25s [ User: pubc - Total duration: 9m25s - Times executed: 1 ]
[ Application: psql - Total duration: 9m25s - Times executed: 1 ]
-
/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();
Date: 2026-06-27 00:09:26 Duration: 9m25s Database: ctdprd51 User: pubc Application: psql
11 9m9s 9m9s 9m9s 1 9m9s select pub2.maint_phenotype_term_derive_data ();Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jun 27 09 1 9m9s 9m9s -
select pub2.maint_phenotype_term_derive_data ();
Date: 2026-06-27 09:41:24 Duration: 9m9s Bind query: yes
12 6m52s 6m52s 6m52s 1 6m52s copy pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jun 27 19 1 6m52s 6m52s -
COPY pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-06-27 19:44:35 Duration: 6m52s
13 6m47s 6m47s 6m47s 1 6m47s copy pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jun 27 18 1 6m47s 6m47s -
COPY pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-06-27 18:57:22 Duration: 6m47s
14 3m22s 3m22s 3m22s 1 3m22s select maint_term_label_derive_nm_fts ();Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jun 27 06 1 3m22s 3m22s -
SELECT maint_term_label_derive_nm_fts ();
Date: 2026-06-27 06:11:36 Duration: 3m22s Bind query: yes
15 3m20s 3m20s 3m20s 1 3m20s 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 #15
Day Hour Count Duration Avg duration Jun 27 00 1 3m20s 3m20s -
ALTER TABLE pub2.term_enrichment_agent ADD CONSTRAINT term_enrichment_agent_pk PRIMARY KEY (term_id, enriched_term_id, agent_term_id);
Date: 2026-06-27 00:29:54 Duration: 3m20s 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-06-27 00:29:53 Duration: 0ms
16 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 #16
Day Hour Count Duration Avg duration Jun 27 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-06-27 03:34:45 Duration: 2m53s Bind query: yes
17 2m26s 2m26s 2m26s 1 2m26s 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 #17
Day Hour Count Duration Avg duration Jun 27 01 1 2m26s 2m26s -
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-06-27 01:34:30 Duration: 2m26s Bind query: yes
18 2m13s 2m13s 2m13s 1 2m13s update pub2.term set has_exposures = false;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jun 27 01 1 2m13s 2m13s -
update pub2.TERM set has_exposures = false;
Date: 2026-06-27 01:02:34 Duration: 2m13s Bind query: yes
19 7s941ms 2m55s 54s728ms 4 3m38s select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jun 27 00 4 3m38s 54s728ms [ User: load - Total duration: 3m38s - Times executed: 4 ]
[ Application: pg_bulkload - Total duration: 3m38s - 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-06-27 00:08:35 Duration: 2m55s 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-06-27 00:58:25 Duration: 20s24ms 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-06-27 00:11:10 Duration: 15s394ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
20 5s2ms 23s855ms 13s632ms 46 10m27s 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 #20
Day Hour Count Duration Avg duration Jun 27 00 46 10m27s 13s632ms -
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-06-27 00:32:11 Duration: 23s855ms 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-06-27 00:32:34 Duration: 23s589ms 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-06-27 00:32:57 Duration: 22s621ms 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,735 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 2 FATAL entries
- 2 ERROR entries
- 1342 WARNING entries
- 6 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 1,069 Max number of times the same event was reported
- 1,352 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 Jun 27 06 1,069 2 224 WARNING: skipping "..." --- only superuser or database owner can vacuum it
Times Reported Most Frequent Error / Event #2
Day Hour Count Jun 27 06 224 3 43 WARNING: skipping "..." --- only superuser can vacuum it
Times Reported Most Frequent Error / Event #3
Day Hour Count Jun 27 06 43 4 6 WARNING: there is no transaction in progress
Times Reported Most Frequent Error / Event #4
Day Hour Count Jun 27 06 2 09 4 5 4 LOG: could not receive data from client: Connection timed out
Times Reported Most Frequent Error / Event #5
Day Hour Count Jun 27 20 1 21 1 22 2 6 2 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #6
Day Hour Count Jun 27 00 1 07 1 7 2 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #7
Day Hour Count Jun 27 00 1 07 1 - ERROR: canceling statement due to user request
Statement: SELECT pg_database_size(datname::text) FROM pg_catalog.pg_database WHERE datistemplate = false AND datname = $1;
Date: 2026-06-27 00:29:07 Database: ctdprd51 Application: User: zbx_monitor Remote:
8 2 LOG: could not send data to client: Broken pipe
Times Reported Most Frequent Error / Event #8
Day Hour Count Jun 27 00 1 07 1