pgBadger-
Global information
- Generated on Tue Mar 25 04:10:04 2025
- Log file: /project/archive/log/postgres/dbdev51/postgresql.log-20250324
- Parsed 12,416 log entries in 2s
- Log start from 2025-03-24 00:08:21 to 2025-03-24 23:53:43
-
Overview
Global Stats
- 34 Number of unique normalized queries
- 77 Number of queries
- 37m31s Total query duration
- 2025-03-24 05:45:13 First query
- 2025-03-24 15:31:35 Last query
- 1 queries/s at 2025-03-24 14:33:52 Query peak
- 37m31s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 37m31s Execute total duration
- 18 Number of events
- 7 Number of unique normalized events
- 6 Max number of times the same event was reported
- 0 Number of cancellation
- 1 Total number of automatic vacuums
- 2 Total number of automatic analyzes
- 0 Number temporary file
- 0 Max size of temporary file
- 0.00 B Average size of temporary file
- 1,517 Total number of sessions
- 35 sessions at 2025-03-24 14:49:01 Session peak
- 37d12h11m20s Total duration of sessions
- 35m36s Average duration of sessions
- 0 Average queries per session
- 1s484ms Average queries duration per session
- 35m34s Average idle time per session
- 1,516 Total number of connections
- 9 connections/s at 2025-03-24 05:45:09 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 1 queries/s Query Peak
- 2025-03-24 14:33:52 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2025-03-24 14:33:52 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2025-03-24 05:45:14 Date
Queries duration
Key values
- 37m31s 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) Mar 24 00 0 0ms 0ms 0ms 0ms 0ms 0ms 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 0 0ms 0ms 0ms 0ms 0ms 0ms 03 0 0ms 0ms 0ms 0ms 0ms 0ms 04 0 0ms 0ms 0ms 0ms 0ms 0ms 05 19 0ms 4s626ms 2s457ms 16s815ms 25s460ms 25s460ms 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 0 0ms 0ms 0ms 0ms 0ms 0ms 10 2 0ms 3m11s 1m37s 0ms 3s435ms 3m11s 11 8 0ms 5s595ms 4s662ms 5s519ms 5s595ms 10s635ms 12 4 0ms 2s91ms 2s34ms 2s6ms 2s66ms 2s91ms 13 20 0ms 5s965ms 2s514ms 5s310ms 5s965ms 7s124ms 14 21 0ms 5s490ms 2s101ms 3s908ms 5s464ms 5s490ms 15 3 0ms 31m6s 10m23s 1s636ms 1s653ms 31m6s 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 Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Mar 24 00 0 0 0ms 0ms 0ms 0ms 01 0 0 0ms 0ms 0ms 0ms 02 0 0 0ms 0ms 0ms 0ms 03 0 0 0ms 0ms 0ms 0ms 04 0 0 0ms 0ms 0ms 0ms 05 17 0 2s486ms 0ms 16s815ms 25s460ms 06 0 0 0ms 0ms 0ms 0ms 07 0 0 0ms 0ms 0ms 0ms 08 0 0 0ms 0ms 0ms 0ms 09 0 0 0ms 0ms 0ms 0ms 10 2 0 1m37s 0ms 0ms 3m11s 11 8 0 4s662ms 3s427ms 5s519ms 10s635ms 12 4 0 2s34ms 0ms 2s6ms 2s91ms 13 20 0 2s514ms 3s927ms 5s310ms 7s124ms 14 21 0 2s101ms 3s486ms 3s908ms 5s490ms 15 3 0 10m23s 0ms 1s636ms 31m6s 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 Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Mar 24 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 Day Hour Prepare Bind Bind/Prepare Percentage of prepare Mar 24 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% 04 0 0 0.00 0.00% 05 0 19 19.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 0 0.00 0.00% 10 0 0 0.00 0.00% 11 0 0 0.00 0.00% 12 0 1 1.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 0 0.00 0.00% 17 0 0 0.00 0.00% 18 0 0 0.00 0.00% 19 0 0 0.00 0.00% 20 0 0 0.00 0.00% 21 0 0 0.00 0.00% 22 0 0 0.00 0.00% 23 0 0 0.00 0.00% Day Hour Count Average / Second Mar 24 00 64 0.02/s 01 64 0.02/s 02 64 0.02/s 03 64 0.02/s 04 64 0.02/s 05 68 0.02/s 06 64 0.02/s 07 64 0.02/s 08 64 0.02/s 09 64 0.02/s 10 64 0.02/s 11 57 0.02/s 12 54 0.01/s 13 63 0.02/s 14 65 0.02/s 15 63 0.02/s 16 63 0.02/s 17 59 0.02/s 18 64 0.02/s 19 64 0.02/s 20 64 0.02/s 21 64 0.02/s 22 64 0.02/s 23 64 0.02/s Day Hour Count Average Duration Average idle time Mar 24 00 64 30m38s 30m38s 01 64 30m40s 30m40s 02 64 30m41s 30m41s 03 64 30m38s 30m38s 04 64 30m40s 30m40s 05 68 28m14s 28m14s 06 64 30m38s 30m38s 07 64 30m39s 30m39s 08 64 30m40s 30m40s 09 64 30m39s 30m39s 10 60 29m38s 29m34s 11 57 30m40s 30m39s 12 54 30m41s 30m41s 13 63 30m50s 30m49s 14 64 30m39s 30m39s 15 63 30m40s 30m10s 16 63 30m40s 30m40s 17 59 30m39s 30m39s 18 64 30m39s 30m39s 19 64 30m41s 30m41s 20 67 2h1m31s 2h1m31s 21 67 54m48s 54m48s 22 64 30m41s 30m41s 23 64 30m39s 30m39s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2025-03-24 05:45:09 Date
Connections per database
Key values
- ctddev51 Main Database
- 1,516 connections Total
Connections per user
Key values
- pubeu Main User
- 1,516 connections Total
-
Sessions
Simultaneous sessions
Key values
- 35 sessions Session Peak
- 2025-03-24 14:49:01 Date
Histogram of session times
Key values
- 1,498 1800000-3600000ms duration
Sessions per database
Key values
- ctddev51 Main Database
- 1,517 sessions Total
Sessions per user
Key values
- pubeu Main User
- 1,517 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 1,517 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 55,891 buffers Checkpoint Peak
- 2025-03-24 16:12:22 Date
- 1619.669 seconds Highest write time
- 0.002 seconds Sync time
Checkpoints Wal files
Key values
- 0 files Wal files usage Peak
- 2025-03-24 06:15:27 Date
Checkpoints distance
Key values
- 890.48 Mo Distance Peak
- 2025-03-24 16:12:22 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Mar 24 00 0 0s 0s 0s 01 0 0s 0s 0s 02 0 0s 0s 0s 03 0 0s 0s 0s 04 0 0s 0s 0s 05 0 0s 0s 0s 06 75 7.642s 0.001s 7.657s 07 0 0s 0s 0s 08 0 0s 0s 0s 09 0 0s 0s 0s 10 26 2.722s 0.001s 2.737s 11 0 0s 0s 0s 12 0 0s 0s 0s 13 0 0s 0s 0s 14 0 0s 0s 0s 15 3,396 340.216s 0.001s 340.297s 16 55,891 1,619.669s 0.002s 1,619.787s 17 0 0s 0s 0s 18 0 0s 0s 0s 19 0 0s 0s 0s 20 0 0s 0s 0s 21 0 0s 0s 0s 22 0 0s 0s 0s 23 0 0s 0s 0s Day Hour Added Removed Recycled Synced files Longest sync Average sync Mar 24 00 0 0 0 0 0s 0s 01 0 0 0 0 0s 0s 02 0 0 0 0 0s 0s 03 0 0 0 0 0s 0s 04 0 0 0 0 0s 0s 05 0 0 0 0 0s 0s 06 0 0 0 17 0.001s 0.001s 07 0 0 0 0 0s 0s 08 0 0 0 0 0s 0s 09 0 0 0 0 0s 0s 10 0 0 0 21 0.001s 0.001s 11 0 0 0 0 0s 0s 12 0 0 0 0 0s 0s 13 0 0 0 0 0s 0s 14 0 0 0 0 0s 0s 15 0 8 0 16 0.001s 0.001s 16 0 21 0 14 0.001s 0.001s 17 0 0 0 0 0s 0s 18 0 0 0 0 0s 0s 19 0 0 0 0 0s 0s 20 0 0 0 0 0s 0s 21 0 0 0 0 0s 0s 22 0 0 0 0 0s 0s 23 0 0 0 0 0s 0s Day Hour Count Avg time (sec) Mar 24 00 0 0s 01 0 0s 02 0 0s 03 0 0s 04 0 0s 05 0 0s 06 0 0s 07 0 0s 08 0 0s 09 0 0s 10 0 0s 11 0 0s 12 0 0s 13 0 0s 14 0 0s 15 0 0s 16 0 0s 17 0 0s 18 0 0s 19 0 0s 20 0 0s 21 0 0s 22 0 0s 23 0 0s Day Hour Mean distance Mean estimate Mar 24 00 0.00 kB 0.00 kB 01 0.00 kB 0.00 kB 02 0.00 kB 0.00 kB 03 0.00 kB 0.00 kB 04 0.00 kB 0.00 kB 05 0.00 kB 0.00 kB 06 390.00 kB 12,763.00 kB 07 0.00 kB 0.00 kB 08 0.00 kB 0.00 kB 09 0.00 kB 0.00 kB 10 78.00 kB 11,494.00 kB 11 0.00 kB 0.00 kB 12 0.00 kB 0.00 kB 13 0.00 kB 0.00 kB 14 0.00 kB 0.00 kB 15 28,838.00 kB 28,838.00 kB 16 455,926.00 kB 455,926.00 kB 17 0.00 kB 0.00 kB 18 0.00 kB 0.00 kB 19 0.00 kB 0.00 kB 20 0.00 kB 0.00 kB 21 0.00 kB 0.00 kB 22 0.00 kB 0.00 kB 23 0.00 kB 0.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 Mar 24 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 -
Vacuums
Vacuums / Analyzes Distribution
Key values
- 0.12 sec Highest CPU-cost vacuum
Table pubc.log_query
Database ctddev51 - 2025-03-24 15:31:35 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctddev51 - Date
Average Autovacuum Duration
Key values
- 0.12 sec Highest CPU-cost vacuum
Table pubc.log_query
Database ctddev51 - 2025-03-24 15:31:35 Date
Analyzes per table
Key values
- pubc.log_query (2) Main table analyzed (database ctddev51)
- 2 analyzes Total
Vacuums per table
Key values
- pubc.log_query (1) Main table vacuumed on database ctddev51
- 1 vacuums Total
Tuples removed per table
Key values
- pubc.log_query (976) Main table with removed tuples on database ctddev51
- 976 tuples Total removed
Pages removed per table
Key values
- pubc.log_query (1502) Main table with removed pages on database ctddev51
- 1502 pages Total removed
Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Mar 24 00 0 0 01 0 0 02 0 0 03 0 0 04 0 0 05 0 1 06 0 0 07 0 0 08 0 0 09 0 0 10 0 0 11 0 0 12 0 0 13 0 0 14 0 0 15 1 1 16 0 0 17 0 0 18 0 0 19 0 0 20 0 0 21 0 0 22 0 0 23 0 0 - 0.12 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
- 75 Total read queries
- 2 Total write queries
Queries by database
Key values
- unknown Main database
- 71 Requests
- 37m17s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 84 Requests
User Request type Count Duration pubeu Total 11 26s37ms cte 2 6s811ms select 9 19s225ms unknown Total 84 37m50s cte 1 1s19ms select 83 37m48s Duration by user
Key values
- 37m50s (unknown) Main time consuming user
User Request type Count Duration pubeu Total 11 26s37ms cte 2 6s811ms select 9 19s225ms unknown Total 84 37m50s cte 1 1s19ms select 83 37m48s Queries by host
Key values
- unknown Main host
- 95 Requests
- 38m16s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 77 Requests
- 37m31s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2025-03-24 05:20:41 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 75 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 31m6s select maint_query_logs_archive ();[ Date: 2025-03-24 15:31:35 ]
2 3m11s select l.http_user_agent from log_query_bots l, excluded_user_agent u where upper(l.http_user_agent) like upper(u.user_agent_pattern);[ Date: 2025-03-24 10:48:41 ]
3 5s965ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%');[ Date: 2025-03-24 13:04:12 ]
4 5s595ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%RIDDLER%');[ Date: 2025-03-24 11:31:15 ]
5 5s519ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%');[ Date: 2025-03-24 11:10:06 ]
6 5s490ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%') AND upper(http_user_agent) NOT like upper('%LTX71%') AND upper(http_user_agent) NOT like upper('%HERITRIX%') AND upper(http_user_agent) NOT like upper('%SLEUTH%');[ Date: 2025-03-24 14:15:56 ]
7 5s464ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%') AND upper(http_user_agent) NOT like upper('%LTX71%');[ Date: 2025-03-24 14:14:55 ]
8 5s370ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%');[ Date: 2025-03-24 11:12:23 ]
9 5s345ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%');[ Date: 2025-03-24 11:11:10 ]
10 5s290ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL');[ Date: 2025-03-24 11:11:51 ]
11 5s100ms select * from log_query_archive where upper(http_user_agent) like upper('%DUCK%');[ Date: 2025-03-24 13:05:49 ]
12 4s626ms select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;[ Date: 2025-03-24 05:48:52 - Bind query: yes ]
13 4s590ms select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;[ Date: 2025-03-24 05:48:57 - Bind query: yes ]
14 4s479ms 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 = 1308127)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;[ Date: 2025-03-24 05:48:31 - Bind query: yes ]
15 4s356ms SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));[ Date: 2025-03-24 05:47:22 - Database: ctddev51 - User: pubeu - Bind query: yes ]
16 4s10ms SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;[ Date: 2025-03-24 05:47:27 - Bind query: yes ]
17 3s741ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');[ Date: 2025-03-24 13:08:43 ]
18 3s667ms SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterPathwayWhereEquals.Name */ tp.term_id FROM term_pathway tp WHERE UPPER(tp.pathway_nm) LIKE 'METABOLISM' AND tp.object_type_id = 4))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases')) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;[ Date: 2025-03-24 05:47:35 - Bind query: yes ]
19 3s435ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%');[ Date: 2025-03-24 10:57:56 ]
20 3s427ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%') OR upper(http_user_agent) NOT like upper('%SPIDER%') OR upper(http_user_agent) NOT like upper('%CRAWL%');[ Date: 2025-03-24 11:08:13 ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 31m6s 1 31m6s 31m6s 31m6s select maint_query_logs_archive ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Mar 24 15 1 31m6s 31m6s -
select maint_query_logs_archive ();
Date: 2025-03-24 15:31:35 Duration: 31m6s
2 3m11s 1 3m11s 3m11s 3m11s select l.http_user_agent from log_query_bots l, excluded_user_agent u where upper(l.http_user_agent) like upper(u.user_agent_pattern);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Mar 24 10 1 3m11s 3m11s -
select l.http_user_agent from log_query_bots l, excluded_user_agent u where upper(l.http_user_agent) like upper(u.user_agent_pattern);
Date: 2025-03-24 10:48:41 Duration: 3m11s
3 52s3ms 25 1s585ms 3s741ms 2s80ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Mar 24 12 1 2s66ms 2s66ms 13 12 27s375ms 2s281ms 14 11 20s908ms 1s900ms 15 1 1s653ms 1s653ms -
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:08:43 Duration: 3s741ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:24:57 Duration: 3s372ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%NUTCH%');
Date: 2025-03-24 13:22:06 Duration: 2s207ms
4 21s776ms 4 5s290ms 5s595ms 5s444ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Mar 24 11 4 21s776ms 5s444ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%RIDDLER%');
Date: 2025-03-24 11:31:15 Duration: 5s595ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:10:06 Duration: 5s519ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:12:23 Duration: 5s370ms
5 20s942ms 11 1s599ms 2s29ms 1s903ms select count(*) from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Mar 24 12 1 1s974ms 1s974ms 13 5 9s821ms 1s964ms 14 5 9s147ms 1s829ms -
select count(*) from log_query_archive where upper(http_user_agent) like upper('%KNOWLEDGE AI%');
Date: 2025-03-24 14:49:01 Duration: 2s29ms
-
select count(*) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:16:40 Duration: 2s1ms
-
select count(*) from log_query_archive where upper(http_user_agent) like upper('%TRACK%');
Date: 2025-03-24 13:28:49 Duration: 1s985ms
6 10s767ms 4 1s636ms 5s100ms 2s691ms select * from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Mar 24 12 1 2s6ms 2s6ms 13 2 7s124ms 3s562ms 15 1 1s636ms 1s636ms -
select * from log_query_archive where upper(http_user_agent) like upper('%DUCK%');
Date: 2025-03-24 13:05:49 Duration: 5s100ms
-
select * from log_query_archive where upper(http_user_agent) like upper('%SLURP%');
Date: 2025-03-24 13:05:12 Duration: 2s23ms
-
select * from log_query_archive where upper(http_user_agent) like upper('%ARCHIVE%');
Date: 2025-03-24 12:58:47 Duration: 2s6ms
7 6s844ms 2 3s417ms 3s427ms 3s422ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Mar 24 11 2 6s844ms 3s422ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%') OR upper(http_user_agent) NOT like upper('%SPIDER%') OR upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:08:13 Duration: 3s427ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%') OR upper(http_user_agent) NOT like upper('%SPIDER%') OR upper(http_user_agent) NOT like upper('%CRAWLER');
Date: 2025-03-24 11:01:40 Duration: 3s417ms
8 5s965ms 1 5s965ms 5s965ms 5s965ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Mar 24 13 1 5s965ms 5s965ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%');
Date: 2025-03-24 13:04:12 Duration: 5s965ms
9 5s490ms 1 5s490ms 5s490ms 5s490ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Mar 24 14 1 5s490ms 5s490ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%') AND upper(http_user_agent) NOT like upper('%LTX71%') AND upper(http_user_agent) NOT like upper('%HERITRIX%') AND upper(http_user_agent) NOT like upper('%SLEUTH%');
Date: 2025-03-24 14:15:56 Duration: 5s490ms
10 5s464ms 1 5s464ms 5s464ms 5s464ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Mar 24 14 1 5s464ms 5s464ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%') AND upper(http_user_agent) NOT like upper('%LTX71%');
Date: 2025-03-24 14:14:55 Duration: 5s464ms
11 5s345ms 1 5s345ms 5s345ms 5s345ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Mar 24 11 1 5s345ms 5s345ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%');
Date: 2025-03-24 11:11:10 Duration: 5s345ms
12 4s626ms 1 4s626ms 4s626ms 4s626ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Mar 24 05 1 4s626ms 4s626ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-03-24 05:48:52 Duration: 4s626ms Bind query: yes
13 4s590ms 1 4s590ms 4s590ms 4s590ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Mar 24 05 1 4s590ms 4s590ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-03-24 05:48:57 Duration: 4s590ms Bind query: yes
14 4s479ms 1 4s479ms 4s479ms 4s479ms 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 #14
Day Hour Count Duration Avg duration Mar 24 05 1 4s479ms 4s479ms -
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 = 1308127)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2025-03-24 05:48:31 Duration: 4s479ms Bind query: yes
15 4s410ms 2 1s19ms 3s391ms 2s205ms with recursive sub_node ( object_id, id, path, lvl ) as ( select n.object_id, n.id, array[n.nm_sort], ? from dag_node n where n.object_id = ? union all select n.object_id, n.id, cast(path || n.nm_sort as varchar(?)[]), sn.lvl + ? from dag_node n inner join sub_node sn on (n.parent_id = sn.id)) select distinct t.nm prinm, t.nm_html prinmhtml, t.secondary_nm secondarynm, t.acc_db_cd accdbcd, t.acc_txt termacc, t.is_leaf isleaf, t.has_chems haschems, t.has_diseases hasdiseases, t.has_exposures hasexposures, t.has_genes hasgenes, sn.lvl, sn.path, max(sn.lvl) over () maxlvl, t.has_phenotypes hasphenotypes from sub_node sn inner join term t on sn.object_id = t.id where sn.lvl <= ? order by sn.path;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Mar 24 05 2 4s410ms 2s205ms [ User: pubeu - Total duration: 3s391ms - Times executed: 1 ]
-
WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;
Date: 2025-03-24 05:45:13 Duration: 3s391ms Database: ctddev51 User: pubeu Bind query: yes
-
WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '1449802' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;
Date: 2025-03-24 05:45:14 Duration: 1s19ms Bind query: yes
16 4s356ms 1 4s356ms 4s356ms 4s356ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Mar 24 05 1 4s356ms 4s356ms [ User: pubeu - Total duration: 4s356ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2025-03-24 05:47:22 Duration: 4s356ms Database: ctddev51 User: pubeu Bind query: yes
17 4s10ms 1 4s10ms 4s10ms 4s10ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Mar 24 05 1 4s10ms 4s10ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2025-03-24 05:47:27 Duration: 4s10ms Bind query: yes
18 3s997ms 2 1s825ms 2s172ms 1s998ms select fg.nm fromgenesymbol, fg.acc_txt fromgeneacc, tg.nm togenesymbol, tg.acc_txt togeneacc, ft.nm fromtaxonnm, ft.secondary_nm fromtaxoncommonnm, ft.acc_txt fromtaxonacc, tt.nm totaxonnm, tt.secondary_nm totaxoncommonnm, tt.acc_txt totaxonacc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( select string_agg(ggt.throughput_txt, ? order by ggt.throughput_txt) from gene_gene_ref_throughput ggt where ggt.gene_gene_reference_id = ggr.id) throughput, count(*) over () fullrowcount from gene_gene_reference ggr inner join term fg on ggr.from_gene_id = fg.id inner join term tg on ggr.to_gene_id = tg.id inner join term ft on ggr.from_taxon_id = ft.id inner join term tt on ggr.to_taxon_id = tt.id where ggr.reference_id = ? order by fg.nm_sort, tg.nm_sort limit ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Mar 24 05 2 3s997ms 1s998ms [ User: pubeu - Total duration: 2s172ms - Times executed: 1 ]
-
SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;
Date: 2025-03-24 05:48:11 Duration: 2s172ms Database: ctddev51 User: pubeu Bind query: yes
-
SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;
Date: 2025-03-24 05:48:09 Duration: 1s825ms Bind query: yes
19 3s667ms 1 3s667ms 3s667ms 3s667ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.id ixnid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id where gcr.gene_id = any (array (( select tp.term_id from term_pathway tp where upper(tp.pathway_nm) like ? and tp.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?)) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by g.nm_sort, c.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Mar 24 05 1 3s667ms 3s667ms -
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterPathwayWhereEquals.Name */ tp.term_id FROM term_pathway tp WHERE UPPER(tp.pathway_nm) LIKE 'METABOLISM' AND tp.object_type_id = 4))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases')) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2025-03-24 05:47:35 Duration: 3s667ms Bind query: yes
20 3s435ms 1 3s435ms 3s435ms 3s435ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Mar 24 10 1 3s435ms 3s435ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%');
Date: 2025-03-24 10:57:56 Duration: 3s435ms
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 25 52s3ms 1s585ms 3s741ms 2s80ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Mar 24 12 1 2s66ms 2s66ms 13 12 27s375ms 2s281ms 14 11 20s908ms 1s900ms 15 1 1s653ms 1s653ms -
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:08:43 Duration: 3s741ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:24:57 Duration: 3s372ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%NUTCH%');
Date: 2025-03-24 13:22:06 Duration: 2s207ms
2 11 20s942ms 1s599ms 2s29ms 1s903ms select count(*) from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Mar 24 12 1 1s974ms 1s974ms 13 5 9s821ms 1s964ms 14 5 9s147ms 1s829ms -
select count(*) from log_query_archive where upper(http_user_agent) like upper('%KNOWLEDGE AI%');
Date: 2025-03-24 14:49:01 Duration: 2s29ms
-
select count(*) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:16:40 Duration: 2s1ms
-
select count(*) from log_query_archive where upper(http_user_agent) like upper('%TRACK%');
Date: 2025-03-24 13:28:49 Duration: 1s985ms
3 4 21s776ms 5s290ms 5s595ms 5s444ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Mar 24 11 4 21s776ms 5s444ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%RIDDLER%');
Date: 2025-03-24 11:31:15 Duration: 5s595ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:10:06 Duration: 5s519ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:12:23 Duration: 5s370ms
4 4 10s767ms 1s636ms 5s100ms 2s691ms select * from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Mar 24 12 1 2s6ms 2s6ms 13 2 7s124ms 3s562ms 15 1 1s636ms 1s636ms -
select * from log_query_archive where upper(http_user_agent) like upper('%DUCK%');
Date: 2025-03-24 13:05:49 Duration: 5s100ms
-
select * from log_query_archive where upper(http_user_agent) like upper('%SLURP%');
Date: 2025-03-24 13:05:12 Duration: 2s23ms
-
select * from log_query_archive where upper(http_user_agent) like upper('%ARCHIVE%');
Date: 2025-03-24 12:58:47 Duration: 2s6ms
5 2 6s844ms 3s417ms 3s427ms 3s422ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Mar 24 11 2 6s844ms 3s422ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%') OR upper(http_user_agent) NOT like upper('%SPIDER%') OR upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:08:13 Duration: 3s427ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%') OR upper(http_user_agent) NOT like upper('%SPIDER%') OR upper(http_user_agent) NOT like upper('%CRAWLER');
Date: 2025-03-24 11:01:40 Duration: 3s417ms
6 2 4s410ms 1s19ms 3s391ms 2s205ms with recursive sub_node ( object_id, id, path, lvl ) as ( select n.object_id, n.id, array[n.nm_sort], ? from dag_node n where n.object_id = ? union all select n.object_id, n.id, cast(path || n.nm_sort as varchar(?)[]), sn.lvl + ? from dag_node n inner join sub_node sn on (n.parent_id = sn.id)) select distinct t.nm prinm, t.nm_html prinmhtml, t.secondary_nm secondarynm, t.acc_db_cd accdbcd, t.acc_txt termacc, t.is_leaf isleaf, t.has_chems haschems, t.has_diseases hasdiseases, t.has_exposures hasexposures, t.has_genes hasgenes, sn.lvl, sn.path, max(sn.lvl) over () maxlvl, t.has_phenotypes hasphenotypes from sub_node sn inner join term t on sn.object_id = t.id where sn.lvl <= ? order by sn.path;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Mar 24 05 2 4s410ms 2s205ms [ User: pubeu - Total duration: 3s391ms - Times executed: 1 ]
-
WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;
Date: 2025-03-24 05:45:13 Duration: 3s391ms Database: ctddev51 User: pubeu Bind query: yes
-
WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '1449802' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;
Date: 2025-03-24 05:45:14 Duration: 1s19ms Bind query: yes
7 2 3s997ms 1s825ms 2s172ms 1s998ms select fg.nm fromgenesymbol, fg.acc_txt fromgeneacc, tg.nm togenesymbol, tg.acc_txt togeneacc, ft.nm fromtaxonnm, ft.secondary_nm fromtaxoncommonnm, ft.acc_txt fromtaxonacc, tt.nm totaxonnm, tt.secondary_nm totaxoncommonnm, tt.acc_txt totaxonacc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( select string_agg(ggt.throughput_txt, ? order by ggt.throughput_txt) from gene_gene_ref_throughput ggt where ggt.gene_gene_reference_id = ggr.id) throughput, count(*) over () fullrowcount from gene_gene_reference ggr inner join term fg on ggr.from_gene_id = fg.id inner join term tg on ggr.to_gene_id = tg.id inner join term ft on ggr.from_taxon_id = ft.id inner join term tt on ggr.to_taxon_id = tt.id where ggr.reference_id = ? order by fg.nm_sort, tg.nm_sort limit ?;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Mar 24 05 2 3s997ms 1s998ms [ User: pubeu - Total duration: 2s172ms - Times executed: 1 ]
-
SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;
Date: 2025-03-24 05:48:11 Duration: 2s172ms Database: ctddev51 User: pubeu Bind query: yes
-
SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;
Date: 2025-03-24 05:48:09 Duration: 1s825ms Bind query: yes
8 1 31m6s 31m6s 31m6s 31m6s select maint_query_logs_archive ();Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Mar 24 15 1 31m6s 31m6s -
select maint_query_logs_archive ();
Date: 2025-03-24 15:31:35 Duration: 31m6s
9 1 3m11s 3m11s 3m11s 3m11s select l.http_user_agent from log_query_bots l, excluded_user_agent u where upper(l.http_user_agent) like upper(u.user_agent_pattern);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Mar 24 10 1 3m11s 3m11s -
select l.http_user_agent from log_query_bots l, excluded_user_agent u where upper(l.http_user_agent) like upper(u.user_agent_pattern);
Date: 2025-03-24 10:48:41 Duration: 3m11s
10 1 5s965ms 5s965ms 5s965ms 5s965ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Mar 24 13 1 5s965ms 5s965ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%');
Date: 2025-03-24 13:04:12 Duration: 5s965ms
11 1 5s490ms 5s490ms 5s490ms 5s490ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Mar 24 14 1 5s490ms 5s490ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%') AND upper(http_user_agent) NOT like upper('%LTX71%') AND upper(http_user_agent) NOT like upper('%HERITRIX%') AND upper(http_user_agent) NOT like upper('%SLEUTH%');
Date: 2025-03-24 14:15:56 Duration: 5s490ms
12 1 5s464ms 5s464ms 5s464ms 5s464ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Mar 24 14 1 5s464ms 5s464ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%') AND upper(http_user_agent) NOT like upper('%LTX71%');
Date: 2025-03-24 14:14:55 Duration: 5s464ms
13 1 5s345ms 5s345ms 5s345ms 5s345ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Mar 24 11 1 5s345ms 5s345ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%');
Date: 2025-03-24 11:11:10 Duration: 5s345ms
14 1 4s626ms 4s626ms 4s626ms 4s626ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Mar 24 05 1 4s626ms 4s626ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-03-24 05:48:52 Duration: 4s626ms Bind query: yes
15 1 4s590ms 4s590ms 4s590ms 4s590ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Mar 24 05 1 4s590ms 4s590ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-03-24 05:48:57 Duration: 4s590ms Bind query: yes
16 1 4s479ms 4s479ms 4s479ms 4s479ms 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 #16
Day Hour Count Duration Avg duration Mar 24 05 1 4s479ms 4s479ms -
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 = 1308127)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2025-03-24 05:48:31 Duration: 4s479ms Bind query: yes
17 1 4s356ms 4s356ms 4s356ms 4s356ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Mar 24 05 1 4s356ms 4s356ms [ User: pubeu - Total duration: 4s356ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2025-03-24 05:47:22 Duration: 4s356ms Database: ctddev51 User: pubeu Bind query: yes
18 1 4s10ms 4s10ms 4s10ms 4s10ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Mar 24 05 1 4s10ms 4s10ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2025-03-24 05:47:27 Duration: 4s10ms Bind query: yes
19 1 3s667ms 3s667ms 3s667ms 3s667ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.id ixnid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id where gcr.gene_id = any (array (( select tp.term_id from term_pathway tp where upper(tp.pathway_nm) like ? and tp.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?)) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by g.nm_sort, c.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Mar 24 05 1 3s667ms 3s667ms -
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterPathwayWhereEquals.Name */ tp.term_id FROM term_pathway tp WHERE UPPER(tp.pathway_nm) LIKE 'METABOLISM' AND tp.object_type_id = 4))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases')) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2025-03-24 05:47:35 Duration: 3s667ms Bind query: yes
20 1 3s435ms 3s435ms 3s435ms 3s435ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Mar 24 10 1 3s435ms 3s435ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%');
Date: 2025-03-24 10:57:56 Duration: 3s435ms
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 31m6s 31m6s 31m6s 1 31m6s select maint_query_logs_archive ();Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Mar 24 15 1 31m6s 31m6s -
select maint_query_logs_archive ();
Date: 2025-03-24 15:31:35 Duration: 31m6s
2 3m11s 3m11s 3m11s 1 3m11s select l.http_user_agent from log_query_bots l, excluded_user_agent u where upper(l.http_user_agent) like upper(u.user_agent_pattern);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Mar 24 10 1 3m11s 3m11s -
select l.http_user_agent from log_query_bots l, excluded_user_agent u where upper(l.http_user_agent) like upper(u.user_agent_pattern);
Date: 2025-03-24 10:48:41 Duration: 3m11s
3 5s965ms 5s965ms 5s965ms 1 5s965ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Mar 24 13 1 5s965ms 5s965ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%');
Date: 2025-03-24 13:04:12 Duration: 5s965ms
4 5s490ms 5s490ms 5s490ms 1 5s490ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Mar 24 14 1 5s490ms 5s490ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%') AND upper(http_user_agent) NOT like upper('%LTX71%') AND upper(http_user_agent) NOT like upper('%HERITRIX%') AND upper(http_user_agent) NOT like upper('%SLEUTH%');
Date: 2025-03-24 14:15:56 Duration: 5s490ms
5 5s464ms 5s464ms 5s464ms 1 5s464ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Mar 24 14 1 5s464ms 5s464ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%') AND upper(http_user_agent) NOT like upper('%RIDDLER%') AND upper(http_user_agent) NOT like upper('%SLURP%') AND upper(http_user_agent) NOT like upper('%LTX71%');
Date: 2025-03-24 14:14:55 Duration: 5s464ms
6 5s290ms 5s595ms 5s444ms 4 21s776ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Mar 24 11 4 21s776ms 5s444ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%RIDDLER%');
Date: 2025-03-24 11:31:15 Duration: 5s595ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:10:06 Duration: 5s519ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%') AND upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:12:23 Duration: 5s370ms
7 5s345ms 5s345ms 5s345ms 1 5s345ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?) and upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Mar 24 11 1 5s345ms 5s345ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') AND upper(http_user_agent) NOT like upper('%BOT%') AND upper(http_user_agent) NOT like upper('%SPIDER%');
Date: 2025-03-24 11:11:10 Duration: 5s345ms
8 4s626ms 4s626ms 4s626ms 1 4s626ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Mar 24 05 1 4s626ms 4s626ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-03-24 05:48:52 Duration: 4s626ms Bind query: yes
9 4s590ms 4s590ms 4s590ms 1 4s590ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Mar 24 05 1 4s590ms 4s590ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-03-24 05:48:57 Duration: 4s590ms Bind query: yes
10 4s479ms 4s479ms 4s479ms 1 4s479ms 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 #10
Day Hour Count Duration Avg duration Mar 24 05 1 4s479ms 4s479ms -
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 = 1308127)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2025-03-24 05:48:31 Duration: 4s479ms Bind query: yes
11 4s356ms 4s356ms 4s356ms 1 4s356ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Mar 24 05 1 4s356ms 4s356ms [ User: pubeu - Total duration: 4s356ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2025-03-24 05:47:22 Duration: 4s356ms Database: ctddev51 User: pubeu Bind query: yes
12 4s10ms 4s10ms 4s10ms 1 4s10ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Mar 24 05 1 4s10ms 4s10ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2025-03-24 05:47:27 Duration: 4s10ms Bind query: yes
13 3s667ms 3s667ms 3s667ms 1 3s667ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.id ixnid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id where gcr.gene_id = any (array (( select tp.term_id from term_pathway tp where upper(tp.pathway_nm) like ? and tp.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?)) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by g.nm_sort, c.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Mar 24 05 1 3s667ms 3s667ms -
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterPathwayWhereEquals.Name */ tp.term_id FROM term_pathway tp WHERE UPPER(tp.pathway_nm) LIKE 'METABOLISM' AND tp.object_type_id = 4))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases')) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2025-03-24 05:47:35 Duration: 3s667ms Bind query: yes
14 3s435ms 3s435ms 3s435ms 1 3s435ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Mar 24 10 1 3s435ms 3s435ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%');
Date: 2025-03-24 10:57:56 Duration: 3s435ms
15 3s417ms 3s427ms 3s422ms 2 6s844ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?) or upper(http_user_agent) not like upper(?);Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Mar 24 11 2 6s844ms 3s422ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%') OR upper(http_user_agent) NOT like upper('%SPIDER%') OR upper(http_user_agent) NOT like upper('%CRAWL%');
Date: 2025-03-24 11:08:13 Duration: 3s427ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) NOT like upper('%CRAWLER%') OR upper(http_user_agent) NOT like upper('%BOT%') OR upper(http_user_agent) NOT like upper('%SPIDER%') OR upper(http_user_agent) NOT like upper('%CRAWLER');
Date: 2025-03-24 11:01:40 Duration: 3s417ms
16 1s636ms 5s100ms 2s691ms 4 10s767ms select * from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Mar 24 12 1 2s6ms 2s6ms 13 2 7s124ms 3s562ms 15 1 1s636ms 1s636ms -
select * from log_query_archive where upper(http_user_agent) like upper('%DUCK%');
Date: 2025-03-24 13:05:49 Duration: 5s100ms
-
select * from log_query_archive where upper(http_user_agent) like upper('%SLURP%');
Date: 2025-03-24 13:05:12 Duration: 2s23ms
-
select * from log_query_archive where upper(http_user_agent) like upper('%ARCHIVE%');
Date: 2025-03-24 12:58:47 Duration: 2s6ms
17 1s19ms 3s391ms 2s205ms 2 4s410ms with recursive sub_node ( object_id, id, path, lvl ) as ( select n.object_id, n.id, array[n.nm_sort], ? from dag_node n where n.object_id = ? union all select n.object_id, n.id, cast(path || n.nm_sort as varchar(?)[]), sn.lvl + ? from dag_node n inner join sub_node sn on (n.parent_id = sn.id)) select distinct t.nm prinm, t.nm_html prinmhtml, t.secondary_nm secondarynm, t.acc_db_cd accdbcd, t.acc_txt termacc, t.is_leaf isleaf, t.has_chems haschems, t.has_diseases hasdiseases, t.has_exposures hasexposures, t.has_genes hasgenes, sn.lvl, sn.path, max(sn.lvl) over () maxlvl, t.has_phenotypes hasphenotypes from sub_node sn inner join term t on sn.object_id = t.id where sn.lvl <= ? order by sn.path;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Mar 24 05 2 4s410ms 2s205ms [ User: pubeu - Total duration: 3s391ms - Times executed: 1 ]
-
WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '597156' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;
Date: 2025-03-24 05:45:13 Duration: 3s391ms Database: ctddev51 User: pubeu Bind query: yes
-
WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '1449802' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;
Date: 2025-03-24 05:45:14 Duration: 1s19ms Bind query: yes
18 1s585ms 3s741ms 2s80ms 25 52s3ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Mar 24 12 1 2s66ms 2s66ms 13 12 27s375ms 2s281ms 14 11 20s908ms 1s900ms 15 1 1s653ms 1s653ms -
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:08:43 Duration: 3s741ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:24:57 Duration: 3s372ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like upper('%NUTCH%');
Date: 2025-03-24 13:22:06 Duration: 2s207ms
19 1s825ms 2s172ms 1s998ms 2 3s997ms select fg.nm fromgenesymbol, fg.acc_txt fromgeneacc, tg.nm togenesymbol, tg.acc_txt togeneacc, ft.nm fromtaxonnm, ft.secondary_nm fromtaxoncommonnm, ft.acc_txt fromtaxonacc, tt.nm totaxonnm, tt.secondary_nm totaxoncommonnm, tt.acc_txt totaxonacc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( select string_agg(ggt.throughput_txt, ? order by ggt.throughput_txt) from gene_gene_ref_throughput ggt where ggt.gene_gene_reference_id = ggr.id) throughput, count(*) over () fullrowcount from gene_gene_reference ggr inner join term fg on ggr.from_gene_id = fg.id inner join term tg on ggr.to_gene_id = tg.id inner join term ft on ggr.from_taxon_id = ft.id inner join term tt on ggr.to_taxon_id = tt.id where ggr.reference_id = ? order by fg.nm_sort, tg.nm_sort limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Mar 24 05 2 3s997ms 1s998ms [ User: pubeu - Total duration: 2s172ms - Times executed: 1 ]
-
SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;
Date: 2025-03-24 05:48:11 Duration: 2s172ms Database: ctddev51 User: pubeu Bind query: yes
-
SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;
Date: 2025-03-24 05:48:09 Duration: 1s825ms Bind query: yes
20 1s599ms 2s29ms 1s903ms 11 20s942ms select count(*) from log_query_archive where upper(http_user_agent) like upper(?);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Mar 24 12 1 1s974ms 1s974ms 13 5 9s821ms 1s964ms 14 5 9s147ms 1s829ms -
select count(*) from log_query_archive where upper(http_user_agent) like upper('%KNOWLEDGE AI%');
Date: 2025-03-24 14:49:01 Duration: 2s29ms
-
select count(*) from log_query_archive where upper(http_user_agent) like upper('%SPIDER%');
Date: 2025-03-24 13:16:40 Duration: 2s1ms
-
select count(*) from log_query_archive where upper(http_user_agent) like upper('%TRACK%');
Date: 2025-03-24 13:28:49 Duration: 1s985ms
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
- 6,207 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
- 10 ERROR entries
- 2 WARNING entries
- 6 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 6 Max number of times the same event was reported
- 18 Total events found
Rank Times reported Error 1 6 LOG: could not receive data from client: Connection timed out
Times Reported Most Frequent Error / Event #1
Day Hour Count Mar 24 20 3 21 3 2 3 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #2
Day Hour Count Mar 24 10 2 15 1 - ERROR: syntax error at or near "excluded_user_agent" at character 48
- ERROR: syntax error at or near "%" at character 78
- ERROR: syntax error at or near "where" at character 63
Statement: select l.http_user_agent from log_query_bots l excluded_user_agent u where upper(l.http_user_agent) like upper(u.user_agent_pattern)
Date: 2025-03-24 10:45:17
Statement: select count(*) from log_query_bots where upper(http_user_agent) like upper(%CRAWLER%) OR upper(http_user_agent) like upper(%BOT%)
Date: 2025-03-24 10:55:54
Statement: select distinct(http_user_agent) from log_query_archive where where upper(http_user_agent) like upper ('%LIB%')
Date: 2025-03-24 15:07:44
3 3 ERROR: duplicate key value violates unique constraint "..."
Times Reported Most Frequent Error / Event #3
Day Hour Count Mar 24 14 3 - ERROR: duplicate key value violates unique constraint "excluded_user_agent_pk"
- ERROR: duplicate key value violates unique constraint "excluded_user_agent_pk"
- ERROR: duplicate key value violates unique constraint "excluded_user_agent_pk"
Detail: Key (user_agent_pattern)=(%ARCHIVE%) already exists.
Statement: INSERT INTO pubc.excluded_user_agent (user_agent_pattern ) VALUES ('%ARCHIVE%') ,('%SPIDER%') , ('%FACEBOOK%') , ('%NUTCH%') , ('%TRACK%') , ('%SITESUCKER%') , ('%KNOWLEDGE AI%') , ('%MEGAINDEX%') , ('%YANDEX%') , ('%PANSCIENT%') ;Date: 2025-03-24 14:52:52 Database: ctddev51 Application: pgAdmin 4 - CONN:9595324 User: pubc Remote:
Detail: Key (user_agent_pattern)=(%SPIDER%) already exists.
Statement: INSERT INTO pubc.excluded_user_agent (user_agent_pattern ) VALUES ('%SPIDER%') , ('%FACEBOOK%') , ('%NUTCH%') , ('%TRACK%') , ('%SITESUCKER%') , ('%KNOWLEDGE AI%') , ('%MEGAINDEX%') , ('%YANDEX%') , ('%PANSCIENT%') ;Date: 2025-03-24 14:53:31
Detail: Key (user_agent_pattern)=(%FACEBOOK%) already exists.
Statement: INSERT INTO pubc.excluded_user_agent (user_agent_pattern ) VALUES ('%FACEBOOK%') , ('%NUTCH%') , ('%TRACK%') , ('%SITESUCKER%') , ('%KNOWLEDGE AI%') , ('%MEGAINDEX%') , ('%YANDEX%') , ('%PANSCIENT%') ;Date: 2025-03-24 14:54:02
4 2 WARNING: is not a PostgreSQL server process
Times Reported Most Frequent Error / Event #4
Day Hour Count Mar 24 10 2 5 2 ERROR: more than one row returned by a subquery used as an expression
Times Reported Most Frequent Error / Event #5
Day Hour Count Mar 24 10 2 - ERROR: more than one row returned by a subquery used as an expression
- ERROR: more than one row returned by a subquery used as an expression
Statement: select count(*) from log_query_bots where upper(http_user_agent) like (select upper(user_agent_pattern) from excluded_user_agent)
Date: 2025-03-24 10:38:41 Database: ctddev51 Application: pgAdmin 4 - CONN:9230116 User: pubc Remote:
Statement: select http_user_agent from log_query_bots where upper(http_user_agent) like (select upper(user_agent_pattern) from excluded_user_agent)
Date: 2025-03-24 10:41:09
6 1 ERROR: column "..." does not exist
Times Reported Most Frequent Error / Event #6
Day Hour Count Mar 24 10 1 - ERROR: column "http_user_agent" does not exist at character 73
Statement: create table test_user_agent as select * from excluded_user_agent where http_user_agent in ('%SPIDER%')
Date: 2025-03-24 10:33:47 Database: ctddev51 Application: pgAdmin 4 - CONN:2095606 User: pubc Remote:
7 1 ERROR: syntax error at end of input
Times Reported Most Frequent Error / Event #7
Day Hour Count Mar 24 14 1 - ERROR: syntax error at end of input at character 553
Statement: select count(*) from log_query_bots where upper(http_user_agent) like upper('%CRAWLER%') OR upper(http_user_agent) like upper('%BOT%') OR upper(http_user_agent) like upper('%SPIDER%') OR upper(http_user_agent) like upper('%CRAWL%') OR upper(http_user_agent) like upper('%RIDDLER%') OR upper(http_user_agent) like upper('%SLURP%') OR upper(http_user_agent) like upper('%LTX71%') OR upper(http_user_agent) like upper('%HERITRIX%') OR upper(http_user_agent) like upper('%SLEUTH%') OR upper(http_user_agent) like upper('%YANDEX%') OR
Date: 2025-03-24 14:29:54