pgBadger-
Global information
- Generated on Thu Jul 30 04:15:05 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260729
- Parsed 4,304 log entries in 3s
- Log start from 2026-07-30 00:00:01 to 2026-07-30 03:48:41
-
Overview
Global Stats
- 46 Number of unique normalized queries
- 34 Number of queries
- 2h58m3s Total query duration
- 2026-07-30 00:00:04 First query
- 2026-07-30 03:41:56 Last query
- 3 queries/s at 2026-07-30 03:41:56 Query peak
- 2h58m3s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 2h58m3s Execute total duration
- 0 Number of events
- 0 Number of unique normalized events
- 0 Max number of times the same event was reported
- 0 Number of cancellation
- 193 Total number of automatic vacuums
- 38 Total number of automatic analyzes
- 91 Number temporary file
- 138.88 MiB Max size of temporary file
- 35.30 MiB Average size of temporary file
- 414 Total number of sessions
- 168 sessions at 2026-07-30 00:43:47 Session peak
- 6d6h27m2s Total duration of sessions
- 21m48s Average duration of sessions
- 0 Average queries per session
- 25s806ms Average queries duration per session
- 21m22s Average idle time per session
- 414 Total number of connections
- 21 connections/s at 2026-07-30 00:33:11 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 3 queries/s Query Peak
- 2026-07-30 03:41:56 Date
SELECT Traffic
Key values
- 3 queries/s Query Peak
- 2026-07-30 03:41:56 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-07-30 00:40:47 Date
Queries duration
Key values
- 2h58m3s 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) Jul 30 00 26 0ms 33m14s 2m11s 1m25s 2m54s 33m14s 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 3 0ms 1h57m12s 40m4s 0ms 3m1s 1h57m12s 03 5 0ms 12s978ms 8s692ms 0ms 18s911ms 24s549ms Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jul 30 00 3 0 3m34s 0ms 0ms 36s214ms 01 0 0 0ms 0ms 0ms 0ms 02 1 0 1h57m12s 0ms 0ms 1h57m12s 03 5 0 8s692ms 0ms 0ms 24s549ms Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jul 30 00 9 9 0 0 2m31s 0ms 5s900ms 2m20s 01 0 0 0 0 0ms 0ms 0ms 0ms 02 1 0 0 0 2m53s 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms Day Hour Prepare Bind Bind/Prepare Percentage of prepare Jul 30 00 0 24 24.00 0.00% 01 0 0 0.00 0.00% 02 0 3 3.00 0.00% 03 0 5 5.00 0.00% Day Hour Count Average / Second Jul 30 00 190 0.05/s 01 81 0.02/s 02 83 0.02/s 03 60 0.02/s Day Hour Count Average Duration Average idle time Jul 30 00 190 12m27s 12m9s 01 81 29m38s 29m38s 02 83 29m38s 28m11s 03 60 29m58s 29m58s -
Connections
Established Connections
Key values
- 21 connections Connection Peak
- 2026-07-30 00:33:11 Date
Connections per database
Key values
- ctdprd51 Main Database
- 414 connections Total
Connections per user
Key values
- pubeu Main User
- 414 connections Total
-
Sessions
Simultaneous sessions
Key values
- 168 sessions Session Peak
- 2026-07-30 00:43:47 Date
Histogram of session times
Key values
- 268 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 414 sessions Total
Sessions per user
Key values
- pubeu Main User
- 414 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 414 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 1,255,826 buffers Checkpoint Peak
- 2026-07-30 01:13:56 Date
- 1619.884 seconds Highest write time
- 0.143 seconds Sync time
Checkpoints Wal files
Key values
- 539 files Wal files usage Peak
- 2026-07-30 01:13:56 Date
Checkpoints distance
Key values
- 17,239.71 Mo Distance Peak
- 2026-07-30 01:13:56 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Jul 30 00 3,572,918 2,587.089s 0.379s 2,593.009s 01 1,780,997 3,248.738s 0.005s 3,250.152s 02 39 3.991s 0.001s 3.996s 03 319,481 3,239.108s 0.012s 3,240.053s Day Hour Added Removed Recycled Synced files Longest sync Average sync Jul 30 00 0 68 2,690 834 0.109s 0.007s 01 0 0 645 158 0.001s 0.003s 02 0 0 0 11 0.001s 0.001s 03 0 287 10 103 0.003s 0.002s Day Hour Count Avg time (sec) Jul 30 00 0 0s 01 0 0s 02 0 0s 03 0 0s Day Hour Mean distance Mean estimate Jul 30 00 7,960,195.60 kB 8,730,804.40 kB 01 3,697,727.33 kB 8,117,015.67 kB 02 66.00 kB 6,618,281.00 kB 03 2,168,769.00 kB 6,015,726.50 kB -
Temporary Files
Size of temporary files
Key values
- 676.25 MiB Temp Files size Peak
- 2026-07-30 00:00:50 Date
Number of temporary files
Key values
- 15 per second Temp Files Peak
- 2026-07-30 00:00:55 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Jul 30 00 91 3.14 GiB 35.30 MiB 01 0 0 0 02 0 0 0 03 0 0 0 Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 10 676.25 MiB 8.00 KiB 136.41 MiB 67.62 MiB alter table pub1.gene_disease add constraint gene_disease_pk primary key (gene_id, disease_id);-
ALTER TABLE pub1.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);
Date: 2026-07-30 00:00:50 Duration: 7s163ms
-
ALTER TABLE pub1.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);
Date: 2026-07-30 00:00:50 Duration: 0ms
2 10 68.04 MiB 8.00 KiB 15.09 MiB 6.80 MiB alter table pub1.phenotype_term add constraint phenotype_term_pk primary key (phenotype_id, term_id);-
ALTER TABLE pub1.phenotype_term ADD CONSTRAINT phenotype_term_pk PRIMARY KEY (phenotype_id, term_id);
Date: 2026-07-30 00:00:55 Duration: 0ms
3 8 68.12 MiB 8.00 KiB 17.61 MiB 8.52 MiB alter table pub1.chem_disease add constraint chem_disease_pk primary key (chem_id, disease_id);-
ALTER TABLE pub1.chem_disease ADD CONSTRAINT chem_disease_pk PRIMARY KEY (chem_id, disease_id);
Date: 2026-07-30 00:01:02 Duration: 0ms
4 5 68.00 MiB 11.48 MiB 14.45 MiB 13.60 MiB create index ix_phenotype_term_term_id on pub1.phenotype_term using btree (term_id);-
CREATE INDEX ix_phenotype_term_term_id ON pub1.phenotype_term USING btree (term_id);
Date: 2026-07-30 00:00:55 Duration: 0ms
5 5 676.21 MiB 131.44 MiB 137.38 MiB 135.24 MiB create index ix_gene_disease_disease on pub1.gene_disease using btree (disease_id);-
CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);
Date: 2026-07-30 00:00:19 Duration: 10s752ms
-
CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);
Date: 2026-07-30 00:00:19 Duration: 0ms Database: ctdprd51 User: pub1
6 5 68.01 MiB 13.34 MiB 13.73 MiB 13.60 MiB create index ix_phenotype_term_phenotype_id on pub1.phenotype_term using btree (phenotype_id);-
CREATE INDEX ix_phenotype_term_phenotype_id ON pub1.phenotype_term USING btree (phenotype_id);
Date: 2026-07-30 00:00:54 Duration: 0ms
7 5 676.07 MiB 133.90 MiB 137.82 MiB 135.21 MiB create index ix_gene_disease_ind_chem_qty on pub1.gene_disease using btree (indirect_chem_qty) where (indirect_chem_qty > ?);-
CREATE INDEX ix_gene_disease_ind_chem_qty ON pub1.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);
Date: 2026-07-30 00:00:42 Duration: 7s815ms
-
CREATE INDEX ix_gene_disease_ind_chem_qty ON pub1.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);
Date: 2026-07-30 00:00:42 Duration: 0ms
8 5 40.00 KiB 8.00 KiB 8.00 KiB 8.00 KiB create index ix_gene_disease_exp_ref_qty on pub1.gene_disease using btree (exposure_reference_qty) where (exposure_reference_qty > ?);-
CREATE INDEX ix_gene_disease_exp_ref_qty ON pub1.gene_disease USING btree (exposure_reference_qty) WHERE (exposure_reference_qty > 0);
Date: 2026-07-30 00:00:43 Duration: 0ms
9 5 676.20 MiB 128.52 MiB 138.88 MiB 135.24 MiB create index ix_gene_disease_network_score on pub1.gene_disease using btree (network_score);-
CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);
Date: 2026-07-30 00:00:34 Duration: 15s432ms
-
CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);
Date: 2026-07-30 00:00:34 Duration: 0ms
10 5 688.00 KiB 128.00 KiB 152.00 KiB 137.60 KiB create index ix_gene_disease_cur_ref_qty on pub1.gene_disease using btree (curated_reference_qty) where (curated_reference_qty > ?);-
CREATE INDEX ix_gene_disease_cur_ref_qty ON pub1.gene_disease USING btree (curated_reference_qty) WHERE (curated_reference_qty > 0);
Date: 2026-07-30 00:00:35 Duration: 0ms
11 4 15.50 MiB 8.00 KiB 7.80 MiB 3.88 MiB alter table pub1.phenotype_term_axn add constraint phenotype_term_axn_pk primary key (phenotype_id, term_id, action_type_nm, action_degree_type_nm);-
ALTER TABLE pub1.phenotype_term_axn ADD CONSTRAINT phenotype_term_axn_pk PRIMARY KEY (phenotype_id, term_id, action_type_nm, action_degree_type_nm);
Date: 2026-07-30 00:00:57 Duration: 0ms
12 4 32.00 KiB 8.00 KiB 8.00 KiB 8.00 KiB create index ix_chem_disease_exp_ref_qty on pub1.chem_disease using btree (exposure_reference_qty) where (exposure_reference_qty > ?);-
CREATE INDEX ix_chem_disease_exp_ref_qty ON pub1.chem_disease USING btree (exposure_reference_qty) WHERE (exposure_reference_qty > 0);
Date: 2026-07-30 00:01:01 Duration: 0ms
13 4 2.04 MiB 504.00 KiB 552.00 KiB 522.00 KiB create index ix_chem_disease_cur_ref_qty on pub1.chem_disease using btree (curated_reference_qty) where (curated_reference_qty > ?);-
CREATE INDEX ix_chem_disease_cur_ref_qty ON pub1.chem_disease USING btree (curated_reference_qty) WHERE (curated_reference_qty > 0);
Date: 2026-07-30 00:01:01 Duration: 0ms
14 4 68.09 MiB 16.97 MiB 17.09 MiB 17.02 MiB create index ix_chem_disease_network_score on pub1.chem_disease using btree (network_score);-
CREATE INDEX ix_chem_disease_network_score ON pub1.chem_disease USING btree (network_score);
Date: 2026-07-30 00:01:00 Duration: 0ms
15 4 68.09 MiB 14.71 MiB 18.39 MiB 17.02 MiB create index ix_chem_disease_disease on pub1.chem_disease using btree (disease_id);-
CREATE INDEX ix_chem_disease_disease ON pub1.chem_disease USING btree (disease_id);
Date: 2026-07-30 00:01:00 Duration: 0ms
16 4 67.20 MiB 15.98 MiB 17.16 MiB 16.80 MiB create index ix_chem_disease_ind_gene_qty on pub1.chem_disease using btree (indirect_gene_qty) where (indirect_gene_qty > ?);-
CREATE INDEX ix_chem_disease_ind_gene_qty ON pub1.chem_disease USING btree (indirect_gene_qty) WHERE (indirect_gene_qty > 0);
Date: 2026-07-30 00:01:01 Duration: 0ms
17 2 7.02 MiB 2.98 MiB 4.05 MiB 3.51 MiB create index ix_phenotype_term_axn_phenotype_id on pub1.phenotype_term_axn using btree (phenotype_id);-
CREATE INDEX ix_phenotype_term_axn_phenotype_id ON pub1.phenotype_term_axn USING btree (phenotype_id);
Date: 2026-07-30 00:00:57 Duration: 0ms
18 2 7.02 MiB 3.06 MiB 3.96 MiB 3.51 MiB create index ix_phenotype_term_axn_term_id on pub1.phenotype_term_axn using btree (term_id);-
CREATE INDEX ix_phenotype_term_axn_term_id ON pub1.phenotype_term_axn USING btree (term_id);
Date: 2026-07-30 00:00:58 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 138.88 MiB CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);[ Date: 2026-07-30 00:00:34 ]
2 137.82 MiB CREATE INDEX ix_gene_disease_ind_chem_qty ON pub1.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);[ Date: 2026-07-30 00:00:42 ]
3 137.38 MiB CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);[ Date: 2026-07-30 00:00:19 - Database: ctdprd51 - User: pub1 ]
4 137.30 MiB CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);[ Date: 2026-07-30 00:00:19 ]
5 136.93 MiB CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);[ Date: 2026-07-30 00:00:34 ]
6 136.57 MiB CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);[ Date: 2026-07-30 00:00:19 ]
7 136.50 MiB CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);[ Date: 2026-07-30 00:00:34 ]
8 136.41 MiB ALTER TABLE pub1.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);[ Date: 2026-07-30 00:00:50 ]
9 136.01 MiB CREATE INDEX ix_gene_disease_ind_chem_qty ON pub1.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);[ Date: 2026-07-30 00:00:42 ]
10 135.38 MiB CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);[ Date: 2026-07-30 00:00:34 ]
11 135.21 MiB ALTER TABLE pub1.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);[ Date: 2026-07-30 00:00:50 ]
12 135.09 MiB ALTER TABLE pub1.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);[ Date: 2026-07-30 00:00:50 ]
13 134.77 MiB ALTER TABLE pub1.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);[ Date: 2026-07-30 00:00:50 ]
14 134.74 MiB ALTER TABLE pub1.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);[ Date: 2026-07-30 00:00:50 ]
15 134.29 MiB CREATE INDEX ix_gene_disease_ind_chem_qty ON pub1.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);[ Date: 2026-07-30 00:00:42 ]
16 134.05 MiB CREATE INDEX ix_gene_disease_ind_chem_qty ON pub1.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);[ Date: 2026-07-30 00:00:42 ]
17 133.90 MiB CREATE INDEX ix_gene_disease_ind_chem_qty ON pub1.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);[ Date: 2026-07-30 00:00:42 ]
18 133.52 MiB CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);[ Date: 2026-07-30 00:00:19 ]
19 131.44 MiB CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);[ Date: 2026-07-30 00:00:19 ]
20 128.52 MiB CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);[ Date: 2026-07-30 00:00:34 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 389.00 sec Highest CPU-cost vacuum
Table pub1.gene_disease
Database ctdprd51 - 2026-07-30 00:49:33 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 389.00 sec Highest CPU-cost vacuum
Table pub1.gene_disease
Database ctdprd51 - 2026-07-30 00:49:33 Date
Analyzes per table
Key values
- pubc.log_query (6) Main table analyzed (database ctdprd51)
- 38 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 6 ctdprd51.pub1.dag_node 1 ctdprd51.pub1.gene_disease 1 ctdprd51.pg_catalog.pg_class 1 ctdprd51.pub1.exp_receptor_race 1 ctdprd51.pub1.exp_stressor 1 ctdprd51.pub1.geographic_region 1 ctdprd51.pub1.ixn 1 ctdprd51.pub1.reference 1 ctdprd51.pub1.term 1 ctdprd51.pub1.exp_study_factor 1 ctdprd51.pub1.reference_exp 1 ctdprd51.pub1.country 1 ctdprd51.pub1.gene_gene_reference 1 ctdprd51.pub1.exp_receptor_gender 1 ctdprd51.pub1.gene_gene 1 ctdprd51.pub1.exposure 1 ctdprd51.pub1.exp_receptor_tobacco_use 1 ctdprd51.pub1.medium 1 ctdprd51.pub1.exp_event_assay_method 1 ctdprd51.pub1.gene_chem_ref_gene_form 1 ctdprd51.pub1.exp_stressor_stressor_src 1 ctdprd51.pub1.exp_event 1 ctdprd51.pub1.phenotype_term 1 ctdprd51.pub1.slim_term_mapping 1 ctdprd51.pub1.gene_gene_ref_throughput 1 ctdprd51.pub1.exp_event_project 1 ctdprd51.pub1.exp_outcome 1 ctdprd51.pub1.exp_event_location 1 ctdprd51.pub1.chem_disease 1 ctdprd51.pub1.exp_anatomy 1 ctdprd51.pub1.exp_receptor 1 ctdprd51.pub1.term_reference 1 Total 38 Vacuums per table
Key values
- pub1.term (36) Main table vacuumed on database ctdprd51
- 193 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pub1.term 36 1 7,197,308 0 251,790 0 0 360,620 206,958 702,224,494 ctdprd51.pub1.dag_node 34 1 6,110,189 0 285,812 0 0 289,686 201,073 497,279,912 ctdprd51.pub1.reference 34 1 5,456,562 0 74,465 5 0 160,292 81,243 161,039,935 ctdprd51.pub1.chem_disease 33 1 3,635,258 0 93,840 0 0 172,137 67,652 227,877,958 ctdprd51.pubc.log_query 30 1 6,493 0 151 0 0 261 78 109,265 ctdprd51.pg_catalog.pg_statistic 1 1 600 0 196 0 125 228 89 436,038 ctdprd51.pub1.exp_receptor_tobacco_use 1 0 1,320 0 2 0 0 1 0 281 ctdprd51.pub1.exp_stressor 1 0 7,065 0 3 0 0 1 1 6,801 ctdprd51.pub1.exposure 1 0 4,178 0 3 0 0 1 1 7,101 ctdprd51.pub1.gene_gene 1 0 13,291 0 5 0 0 6,594 2 402,825 ctdprd51.pub1.exp_receptor_race 1 0 1,434 0 2 0 0 1 0 281 ctdprd51.pub1.gene_disease 1 1 3,088,535 0 1,002,351 0 0 1,710,477 902,466 2,234,849,673 ctdprd51.pg_toast.pg_toast_11936277 1 1 93 0 3 0 0 50 10 11,996 ctdprd51.pub1.exp_event_assay_method 1 0 5,595 0 3 0 0 1 1 5,981 ctdprd51.pub1.gene_chem_ref_gene_form 1 0 36,227 0 3 0 0 18,063 2 1,077,516 ctdprd51.pub1.exp_stressor_stressor_src 1 0 3,043 0 3 0 0 1 0 281 ctdprd51.pub1.exp_event 1 0 14,088 0 2 0 0 1 0 281 ctdprd51.pub1.phenotype_term 1 1 223,192 0 687 0 0 170,437 65,810 153,996,278 ctdprd51.pub1.ixn 1 1 1,647,242 0 98 0 0 1,094,971 49,963 256,494,455 ctdprd51.pub1.gene_gene_reference 1 0 33,327 0 3 0 0 16,586 1 986,993 ctdprd51.pub1.exp_event_project 1 0 2,428 0 2 0 0 1 0 281 ctdprd51.pub1.reference_exp 1 0 346 0 3 0 0 1 1 3,389 ctdprd51.pub1.exp_study_factor 1 0 81 0 2 0 0 1 0 281 ctdprd51.pub1.gene_gene_ref_throughput 1 0 15,995 0 3 0 0 7,958 1 477,941 ctdprd51.pub1.slim_term_mapping 1 0 606 0 4 0 0 265 2 30,058 ctdprd51.pub1.exp_receptor 1 0 8,176 0 2 0 0 1 0 281 ctdprd51.pub1.exp_anatomy 1 0 163 0 2 0 0 1 0 281 ctdprd51.pub1.term_reference 1 0 40,786 0 5 0 0 20,338 2 1,212,581 ctdprd51.pub1.exp_event_location 1 0 3,882 0 3 0 0 1 1 6,057 ctdprd51.pub1.exp_receptor_gender 1 0 3,006 0 2 0 0 1 0 281 ctdprd51.pub1.exp_outcome 1 0 1,000 0 3 0 0 1 1 6,145 Total 193 10 27,561,509 153,044 1,709,453 5 125 4,028,978 1,575,358 4,238,545,921 Tuples removed per table
Key values
- pub1.gene_disease (35382373) Main table with removed tuples on database ctdprd51
- 46637048 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pub1.gene_disease 1 1 35,382,373 35,382,373 0 0 520,329 ctdprd51.pub1.chem_disease 33 1 3,562,194 231,542,610 113,990,208 0 1,727,121 ctdprd51.pub1.phenotype_term 1 1 3,557,222 3,557,222 0 0 66,491 ctdprd51.pub1.term 36 1 2,215,345 157,051,247 77,537,075 0 3,474,792 ctdprd51.pub1.dag_node 34 1 1,825,008 122,009,112 60,225,264 0 2,969,254 ctdprd51.pub1.ixn 1 1 57,874 2,535,212 0 0 603,943 ctdprd51.pub1.reference 34 1 35,085 13,753,982 6,837,099 0 2,687,462 ctdprd51.pubc.log_query 30 1 1,631 59,251 51,172 0 2,092 ctdprd51.pg_catalog.pg_statistic 1 1 248 3,385 301 0 410 ctdprd51.pg_toast.pg_toast_11936277 1 1 68 71 0 0 22 ctdprd51.pub1.exp_receptor_tobacco_use 1 0 0 88,384 0 0 624 ctdprd51.pub1.exp_stressor 1 0 0 239,186 0 0 3,502 ctdprd51.pub1.exposure 1 0 0 246,770 0 0 2,035 ctdprd51.pub1.gene_gene 1 0 0 1,219,652 0 0 6,593 ctdprd51.pub1.exp_receptor_race 1 0 0 105,119 0 0 681 ctdprd51.pub1.exp_event_assay_method 1 0 0 274,309 0 0 2,768 ctdprd51.pub1.gene_chem_ref_gene_form 1 0 0 3,334,360 0 0 18,062 ctdprd51.pub1.exp_stressor_stressor_src 1 0 0 337,026 0 0 1,492 ctdprd51.pub1.exp_event 1 0 0 235,732 0 0 6,965 ctdprd51.pub1.gene_gene_reference 1 0 0 1,520,765 0 0 16,585 ctdprd51.pub1.exp_event_project 1 0 0 113,913 0 0 1,191 ctdprd51.pub1.reference_exp 1 0 0 3,747 0 0 135 ctdprd51.pub1.exp_study_factor 1 0 0 1,794 0 0 11 ctdprd51.pub1.gene_gene_ref_throughput 1 0 0 1,528,421 0 0 7,957 ctdprd51.pub1.slim_term_mapping 1 0 0 33,517 0 0 264 ctdprd51.pub1.exp_receptor 1 0 0 218,063 0 0 4,058 ctdprd51.pub1.exp_anatomy 1 0 0 4,377 0 0 37 ctdprd51.pub1.term_reference 1 0 0 3,762,172 0 0 20,337 ctdprd51.pub1.exp_event_location 1 0 0 282,536 0 0 1,889 ctdprd51.pub1.exp_receptor_gender 1 0 0 214,677 0 0 1,487 ctdprd51.pub1.exp_outcome 1 0 0 48,479 0 0 441 Total 193 10 46,637,048 579,707,464 258,641,119 0 12,149,030 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.pg_catalog.pg_statistic 1 1 248 0 ctdprd51.pub1.exp_receptor_tobacco_use 1 0 0 0 ctdprd51.pub1.exp_stressor 1 0 0 0 ctdprd51.pub1.exposure 1 0 0 0 ctdprd51.pub1.gene_gene 1 0 0 0 ctdprd51.pub1.exp_receptor_race 1 0 0 0 ctdprd51.pub1.dag_node 34 1 1825008 0 ctdprd51.pub1.gene_disease 1 1 35382373 0 ctdprd51.pg_toast.pg_toast_11936277 1 1 68 0 ctdprd51.pub1.term 36 1 2215345 0 ctdprd51.pub1.exp_event_assay_method 1 0 0 0 ctdprd51.pub1.gene_chem_ref_gene_form 1 0 0 0 ctdprd51.pub1.exp_stressor_stressor_src 1 0 0 0 ctdprd51.pub1.exp_event 1 0 0 0 ctdprd51.pub1.phenotype_term 1 1 3557222 0 ctdprd51.pub1.reference 34 1 35085 0 ctdprd51.pub1.ixn 1 1 57874 0 ctdprd51.pub1.gene_gene_reference 1 0 0 0 ctdprd51.pub1.exp_event_project 1 0 0 0 ctdprd51.pub1.reference_exp 1 0 0 0 ctdprd51.pub1.exp_study_factor 1 0 0 0 ctdprd51.pub1.gene_gene_ref_throughput 1 0 0 0 ctdprd51.pub1.slim_term_mapping 1 0 0 0 ctdprd51.pub1.exp_receptor 1 0 0 0 ctdprd51.pub1.exp_anatomy 1 0 0 0 ctdprd51.pub1.term_reference 1 0 0 0 ctdprd51.pubc.log_query 30 1 1631 0 ctdprd51.pub1.exp_event_location 1 0 0 0 ctdprd51.pub1.chem_disease 33 1 3562194 0 ctdprd51.pub1.exp_receptor_gender 1 0 0 0 ctdprd51.pub1.exp_outcome 1 0 0 0 Total 193 10 46,637,048 0 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Jul 30 00 191 32 01 0 2 02 2 3 03 0 1 - 389.00 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
- 9 Total read queries
- 23 Total write queries
Queries by database
Key values
- unknown Main database
- 24 Requests
- 2h46m6s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 431 Requests
User Request type Count Duration edit Total 2 17s999ms insert 2 17s999ms load Total 45 2h7m18s select 45 2h7m18s postgres Total 24 26m41s copy to 24 26m41s pub1 Total 6 35m32s insert 4 35m14s select 2 17s84ms pubc Total 2 19m7s select 2 19m7s pubeu Total 27 4m select 27 4m qaeu Total 4 23s696ms select 4 23s696ms unknown Total 431 12h54m51s copy to 84 17m56s ddl 60 1h31m8s insert 26 1h37m33s others 15 9m45s select 237 8h35m28s update 9 42m59s Duration by user
Key values
- 12h54m51s (unknown) Main time consuming user
User Request type Count Duration edit Total 2 17s999ms insert 2 17s999ms load Total 45 2h7m18s select 45 2h7m18s postgres Total 24 26m41s copy to 24 26m41s pub1 Total 6 35m32s insert 4 35m14s select 2 17s84ms pubc Total 2 19m7s select 2 19m7s pubeu Total 27 4m select 27 4m qaeu Total 4 23s696ms select 4 23s696ms unknown Total 431 12h54m51s copy to 84 17m56s ddl 60 1h31m8s insert 26 1h37m33s others 15 9m45s select 237 8h35m28s update 9 42m59s Queries by host
Key values
- unknown Main host
- 541 Requests
- 16h28m12s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 32 Requests
- 2h47m56s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-07-30 02:42:59 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 20 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 1h57m12s select pub1.maint_gene_chem_ref_gene_form_refresh ();[ Date: 2026-07-30 02:44:16 - Bind query: yes ]
2 33m14s update pub1.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));[ Date: 2026-07-30 00:40:47 - Bind query: yes ]
3 9m46s /* * 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-07-30 00:09:48 - Database: ctdprd51 - User: pubc - Application: psql ]
4 2m54s update pub1.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));[ Date: 2026-07-30 00:43:41 - Bind query: yes ]
5 2m53s INSERT INTO pub1.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 pub1.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub1.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub1.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub1.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.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 pub1.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.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, pub1.TERM t, edit.IXN i, pub1.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub1.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 pub1.EXP_ANATOMY ea, pub1.EXP_OUTCOME eo, pub1.EXPOSURE e, pub1.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 pub1.IXN i, pub1.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub1.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 pub1.EXP_EVENT ee, pub1.EXPOSURE e, pub1.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-07-30 02:47:10 - Bind query: yes ]
6 2m11s update pub1.TERM set has_exposures = false;[ Date: 2026-07-30 00:04:16 - Bind query: yes ]
7 1m25s update pub1.CHEM_DISEASE cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.CHEM_DISEASE_REFERENCE cdr where cd.chem_id = cdr.chem_id and cd.disease_id = cdr.disease_id and reference_id in ( select id from pub1.REFERENCE r where has_exposures = true));[ Date: 2026-07-30 00:07:32 - Bind query: yes ]
8 1m24s update pub1.DAG_NODE set has_exposures = false;[ Date: 2026-07-30 00:05:50 - Bind query: yes ]
9 1m18s update pub1.IXN set ixn_xml = replace(ixn_xml, '''', '"');[ Date: 2026-07-30 00:46:58 - Bind query: yes ]
10 1m4s INSERT INTO pub1.GENE_GENE_REFERENCE (id, from_gene_id, to_gene_id, reference_id, from_taxon_id, to_taxon_id, src_db_id, experimental_sys_nm, experimental_sys_type) select ggr. id, ggr.from_gene_id, ggr.to_gene_id, l.object_id, ggr.from_taxon_id, ggr.to_taxon_id, ggr.src_db_id, ggr.experimental_sys_nm, ggr.experimental_sys_type from load.GENE_GENE_REFERENCE ggr, edit.DB_LINK l where ggr.reference_acc_txt = l.acc_txt and l.object_type_id = 7 and l.db_id = 16;[ Date: 2026-07-30 00:45:07 - Bind query: yes ]
11 36s214ms SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1321052') and diseaseTerm.object_type_id = 3 AND EXISTS ( SELECT 1 FROM slim_term_mapping stm WHERE stm.mapped_term_id = pt.term_id AND stm.slim_term_nm = 'Cancer') ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;[ Date: 2026-07-30 00:52:31 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
12 21s4ms insert into pub1.GENE_GENE (from_gene_id, to_gene_id, reference_qty) select from_gene_id, to_gene_id, reference_qty from load.GENE_GENE;[ Date: 2026-07-30 00:44:03 - Database: ctdprd51 - User: pub1 - Bind query: yes ]
13 20s931ms SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.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-07-30 00:00:04 - Database: ctdprd51 - User: load - Application: pg_bulkload - Bind query: yes ]
14 17s79ms INSERT INTO pub1.DB_LINK (object_type_id, object_id, db_id, acc_txt, type_cd, is_primary) SELECT object_type_id, object_id, db_id, acc_txt, type_cd, is_primary FROM edit.DB_LINK where object_type_id = 4 and (object_type_id, object_id, db_id, acc_txt) not in ( select object_type_id, object_id, db_id, acc_txt from pub1.DB_LINK where object_type_id = 4);[ Date: 2026-07-30 00:45:39 - Bind query: yes ]
15 15s551ms update pub1.REFERENCE set has_exposures = false;[ Date: 2026-07-30 00:06:06 - Bind query: yes ]
16 15s432ms CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);[ Date: 2026-07-30 00:00:34 - Bind query: yes ]
17 14s578ms INSERT INTO pub1.GENE_GENE_REF_THROUGHPUT (gene_gene_reference_id, throughput_txt) select gene_gene_reference_id, throughput_txt from load.GENE_GENE_REF_THROUGHPUT ggrt where ggrt.gene_gene_reference_id in ( select id from pub1.GENE_GENE_REFERENCE);[ Date: 2026-07-30 00:45:22 - Bind query: yes ]
18 12s978ms SELECT /* ChemGODAO */ ;[ Date: 2026-07-30 03:00:23 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
19 10s752ms CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);[ Date: 2026-07-30 00:00:19 - Bind query: yes ]
20 10s263ms INSERT INTO pub1.EXPOSURE (id, reference_id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id) SELECT e.id, r.id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id FROM edit.EXPOSURE e INNER JOIN pub1.REFERENCE r ON e.reference_acc_txt = r.acc_txt AND r.acc_db_cd = 'PUBMED';[ Date: 2026-07-30 00:02:05 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 1h57m12s 1 1h57m12s 1h57m12s 1h57m12s select pub1.maint_gene_chem_ref_gene_form_refresh ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 30 02 1 1h57m12s 1h57m12s -
select pub1.maint_gene_chem_ref_gene_form_refresh ();
Date: 2026-07-30 02:44:16 Duration: 1h57m12s Bind query: yes
2 33m14s 1 33m14s 33m14s 33m14s update pub1.gene_disease gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.reference r where has_exposures = true));Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jul 30 00 1 33m14s 33m14s -
update pub1.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:40:47 Duration: 33m14s Bind query: yes
3 9m46s 1 9m46s 9m46s 9m46s select maint_query_logs_archive ();Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jul 30 00 1 9m46s 9m46s [ User: pubc - Total duration: 9m46s - Times executed: 1 ]
[ Application: psql - Total duration: 9m46s - 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-07-30 00:09:48 Duration: 9m46s Database: ctdprd51 User: pubc Application: psql
4 2m54s 1 2m54s 2m54s 2m54s update pub1.phenotype_term pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.reference r where has_exposures = true));Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jul 30 00 1 2m54s 2m54s -
update pub1.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:43:41 Duration: 2m54s Bind query: yes
5 2m53s 1 2m53s 2m53s 2m53s insert into pub1.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 pub1.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference union select chem_id as term_id, ( select object_type_id from pub1.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference union select taxon_id as term_id, ( select object_type_id from pub1.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference where taxon_id is not null union select chem_id as term_id, ( select object_type_id from pub1.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.chem_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub1.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.chem_disease_reference where source_cd = ? union select gene_id as term_id, ( select object_type_id from pub1.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub1.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.term where id = phenotype_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.phenotype_term_reference where source_cd = ? union select term_id, ( select object_type_id from pub1.term where id = term_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.phenotype_term_reference where source_cd = ? union select taxon_id as term_id, ( select object_type_id from pub1.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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 pub1.term where id = from_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select to_gene_id as term_id, ( select object_type_id from pub1.term where id = to_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select from_taxon_id as term_id, ( select object_type_id from pub1.term where id = from_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select to_taxon_id as term_id, ( select object_type_id from pub1.term where id = to_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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, pub1.term t, edit.ixn i, pub1.reference r where ri.taxon_acc_txt = t.acc_txt and t.object_type_id = ( select id from pub1.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 pub1.exp_anatomy ea, pub1.exp_outcome eo, pub1.exposure e, pub1.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 pub1.ixn i, pub1.ixn_anatomy ia, edit.reference_ixn ri, pub1.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 pub1.exp_event ee, pub1.exposure e, pub1.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 #5
Day Hour Count Duration Avg duration Jul 30 02 1 2m53s 2m53s -
INSERT INTO pub1.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 pub1.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub1.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub1.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub1.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.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 pub1.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.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, pub1.TERM t, edit.IXN i, pub1.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub1.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 pub1.EXP_ANATOMY ea, pub1.EXP_OUTCOME eo, pub1.EXPOSURE e, pub1.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 pub1.IXN i, pub1.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub1.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 pub1.EXP_EVENT ee, pub1.EXPOSURE e, pub1.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-07-30 02:47:10 Duration: 2m53s Bind query: yes
6 2m11s 1 2m11s 2m11s 2m11s update pub1.term set has_exposures = false;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jul 30 00 1 2m11s 2m11s -
update pub1.TERM set has_exposures = false;
Date: 2026-07-30 00:04:16 Duration: 2m11s Bind query: yes
7 1m25s 1 1m25s 1m25s 1m25s update pub1.chem_disease cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.chem_disease_reference cdr where cd.chem_id = cdr.chem_id and cd.disease_id = cdr.disease_id and reference_id in ( select id from pub1.reference r where has_exposures = true));Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 30 00 1 1m25s 1m25s -
update pub1.CHEM_DISEASE cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.CHEM_DISEASE_REFERENCE cdr where cd.chem_id = cdr.chem_id and cd.disease_id = cdr.disease_id and reference_id in ( select id from pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:07:32 Duration: 1m25s Bind query: yes
8 1m24s 1 1m24s 1m24s 1m24s update pub1.dag_node set has_exposures = false;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 30 00 1 1m24s 1m24s -
update pub1.DAG_NODE set has_exposures = false;
Date: 2026-07-30 00:05:50 Duration: 1m24s Bind query: yes
9 1m18s 1 1m18s 1m18s 1m18s update pub1.ixn set ixn_xml = replace(ixn_xml, ?, ?);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 30 00 1 1m18s 1m18s -
update pub1.IXN set ixn_xml = replace(ixn_xml, '''', '"');
Date: 2026-07-30 00:46:58 Duration: 1m18s Bind query: yes
10 1m4s 1 1m4s 1m4s 1m4s insert into pub1.gene_gene_reference (id, from_gene_id, to_gene_id, reference_id, from_taxon_id, to_taxon_id, src_db_id, experimental_sys_nm, experimental_sys_type) select ggr. id, ggr.from_gene_id, ggr.to_gene_id, l.object_id, ggr.from_taxon_id, ggr.to_taxon_id, ggr.src_db_id, ggr.experimental_sys_nm, ggr.experimental_sys_type from load.gene_gene_reference ggr, edit.db_link l where ggr.reference_acc_txt = l.acc_txt and l.object_type_id = ? and l.db_id = ?;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 30 00 1 1m4s 1m4s -
INSERT INTO pub1.GENE_GENE_REFERENCE (id, from_gene_id, to_gene_id, reference_id, from_taxon_id, to_taxon_id, src_db_id, experimental_sys_nm, experimental_sys_type) select ggr. id, ggr.from_gene_id, ggr.to_gene_id, l.object_id, ggr.from_taxon_id, ggr.to_taxon_id, ggr.src_db_id, ggr.experimental_sys_nm, ggr.experimental_sys_type from load.GENE_GENE_REFERENCE ggr, edit.DB_LINK l where ggr.reference_acc_txt = l.acc_txt and l.object_type_id = 7 and l.db_id = 16;
Date: 2026-07-30 00:45:07 Duration: 1m4s Bind query: yes
11 36s214ms 1 36s214ms 36s214ms 36s214ms select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where phenotypeterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? and exists ( select ? from slim_term_mapping stm where stm.mapped_term_id = pt.term_id and stm.slim_term_nm = ?) order by chemnetworkcount desc, genenetworkcount desc limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 30 00 1 36s214ms 36s214ms [ User: pubeu - Total duration: 36s214ms - Times executed: 1 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1321052') and diseaseTerm.object_type_id = 3 AND EXISTS ( SELECT 1 FROM slim_term_mapping stm WHERE stm.mapped_term_id = pt.term_id AND stm.slim_term_nm = 'Cancer') ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-07-30 00:52:31 Duration: 36s214ms Database: ctdprd51 User: pubeu Bind query: yes
12 29s648ms 3 6s685ms 12s978ms 9s882ms select ;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 30 03 3 29s648ms 9s882ms [ User: pubeu - Total duration: 29s648ms - Times executed: 3 ]
-
SELECT /* ChemGODAO */ ;
Date: 2026-07-30 03:00:23 Duration: 12s978ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* RefsDAO */ ;
Date: 2026-07-30 03:41:56 Duration: 9s984ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* RefsDAO */ ;
Date: 2026-07-30 03:41:56 Duration: 6s685ms Database: ctdprd51 User: pubeu Bind query: yes
13 21s4ms 1 21s4ms 21s4ms 21s4ms insert into pub1.gene_gene (from_gene_id, to_gene_id, reference_qty) select from_gene_id, to_gene_id, reference_qty from load.gene_gene;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 30 00 1 21s4ms 21s4ms [ User: pub1 - Total duration: 21s4ms - Times executed: 1 ]
-
insert into pub1.GENE_GENE (from_gene_id, to_gene_id, reference_qty) select from_gene_id, to_gene_id, reference_qty from load.GENE_GENE;
Date: 2026-07-30 00:44:03 Duration: 21s4ms Database: ctdprd51 User: pub1 Bind query: yes
14 20s931ms 1 20s931ms 20s931ms 20s931ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 30 00 1 20s931ms 20s931ms [ User: load - Total duration: 20s931ms - Times executed: 1 ]
[ Application: pg_bulkload - Total duration: 20s931ms - Times executed: 1 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.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-07-30 00:00:04 Duration: 20s931ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
15 17s79ms 1 17s79ms 17s79ms 17s79ms insert into pub1.db_link (object_type_id, object_id, db_id, acc_txt, type_cd, is_primary) select object_type_id, object_id, db_id, acc_txt, type_cd, is_primary from edit.db_link where object_type_id = ? and (object_type_id, object_id, db_id, acc_txt) not in ( select object_type_id, object_id, db_id, acc_txt from pub1.db_link where object_type_id = ?);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 30 00 1 17s79ms 17s79ms -
INSERT INTO pub1.DB_LINK (object_type_id, object_id, db_id, acc_txt, type_cd, is_primary) SELECT object_type_id, object_id, db_id, acc_txt, type_cd, is_primary FROM edit.DB_LINK where object_type_id = 4 and (object_type_id, object_id, db_id, acc_txt) not in ( select object_type_id, object_id, db_id, acc_txt from pub1.DB_LINK where object_type_id = 4);
Date: 2026-07-30 00:45:39 Duration: 17s79ms Bind query: yes
16 15s551ms 1 15s551ms 15s551ms 15s551ms update pub1.reference set has_exposures = false;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 30 00 1 15s551ms 15s551ms -
update pub1.REFERENCE set has_exposures = false;
Date: 2026-07-30 00:06:06 Duration: 15s551ms Bind query: yes
17 15s432ms 1 15s432ms 15s432ms 15s432ms create index ix_gene_disease_network_score on pub1.gene_disease using btree (network_score);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 30 00 1 15s432ms 15s432ms -
CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);
Date: 2026-07-30 00:00:34 Duration: 15s432ms Bind query: yes
-
CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);
Date: 2026-07-30 00:00:34 Duration: 0ms
18 14s578ms 1 14s578ms 14s578ms 14s578ms insert into pub1.gene_gene_ref_throughput (gene_gene_reference_id, throughput_txt) select gene_gene_reference_id, throughput_txt from load.gene_gene_ref_throughput ggrt where ggrt.gene_gene_reference_id in ( select id from pub1.gene_gene_reference);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jul 30 00 1 14s578ms 14s578ms -
INSERT INTO pub1.GENE_GENE_REF_THROUGHPUT (gene_gene_reference_id, throughput_txt) select gene_gene_reference_id, throughput_txt from load.GENE_GENE_REF_THROUGHPUT ggrt where ggrt.gene_gene_reference_id in ( select id from pub1.GENE_GENE_REFERENCE);
Date: 2026-07-30 00:45:22 Duration: 14s578ms Bind query: yes
19 10s752ms 1 10s752ms 10s752ms 10s752ms create index ix_gene_disease_disease on pub1.gene_disease using btree (disease_id);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jul 30 00 1 10s752ms 10s752ms -
CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);
Date: 2026-07-30 00:00:19 Duration: 10s752ms Bind query: yes
-
CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);
Date: 2026-07-30 00:00:19 Duration: 0ms Database: ctdprd51 User: pub1
20 10s263ms 1 10s263ms 10s263ms 10s263ms insert into pub1.exposure (id, reference_id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id) select e.id, r.id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id from edit.exposure e inner join pub1.reference r on e.reference_acc_txt = r.acc_txt and r.acc_db_cd = ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jul 30 00 1 10s263ms 10s263ms -
INSERT INTO pub1.EXPOSURE (id, reference_id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id) SELECT e.id, r.id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id FROM edit.EXPOSURE e INNER JOIN pub1.REFERENCE r ON e.reference_acc_txt = r.acc_txt AND r.acc_db_cd = 'PUBMED';
Date: 2026-07-30 00:02:05 Duration: 10s263ms Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 3 29s648ms 6s685ms 12s978ms 9s882ms select ;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 30 03 3 29s648ms 9s882ms [ User: pubeu - Total duration: 29s648ms - Times executed: 3 ]
-
SELECT /* ChemGODAO */ ;
Date: 2026-07-30 03:00:23 Duration: 12s978ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* RefsDAO */ ;
Date: 2026-07-30 03:41:56 Duration: 9s984ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* RefsDAO */ ;
Date: 2026-07-30 03:41:56 Duration: 6s685ms Database: ctdprd51 User: pubeu Bind query: yes
2 1 1h57m12s 1h57m12s 1h57m12s 1h57m12s select pub1.maint_gene_chem_ref_gene_form_refresh ();Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jul 30 02 1 1h57m12s 1h57m12s -
select pub1.maint_gene_chem_ref_gene_form_refresh ();
Date: 2026-07-30 02:44:16 Duration: 1h57m12s Bind query: yes
3 1 33m14s 33m14s 33m14s 33m14s update pub1.gene_disease gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.reference r where has_exposures = true));Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jul 30 00 1 33m14s 33m14s -
update pub1.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:40:47 Duration: 33m14s Bind query: yes
4 1 9m46s 9m46s 9m46s 9m46s select maint_query_logs_archive ();Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jul 30 00 1 9m46s 9m46s [ User: pubc - Total duration: 9m46s - Times executed: 1 ]
[ Application: psql - Total duration: 9m46s - 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-07-30 00:09:48 Duration: 9m46s Database: ctdprd51 User: pubc Application: psql
5 1 2m54s 2m54s 2m54s 2m54s update pub1.phenotype_term pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.reference r where has_exposures = true));Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jul 30 00 1 2m54s 2m54s -
update pub1.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:43:41 Duration: 2m54s Bind query: yes
6 1 2m53s 2m53s 2m53s 2m53s insert into pub1.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 pub1.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference union select chem_id as term_id, ( select object_type_id from pub1.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference union select taxon_id as term_id, ( select object_type_id from pub1.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference where taxon_id is not null union select chem_id as term_id, ( select object_type_id from pub1.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.chem_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub1.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.chem_disease_reference where source_cd = ? union select gene_id as term_id, ( select object_type_id from pub1.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub1.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.term where id = phenotype_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.phenotype_term_reference where source_cd = ? union select term_id, ( select object_type_id from pub1.term where id = term_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.phenotype_term_reference where source_cd = ? union select taxon_id as term_id, ( select object_type_id from pub1.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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 pub1.term where id = from_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select to_gene_id as term_id, ( select object_type_id from pub1.term where id = to_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select from_taxon_id as term_id, ( select object_type_id from pub1.term where id = from_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select to_taxon_id as term_id, ( select object_type_id from pub1.term where id = to_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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, pub1.term t, edit.ixn i, pub1.reference r where ri.taxon_acc_txt = t.acc_txt and t.object_type_id = ( select id from pub1.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 pub1.exp_anatomy ea, pub1.exp_outcome eo, pub1.exposure e, pub1.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 pub1.ixn i, pub1.ixn_anatomy ia, edit.reference_ixn ri, pub1.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 pub1.exp_event ee, pub1.exposure e, pub1.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 #6
Day Hour Count Duration Avg duration Jul 30 02 1 2m53s 2m53s -
INSERT INTO pub1.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 pub1.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub1.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub1.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub1.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.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 pub1.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.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, pub1.TERM t, edit.IXN i, pub1.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub1.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 pub1.EXP_ANATOMY ea, pub1.EXP_OUTCOME eo, pub1.EXPOSURE e, pub1.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 pub1.IXN i, pub1.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub1.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 pub1.EXP_EVENT ee, pub1.EXPOSURE e, pub1.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-07-30 02:47:10 Duration: 2m53s Bind query: yes
7 1 2m11s 2m11s 2m11s 2m11s update pub1.term set has_exposures = false;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 30 00 1 2m11s 2m11s -
update pub1.TERM set has_exposures = false;
Date: 2026-07-30 00:04:16 Duration: 2m11s Bind query: yes
8 1 1m25s 1m25s 1m25s 1m25s update pub1.chem_disease cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.chem_disease_reference cdr where cd.chem_id = cdr.chem_id and cd.disease_id = cdr.disease_id and reference_id in ( select id from pub1.reference r where has_exposures = true));Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 30 00 1 1m25s 1m25s -
update pub1.CHEM_DISEASE cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.CHEM_DISEASE_REFERENCE cdr where cd.chem_id = cdr.chem_id and cd.disease_id = cdr.disease_id and reference_id in ( select id from pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:07:32 Duration: 1m25s Bind query: yes
9 1 1m24s 1m24s 1m24s 1m24s update pub1.dag_node set has_exposures = false;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 30 00 1 1m24s 1m24s -
update pub1.DAG_NODE set has_exposures = false;
Date: 2026-07-30 00:05:50 Duration: 1m24s Bind query: yes
10 1 1m18s 1m18s 1m18s 1m18s update pub1.ixn set ixn_xml = replace(ixn_xml, ?, ?);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 30 00 1 1m18s 1m18s -
update pub1.IXN set ixn_xml = replace(ixn_xml, '''', '"');
Date: 2026-07-30 00:46:58 Duration: 1m18s Bind query: yes
11 1 1m4s 1m4s 1m4s 1m4s insert into pub1.gene_gene_reference (id, from_gene_id, to_gene_id, reference_id, from_taxon_id, to_taxon_id, src_db_id, experimental_sys_nm, experimental_sys_type) select ggr. id, ggr.from_gene_id, ggr.to_gene_id, l.object_id, ggr.from_taxon_id, ggr.to_taxon_id, ggr.src_db_id, ggr.experimental_sys_nm, ggr.experimental_sys_type from load.gene_gene_reference ggr, edit.db_link l where ggr.reference_acc_txt = l.acc_txt and l.object_type_id = ? and l.db_id = ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 30 00 1 1m4s 1m4s -
INSERT INTO pub1.GENE_GENE_REFERENCE (id, from_gene_id, to_gene_id, reference_id, from_taxon_id, to_taxon_id, src_db_id, experimental_sys_nm, experimental_sys_type) select ggr. id, ggr.from_gene_id, ggr.to_gene_id, l.object_id, ggr.from_taxon_id, ggr.to_taxon_id, ggr.src_db_id, ggr.experimental_sys_nm, ggr.experimental_sys_type from load.GENE_GENE_REFERENCE ggr, edit.DB_LINK l where ggr.reference_acc_txt = l.acc_txt and l.object_type_id = 7 and l.db_id = 16;
Date: 2026-07-30 00:45:07 Duration: 1m4s Bind query: yes
12 1 36s214ms 36s214ms 36s214ms 36s214ms select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where phenotypeterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? and exists ( select ? from slim_term_mapping stm where stm.mapped_term_id = pt.term_id and stm.slim_term_nm = ?) order by chemnetworkcount desc, genenetworkcount desc limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 30 00 1 36s214ms 36s214ms [ User: pubeu - Total duration: 36s214ms - Times executed: 1 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1321052') and diseaseTerm.object_type_id = 3 AND EXISTS ( SELECT 1 FROM slim_term_mapping stm WHERE stm.mapped_term_id = pt.term_id AND stm.slim_term_nm = 'Cancer') ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-07-30 00:52:31 Duration: 36s214ms Database: ctdprd51 User: pubeu Bind query: yes
13 1 21s4ms 21s4ms 21s4ms 21s4ms insert into pub1.gene_gene (from_gene_id, to_gene_id, reference_qty) select from_gene_id, to_gene_id, reference_qty from load.gene_gene;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 30 00 1 21s4ms 21s4ms [ User: pub1 - Total duration: 21s4ms - Times executed: 1 ]
-
insert into pub1.GENE_GENE (from_gene_id, to_gene_id, reference_qty) select from_gene_id, to_gene_id, reference_qty from load.GENE_GENE;
Date: 2026-07-30 00:44:03 Duration: 21s4ms Database: ctdprd51 User: pub1 Bind query: yes
14 1 20s931ms 20s931ms 20s931ms 20s931ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 30 00 1 20s931ms 20s931ms [ User: load - Total duration: 20s931ms - Times executed: 1 ]
[ Application: pg_bulkload - Total duration: 20s931ms - Times executed: 1 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.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-07-30 00:00:04 Duration: 20s931ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
15 1 17s79ms 17s79ms 17s79ms 17s79ms insert into pub1.db_link (object_type_id, object_id, db_id, acc_txt, type_cd, is_primary) select object_type_id, object_id, db_id, acc_txt, type_cd, is_primary from edit.db_link where object_type_id = ? and (object_type_id, object_id, db_id, acc_txt) not in ( select object_type_id, object_id, db_id, acc_txt from pub1.db_link where object_type_id = ?);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 30 00 1 17s79ms 17s79ms -
INSERT INTO pub1.DB_LINK (object_type_id, object_id, db_id, acc_txt, type_cd, is_primary) SELECT object_type_id, object_id, db_id, acc_txt, type_cd, is_primary FROM edit.DB_LINK where object_type_id = 4 and (object_type_id, object_id, db_id, acc_txt) not in ( select object_type_id, object_id, db_id, acc_txt from pub1.DB_LINK where object_type_id = 4);
Date: 2026-07-30 00:45:39 Duration: 17s79ms Bind query: yes
16 1 15s551ms 15s551ms 15s551ms 15s551ms update pub1.reference set has_exposures = false;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 30 00 1 15s551ms 15s551ms -
update pub1.REFERENCE set has_exposures = false;
Date: 2026-07-30 00:06:06 Duration: 15s551ms Bind query: yes
17 1 15s432ms 15s432ms 15s432ms 15s432ms create index ix_gene_disease_network_score on pub1.gene_disease using btree (network_score);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 30 00 1 15s432ms 15s432ms -
CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);
Date: 2026-07-30 00:00:34 Duration: 15s432ms Bind query: yes
-
CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);
Date: 2026-07-30 00:00:34 Duration: 0ms
18 1 14s578ms 14s578ms 14s578ms 14s578ms insert into pub1.gene_gene_ref_throughput (gene_gene_reference_id, throughput_txt) select gene_gene_reference_id, throughput_txt from load.gene_gene_ref_throughput ggrt where ggrt.gene_gene_reference_id in ( select id from pub1.gene_gene_reference);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jul 30 00 1 14s578ms 14s578ms -
INSERT INTO pub1.GENE_GENE_REF_THROUGHPUT (gene_gene_reference_id, throughput_txt) select gene_gene_reference_id, throughput_txt from load.GENE_GENE_REF_THROUGHPUT ggrt where ggrt.gene_gene_reference_id in ( select id from pub1.GENE_GENE_REFERENCE);
Date: 2026-07-30 00:45:22 Duration: 14s578ms Bind query: yes
19 1 10s752ms 10s752ms 10s752ms 10s752ms create index ix_gene_disease_disease on pub1.gene_disease using btree (disease_id);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jul 30 00 1 10s752ms 10s752ms -
CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);
Date: 2026-07-30 00:00:19 Duration: 10s752ms Bind query: yes
-
CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);
Date: 2026-07-30 00:00:19 Duration: 0ms Database: ctdprd51 User: pub1
20 1 10s263ms 10s263ms 10s263ms 10s263ms insert into pub1.exposure (id, reference_id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id) select e.id, r.id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id from edit.exposure e inner join pub1.reference r on e.reference_acc_txt = r.acc_txt and r.acc_db_cd = ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jul 30 00 1 10s263ms 10s263ms -
INSERT INTO pub1.EXPOSURE (id, reference_id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id) SELECT e.id, r.id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id FROM edit.EXPOSURE e INNER JOIN pub1.REFERENCE r ON e.reference_acc_txt = r.acc_txt AND r.acc_db_cd = 'PUBMED';
Date: 2026-07-30 00:02:05 Duration: 10s263ms Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 1h57m12s 1h57m12s 1h57m12s 1 1h57m12s select pub1.maint_gene_chem_ref_gene_form_refresh ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 30 02 1 1h57m12s 1h57m12s -
select pub1.maint_gene_chem_ref_gene_form_refresh ();
Date: 2026-07-30 02:44:16 Duration: 1h57m12s Bind query: yes
2 33m14s 33m14s 33m14s 1 33m14s update pub1.gene_disease gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.reference r where has_exposures = true));Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jul 30 00 1 33m14s 33m14s -
update pub1.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:40:47 Duration: 33m14s Bind query: yes
3 9m46s 9m46s 9m46s 1 9m46s select maint_query_logs_archive ();Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jul 30 00 1 9m46s 9m46s [ User: pubc - Total duration: 9m46s - Times executed: 1 ]
[ Application: psql - Total duration: 9m46s - 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-07-30 00:09:48 Duration: 9m46s Database: ctdprd51 User: pubc Application: psql
4 2m54s 2m54s 2m54s 1 2m54s update pub1.phenotype_term pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.reference r where has_exposures = true));Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jul 30 00 1 2m54s 2m54s -
update pub1.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub1.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 pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:43:41 Duration: 2m54s Bind query: yes
5 2m53s 2m53s 2m53s 1 2m53s insert into pub1.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 pub1.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference union select chem_id as term_id, ( select object_type_id from pub1.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference union select taxon_id as term_id, ( select object_type_id from pub1.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_chem_reference where taxon_id is not null union select chem_id as term_id, ( select object_type_id from pub1.term where id = chem_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.chem_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub1.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.chem_disease_reference where source_cd = ? union select gene_id as term_id, ( select object_type_id from pub1.term where id = gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_disease_reference where source_cd = ? union select disease_id as term_id, ( select object_type_id from pub1.term where id = disease_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.exposure e, pub1.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 pub1.term where id = phenotype_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.phenotype_term_reference where source_cd = ? union select term_id, ( select object_type_id from pub1.term where id = term_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.phenotype_term_reference where source_cd = ? union select taxon_id as term_id, ( select object_type_id from pub1.term where id = taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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 pub1.term where id = from_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select to_gene_id as term_id, ( select object_type_id from pub1.term where id = to_gene_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select from_taxon_id as term_id, ( select object_type_id from pub1.term where id = from_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.gene_gene_reference union select to_taxon_id as term_id, ( select object_type_id from pub1.term where id = to_taxon_id), reference_id, ( select id from edit.ixn_type where nm = ?) as ixn_type_id from pub1.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, pub1.term t, edit.ixn i, pub1.reference r where ri.taxon_acc_txt = t.acc_txt and t.object_type_id = ( select id from pub1.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 pub1.exp_anatomy ea, pub1.exp_outcome eo, pub1.exposure e, pub1.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 pub1.ixn i, pub1.ixn_anatomy ia, edit.reference_ixn ri, pub1.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 pub1.exp_event ee, pub1.exposure e, pub1.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 #5
Day Hour Count Duration Avg duration Jul 30 02 1 2m53s 2m53s -
INSERT INTO pub1.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 pub1.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub1.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub1.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub1.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub1.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.EXPOSURE e, pub1.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 pub1.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub1.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 pub1.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub1.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub1.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, pub1.TERM t, edit.IXN i, pub1.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub1.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 pub1.EXP_ANATOMY ea, pub1.EXP_OUTCOME eo, pub1.EXPOSURE e, pub1.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 pub1.IXN i, pub1.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub1.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 pub1.EXP_EVENT ee, pub1.EXPOSURE e, pub1.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-07-30 02:47:10 Duration: 2m53s Bind query: yes
6 2m11s 2m11s 2m11s 1 2m11s update pub1.term set has_exposures = false;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jul 30 00 1 2m11s 2m11s -
update pub1.TERM set has_exposures = false;
Date: 2026-07-30 00:04:16 Duration: 2m11s Bind query: yes
7 1m25s 1m25s 1m25s 1 1m25s update pub1.chem_disease cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.chem_disease_reference cdr where cd.chem_id = cdr.chem_id and cd.disease_id = cdr.disease_id and reference_id in ( select id from pub1.reference r where has_exposures = true));Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 30 00 1 1m25s 1m25s -
update pub1.CHEM_DISEASE cd set exposure_reference_qty = ( select count(distinct reference_id) from pub1.CHEM_DISEASE_REFERENCE cdr where cd.chem_id = cdr.chem_id and cd.disease_id = cdr.disease_id and reference_id in ( select id from pub1.REFERENCE r where has_exposures = true));
Date: 2026-07-30 00:07:32 Duration: 1m25s Bind query: yes
8 1m24s 1m24s 1m24s 1 1m24s update pub1.dag_node set has_exposures = false;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 30 00 1 1m24s 1m24s -
update pub1.DAG_NODE set has_exposures = false;
Date: 2026-07-30 00:05:50 Duration: 1m24s Bind query: yes
9 1m18s 1m18s 1m18s 1 1m18s update pub1.ixn set ixn_xml = replace(ixn_xml, ?, ?);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 30 00 1 1m18s 1m18s -
update pub1.IXN set ixn_xml = replace(ixn_xml, '''', '"');
Date: 2026-07-30 00:46:58 Duration: 1m18s Bind query: yes
10 1m4s 1m4s 1m4s 1 1m4s insert into pub1.gene_gene_reference (id, from_gene_id, to_gene_id, reference_id, from_taxon_id, to_taxon_id, src_db_id, experimental_sys_nm, experimental_sys_type) select ggr. id, ggr.from_gene_id, ggr.to_gene_id, l.object_id, ggr.from_taxon_id, ggr.to_taxon_id, ggr.src_db_id, ggr.experimental_sys_nm, ggr.experimental_sys_type from load.gene_gene_reference ggr, edit.db_link l where ggr.reference_acc_txt = l.acc_txt and l.object_type_id = ? and l.db_id = ?;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 30 00 1 1m4s 1m4s -
INSERT INTO pub1.GENE_GENE_REFERENCE (id, from_gene_id, to_gene_id, reference_id, from_taxon_id, to_taxon_id, src_db_id, experimental_sys_nm, experimental_sys_type) select ggr. id, ggr.from_gene_id, ggr.to_gene_id, l.object_id, ggr.from_taxon_id, ggr.to_taxon_id, ggr.src_db_id, ggr.experimental_sys_nm, ggr.experimental_sys_type from load.GENE_GENE_REFERENCE ggr, edit.DB_LINK l where ggr.reference_acc_txt = l.acc_txt and l.object_type_id = 7 and l.db_id = 16;
Date: 2026-07-30 00:45:07 Duration: 1m4s Bind query: yes
11 36s214ms 36s214ms 36s214ms 1 36s214ms select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where phenotypeterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? and exists ( select ? from slim_term_mapping stm where stm.mapped_term_id = pt.term_id and stm.slim_term_nm = ?) order by chemnetworkcount desc, genenetworkcount desc limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 30 00 1 36s214ms 36s214ms [ User: pubeu - Total duration: 36s214ms - Times executed: 1 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1321052') and diseaseTerm.object_type_id = 3 AND EXISTS ( SELECT 1 FROM slim_term_mapping stm WHERE stm.mapped_term_id = pt.term_id AND stm.slim_term_nm = 'Cancer') ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2026-07-30 00:52:31 Duration: 36s214ms Database: ctdprd51 User: pubeu Bind query: yes
12 21s4ms 21s4ms 21s4ms 1 21s4ms insert into pub1.gene_gene (from_gene_id, to_gene_id, reference_qty) select from_gene_id, to_gene_id, reference_qty from load.gene_gene;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 30 00 1 21s4ms 21s4ms [ User: pub1 - Total duration: 21s4ms - Times executed: 1 ]
-
insert into pub1.GENE_GENE (from_gene_id, to_gene_id, reference_qty) select from_gene_id, to_gene_id, reference_qty from load.GENE_GENE;
Date: 2026-07-30 00:44:03 Duration: 21s4ms Database: ctdprd51 User: pub1 Bind query: yes
13 20s931ms 20s931ms 20s931ms 1 20s931ms select * from pgbulkload.pg_bulkload (?);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 30 00 1 20s931ms 20s931ms [ User: load - Total duration: 20s931ms - Times executed: 1 ]
[ Application: pg_bulkload - Total duration: 20s931ms - Times executed: 1 ]
-
SELECT * FROM pgbulkload.pg_bulkload ('{TABLE=pub1.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-07-30 00:00:04 Duration: 20s931ms Database: ctdprd51 User: load Application: pg_bulkload Bind query: yes
14 17s79ms 17s79ms 17s79ms 1 17s79ms insert into pub1.db_link (object_type_id, object_id, db_id, acc_txt, type_cd, is_primary) select object_type_id, object_id, db_id, acc_txt, type_cd, is_primary from edit.db_link where object_type_id = ? and (object_type_id, object_id, db_id, acc_txt) not in ( select object_type_id, object_id, db_id, acc_txt from pub1.db_link where object_type_id = ?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 30 00 1 17s79ms 17s79ms -
INSERT INTO pub1.DB_LINK (object_type_id, object_id, db_id, acc_txt, type_cd, is_primary) SELECT object_type_id, object_id, db_id, acc_txt, type_cd, is_primary FROM edit.DB_LINK where object_type_id = 4 and (object_type_id, object_id, db_id, acc_txt) not in ( select object_type_id, object_id, db_id, acc_txt from pub1.DB_LINK where object_type_id = 4);
Date: 2026-07-30 00:45:39 Duration: 17s79ms Bind query: yes
15 15s551ms 15s551ms 15s551ms 1 15s551ms update pub1.reference set has_exposures = false;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 30 00 1 15s551ms 15s551ms -
update pub1.REFERENCE set has_exposures = false;
Date: 2026-07-30 00:06:06 Duration: 15s551ms Bind query: yes
16 15s432ms 15s432ms 15s432ms 1 15s432ms create index ix_gene_disease_network_score on pub1.gene_disease using btree (network_score);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 30 00 1 15s432ms 15s432ms -
CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);
Date: 2026-07-30 00:00:34 Duration: 15s432ms Bind query: yes
-
CREATE INDEX ix_gene_disease_network_score ON pub1.gene_disease USING btree (network_score);
Date: 2026-07-30 00:00:34 Duration: 0ms
17 14s578ms 14s578ms 14s578ms 1 14s578ms insert into pub1.gene_gene_ref_throughput (gene_gene_reference_id, throughput_txt) select gene_gene_reference_id, throughput_txt from load.gene_gene_ref_throughput ggrt where ggrt.gene_gene_reference_id in ( select id from pub1.gene_gene_reference);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 30 00 1 14s578ms 14s578ms -
INSERT INTO pub1.GENE_GENE_REF_THROUGHPUT (gene_gene_reference_id, throughput_txt) select gene_gene_reference_id, throughput_txt from load.GENE_GENE_REF_THROUGHPUT ggrt where ggrt.gene_gene_reference_id in ( select id from pub1.GENE_GENE_REFERENCE);
Date: 2026-07-30 00:45:22 Duration: 14s578ms Bind query: yes
18 10s752ms 10s752ms 10s752ms 1 10s752ms create index ix_gene_disease_disease on pub1.gene_disease using btree (disease_id);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jul 30 00 1 10s752ms 10s752ms -
CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);
Date: 2026-07-30 00:00:19 Duration: 10s752ms Bind query: yes
-
CREATE INDEX ix_gene_disease_disease ON pub1.gene_disease USING btree (disease_id);
Date: 2026-07-30 00:00:19 Duration: 0ms Database: ctdprd51 User: pub1
19 10s263ms 10s263ms 10s263ms 1 10s263ms insert into pub1.exposure (id, reference_id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id) select e.id, r.id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id from edit.exposure e inner join pub1.reference r on e.reference_acc_txt = r.acc_txt and r.acc_db_cd = ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jul 30 00 1 10s263ms 10s263ms -
INSERT INTO pub1.EXPOSURE (id, reference_id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id) SELECT e.id, r.id, reference_acc_txt, reference_acc_db_id, exp_stressor_id, exp_receptor_id, exp_event_id, exp_outcome_id FROM edit.EXPOSURE e INNER JOIN pub1.REFERENCE r ON e.reference_acc_txt = r.acc_txt AND r.acc_db_cd = 'PUBMED';
Date: 2026-07-30 00:02:05 Duration: 10s263ms Bind query: yes
20 6s685ms 12s978ms 9s882ms 3 29s648ms select ;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jul 30 03 3 29s648ms 9s882ms [ User: pubeu - Total duration: 29s648ms - Times executed: 3 ]
-
SELECT /* ChemGODAO */ ;
Date: 2026-07-30 03:00:23 Duration: 12s978ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* RefsDAO */ ;
Date: 2026-07-30 03:41:56 Duration: 9s984ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* RefsDAO */ ;
Date: 2026-07-30 03:41:56 Duration: 6s685ms Database: ctdprd51 User: pubeu Bind query: yes
Time consuming prepare
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
Time consuming bind
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
-
Events
Log levels
Key values
- 2,135 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 0 FATAL entries
- 0 ERROR entries
- 0 WARNING entries
- 0 EVENTLOG entries
Events per 5 minutes
NO DATASET
Most Frequent Errors/Events
Key values
- 0 Max number of times the same event was reported
- 0 Total events found
Rank Times reported Error NO DATASET