pgBadger-
Global information
- Generated on Mon Sep 28 04:15:05 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260927
- Parsed 17,631 log entries in 4s
- Log start from 2026-09-27 00:00:01 to 2026-09-28 03:42:21
-
Overview
Global Stats
- 7 Number of unique normalized queries
- 14 Number of queries
- 20m38s Total query duration
- 2026-09-27 00:09:28 First query
- 2026-09-28 00:09:39 Last query
- 1 queries/s at 2026-09-27 04:10:04 Query peak
- 20m38s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 20m38s Execute total duration
- 1 Number of events
- 1 Number of unique normalized events
- 1 Max number of times the same event was reported
- 0 Number of cancellation
- 5 Total number of automatic vacuums
- 22 Total number of automatic analyzes
- 0 Number temporary file
- 0 Max size of temporary file
- 0.00 B Average size of temporary file
- 2,157 Total number of sessions
- 43 sessions at 2026-09-27 00:56:23 Session peak
- 46d8h18m24s Total duration of sessions
- 30m56s Average duration of sessions
- 0 Average queries per session
- 574ms Average queries duration per session
- 30m55s Average idle time per session
- 2,157 Total number of connections
- 9 connections/s at 2026-09-27 05:40:08 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 1 queries/s Query Peak
- 2026-09-27 04:10:04 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2026-09-27 04:10:04 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-09-27 11:33:09 Date
Queries duration
Key values
- 20m38s 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) Sep 27 00 2 0ms 9m26s 4m46s 0ms 0ms 9m33s 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 1 0ms 11s564ms 11s564ms 0ms 0ms 11s564ms 03 0 0ms 0ms 0ms 0ms 0ms 0ms 04 4 0ms 6s310ms 5s740ms 0ms 0ms 22s962ms 05 2 0ms 5s902ms 5s806ms 0ms 5s710ms 5s902ms 06 0 0ms 0ms 0ms 0ms 0ms 0ms 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 0 0ms 0ms 0ms 0ms 0ms 0ms 09 1 0ms 6s557ms 6s557ms 0ms 0ms 6s557ms 10 0 0ms 0ms 0ms 0ms 0ms 0ms 11 2 0ms 17s200ms 17s40ms 0ms 16s880ms 17s200ms 12 0 0ms 0ms 0ms 0ms 0ms 0ms 13 0 0ms 0ms 0ms 0ms 0ms 0ms 14 0 0ms 0ms 0ms 0ms 0ms 0ms 15 0 0ms 0ms 0ms 0ms 0ms 0ms 16 0 0ms 0ms 0ms 0ms 0ms 0ms 17 0 0ms 0ms 0ms 0ms 0ms 0ms 18 0 0ms 0ms 0ms 0ms 0ms 0ms 19 0 0ms 0ms 0ms 0ms 0ms 0ms 20 0 0ms 0ms 0ms 0ms 0ms 0ms 21 0 0ms 0ms 0ms 0ms 0ms 0ms 22 0 0ms 0ms 0ms 0ms 0ms 0ms 23 0 0ms 0ms 0ms 0ms 0ms 0ms Sep 28 00 2 0ms 9m30s 4m48s 0ms 0ms 9m37s 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 0 0ms 0ms 0ms 0ms 0ms 0ms 03 0 0ms 0ms 0ms 0ms 0ms 0ms Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Sep 27 00 1 0 9m26s 0ms 0ms 9m26s 01 0 0 0ms 0ms 0ms 0ms 02 1 0 11s564ms 0ms 0ms 11s564ms 03 0 0 0ms 0ms 0ms 0ms 04 4 0 5s740ms 0ms 0ms 22s962ms 05 2 0 5s806ms 0ms 0ms 5s902ms 06 0 0 0ms 0ms 0ms 0ms 07 0 0 0ms 0ms 0ms 0ms 08 0 0 0ms 0ms 0ms 0ms 09 1 0 6s557ms 0ms 0ms 6s557ms 10 0 0 0ms 0ms 0ms 0ms 11 0 0 0ms 0ms 0ms 0ms 12 0 0 0ms 0ms 0ms 0ms 13 0 0 0ms 0ms 0ms 0ms 14 0 0 0ms 0ms 0ms 0ms 15 0 0 0ms 0ms 0ms 0ms 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 0 0 0ms 0ms 0ms 0ms 19 0 0 0ms 0ms 0ms 0ms 20 0 0 0ms 0ms 0ms 0ms 21 0 0 0ms 0ms 0ms 0ms 22 0 0 0ms 0ms 0ms 0ms 23 0 0 0ms 0ms 0ms 0ms Sep 28 00 1 0 9m30s 0ms 0ms 9m30s 01 0 0 0ms 0ms 0ms 0ms 02 0 0 0ms 0ms 0ms 0ms 03 0 0 0ms 0ms 0ms 0ms Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Sep 27 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 0 0 0 0 0ms 0ms 0ms 0ms 12 0 0 0 0 0ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 0 0 0 0 0ms 0ms 0ms 0ms 17 0 0 0 0 0ms 0ms 0ms 0ms 18 0 0 0 0 0ms 0ms 0ms 0ms 19 0 0 0 0 0ms 0ms 0ms 0ms 20 0 0 0 0 0ms 0ms 0ms 0ms 21 0 0 0 0 0ms 0ms 0ms 0ms 22 0 0 0 0 0ms 0ms 0ms 0ms 23 0 0 0 0 0ms 0ms 0ms 0ms Sep 28 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms Day Hour Prepare Bind Bind/Prepare Percentage of prepare Sep 27 00 0 0 0.00 0.00% 01 0 0 0.00 0.00% 02 0 1 1.00 0.00% 03 0 0 0.00 0.00% 04 0 4 4.00 0.00% 05 0 2 2.00 0.00% 06 0 0 0.00 0.00% 07 0 0 0.00 0.00% 08 0 0 0.00 0.00% 09 0 1 1.00 0.00% 10 0 0 0.00 0.00% 11 0 2 2.00 0.00% 12 0 0 0.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 0 0.00 0.00% 17 0 0 0.00 0.00% 18 0 0 0.00 0.00% 19 0 0 0.00 0.00% 20 0 0 0.00 0.00% 21 0 0 0.00 0.00% 22 0 0 0.00 0.00% 23 0 0 0.00 0.00% Sep 28 00 0 0 0.00 0.00% 01 0 0 0.00 0.00% 02 0 0 0.00 0.00% 03 0 0 0.00 0.00% Day Hour Count Average / Second Sep 27 00 76 0.02/s 01 74 0.02/s 02 79 0.02/s 03 78 0.02/s 04 78 0.02/s 05 88 0.02/s 06 72 0.02/s 07 82 0.02/s 08 83 0.02/s 09 81 0.02/s 10 78 0.02/s 11 81 0.02/s 12 81 0.02/s 13 80 0.02/s 14 76 0.02/s 15 79 0.02/s 16 78 0.02/s 17 77 0.02/s 18 78 0.02/s 19 78 0.02/s 20 66 0.02/s 21 79 0.02/s 22 75 0.02/s 23 74 0.02/s Sep 28 00 78 0.02/s 01 71 0.02/s 02 79 0.02/s 03 58 0.02/s Day Hour Count Average Duration Average idle time Sep 27 00 76 32m37s 32m30s 01 74 32m32s 32m32s 02 79 30m59s 30m59s 03 78 31m19s 31m19s 04 78 31m36s 31m35s 05 88 27m14s 27m14s 06 72 31m45s 31m45s 07 82 29m57s 29m57s 08 83 29m29s 29m29s 09 81 30m10s 30m10s 10 78 30m45s 30m45s 11 81 30m1s 30m1s 12 81 30m23s 30m23s 13 80 29m47s 29m47s 14 76 31m26s 31m26s 15 79 31m12s 31m12s 16 78 30m46s 30m46s 17 77 31m13s 31m13s 18 78 31m7s 31m7s 19 78 31m7s 31m7s 20 66 31m40s 31m40s 21 79 30m11s 30m11s 22 75 31m46s 31m46s 23 74 32m51s 32m51s Sep 28 00 78 31m33s 31m25s 01 71 32m4s 32m4s 02 79 31m33s 31m33s 03 58 30m5s 30m5s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2026-09-27 05:40:08 Date
Connections per database
Key values
- ctdprd51 Main Database
- 2,157 connections Total
Connections per user
Key values
- pubeu Main User
- 2,157 connections Total
-
Sessions
Simultaneous sessions
Key values
- 43 sessions Session Peak
- 2026-09-27 00:56:23 Date
Histogram of session times
Key values
- 2,047 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,157 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,157 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 2,157 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 48,407 buffers Checkpoint Peak
- 2026-09-28 00:23:27 Date
- 1619.865 seconds Highest write time
- 0.003 seconds Sync time
Checkpoints Wal files
Key values
- 32 files Wal files usage Peak
- 2026-09-28 00:23:27 Date
Checkpoints distance
Key values
- 1,011.82 Mo Distance Peak
- 2026-09-28 00:23:27 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Sep 27 00 408 41.058s 0.004s 41.13s 01 49 5.064s 0.002s 5.092s 02 95 9.69s 0.003s 9.721s 03 1,645 164.923s 0.003s 165.008s 04 353 35.542s 0.002s 35.571s 05 303 30.571s 0.002s 30.601s 06 732 73.503s 0.003s 73.533s 07 533 53.587s 0.003s 53.631s 08 234 23.498s 0.002s 23.527s 09 273 27.519s 0.002s 27.548s 10 79 8.082s 0.002s 8.112s 11 128 13.01s 0.002s 13.039s 12 61 6.324s 0.002s 6.355s 13 337 33.949s 0.003s 33.979s 14 45 4.705s 0.002s 4.734s 15 1,687 169.026s 0.003s 169.111s 16 15 1.655s 0.002s 1.684s 17 9 1.014s 0.001s 1.029s 18 16 1.789s 0.002s 1.818s 19 23 2.459s 0.002s 2.488s 20 77 7.914s 0.002s 7.942s 21 76 7.808s 0.002s 7.838s 22 173 17.506s 0.002s 17.537s 23 36 3.689s 0.001s 3.704s Sep 28 00 48,875 1,666.949s 0.006s 1,667.46s 01 1,337 134.192s 0.003s 134.223s 02 57 5.882s 0.002s 5.912s 03 182 18.334s 0.001s 18.349s Day Hour Added Removed Recycled Synced files Longest sync Average sync Sep 27 00 0 0 0 64 0.002s 0.002s 01 0 0 0 19 0.001s 0.002s 02 0 0 0 22 0.001s 0.002s 03 0 0 1 37 0.001s 0.002s 04 0 0 0 37 0.001s 0.002s 05 0 0 0 73 0.001s 0.002s 06 0 0 0 80 0.001s 0.002s 07 0 0 1 96 0.001s 0.002s 08 0 0 0 81 0.001s 0.002s 09 0 0 0 44 0.001s 0.002s 10 0 0 0 18 0.001s 0.002s 11 0 0 0 21 0.001s 0.002s 12 0 0 0 21 0.001s 0.002s 13 0 0 0 38 0.001s 0.002s 14 0 0 0 16 0.001s 0.002s 15 0 0 1 22 0.001s 0.002s 16 0 0 0 13 0.001s 0.002s 17 0 0 0 6 0.001s 0.001s 18 0 0 0 12 0.001s 0.002s 19 0 0 0 15 0.001s 0.002s 20 0 0 0 20 0.001s 0.002s 21 0 0 0 21 0.001s 0.002s 22 0 0 0 28 0.001s 0.002s 23 0 0 0 10 0.001s 0.001s Sep 28 00 0 0 32 94 0.001s 0.003s 01 0 0 0 39 0.001s 0.002s 02 0 0 0 21 0.001s 0.002s 03 0 0 0 20 0.001s 0.001s Day Hour Count Avg time (sec) Sep 27 00 0 0s 01 0 0s 02 0 0s 03 0 0s 04 0 0s 05 0 0s 06 0 0s 07 0 0s 08 0 0s 09 0 0s 10 0 0s 11 0 0s 12 0 0s 13 0 0s 14 0 0s 15 0 0s 16 0 0s 17 0 0s 18 0 0s 19 0 0s 20 0 0s 21 0 0s 22 0 0s 23 0 0s Sep 28 00 0 0s 01 0 0s 02 0 0s 03 0 0s Day Hour Mean distance Mean estimate Sep 27 00 1,146.00 kB 2,968.50 kB 01 15.50 kB 2,407.50 kB 02 159.50 kB 1,979.50 kB 03 5,090.50 kB 5,800.00 kB 04 334.00 kB 8,495.00 kB 05 519.50 kB 6,951.00 kB 06 2,958.00 kB 5,974.50 kB 07 1,724.50 kB 5,309.00 kB 08 693.00 kB 4,544.00 kB 09 748.00 kB 3,788.00 kB 10 73.50 kB 3,121.50 kB 11 281.50 kB 2,579.00 kB 12 104.50 kB 2,111.50 kB 13 1,061.50 kB 1,917.00 kB 14 43.50 kB 1,565.00 kB 15 6,643.00 kB 7,290.00 kB 16 26.50 kB 11,324.50 kB 17 38.00 kB 9,660.00 kB 18 33.00 kB 8,265.00 kB 19 51.50 kB 6,702.50 kB 20 76.50 kB 5,443.00 kB 21 114.50 kB 4,430.00 kB 22 312.50 kB 3,629.00 kB 23 140.00 kB 3,132.00 kB Sep 28 00 173,778.33 kB 468,179.00 kB 01 4,359.50 kB 359,839.50 kB 02 60.50 kB 291,496.00 kB 03 528.00 kB 248,595.00 kB -
Temporary Files
Size of temporary files
Key values
- 0 Temp Files size Peak
- Date
Size of temporary files (5 minutes period)
NO DATASET
Number of temporary files
Key values
- 0 per second Temp Files Peak
- Date
Number of temporary files (5 minutes period)
NO DATASET
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Sep 27 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 0 0 0 07 0 0 0 08 0 0 0 09 0 0 0 10 0 0 0 11 0 0 0 12 0 0 0 13 0 0 0 14 0 0 0 15 0 0 0 16 0 0 0 17 0 0 0 18 0 0 0 19 0 0 0 20 0 0 0 21 0 0 0 22 0 0 0 23 0 0 0 Sep 28 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 -
Vacuums
Vacuums / Analyzes Distribution
Key values
- 0.32 sec Highest CPU-cost vacuum
Table pub2.term_set_enrichment
Database ctdprd51 - 2026-09-27 23:36:03 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 0.32 sec Highest CPU-cost vacuum
Table pub2.term_set_enrichment
Database ctdprd51 - 2026-09-27 23:36:03 Date
Analyzes per table
Key values
- pubc.log_query (22) Main table analyzed (database ctdprd51)
- 22 analyzes Total
Vacuums per table
Key values
- pubc.log_query (2) Main table vacuumed on database ctdprd51
- 5 vacuums Total
Index Buffer usage Skipped WAL usage Frozen Table Vacuums scans hits misses dirtied pins frozen records full page bytes pages tuples ctdprd51.pubc.log_query 2 2 522 0 79 0 0 163 50 350,885 0 0 ctdprd51.pg_catalog.pg_class 1 1 352 0 65 0 0 153 62 276,368 0 0 ctdprd51.pub2.term_set_enrichment 1 0 3,460 0 1,396 0 0 1,678 2 111,173 0 0 ctdprd51.pg_catalog.pg_attribute 1 1 649 0 142 0 37 275 115 472,403 0 0 Total 5 4 4,983 214 1,682 0 37 2,269 229 1,210,829 0 0 Vacuum throughput per table
Key values
- pub2.term_set_enrichment (0.32) Max CPU elapsed for vacuum on database ctdprd51
- unknown (0 ms) Max I/O read time for vacuum on database ctdprd51
- unknown (0 ms) Max I/O write time for vacuum on database ctdprd51
Tuples removed per table
Key values
- pg_catalog.pg_attribute (1113) Main table with removed tuples on database ctdprd51
- 1308 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pg_catalog.pg_attribute 1 1 1,113 9,001 0 0 236 ctdprd51.pg_catalog.pg_class 1 1 182 1,835 0 0 94 ctdprd51.pubc.log_query 2 2 13 2,231 0 0 83 ctdprd51.pub2.term_set_enrichment 1 0 0 456,206 0 0 7,562 Total 5 4 1,308 469,273 0 0 7,975 Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Sep 27 00 1 0 01 0 0 02 0 2 03 0 0 04 0 2 05 1 3 06 0 1 07 0 2 08 0 2 09 0 2 10 0 0 11 1 0 12 0 0 13 0 1 14 0 0 15 0 0 16 0 0 17 0 0 18 0 0 19 0 0 20 0 0 21 0 0 22 0 1 23 1 1 Sep 28 00 1 0 01 0 2 02 0 1 03 0 2 - 0.32 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
- 10 Total read queries
- 2 Total write queries
Queries by database
Key values
- ctdprd51 Main database
- 11 Requests
- 20m18s (ctdprd51)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 122 Requests
User Request type Count Duration postgres Total 2 40s440ms copy to 2 40s440ms pubc Total 4 37m55s select 4 37m55s pubeu Total 38 9m48s cte 8 2m16s select 30 7m32s qaeu Total 6 34s752ms select 6 34s752ms unknown Total 122 3h19m28s copy to 112 3h18m13s others 4 27s815ms select 6 47s604ms Duration by user
Key values
- 3h19m28s (unknown) Main time consuming user
User Request type Count Duration postgres Total 2 40s440ms copy to 2 40s440ms pubc Total 4 37m55s select 4 37m55s pubeu Total 38 9m48s cte 8 2m16s select 30 7m32s qaeu Total 6 34s752ms select 6 34s752ms unknown Total 122 3h19m28s copy to 112 3h18m13s others 4 27s815ms select 6 47s604ms Queries by host
Key values
- unknown Main host
- 172 Requests
- 4h8m27s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 12 Requests
- 18m57s (psql)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-09-27 04:10:11 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 9 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 9m30s /* * 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-09-28 00:09:32 - Database: ctdprd51 - User: pubc - Application: psql ]
2 9m26s /* * 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-09-27 00:09:28 - Database: ctdprd51 - User: pubc - Application: psql ]
3 17s200ms WITH anatomy AS ( SELECT chem_conc_id, array_agg(anatomy_id ORDER BY anatomy_id) AS anatomy_ids, string_agg(DISTINCT anatomy_nm_html || '^' || anatomy_acc_txt || '^' || position_seq || '^' || anatomy_acc_db_id || '^' || anatomy_nm, '|') AS anatomyTerms FROM chem_conc_anatomy cca GROUP BY chem_conc_id ), reference_groups AS ( SELECT cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, string_agg(DISTINCT r.acc_txt, '|') AS refAcc, COUNT(DISTINCT cc.reference_id) AS referenceCount FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id LEFT JOIN reference r ON r.id = cc.reference_id GROUP BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ), deduplicated_cc AS ( SELECT cc.*, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, a.anatomyTerms, ROW_NUMBER() OVER (PARTITION BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ORDER BY cc.id) AS rn FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id WHERE EXISTS ( SELECT 1 FROM chem_conc cc_ref LEFT JOIN anatomy a_ref ON a_ref.chem_conc_id = cc_ref.id WHERE cc_ref.chem_id IS NOT DISTINCT FROM cc.chem_id AND cc_ref.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND cc_ref.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND cc_ref.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND cc_ref.disease_id IS NOT DISTINCT FROM cc.disease_id AND cc_ref.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND cc_ref.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND cc_ref.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND COALESCE(a_ref.anatomy_ids, ARRAY[]::integer[]) = COALESCE(a.anatomy_ids, ARRAY[]::integer[]))) SELECT /* ChemConcentrationDao */ cc.chem_id AS chem_id, t.nm AS chem, t.nm_html AS chemNmHtml, t.acc_txt AS chemAcc, t.secondary_nm AS casRN, cc.chem_conc || ' ' || cc.chem_conc_uom_nm AS concentration, cc.ixn_qualifier_nm AS vivoVitro, cc.chem_conc_exp_route_nm AS expRoute, cc.taxon_nm_html AS organism, cc.anatomyTerms AS anatomyTerms, cc.anatomyTerms AS anatomyTermsSort, cc.action_type_cd AS directEvidence, cc.action_type_cd AS directEvidenceSort, d.nm AS diseasenm, d.acc_txt AS diseaseacc, d.acc_db_cd AS diseaseaccdbcd, d.id AS diseaseid, tx.nm AS taxonnm, tx.secondary_nm AS taxoncomnm, tx.id AS taxonid, tx.acc_txt AS taxonacc, rg.refAcc, rg.referenceCount, COUNT(*) OVER () fullRowCount FROM deduplicated_cc cc INNER JOIN term t ON cc.chem_id = t.id INNER JOIN term tx ON cc.taxon_id = tx.id LEFT JOIN term d ON cc.disease_id = d.id INNER JOIN reference_groups rg ON rg.chem_id IS NOT DISTINCT FROM cc.chem_id AND rg.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND rg.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND rg.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND rg.disease_id IS NOT DISTINCT FROM cc.disease_id AND rg.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND rg.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND rg.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND rg.anatomy_ids = cc.anatomy_ids WHERE cc.rn = 1 AND t.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1370327') ORDER BY organism LIMIT 50;[ Date: 2026-09-27 11:30:15 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
4 16s880ms WITH anatomy AS ( SELECT chem_conc_id, array_agg(anatomy_id ORDER BY anatomy_id) AS anatomy_ids, string_agg(DISTINCT anatomy_nm_html || '^' || anatomy_acc_txt || '^' || position_seq || '^' || anatomy_acc_db_id || '^' || anatomy_nm, '|') AS anatomyTerms FROM chem_conc_anatomy cca GROUP BY chem_conc_id ), reference_groups AS ( SELECT cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, string_agg(DISTINCT r.acc_txt, '|') AS refAcc, COUNT(DISTINCT cc.reference_id) AS referenceCount FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id LEFT JOIN reference r ON r.id = cc.reference_id GROUP BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ), deduplicated_cc AS ( SELECT cc.*, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, a.anatomyTerms, ROW_NUMBER() OVER (PARTITION BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ORDER BY cc.id) AS rn FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id WHERE EXISTS ( SELECT 1 FROM chem_conc cc_ref LEFT JOIN anatomy a_ref ON a_ref.chem_conc_id = cc_ref.id WHERE cc_ref.chem_id IS NOT DISTINCT FROM cc.chem_id AND cc_ref.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND cc_ref.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND cc_ref.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND cc_ref.disease_id IS NOT DISTINCT FROM cc.disease_id AND cc_ref.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND cc_ref.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND cc_ref.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND COALESCE(a_ref.anatomy_ids, ARRAY[]::integer[]) = COALESCE(a.anatomy_ids, ARRAY[]::integer[]))) SELECT /* ChemConcentrationDao */ cc.chem_id AS chem_id, t.nm AS chem, t.nm_html AS chemNmHtml, t.acc_txt AS chemAcc, t.secondary_nm AS casRN, cc.chem_conc || ' ' || cc.chem_conc_uom_nm AS concentration, cc.ixn_qualifier_nm AS vivoVitro, cc.chem_conc_exp_route_nm AS expRoute, cc.taxon_nm_html AS organism, cc.anatomyTerms AS anatomyTerms, cc.anatomyTerms AS anatomyTermsSort, cc.action_type_cd AS directEvidence, cc.action_type_cd AS directEvidenceSort, d.nm AS diseasenm, d.acc_txt AS diseaseacc, d.acc_db_cd AS diseaseaccdbcd, d.id AS diseaseid, tx.nm AS taxonnm, tx.secondary_nm AS taxoncomnm, tx.id AS taxonid, tx.acc_txt AS taxonacc, rg.refAcc, rg.referenceCount, COUNT(*) OVER () fullRowCount FROM deduplicated_cc cc INNER JOIN term t ON cc.chem_id = t.id INNER JOIN term tx ON cc.taxon_id = tx.id LEFT JOIN term d ON cc.disease_id = d.id INNER JOIN reference_groups rg ON rg.chem_id IS NOT DISTINCT FROM cc.chem_id AND rg.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND rg.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND rg.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND rg.disease_id IS NOT DISTINCT FROM cc.disease_id AND rg.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND rg.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND rg.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND rg.anatomy_ids = cc.anatomy_ids WHERE cc.rn = 1 AND t.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1370327') ORDER BY organism LIMIT 50;[ Date: 2026-09-27 11:33:09 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
5 11s564ms SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2198383') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;[ Date: 2026-09-27 02:12:54 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
6 7s141ms VACUUM ANALYZE log_query_archive;[ Date: 2026-09-27 00:09:35 ]
7 6s765ms VACUUM ANALYZE log_query_archive;[ Date: 2026-09-28 00:09:39 ]
8 6s557ms SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'CTD_GENES_CHEM_CURATED') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'CTD_GENES_CHEM_CURATED') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CTD_GENES_CHEM_CURATED')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'CTD_GENES_CHEM_CURATED')) ii GROUP BY ii.cd;[ Date: 2026-09-27 09:54:26 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
9 6s310ms SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;[ Date: 2026-09-27 04:10:04 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
10 5s902ms SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;[ Date: 2026-09-27 05:43:37 - Database: ctdprd51 - User: qaeu - Bind query: yes ]
11 5s710ms SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;[ Date: 2026-09-27 05:48:44 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
12 5s638ms SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;[ Date: 2026-09-27 04:10:08 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
13 5s528ms SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;[ Date: 2026-09-27 04:10:13 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
14 5s485ms SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;[ Date: 2026-09-27 04:10:10 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 18m57s 2 9m26s 9m30s 9m28s select maint_query_logs_archive ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 27 00 1 9m26s 9m26s Sep 28 00 1 9m30s 9m30s [ User: pubc - Total duration: 18m57s - Times executed: 2 ]
[ Application: psql - Total duration: 18m57s - Times executed: 2 ]
-
/* * 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-09-28 00:09:32 Duration: 9m30s Database: ctdprd51 User: pubc Application: psql
-
/* * 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-09-27 00:09:28 Duration: 9m26s Database: ctdprd51 User: pubc Application: psql
2 34s81ms 2 16s880ms 17s200ms 17s40ms with anatomy as ( select chem_conc_id, array_agg(anatomy_id order by anatomy_id) as anatomy_ids, string_agg(distinct anatomy_nm_html || ? || anatomy_acc_txt || ? || position_seq || ? || anatomy_acc_db_id || ? || anatomy_nm, ?) as anatomyterms from chem_conc_anatomy cca group by chem_conc_id ), reference_groups as ( select cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) as anatomy_ids, string_agg(distinct r.acc_txt, ?) as refacc, count(distinct cc.reference_id) as referencecount from chem_conc cc left join anatomy a on a.chem_conc_id = cc.id left join reference r on r.id = cc.reference_id group by cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) ), deduplicated_cc as ( select cc.*, coalesce(a.anatomy_ids, array[]::integer[]) as anatomy_ids, a.anatomyterms, row_number() over (partition by cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) order by cc.id) as rn from chem_conc cc left join anatomy a on a.chem_conc_id = cc.id where exists ( select ? from chem_conc cc_ref left join anatomy a_ref on a_ref.chem_conc_id = cc_ref.id where cc_ref.chem_id is not distinct from cc.chem_id and cc_ref.chem_conc is not distinct from cc.chem_conc and cc_ref.chem_conc_uom_nm is not distinct from cc.chem_conc_uom_nm and cc_ref.taxon_id is not distinct from cc.taxon_id and cc_ref.disease_id is not distinct from cc.disease_id and cc_ref.chem_conc_exp_route_nm is not distinct from cc.chem_conc_exp_route_nm and cc_ref.ixn_qualifier_nm is not distinct from cc.ixn_qualifier_nm and cc_ref.action_type_cd is not distinct from cc.action_type_cd and coalesce(a_ref.anatomy_ids, array[]::integer[]) = coalesce(a.anatomy_ids, array[]::integer[]))) select cc.chem_id as chem_id, t.nm as chem, t.nm_html as chemnmhtml, t.acc_txt as chemacc, t.secondary_nm as casrn, cc.chem_conc || ? || cc.chem_conc_uom_nm as concentration, cc.ixn_qualifier_nm as vivovitro, cc.chem_conc_exp_route_nm as exproute, cc.taxon_nm_html as organism, cc.anatomyterms as anatomyterms, cc.anatomyterms as anatomytermssort, cc.action_type_cd as directevidence, cc.action_type_cd as directevidencesort, d.nm as diseasenm, d.acc_txt as diseaseacc, d.acc_db_cd as diseaseaccdbcd, d.id as diseaseid, tx.nm as taxonnm, tx.secondary_nm as taxoncomnm, tx.id as taxonid, tx.acc_txt as taxonacc, rg.refacc, rg.referencecount, count(*) over () fullrowcount from deduplicated_cc cc inner join term t on cc.chem_id = t.id inner join term tx on cc.taxon_id = tx.id left join term d on cc.disease_id = d.id inner join reference_groups rg on rg.chem_id is not distinct from cc.chem_id and rg.chem_conc is not distinct from cc.chem_conc and rg.chem_conc_uom_nm is not distinct from cc.chem_conc_uom_nm and rg.taxon_id is not distinct from cc.taxon_id and rg.disease_id is not distinct from cc.disease_id and rg.chem_conc_exp_route_nm is not distinct from cc.chem_conc_exp_route_nm and rg.ixn_qualifier_nm is not distinct from cc.ixn_qualifier_nm and rg.action_type_cd is not distinct from cc.action_type_cd and rg.anatomy_ids = cc.anatomy_ids where cc.rn = ? and t.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) order by organism limit ?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 27 11 2 34s81ms 17s40ms [ User: pubeu - Total duration: 34s81ms - Times executed: 2 ]
-
WITH anatomy AS ( SELECT chem_conc_id, array_agg(anatomy_id ORDER BY anatomy_id) AS anatomy_ids, string_agg(DISTINCT anatomy_nm_html || '^' || anatomy_acc_txt || '^' || position_seq || '^' || anatomy_acc_db_id || '^' || anatomy_nm, '|') AS anatomyTerms FROM chem_conc_anatomy cca GROUP BY chem_conc_id ), reference_groups AS ( SELECT cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, string_agg(DISTINCT r.acc_txt, '|') AS refAcc, COUNT(DISTINCT cc.reference_id) AS referenceCount FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id LEFT JOIN reference r ON r.id = cc.reference_id GROUP BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ), deduplicated_cc AS ( SELECT cc.*, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, a.anatomyTerms, ROW_NUMBER() OVER (PARTITION BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ORDER BY cc.id) AS rn FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id WHERE EXISTS ( SELECT 1 FROM chem_conc cc_ref LEFT JOIN anatomy a_ref ON a_ref.chem_conc_id = cc_ref.id WHERE cc_ref.chem_id IS NOT DISTINCT FROM cc.chem_id AND cc_ref.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND cc_ref.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND cc_ref.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND cc_ref.disease_id IS NOT DISTINCT FROM cc.disease_id AND cc_ref.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND cc_ref.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND cc_ref.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND COALESCE(a_ref.anatomy_ids, ARRAY[]::integer[]) = COALESCE(a.anatomy_ids, ARRAY[]::integer[]))) SELECT /* ChemConcentrationDao */ cc.chem_id AS chem_id, t.nm AS chem, t.nm_html AS chemNmHtml, t.acc_txt AS chemAcc, t.secondary_nm AS casRN, cc.chem_conc || ' ' || cc.chem_conc_uom_nm AS concentration, cc.ixn_qualifier_nm AS vivoVitro, cc.chem_conc_exp_route_nm AS expRoute, cc.taxon_nm_html AS organism, cc.anatomyTerms AS anatomyTerms, cc.anatomyTerms AS anatomyTermsSort, cc.action_type_cd AS directEvidence, cc.action_type_cd AS directEvidenceSort, d.nm AS diseasenm, d.acc_txt AS diseaseacc, d.acc_db_cd AS diseaseaccdbcd, d.id AS diseaseid, tx.nm AS taxonnm, tx.secondary_nm AS taxoncomnm, tx.id AS taxonid, tx.acc_txt AS taxonacc, rg.refAcc, rg.referenceCount, COUNT(*) OVER () fullRowCount FROM deduplicated_cc cc INNER JOIN term t ON cc.chem_id = t.id INNER JOIN term tx ON cc.taxon_id = tx.id LEFT JOIN term d ON cc.disease_id = d.id INNER JOIN reference_groups rg ON rg.chem_id IS NOT DISTINCT FROM cc.chem_id AND rg.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND rg.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND rg.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND rg.disease_id IS NOT DISTINCT FROM cc.disease_id AND rg.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND rg.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND rg.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND rg.anatomy_ids = cc.anatomy_ids WHERE cc.rn = 1 AND t.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1370327') ORDER BY organism LIMIT 50;
Date: 2026-09-27 11:30:15 Duration: 17s200ms Database: ctdprd51 User: pubeu Bind query: yes
-
WITH anatomy AS ( SELECT chem_conc_id, array_agg(anatomy_id ORDER BY anatomy_id) AS anatomy_ids, string_agg(DISTINCT anatomy_nm_html || '^' || anatomy_acc_txt || '^' || position_seq || '^' || anatomy_acc_db_id || '^' || anatomy_nm, '|') AS anatomyTerms FROM chem_conc_anatomy cca GROUP BY chem_conc_id ), reference_groups AS ( SELECT cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, string_agg(DISTINCT r.acc_txt, '|') AS refAcc, COUNT(DISTINCT cc.reference_id) AS referenceCount FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id LEFT JOIN reference r ON r.id = cc.reference_id GROUP BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ), deduplicated_cc AS ( SELECT cc.*, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, a.anatomyTerms, ROW_NUMBER() OVER (PARTITION BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ORDER BY cc.id) AS rn FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id WHERE EXISTS ( SELECT 1 FROM chem_conc cc_ref LEFT JOIN anatomy a_ref ON a_ref.chem_conc_id = cc_ref.id WHERE cc_ref.chem_id IS NOT DISTINCT FROM cc.chem_id AND cc_ref.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND cc_ref.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND cc_ref.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND cc_ref.disease_id IS NOT DISTINCT FROM cc.disease_id AND cc_ref.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND cc_ref.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND cc_ref.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND COALESCE(a_ref.anatomy_ids, ARRAY[]::integer[]) = COALESCE(a.anatomy_ids, ARRAY[]::integer[]))) SELECT /* ChemConcentrationDao */ cc.chem_id AS chem_id, t.nm AS chem, t.nm_html AS chemNmHtml, t.acc_txt AS chemAcc, t.secondary_nm AS casRN, cc.chem_conc || ' ' || cc.chem_conc_uom_nm AS concentration, cc.ixn_qualifier_nm AS vivoVitro, cc.chem_conc_exp_route_nm AS expRoute, cc.taxon_nm_html AS organism, cc.anatomyTerms AS anatomyTerms, cc.anatomyTerms AS anatomyTermsSort, cc.action_type_cd AS directEvidence, cc.action_type_cd AS directEvidenceSort, d.nm AS diseasenm, d.acc_txt AS diseaseacc, d.acc_db_cd AS diseaseaccdbcd, d.id AS diseaseid, tx.nm AS taxonnm, tx.secondary_nm AS taxoncomnm, tx.id AS taxonid, tx.acc_txt AS taxonacc, rg.refAcc, rg.referenceCount, COUNT(*) OVER () fullRowCount FROM deduplicated_cc cc INNER JOIN term t ON cc.chem_id = t.id INNER JOIN term tx ON cc.taxon_id = tx.id LEFT JOIN term d ON cc.disease_id = d.id INNER JOIN reference_groups rg ON rg.chem_id IS NOT DISTINCT FROM cc.chem_id AND rg.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND rg.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND rg.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND rg.disease_id IS NOT DISTINCT FROM cc.disease_id AND rg.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND rg.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND rg.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND rg.anatomy_ids = cc.anatomy_ids WHERE cc.rn = 1 AND t.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1370327') ORDER BY organism LIMIT 50;
Date: 2026-09-27 11:33:09 Duration: 16s880ms Database: ctdprd51 User: pubeu Bind query: yes
3 22s962ms 4 5s485ms 6s310ms 5s740ms select d.abbr dagabbr, d.nm dagnm, gt.level_min_no daglevelmin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pvalcorrected, te.raw_p_val pvalraw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, count(*) over () fullrowcount from term_enrichment te inner join dag_node gt on te.enriched_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where te.term_id = ? and te.enriched_object_type_id = ? order by te.corrected_p_val, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 27 04 4 22s962ms 5s740ms [ User: pubeu - Total duration: 17s476ms - Times executed: 3 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:04 Duration: 6s310ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:08 Duration: 5s638ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:13 Duration: 5s528ms Database: ctdprd51 User: pubeu Bind query: yes
4 13s907ms 2 6s765ms 7s141ms 6s953ms vacuum analyze log_query_archive;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 27 00 1 7s141ms 7s141ms Sep 28 00 1 6s765ms 6s765ms -
VACUUM ANALYZE log_query_archive;
Date: 2026-09-27 00:09:35 Duration: 7s141ms
-
VACUUM ANALYZE log_query_archive;
Date: 2026-09-28 00:09:39 Duration: 6s765ms
5 11s613ms 2 5s710ms 5s902ms 5s806ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 27 05 2 11s613ms 5s806ms [ User: qaeu - Total duration: 5s902ms - Times executed: 1 ]
[ User: pubeu - Total duration: 5s710ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-09-27 05:43:37 Duration: 5s902ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-09-27 05:48:44 Duration: 5s710ms Database: ctdprd51 User: pubeu Bind query: yes
6 11s564ms 1 11s564ms 11s564ms 11s564ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 27 02 1 11s564ms 11s564ms [ User: pubeu - Total duration: 11s564ms - Times executed: 1 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2198383') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-09-27 02:12:54 Duration: 11s564ms Database: ctdprd51 User: pubeu Bind query: yes
7 6s557ms 1 6s557ms 6s557ms 6s557ms select ii.cd, count(ii.id) cnt from ( select ot.cd, tl.term_id id from object_type ot inner join term_label tl on ot.id = tl.object_type_id where tl.nm_fts @@ to_tsquery(?, ?) union select ?, r.id from reference r where r.title_abstract_fts @@ to_tsquery(?, ?) or r.id in ( select rpr.reference_id from reference_party_role rpr inner join reference_party rp on rpr.reference_party_id = rp.id where (substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ?)) union select ot.cd, l.object_id from db_link l inner join object_type ot on l.object_type_id = ot.id where l.type_cd = ? and (upper(l.acc_txt) like ?)) ii group by ii.cd;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 27 09 1 6s557ms 6s557ms [ User: pubeu - Total duration: 6s557ms - Times executed: 1 ]
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'CTD_GENES_CHEM_CURATED') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'CTD_GENES_CHEM_CURATED') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CTD_GENES_CHEM_CURATED')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'CTD_GENES_CHEM_CURATED')) ii GROUP BY ii.cd;
Date: 2026-09-27 09:54:26 Duration: 6s557ms Database: ctdprd51 User: pubeu Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 4 22s962ms 5s485ms 6s310ms 5s740ms select d.abbr dagabbr, d.nm dagnm, gt.level_min_no daglevelmin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pvalcorrected, te.raw_p_val pvalraw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, count(*) over () fullrowcount from term_enrichment te inner join dag_node gt on te.enriched_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where te.term_id = ? and te.enriched_object_type_id = ? order by te.corrected_p_val, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 27 04 4 22s962ms 5s740ms [ User: pubeu - Total duration: 17s476ms - Times executed: 3 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:04 Duration: 6s310ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:08 Duration: 5s638ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:13 Duration: 5s528ms Database: ctdprd51 User: pubeu Bind query: yes
2 2 18m57s 9m26s 9m30s 9m28s select maint_query_logs_archive ();Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 27 00 1 9m26s 9m26s Sep 28 00 1 9m30s 9m30s [ User: pubc - Total duration: 18m57s - Times executed: 2 ]
[ Application: psql - Total duration: 18m57s - Times executed: 2 ]
-
/* * 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-09-28 00:09:32 Duration: 9m30s Database: ctdprd51 User: pubc Application: psql
-
/* * 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-09-27 00:09:28 Duration: 9m26s Database: ctdprd51 User: pubc Application: psql
3 2 34s81ms 16s880ms 17s200ms 17s40ms with anatomy as ( select chem_conc_id, array_agg(anatomy_id order by anatomy_id) as anatomy_ids, string_agg(distinct anatomy_nm_html || ? || anatomy_acc_txt || ? || position_seq || ? || anatomy_acc_db_id || ? || anatomy_nm, ?) as anatomyterms from chem_conc_anatomy cca group by chem_conc_id ), reference_groups as ( select cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) as anatomy_ids, string_agg(distinct r.acc_txt, ?) as refacc, count(distinct cc.reference_id) as referencecount from chem_conc cc left join anatomy a on a.chem_conc_id = cc.id left join reference r on r.id = cc.reference_id group by cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) ), deduplicated_cc as ( select cc.*, coalesce(a.anatomy_ids, array[]::integer[]) as anatomy_ids, a.anatomyterms, row_number() over (partition by cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) order by cc.id) as rn from chem_conc cc left join anatomy a on a.chem_conc_id = cc.id where exists ( select ? from chem_conc cc_ref left join anatomy a_ref on a_ref.chem_conc_id = cc_ref.id where cc_ref.chem_id is not distinct from cc.chem_id and cc_ref.chem_conc is not distinct from cc.chem_conc and cc_ref.chem_conc_uom_nm is not distinct from cc.chem_conc_uom_nm and cc_ref.taxon_id is not distinct from cc.taxon_id and cc_ref.disease_id is not distinct from cc.disease_id and cc_ref.chem_conc_exp_route_nm is not distinct from cc.chem_conc_exp_route_nm and cc_ref.ixn_qualifier_nm is not distinct from cc.ixn_qualifier_nm and cc_ref.action_type_cd is not distinct from cc.action_type_cd and coalesce(a_ref.anatomy_ids, array[]::integer[]) = coalesce(a.anatomy_ids, array[]::integer[]))) select cc.chem_id as chem_id, t.nm as chem, t.nm_html as chemnmhtml, t.acc_txt as chemacc, t.secondary_nm as casrn, cc.chem_conc || ? || cc.chem_conc_uom_nm as concentration, cc.ixn_qualifier_nm as vivovitro, cc.chem_conc_exp_route_nm as exproute, cc.taxon_nm_html as organism, cc.anatomyterms as anatomyterms, cc.anatomyterms as anatomytermssort, cc.action_type_cd as directevidence, cc.action_type_cd as directevidencesort, d.nm as diseasenm, d.acc_txt as diseaseacc, d.acc_db_cd as diseaseaccdbcd, d.id as diseaseid, tx.nm as taxonnm, tx.secondary_nm as taxoncomnm, tx.id as taxonid, tx.acc_txt as taxonacc, rg.refacc, rg.referencecount, count(*) over () fullrowcount from deduplicated_cc cc inner join term t on cc.chem_id = t.id inner join term tx on cc.taxon_id = tx.id left join term d on cc.disease_id = d.id inner join reference_groups rg on rg.chem_id is not distinct from cc.chem_id and rg.chem_conc is not distinct from cc.chem_conc and rg.chem_conc_uom_nm is not distinct from cc.chem_conc_uom_nm and rg.taxon_id is not distinct from cc.taxon_id and rg.disease_id is not distinct from cc.disease_id and rg.chem_conc_exp_route_nm is not distinct from cc.chem_conc_exp_route_nm and rg.ixn_qualifier_nm is not distinct from cc.ixn_qualifier_nm and rg.action_type_cd is not distinct from cc.action_type_cd and rg.anatomy_ids = cc.anatomy_ids where cc.rn = ? and t.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) order by organism limit ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 27 11 2 34s81ms 17s40ms [ User: pubeu - Total duration: 34s81ms - Times executed: 2 ]
-
WITH anatomy AS ( SELECT chem_conc_id, array_agg(anatomy_id ORDER BY anatomy_id) AS anatomy_ids, string_agg(DISTINCT anatomy_nm_html || '^' || anatomy_acc_txt || '^' || position_seq || '^' || anatomy_acc_db_id || '^' || anatomy_nm, '|') AS anatomyTerms FROM chem_conc_anatomy cca GROUP BY chem_conc_id ), reference_groups AS ( SELECT cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, string_agg(DISTINCT r.acc_txt, '|') AS refAcc, COUNT(DISTINCT cc.reference_id) AS referenceCount FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id LEFT JOIN reference r ON r.id = cc.reference_id GROUP BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ), deduplicated_cc AS ( SELECT cc.*, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, a.anatomyTerms, ROW_NUMBER() OVER (PARTITION BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ORDER BY cc.id) AS rn FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id WHERE EXISTS ( SELECT 1 FROM chem_conc cc_ref LEFT JOIN anatomy a_ref ON a_ref.chem_conc_id = cc_ref.id WHERE cc_ref.chem_id IS NOT DISTINCT FROM cc.chem_id AND cc_ref.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND cc_ref.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND cc_ref.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND cc_ref.disease_id IS NOT DISTINCT FROM cc.disease_id AND cc_ref.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND cc_ref.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND cc_ref.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND COALESCE(a_ref.anatomy_ids, ARRAY[]::integer[]) = COALESCE(a.anatomy_ids, ARRAY[]::integer[]))) SELECT /* ChemConcentrationDao */ cc.chem_id AS chem_id, t.nm AS chem, t.nm_html AS chemNmHtml, t.acc_txt AS chemAcc, t.secondary_nm AS casRN, cc.chem_conc || ' ' || cc.chem_conc_uom_nm AS concentration, cc.ixn_qualifier_nm AS vivoVitro, cc.chem_conc_exp_route_nm AS expRoute, cc.taxon_nm_html AS organism, cc.anatomyTerms AS anatomyTerms, cc.anatomyTerms AS anatomyTermsSort, cc.action_type_cd AS directEvidence, cc.action_type_cd AS directEvidenceSort, d.nm AS diseasenm, d.acc_txt AS diseaseacc, d.acc_db_cd AS diseaseaccdbcd, d.id AS diseaseid, tx.nm AS taxonnm, tx.secondary_nm AS taxoncomnm, tx.id AS taxonid, tx.acc_txt AS taxonacc, rg.refAcc, rg.referenceCount, COUNT(*) OVER () fullRowCount FROM deduplicated_cc cc INNER JOIN term t ON cc.chem_id = t.id INNER JOIN term tx ON cc.taxon_id = tx.id LEFT JOIN term d ON cc.disease_id = d.id INNER JOIN reference_groups rg ON rg.chem_id IS NOT DISTINCT FROM cc.chem_id AND rg.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND rg.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND rg.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND rg.disease_id IS NOT DISTINCT FROM cc.disease_id AND rg.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND rg.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND rg.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND rg.anatomy_ids = cc.anatomy_ids WHERE cc.rn = 1 AND t.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1370327') ORDER BY organism LIMIT 50;
Date: 2026-09-27 11:30:15 Duration: 17s200ms Database: ctdprd51 User: pubeu Bind query: yes
-
WITH anatomy AS ( SELECT chem_conc_id, array_agg(anatomy_id ORDER BY anatomy_id) AS anatomy_ids, string_agg(DISTINCT anatomy_nm_html || '^' || anatomy_acc_txt || '^' || position_seq || '^' || anatomy_acc_db_id || '^' || anatomy_nm, '|') AS anatomyTerms FROM chem_conc_anatomy cca GROUP BY chem_conc_id ), reference_groups AS ( SELECT cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, string_agg(DISTINCT r.acc_txt, '|') AS refAcc, COUNT(DISTINCT cc.reference_id) AS referenceCount FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id LEFT JOIN reference r ON r.id = cc.reference_id GROUP BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ), deduplicated_cc AS ( SELECT cc.*, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, a.anatomyTerms, ROW_NUMBER() OVER (PARTITION BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ORDER BY cc.id) AS rn FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id WHERE EXISTS ( SELECT 1 FROM chem_conc cc_ref LEFT JOIN anatomy a_ref ON a_ref.chem_conc_id = cc_ref.id WHERE cc_ref.chem_id IS NOT DISTINCT FROM cc.chem_id AND cc_ref.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND cc_ref.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND cc_ref.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND cc_ref.disease_id IS NOT DISTINCT FROM cc.disease_id AND cc_ref.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND cc_ref.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND cc_ref.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND COALESCE(a_ref.anatomy_ids, ARRAY[]::integer[]) = COALESCE(a.anatomy_ids, ARRAY[]::integer[]))) SELECT /* ChemConcentrationDao */ cc.chem_id AS chem_id, t.nm AS chem, t.nm_html AS chemNmHtml, t.acc_txt AS chemAcc, t.secondary_nm AS casRN, cc.chem_conc || ' ' || cc.chem_conc_uom_nm AS concentration, cc.ixn_qualifier_nm AS vivoVitro, cc.chem_conc_exp_route_nm AS expRoute, cc.taxon_nm_html AS organism, cc.anatomyTerms AS anatomyTerms, cc.anatomyTerms AS anatomyTermsSort, cc.action_type_cd AS directEvidence, cc.action_type_cd AS directEvidenceSort, d.nm AS diseasenm, d.acc_txt AS diseaseacc, d.acc_db_cd AS diseaseaccdbcd, d.id AS diseaseid, tx.nm AS taxonnm, tx.secondary_nm AS taxoncomnm, tx.id AS taxonid, tx.acc_txt AS taxonacc, rg.refAcc, rg.referenceCount, COUNT(*) OVER () fullRowCount FROM deduplicated_cc cc INNER JOIN term t ON cc.chem_id = t.id INNER JOIN term tx ON cc.taxon_id = tx.id LEFT JOIN term d ON cc.disease_id = d.id INNER JOIN reference_groups rg ON rg.chem_id IS NOT DISTINCT FROM cc.chem_id AND rg.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND rg.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND rg.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND rg.disease_id IS NOT DISTINCT FROM cc.disease_id AND rg.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND rg.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND rg.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND rg.anatomy_ids = cc.anatomy_ids WHERE cc.rn = 1 AND t.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1370327') ORDER BY organism LIMIT 50;
Date: 2026-09-27 11:33:09 Duration: 16s880ms Database: ctdprd51 User: pubeu Bind query: yes
4 2 13s907ms 6s765ms 7s141ms 6s953ms vacuum analyze log_query_archive;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 27 00 1 7s141ms 7s141ms Sep 28 00 1 6s765ms 6s765ms -
VACUUM ANALYZE log_query_archive;
Date: 2026-09-27 00:09:35 Duration: 7s141ms
-
VACUUM ANALYZE log_query_archive;
Date: 2026-09-28 00:09:39 Duration: 6s765ms
5 2 11s613ms 5s710ms 5s902ms 5s806ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 27 05 2 11s613ms 5s806ms [ User: qaeu - Total duration: 5s902ms - Times executed: 1 ]
[ User: pubeu - Total duration: 5s710ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-09-27 05:43:37 Duration: 5s902ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-09-27 05:48:44 Duration: 5s710ms Database: ctdprd51 User: pubeu Bind query: yes
6 1 11s564ms 11s564ms 11s564ms 11s564ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 27 02 1 11s564ms 11s564ms [ User: pubeu - Total duration: 11s564ms - Times executed: 1 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2198383') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-09-27 02:12:54 Duration: 11s564ms Database: ctdprd51 User: pubeu Bind query: yes
7 1 6s557ms 6s557ms 6s557ms 6s557ms select ii.cd, count(ii.id) cnt from ( select ot.cd, tl.term_id id from object_type ot inner join term_label tl on ot.id = tl.object_type_id where tl.nm_fts @@ to_tsquery(?, ?) union select ?, r.id from reference r where r.title_abstract_fts @@ to_tsquery(?, ?) or r.id in ( select rpr.reference_id from reference_party_role rpr inner join reference_party rp on rpr.reference_party_id = rp.id where (substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ?)) union select ot.cd, l.object_id from db_link l inner join object_type ot on l.object_type_id = ot.id where l.type_cd = ? and (upper(l.acc_txt) like ?)) ii group by ii.cd;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 27 09 1 6s557ms 6s557ms [ User: pubeu - Total duration: 6s557ms - Times executed: 1 ]
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'CTD_GENES_CHEM_CURATED') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'CTD_GENES_CHEM_CURATED') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CTD_GENES_CHEM_CURATED')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'CTD_GENES_CHEM_CURATED')) ii GROUP BY ii.cd;
Date: 2026-09-27 09:54:26 Duration: 6s557ms Database: ctdprd51 User: pubeu Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 9m26s 9m30s 9m28s 2 18m57s select maint_query_logs_archive ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 27 00 1 9m26s 9m26s Sep 28 00 1 9m30s 9m30s [ User: pubc - Total duration: 18m57s - Times executed: 2 ]
[ Application: psql - Total duration: 18m57s - Times executed: 2 ]
-
/* * 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-09-28 00:09:32 Duration: 9m30s Database: ctdprd51 User: pubc Application: psql
-
/* * 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-09-27 00:09:28 Duration: 9m26s Database: ctdprd51 User: pubc Application: psql
2 16s880ms 17s200ms 17s40ms 2 34s81ms with anatomy as ( select chem_conc_id, array_agg(anatomy_id order by anatomy_id) as anatomy_ids, string_agg(distinct anatomy_nm_html || ? || anatomy_acc_txt || ? || position_seq || ? || anatomy_acc_db_id || ? || anatomy_nm, ?) as anatomyterms from chem_conc_anatomy cca group by chem_conc_id ), reference_groups as ( select cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) as anatomy_ids, string_agg(distinct r.acc_txt, ?) as refacc, count(distinct cc.reference_id) as referencecount from chem_conc cc left join anatomy a on a.chem_conc_id = cc.id left join reference r on r.id = cc.reference_id group by cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) ), deduplicated_cc as ( select cc.*, coalesce(a.anatomy_ids, array[]::integer[]) as anatomy_ids, a.anatomyterms, row_number() over (partition by cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, coalesce(a.anatomy_ids, array[]::integer[]) order by cc.id) as rn from chem_conc cc left join anatomy a on a.chem_conc_id = cc.id where exists ( select ? from chem_conc cc_ref left join anatomy a_ref on a_ref.chem_conc_id = cc_ref.id where cc_ref.chem_id is not distinct from cc.chem_id and cc_ref.chem_conc is not distinct from cc.chem_conc and cc_ref.chem_conc_uom_nm is not distinct from cc.chem_conc_uom_nm and cc_ref.taxon_id is not distinct from cc.taxon_id and cc_ref.disease_id is not distinct from cc.disease_id and cc_ref.chem_conc_exp_route_nm is not distinct from cc.chem_conc_exp_route_nm and cc_ref.ixn_qualifier_nm is not distinct from cc.ixn_qualifier_nm and cc_ref.action_type_cd is not distinct from cc.action_type_cd and coalesce(a_ref.anatomy_ids, array[]::integer[]) = coalesce(a.anatomy_ids, array[]::integer[]))) select cc.chem_id as chem_id, t.nm as chem, t.nm_html as chemnmhtml, t.acc_txt as chemacc, t.secondary_nm as casrn, cc.chem_conc || ? || cc.chem_conc_uom_nm as concentration, cc.ixn_qualifier_nm as vivovitro, cc.chem_conc_exp_route_nm as exproute, cc.taxon_nm_html as organism, cc.anatomyterms as anatomyterms, cc.anatomyterms as anatomytermssort, cc.action_type_cd as directevidence, cc.action_type_cd as directevidencesort, d.nm as diseasenm, d.acc_txt as diseaseacc, d.acc_db_cd as diseaseaccdbcd, d.id as diseaseid, tx.nm as taxonnm, tx.secondary_nm as taxoncomnm, tx.id as taxonid, tx.acc_txt as taxonacc, rg.refacc, rg.referencecount, count(*) over () fullrowcount from deduplicated_cc cc inner join term t on cc.chem_id = t.id inner join term tx on cc.taxon_id = tx.id left join term d on cc.disease_id = d.id inner join reference_groups rg on rg.chem_id is not distinct from cc.chem_id and rg.chem_conc is not distinct from cc.chem_conc and rg.chem_conc_uom_nm is not distinct from cc.chem_conc_uom_nm and rg.taxon_id is not distinct from cc.taxon_id and rg.disease_id is not distinct from cc.disease_id and rg.chem_conc_exp_route_nm is not distinct from cc.chem_conc_exp_route_nm and rg.ixn_qualifier_nm is not distinct from cc.ixn_qualifier_nm and rg.action_type_cd is not distinct from cc.action_type_cd and rg.anatomy_ids = cc.anatomy_ids where cc.rn = ? and t.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) order by organism limit ?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 27 11 2 34s81ms 17s40ms [ User: pubeu - Total duration: 34s81ms - Times executed: 2 ]
-
WITH anatomy AS ( SELECT chem_conc_id, array_agg(anatomy_id ORDER BY anatomy_id) AS anatomy_ids, string_agg(DISTINCT anatomy_nm_html || '^' || anatomy_acc_txt || '^' || position_seq || '^' || anatomy_acc_db_id || '^' || anatomy_nm, '|') AS anatomyTerms FROM chem_conc_anatomy cca GROUP BY chem_conc_id ), reference_groups AS ( SELECT cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, string_agg(DISTINCT r.acc_txt, '|') AS refAcc, COUNT(DISTINCT cc.reference_id) AS referenceCount FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id LEFT JOIN reference r ON r.id = cc.reference_id GROUP BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ), deduplicated_cc AS ( SELECT cc.*, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, a.anatomyTerms, ROW_NUMBER() OVER (PARTITION BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ORDER BY cc.id) AS rn FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id WHERE EXISTS ( SELECT 1 FROM chem_conc cc_ref LEFT JOIN anatomy a_ref ON a_ref.chem_conc_id = cc_ref.id WHERE cc_ref.chem_id IS NOT DISTINCT FROM cc.chem_id AND cc_ref.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND cc_ref.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND cc_ref.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND cc_ref.disease_id IS NOT DISTINCT FROM cc.disease_id AND cc_ref.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND cc_ref.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND cc_ref.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND COALESCE(a_ref.anatomy_ids, ARRAY[]::integer[]) = COALESCE(a.anatomy_ids, ARRAY[]::integer[]))) SELECT /* ChemConcentrationDao */ cc.chem_id AS chem_id, t.nm AS chem, t.nm_html AS chemNmHtml, t.acc_txt AS chemAcc, t.secondary_nm AS casRN, cc.chem_conc || ' ' || cc.chem_conc_uom_nm AS concentration, cc.ixn_qualifier_nm AS vivoVitro, cc.chem_conc_exp_route_nm AS expRoute, cc.taxon_nm_html AS organism, cc.anatomyTerms AS anatomyTerms, cc.anatomyTerms AS anatomyTermsSort, cc.action_type_cd AS directEvidence, cc.action_type_cd AS directEvidenceSort, d.nm AS diseasenm, d.acc_txt AS diseaseacc, d.acc_db_cd AS diseaseaccdbcd, d.id AS diseaseid, tx.nm AS taxonnm, tx.secondary_nm AS taxoncomnm, tx.id AS taxonid, tx.acc_txt AS taxonacc, rg.refAcc, rg.referenceCount, COUNT(*) OVER () fullRowCount FROM deduplicated_cc cc INNER JOIN term t ON cc.chem_id = t.id INNER JOIN term tx ON cc.taxon_id = tx.id LEFT JOIN term d ON cc.disease_id = d.id INNER JOIN reference_groups rg ON rg.chem_id IS NOT DISTINCT FROM cc.chem_id AND rg.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND rg.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND rg.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND rg.disease_id IS NOT DISTINCT FROM cc.disease_id AND rg.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND rg.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND rg.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND rg.anatomy_ids = cc.anatomy_ids WHERE cc.rn = 1 AND t.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1370327') ORDER BY organism LIMIT 50;
Date: 2026-09-27 11:30:15 Duration: 17s200ms Database: ctdprd51 User: pubeu Bind query: yes
-
WITH anatomy AS ( SELECT chem_conc_id, array_agg(anatomy_id ORDER BY anatomy_id) AS anatomy_ids, string_agg(DISTINCT anatomy_nm_html || '^' || anatomy_acc_txt || '^' || position_seq || '^' || anatomy_acc_db_id || '^' || anatomy_nm, '|') AS anatomyTerms FROM chem_conc_anatomy cca GROUP BY chem_conc_id ), reference_groups AS ( SELECT cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, string_agg(DISTINCT r.acc_txt, '|') AS refAcc, COUNT(DISTINCT cc.reference_id) AS referenceCount FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id LEFT JOIN reference r ON r.id = cc.reference_id GROUP BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ), deduplicated_cc AS ( SELECT cc.*, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) AS anatomy_ids, a.anatomyTerms, ROW_NUMBER() OVER (PARTITION BY cc.chem_id, cc.chem_conc, cc.chem_conc_uom_nm, cc.taxon_id, cc.disease_id, cc.chem_conc_exp_route_nm, cc.ixn_qualifier_nm, cc.action_type_cd, COALESCE(a.anatomy_ids, ARRAY[]::integer[]) ORDER BY cc.id) AS rn FROM chem_conc cc LEFT JOIN anatomy a ON a.chem_conc_id = cc.id WHERE EXISTS ( SELECT 1 FROM chem_conc cc_ref LEFT JOIN anatomy a_ref ON a_ref.chem_conc_id = cc_ref.id WHERE cc_ref.chem_id IS NOT DISTINCT FROM cc.chem_id AND cc_ref.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND cc_ref.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND cc_ref.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND cc_ref.disease_id IS NOT DISTINCT FROM cc.disease_id AND cc_ref.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND cc_ref.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND cc_ref.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND COALESCE(a_ref.anatomy_ids, ARRAY[]::integer[]) = COALESCE(a.anatomy_ids, ARRAY[]::integer[]))) SELECT /* ChemConcentrationDao */ cc.chem_id AS chem_id, t.nm AS chem, t.nm_html AS chemNmHtml, t.acc_txt AS chemAcc, t.secondary_nm AS casRN, cc.chem_conc || ' ' || cc.chem_conc_uom_nm AS concentration, cc.ixn_qualifier_nm AS vivoVitro, cc.chem_conc_exp_route_nm AS expRoute, cc.taxon_nm_html AS organism, cc.anatomyTerms AS anatomyTerms, cc.anatomyTerms AS anatomyTermsSort, cc.action_type_cd AS directEvidence, cc.action_type_cd AS directEvidenceSort, d.nm AS diseasenm, d.acc_txt AS diseaseacc, d.acc_db_cd AS diseaseaccdbcd, d.id AS diseaseid, tx.nm AS taxonnm, tx.secondary_nm AS taxoncomnm, tx.id AS taxonid, tx.acc_txt AS taxonacc, rg.refAcc, rg.referenceCount, COUNT(*) OVER () fullRowCount FROM deduplicated_cc cc INNER JOIN term t ON cc.chem_id = t.id INNER JOIN term tx ON cc.taxon_id = tx.id LEFT JOIN term d ON cc.disease_id = d.id INNER JOIN reference_groups rg ON rg.chem_id IS NOT DISTINCT FROM cc.chem_id AND rg.chem_conc IS NOT DISTINCT FROM cc.chem_conc AND rg.chem_conc_uom_nm IS NOT DISTINCT FROM cc.chem_conc_uom_nm AND rg.taxon_id IS NOT DISTINCT FROM cc.taxon_id AND rg.disease_id IS NOT DISTINCT FROM cc.disease_id AND rg.chem_conc_exp_route_nm IS NOT DISTINCT FROM cc.chem_conc_exp_route_nm AND rg.ixn_qualifier_nm IS NOT DISTINCT FROM cc.ixn_qualifier_nm AND rg.action_type_cd IS NOT DISTINCT FROM cc.action_type_cd AND rg.anatomy_ids = cc.anatomy_ids WHERE cc.rn = 1 AND t.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1370327') ORDER BY organism LIMIT 50;
Date: 2026-09-27 11:33:09 Duration: 16s880ms Database: ctdprd51 User: pubeu Bind query: yes
3 11s564ms 11s564ms 11s564ms 1 11s564ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 27 02 1 11s564ms 11s564ms [ User: pubeu - Total duration: 11s564ms - Times executed: 1 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2198383') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-09-27 02:12:54 Duration: 11s564ms Database: ctdprd51 User: pubeu Bind query: yes
4 6s765ms 7s141ms 6s953ms 2 13s907ms vacuum analyze log_query_archive;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 27 00 1 7s141ms 7s141ms Sep 28 00 1 6s765ms 6s765ms -
VACUUM ANALYZE log_query_archive;
Date: 2026-09-27 00:09:35 Duration: 7s141ms
-
VACUUM ANALYZE log_query_archive;
Date: 2026-09-28 00:09:39 Duration: 6s765ms
5 6s557ms 6s557ms 6s557ms 1 6s557ms select ii.cd, count(ii.id) cnt from ( select ot.cd, tl.term_id id from object_type ot inner join term_label tl on ot.id = tl.object_type_id where tl.nm_fts @@ to_tsquery(?, ?) union select ?, r.id from reference r where r.title_abstract_fts @@ to_tsquery(?, ?) or r.id in ( select rpr.reference_id from reference_party_role rpr inner join reference_party rp on rpr.reference_party_id = rp.id where (substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ?)) union select ot.cd, l.object_id from db_link l inner join object_type ot on l.object_type_id = ot.id where l.type_cd = ? and (upper(l.acc_txt) like ?)) ii group by ii.cd;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 27 09 1 6s557ms 6s557ms [ User: pubeu - Total duration: 6s557ms - Times executed: 1 ]
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', 'CTD_GENES_CHEM_CURATED') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', 'CTD_GENES_CHEM_CURATED') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CTD_GENES_CHEM_CURATED')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE 'CTD_GENES_CHEM_CURATED')) ii GROUP BY ii.cd;
Date: 2026-09-27 09:54:26 Duration: 6s557ms Database: ctdprd51 User: pubeu Bind query: yes
6 5s710ms 5s902ms 5s806ms 2 11s613ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 27 05 2 11s613ms 5s806ms [ User: qaeu - Total duration: 5s902ms - Times executed: 1 ]
[ User: pubeu - Total duration: 5s710ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-09-27 05:43:37 Duration: 5s902ms Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-09-27 05:48:44 Duration: 5s710ms Database: ctdprd51 User: pubeu Bind query: yes
7 5s485ms 6s310ms 5s740ms 4 22s962ms select d.abbr dagabbr, d.nm dagnm, gt.level_min_no daglevelmin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pvalcorrected, te.raw_p_val pvalraw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, count(*) over () fullrowcount from term_enrichment te inner join dag_node gt on te.enriched_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where te.term_id = ? and te.enriched_object_type_id = ? order by te.corrected_p_val, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 27 04 4 22s962ms 5s740ms [ User: pubeu - Total duration: 17s476ms - Times executed: 3 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:04 Duration: 6s310ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:08 Duration: 5s638ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1470774' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-09-27 04:10:13 Duration: 5s528ms 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
- 8,790 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
- 1 ERROR entries
- 0 WARNING entries
- 0 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 1 Max number of times the same event was reported
- 1 Total events found
Rank Times reported Error 1 1 ERROR: invalid byte sequence for encoding
Times Reported Most Frequent Error / Event #1
Day Hour Count Sep 27 07 1 - ERROR: invalid byte sequence for encoding "UTF8": 0x00
Context: unnamed portal parameter $1
Statement: SELECT /* ObjectIdDAOImpl.LabelsAndAccs */ t.id ,t.nm ,t.nm_sort nmSort ,t.acc_txt acc ,t.acc_db_cd accDbCd FROM term t ,(SELECT li.term_id FROM term_label li WHERE UPPER(li.nm) = $1 AND li.object_type_id = 2 UNION SELECT l.object_id FROM db_link l WHERE upper( l.acc_txt ) = $2 AND l.object_type_id = 2 AND l.type_cd = 'A') ids WHERE t.id = ids.term_id ORDER BY CASE WHEN UPPER(t.nm) = $3 THEN 1 ELSE 2 END ,t.nm_sortDate: 2026-09-27 07:26:51 Database: ctdprd51 Application: User: pubeu Remote: