pgBadger-
Global information
- Generated on Sat Dec 14 04:10:04 2024
- Log file: /project/archive/log/postgres/dbdev51/postgresql.log-20241213
- Parsed 12,317 log entries in 2s
- Log start from 2024-12-13 00:00:34 to 2024-12-13 23:47:17
-
Overview
Global Stats
- 26 Number of unique normalized queries
- 31 Number of queries
- 9m43s Total query duration
- 2024-12-13 05:45:12 First query
- 2024-12-13 15:16:28 Last query
- 1 queries/s at 2024-12-13 11:22:08 Query peak
- 9m43s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 9m43s Execute total duration
- 36 Number of events
- 9 Number of unique normalized events
- 10 Max number of times the same event was reported
- 0 Number of cancellation
- 0 Total number of automatic vacuums
- 0 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
- 36 sessions at 2024-12-13 11:20:12 Session peak
- 34d17h4m46s Total duration of sessions
- 32m56s Average duration of sessions
- 0 Average queries per session
- 384ms Average queries duration per session
- 32m56s Average idle time per session
- 1,517 Total number of connections
- 9 connections/s at 2024-12-13 05:45:08 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 1 queries/s Query Peak
- 2024-12-13 11:22:08 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2024-12-13 11:22:08 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2024-12-13 05:45:12 Date
Queries duration
Key values
- 9m43s 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) Dec 13 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 6s664ms 2s566ms 4s632ms 15s955ms 28s165ms 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 28s612ms 14s990ms 0ms 1s367ms 28s612ms 11 1 0ms 1m38s 1m38s 0ms 0ms 1m38s 12 0 0ms 0ms 0ms 0ms 0ms 0ms 13 4 0ms 1m38s 1m35s 1m33s 1m34s 1m38s 14 0 0ms 0ms 0ms 0ms 0ms 0ms 15 5 0ms 6s989ms 5s118ms 4s685ms 6s989ms 10s391ms 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) Dec 13 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 2s595ms 0ms 0ms 28s165ms 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 14s990ms 0ms 0ms 28s612ms 11 1 0 1m38s 0ms 0ms 1m38s 12 0 0 0ms 0ms 0ms 0ms 13 4 0 1m35s 0ms 1m33s 1m38s 14 0 0 0ms 0ms 0ms 0ms 15 5 0 5s118ms 0ms 4s685ms 10s391ms 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) Dec 13 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 Dec 13 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 0 0.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 0 0.00 0.00% 17 0 0 0.00 0.00% 18 0 0 0.00 0.00% 19 0 0 0.00 0.00% 20 0 0 0.00 0.00% 21 0 0 0.00 0.00% 22 0 0 0.00 0.00% 23 0 0 0.00 0.00% Day Hour Count Average / Second Dec 13 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 69 0.02/s 06 64 0.02/s 07 64 0.02/s 08 64 0.02/s 09 64 0.02/s 10 68 0.02/s 11 64 0.02/s 12 65 0.02/s 13 65 0.02/s 14 63 0.02/s 15 64 0.02/s 16 62 0.02/s 17 59 0.02/s 18 59 0.02/s 19 62 0.02/s 20 56 0.02/s 21 62 0.02/s 22 59 0.02/s 23 64 0.02/s Day Hour Count Average Duration Average idle time Dec 13 00 64 30m40s 30m40s 01 64 30m40s 30m40s 02 64 30m38s 30m38s 03 64 30m40s 30m40s 04 64 30m40s 30m40s 05 69 28m9s 28m8s 06 64 30m39s 30m39s 07 64 30m39s 30m39s 08 64 30m38s 30m38s 09 64 30m41s 30m41s 10 63 30m39s 30m39s 11 63 30m39s 30m38s 12 64 30m41s 30m41s 13 64 30m38s 30m33s 14 63 30m40s 30m40s 15 64 30m40s 30m40s 16 62 30m39s 30m39s 17 59 30m40s 30m40s 18 62 50m49s 50m49s 19 67 1h6m15s 1h6m15s 20 56 30m40s 30m40s 21 62 30m39s 30m39s 22 59 30m39s 30m39s 23 64 30m41s 30m41s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2024-12-13 05:45:08 Date
Connections per database
Key values
- ctddev51 Main Database
- 1,517 connections Total
Connections per user
Key values
- pubeu Main User
- 1,517 connections Total
-
Sessions
Simultaneous sessions
Key values
- 36 sessions Session Peak
- 2024-12-13 11:20:12 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
- 41 buffers Checkpoint Peak
- 2024-12-13 06:01:58 Date
- 4.215 seconds Highest write time
- 0.001 seconds Sync time
Checkpoints Wal files
Key values
- 0 files Wal files usage Peak
- 2024-12-13 06:01:58 Date
Checkpoints distance
Key values
- 0.57 Mo Distance Peak
- 2024-12-13 06:01:58 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Dec 13 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 41 4.215s 0.001s 4.23s 07 0 0s 0s 0s 08 0 0s 0s 0s 09 0 0s 0s 0s 10 3 0.477s 0.001s 0.492s 11 0 0s 0s 0s 12 0 0s 0s 0s 13 0 0s 0s 0s 14 0 0s 0s 0s 15 0 0s 0s 0s 16 0 0s 0s 0s 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 Dec 13 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 7 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 2 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 0 0 0 0s 0s 16 0 0 0 0 0s 0s 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) Dec 13 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 Dec 13 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 291.00 kB 291.00 kB 07 0.00 kB 0.00 kB 08 0.00 kB 0.00 kB 09 0.00 kB 0.00 kB 10 7.00 kB 263.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 0.00 kB 0.00 kB 16 0.00 kB 0.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 Dec 13 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 sec Highest CPU-cost vacuum
Table
Database - Date
- 0 sec Highest CPU-cost analyze
Table
Database - Date
Autovacuum actions (5 minutes period)
NO DATASET
Average Autovacuum Duration
Key values
- 0 sec Highest CPU-cost vacuum
Table
Database - Date
Average Autovacuum Duration (5 minutes average)
NO DATASET
Analyzes per table
Key values
- unknown (0) Main table analyzed (database )
- 0 analyzes Total
Vacuums per table
Key values
- unknown (0) Main table vacuumed on database
- 0 vacuums Total
Tuples removed per table
Key values
- unknown (0) Main table with removed tuples on database
- 0 tuples Total removed
Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Dec 13 00 0 0 01 0 0 02 0 0 03 0 0 04 0 0 05 0 0 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 0 0 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 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
- 29 Total read queries
- 2 Total write queries
Queries by database
Key values
- unknown Main database
- 24 Requests
- 8m58s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 37 Requests
User Request type Count Duration edit Total 2 29s980ms select 2 29s980ms pubeu Total 9 30s191ms cte 2 6s697ms select 7 23s494ms unknown Total 37 9m28s cte 1 1s97ms select 36 9m27s Duration by user
Key values
- 9m28s (unknown) Main time consuming user
User Request type Count Duration edit Total 2 29s980ms select 2 29s980ms pubeu Total 9 30s191ms cte 2 6s697ms select 7 23s494ms unknown Total 37 9m28s cte 1 1s97ms select 36 9m27s Queries by host
Key values
- unknown Main host
- 48 Requests
- 10m29s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 29 Requests
- 9m13s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2024-12-13 08:48:38 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 25 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 1m38s SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));[ Date: 2024-12-13 11:22:08 ]
2 1m38s SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));[ Date: 2024-12-13 13:53:16 ]
3 1m34s SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));[ Date: 2024-12-13 13:32:48 ]
4 1m33s select object_type_id, count(*) from ( SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn RI WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)))) AS test GROUP BY object_type_id;[ Date: 2024-12-13 13:24:34 ]
5 1m33s -- How many PubTator'd chemical, disease, and gene terms were actually curated? -- ANSWER: SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn RI WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));[ Date: 2024-12-13 13:19:25 ]
6 28s612ms select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ri.reference_acc_txt IN ( SELECT reference_acc_txt FROM tm_reference_term);[ Date: 2024-12-13 10:28:55 - Database: ctddev51 - User: edit - Application: pgAdmin 4 - CONN:3022153 ]
7 6s989ms SELECT ia.acc_txt, trt.* -- ,trt.primary_term_acc_txt -- ,ri.reference_acc_txt -- ,ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;[ Date: 2024-12-13 15:14:34 ]
8 6s664ms 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: 2024-12-13 05:48:54 - Bind query: yes ]
9 6s489ms 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: 2024-12-13 05:48:47 - Bind query: yes ]
10 5s773ms SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id;[ Date: 2024-12-13 15:06:00 ]
11 4s700ms 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 = 1301125)) 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: 2024-12-13 05:48:25 - Database: ctddev51 - User: pubeu - Bind query: yes ]
12 4s685ms SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;[ Date: 2024-12-13 15:13:49 ]
13 4s617ms SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id order by create_tm asc;[ Date: 2024-12-13 15:06:58 ]
14 4s82ms 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: 2024-12-13 05:47:19 - Database: ctddev51 - User: pubeu - Bind query: yes ]
15 3s982ms 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: 2024-12-13 05:47:24 - Bind query: yes ]
16 3s534ms 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 = '594258' 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: 2024-12-13 05:45:12 - Database: ctddev51 - User: pubeu - Bind query: yes ]
17 3s523ms SELECT distinct ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, tr.acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;[ Date: 2024-12-13 15:16:28 ]
18 3s519ms 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: 2024-12-13 05:47:32 - Bind query: yes ]
19 1s527ms SELECT /* GeneBrowseTermsDAO */ t.nm, t.nm_html nmHtml, t.secondary_nm secondaryNm, t.acc_txt acc, 'name:' || t.nm accQueryStr, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term t WHERE t.object_type_id = '4' AND REGEXP_REPLACE(UPPER(SUBSTRING(t.nm, 1, 1)), '[^A-Z]', '#') = 'A' ORDER BY t.nm_sort LIMIT 100;[ Date: 2024-12-13 05:47:09 - Bind query: yes ]
20 1s520ms 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.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) 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: 2024-12-13 05:47:28 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 6m25s 4 1m33s 1m38s 1m36s select distinct ia.object_type_id, ia.acc_txt, ri.reference_acc_txt from ixn_actor ia, ixn i, reference_ixn ri where ia.object_type_id in (...) and ia.ixn_id = i.id and i.root_id = ri.ixn_id and (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) in ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id and trt.object_type_id in (...) and tm.acc_txt in ( select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ia.object_type_id in (...) and ri.reference_acc_txt in ( select acc_txt from tm_reference)));Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Dec 13 11 1 1m38s 1m38s 13 3 4m46s 1m35s -
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 11:22:08 Duration: 1m38s
-
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 13:53:16 Duration: 1m38s
-
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 13:32:48 Duration: 1m34s
2 1m33s 1 1m33s 1m33s 1m33s select object_type_id, count(*) from ( select distinct ia.object_type_id, ia.acc_txt, ri.reference_acc_txt from ixn_actor ia, ixn i, reference_ixn ri where ia.object_type_id in (...) and ia.ixn_id = i.id and i.root_id = ri.ixn_id and (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) in ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id and trt.object_type_id in (...) and tm.acc_txt in ( select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ia.object_type_id in (...) and ri.reference_acc_txt in ( select acc_txt from tm_reference)))) as test group by object_type_id;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Dec 13 13 1 1m33s 1m33s -
select object_type_id, count(*) from ( SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn RI WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)))) AS test GROUP BY object_type_id;
Date: 2024-12-13 13:24:34 Duration: 1m33s
3 28s612ms 1 28s612ms 28s612ms 28s612ms select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ri.reference_acc_txt in ( select reference_acc_txt from tm_reference_term);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Dec 13 10 1 28s612ms 28s612ms [ User: edit - Total duration: 28s612ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:3022153 - Total duration: 28s612ms - Times executed: 1 ]
-
select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ri.reference_acc_txt IN ( SELECT reference_acc_txt FROM tm_reference_term);
Date: 2024-12-13 10:28:55 Duration: 28s612ms Database: ctddev51 User: edit Application: pgAdmin 4 - CONN:3022153
4 6s989ms 1 6s989ms 6s989ms 6s989ms select ia.acc_txt, trt.* from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Dec 13 15 1 6s989ms 6s989ms -
SELECT ia.acc_txt, trt.* -- ,trt.primary_term_acc_txt -- ,ri.reference_acc_txt -- ,ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:14:34 Duration: 6s989ms
5 6s664ms 1 6s664ms 6s664ms 6s664ms 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 #5
Day Hour Count Duration Avg duration Dec 13 05 1 6s664ms 6s664ms -
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: 2024-12-13 05:48:54 Duration: 6s664ms Bind query: yes
6 6s489ms 1 6s489ms 6s489ms 6s489ms 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 #6
Day Hour Count Duration Avg duration Dec 13 05 1 6s489ms 6s489ms -
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: 2024-12-13 05:48:47 Duration: 6s489ms Bind query: yes
7 5s773ms 1 5s773ms 5s773ms 5s773ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Dec 13 15 1 5s773ms 5s773ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id;
Date: 2024-12-13 15:06:00 Duration: 5s773ms
8 4s700ms 1 4s700ms 4s700ms 4s700ms 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 #8
Day Hour Count Duration Avg duration Dec 13 05 1 4s700ms 4s700ms [ User: pubeu - Total duration: 4s700ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1301125)) 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: 2024-12-13 05:48:25 Duration: 4s700ms Database: ctddev51 User: pubeu Bind query: yes
9 4s685ms 1 4s685ms 4s685ms 4s685ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Dec 13 15 1 4s685ms 4s685ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:13:49 Duration: 4s685ms
10 4s632ms 2 1s97ms 3s534ms 2s316ms 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 #10
Day Hour Count Duration Avg duration Dec 13 05 2 4s632ms 2s316ms [ User: pubeu - Total duration: 3s534ms - 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 = '594258' 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: 2024-12-13 05:45:12 Duration: 3s534ms 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 = '1442653' 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: 2024-12-13 05:45:13 Duration: 1s97ms Bind query: yes
11 4s617ms 1 4s617ms 4s617ms 4s617ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id order by create_tm asc;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Dec 13 15 1 4s617ms 4s617ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id order by create_tm asc;
Date: 2024-12-13 15:06:58 Duration: 4s617ms
12 4s82ms 1 4s82ms 4s82ms 4s82ms 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 #12
Day Hour Count Duration Avg duration Dec 13 05 1 4s82ms 4s82ms [ User: pubeu - Total duration: 4s82ms - 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: 2024-12-13 05:47:19 Duration: 4s82ms Database: ctddev51 User: pubeu Bind query: yes
13 3s982ms 1 3s982ms 3s982ms 3s982ms 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 #13
Day Hour Count Duration Avg duration Dec 13 05 1 3s982ms 3s982ms -
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: 2024-12-13 05:47:24 Duration: 3s982ms Bind query: yes
14 3s523ms 1 3s523ms 3s523ms 3s523ms select distinct ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, tr.acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Dec 13 15 1 3s523ms 3s523ms -
SELECT distinct ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, tr.acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:16:28 Duration: 3s523ms
15 3s519ms 1 3s519ms 3s519ms 3s519ms 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 #15
Day Hour Count Duration Avg duration Dec 13 05 1 3s519ms 3s519ms -
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: 2024-12-13 05:47:32 Duration: 3s519ms Bind query: yes
16 2s780ms 2 1s325ms 1s455ms 1s390ms 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 #16
Day Hour Count Duration Avg duration Dec 13 05 2 2s780ms 1s390ms -
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: 2024-12-13 05:48:06 Duration: 1s455ms 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: 2024-12-13 05:48:04 Duration: 1s325ms Bind query: yes
17 1s527ms 1 1s527ms 1s527ms 1s527ms select t.nm, t.nm_html nmhtml, t.secondary_nm secondarynm, t.acc_txt acc, ? || t.nm accquerystr, t.has_chems haschems, t.has_diseases hasdiseases, t.has_exposures hasexposures, t.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term t where t.object_type_id = ? and regexp_replace(upper(substring(t.nm, ?, ?)), ?, ?) = ? order by t.nm_sort limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Dec 13 05 1 1s527ms 1s527ms -
SELECT /* GeneBrowseTermsDAO */ t.nm, t.nm_html nmHtml, t.secondary_nm secondaryNm, t.acc_txt acc, 'name:' || t.nm accQueryStr, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term t WHERE t.object_type_id = '4' AND REGEXP_REPLACE(UPPER(SUBSTRING(t.nm, 1, 1)), '[^A-Z]', '#') = 'A' ORDER BY t.nm_sort LIMIT 100;
Date: 2024-12-13 05:47:09 Duration: 1s527ms Bind query: yes
18 1s520ms 1 1s520ms 1s520ms 1s520ms 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.taxon_id = any (array ( select dp.descendant_object_id from term t inner join dag_path dp on t.id = dp.ancestor_object_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 Dec 13 05 1 1s520ms 1s520ms -
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.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) 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: 2024-12-13 05:47:28 Duration: 1s520ms Bind query: yes
19 1s450ms 1 1s450ms 1s450ms 1s450ms select coalesce(d.abbr_display, d.nm_display) nm # ?, d.description # ?, coalesce(d.abbr, d.nm) anchor # ?, get_homepage_url (d.id) url # ? from db d # ? where d.id in (# ? select l.db_id # ? from db_link l # ? where l.type_cd = ? # ? and l.object_type_id = ?) # ? order by ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Dec 13 05 1 1s450ms 1s450ms -
SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;
Date: 2024-12-13 05:48:31 Duration: 1s450ms Bind query: yes
20 1s367ms 1 1s367ms 1s367ms 1s367ms select * from ixn_actor where ixn_id not in ( select root_id from ixn) limit ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Dec 13 10 1 1s367ms 1s367ms [ User: edit - Total duration: 1s367ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:2448083 - Total duration: 1s367ms - Times executed: 1 ]
-
select * from ixn_actor where ixn_id not in ( select root_id from ixn) limit 100;
Date: 2024-12-13 10:15:32 Duration: 1s367ms Database: ctddev51 User: edit Application: pgAdmin 4 - CONN:2448083
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 4 6m25s 1m33s 1m38s 1m36s select distinct ia.object_type_id, ia.acc_txt, ri.reference_acc_txt from ixn_actor ia, ixn i, reference_ixn ri where ia.object_type_id in (...) and ia.ixn_id = i.id and i.root_id = ri.ixn_id and (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) in ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id and trt.object_type_id in (...) and tm.acc_txt in ( select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ia.object_type_id in (...) and ri.reference_acc_txt in ( select acc_txt from tm_reference)));Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Dec 13 11 1 1m38s 1m38s 13 3 4m46s 1m35s -
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 11:22:08 Duration: 1m38s
-
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 13:53:16 Duration: 1m38s
-
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 13:32:48 Duration: 1m34s
2 2 4s632ms 1s97ms 3s534ms 2s316ms 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 #2
Day Hour Count Duration Avg duration Dec 13 05 2 4s632ms 2s316ms [ User: pubeu - Total duration: 3s534ms - 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 = '594258' 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: 2024-12-13 05:45:12 Duration: 3s534ms 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 = '1442653' 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: 2024-12-13 05:45:13 Duration: 1s97ms Bind query: yes
3 2 2s780ms 1s325ms 1s455ms 1s390ms 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 #3
Day Hour Count Duration Avg duration Dec 13 05 2 2s780ms 1s390ms -
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: 2024-12-13 05:48:06 Duration: 1s455ms 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: 2024-12-13 05:48:04 Duration: 1s325ms Bind query: yes
4 1 1m33s 1m33s 1m33s 1m33s select object_type_id, count(*) from ( select distinct ia.object_type_id, ia.acc_txt, ri.reference_acc_txt from ixn_actor ia, ixn i, reference_ixn ri where ia.object_type_id in (...) and ia.ixn_id = i.id and i.root_id = ri.ixn_id and (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) in ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id and trt.object_type_id in (...) and tm.acc_txt in ( select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ia.object_type_id in (...) and ri.reference_acc_txt in ( select acc_txt from tm_reference)))) as test group by object_type_id;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Dec 13 13 1 1m33s 1m33s -
select object_type_id, count(*) from ( SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn RI WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)))) AS test GROUP BY object_type_id;
Date: 2024-12-13 13:24:34 Duration: 1m33s
5 1 28s612ms 28s612ms 28s612ms 28s612ms select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ri.reference_acc_txt in ( select reference_acc_txt from tm_reference_term);Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Dec 13 10 1 28s612ms 28s612ms [ User: edit - Total duration: 28s612ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:3022153 - Total duration: 28s612ms - Times executed: 1 ]
-
select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ri.reference_acc_txt IN ( SELECT reference_acc_txt FROM tm_reference_term);
Date: 2024-12-13 10:28:55 Duration: 28s612ms Database: ctddev51 User: edit Application: pgAdmin 4 - CONN:3022153
6 1 6s989ms 6s989ms 6s989ms 6s989ms select ia.acc_txt, trt.* from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Dec 13 15 1 6s989ms 6s989ms -
SELECT ia.acc_txt, trt.* -- ,trt.primary_term_acc_txt -- ,ri.reference_acc_txt -- ,ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:14:34 Duration: 6s989ms
7 1 6s664ms 6s664ms 6s664ms 6s664ms 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 #7
Day Hour Count Duration Avg duration Dec 13 05 1 6s664ms 6s664ms -
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: 2024-12-13 05:48:54 Duration: 6s664ms Bind query: yes
8 1 6s489ms 6s489ms 6s489ms 6s489ms 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 Dec 13 05 1 6s489ms 6s489ms -
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: 2024-12-13 05:48:47 Duration: 6s489ms Bind query: yes
9 1 5s773ms 5s773ms 5s773ms 5s773ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Dec 13 15 1 5s773ms 5s773ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id;
Date: 2024-12-13 15:06:00 Duration: 5s773ms
10 1 4s700ms 4s700ms 4s700ms 4s700ms 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 Dec 13 05 1 4s700ms 4s700ms [ User: pubeu - Total duration: 4s700ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1301125)) 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: 2024-12-13 05:48:25 Duration: 4s700ms Database: ctddev51 User: pubeu Bind query: yes
11 1 4s685ms 4s685ms 4s685ms 4s685ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Dec 13 15 1 4s685ms 4s685ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:13:49 Duration: 4s685ms
12 1 4s617ms 4s617ms 4s617ms 4s617ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id order by create_tm asc;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Dec 13 15 1 4s617ms 4s617ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id order by create_tm asc;
Date: 2024-12-13 15:06:58 Duration: 4s617ms
13 1 4s82ms 4s82ms 4s82ms 4s82ms 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 #13
Day Hour Count Duration Avg duration Dec 13 05 1 4s82ms 4s82ms [ User: pubeu - Total duration: 4s82ms - 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: 2024-12-13 05:47:19 Duration: 4s82ms Database: ctddev51 User: pubeu Bind query: yes
14 1 3s982ms 3s982ms 3s982ms 3s982ms 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 #14
Day Hour Count Duration Avg duration Dec 13 05 1 3s982ms 3s982ms -
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: 2024-12-13 05:47:24 Duration: 3s982ms Bind query: yes
15 1 3s523ms 3s523ms 3s523ms 3s523ms select distinct ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, tr.acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Dec 13 15 1 3s523ms 3s523ms -
SELECT distinct ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, tr.acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:16:28 Duration: 3s523ms
16 1 3s519ms 3s519ms 3s519ms 3s519ms 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 #16
Day Hour Count Duration Avg duration Dec 13 05 1 3s519ms 3s519ms -
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: 2024-12-13 05:47:32 Duration: 3s519ms Bind query: yes
17 1 1s527ms 1s527ms 1s527ms 1s527ms select t.nm, t.nm_html nmhtml, t.secondary_nm secondarynm, t.acc_txt acc, ? || t.nm accquerystr, t.has_chems haschems, t.has_diseases hasdiseases, t.has_exposures hasexposures, t.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term t where t.object_type_id = ? and regexp_replace(upper(substring(t.nm, ?, ?)), ?, ?) = ? order by t.nm_sort limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Dec 13 05 1 1s527ms 1s527ms -
SELECT /* GeneBrowseTermsDAO */ t.nm, t.nm_html nmHtml, t.secondary_nm secondaryNm, t.acc_txt acc, 'name:' || t.nm accQueryStr, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term t WHERE t.object_type_id = '4' AND REGEXP_REPLACE(UPPER(SUBSTRING(t.nm, 1, 1)), '[^A-Z]', '#') = 'A' ORDER BY t.nm_sort LIMIT 100;
Date: 2024-12-13 05:47:09 Duration: 1s527ms Bind query: yes
18 1 1s520ms 1s520ms 1s520ms 1s520ms 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.taxon_id = any (array ( select dp.descendant_object_id from term t inner join dag_path dp on t.id = dp.ancestor_object_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 Dec 13 05 1 1s520ms 1s520ms -
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.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) 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: 2024-12-13 05:47:28 Duration: 1s520ms Bind query: yes
19 1 1s450ms 1s450ms 1s450ms 1s450ms select coalesce(d.abbr_display, d.nm_display) nm # ?, d.description # ?, coalesce(d.abbr, d.nm) anchor # ?, get_homepage_url (d.id) url # ? from db d # ? where d.id in (# ? select l.db_id # ? from db_link l # ? where l.type_cd = ? # ? and l.object_type_id = ?) # ? order by ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Dec 13 05 1 1s450ms 1s450ms -
SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;
Date: 2024-12-13 05:48:31 Duration: 1s450ms Bind query: yes
20 1 1s367ms 1s367ms 1s367ms 1s367ms select * from ixn_actor where ixn_id not in ( select root_id from ixn) limit ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Dec 13 10 1 1s367ms 1s367ms [ User: edit - Total duration: 1s367ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:2448083 - Total duration: 1s367ms - Times executed: 1 ]
-
select * from ixn_actor where ixn_id not in ( select root_id from ixn) limit 100;
Date: 2024-12-13 10:15:32 Duration: 1s367ms Database: ctddev51 User: edit Application: pgAdmin 4 - CONN:2448083
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 1m33s 1m38s 1m36s 4 6m25s select distinct ia.object_type_id, ia.acc_txt, ri.reference_acc_txt from ixn_actor ia, ixn i, reference_ixn ri where ia.object_type_id in (...) and ia.ixn_id = i.id and i.root_id = ri.ixn_id and (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) in ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id and trt.object_type_id in (...) and tm.acc_txt in ( select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ia.object_type_id in (...) and ri.reference_acc_txt in ( select acc_txt from tm_reference)));Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Dec 13 11 1 1m38s 1m38s 13 3 4m46s 1m35s -
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 11:22:08 Duration: 1m38s
-
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 13:53:16 Duration: 1m38s
-
SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn ri WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)));
Date: 2024-12-13 13:32:48 Duration: 1m34s
2 1m33s 1m33s 1m33s 1 1m33s select object_type_id, count(*) from ( select distinct ia.object_type_id, ia.acc_txt, ri.reference_acc_txt from ixn_actor ia, ixn i, reference_ixn ri where ia.object_type_id in (...) and ia.ixn_id = i.id and i.root_id = ri.ixn_id and (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) in ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id and trt.object_type_id in (...) and tm.acc_txt in ( select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ia.object_type_id in (...) and ri.reference_acc_txt in ( select acc_txt from tm_reference)))) as test group by object_type_id;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Dec 13 13 1 1m33s 1m33s -
select object_type_id, count(*) from ( SELECT DISTINCT ia.object_type_id, ia.acc_txt, ri.reference_acc_txt FROM ixn_actor ia, ixn i, reference_ixn RI WHERE ia.OBJECT_type_id IN (2, 3, 4) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND (ia.object_type_id, ia.acc_txt, ri.reference_acc_txt) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt, tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN (2, 3, 4) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN (2, 3, 4) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference)))) AS test GROUP BY object_type_id;
Date: 2024-12-13 13:24:34 Duration: 1m33s
3 28s612ms 28s612ms 28s612ms 1 28s612ms select distinct reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia where ri.ixn_id = i.root_id and i.id = ia.ixn_id and ri.reference_acc_txt in ( select reference_acc_txt from tm_reference_term);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Dec 13 10 1 28s612ms 28s612ms [ User: edit - Total duration: 28s612ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:3022153 - Total duration: 28s612ms - Times executed: 1 ]
-
select DISTINCT reference_acc_txt from reference_ixn ri, ixn i, ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ri.reference_acc_txt IN ( SELECT reference_acc_txt FROM tm_reference_term);
Date: 2024-12-13 10:28:55 Duration: 28s612ms Database: ctddev51 User: edit Application: pgAdmin 4 - CONN:3022153
4 6s989ms 6s989ms 6s989ms 1 6s989ms select ia.acc_txt, trt.* from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Dec 13 15 1 6s989ms 6s989ms -
SELECT ia.acc_txt, trt.* -- ,trt.primary_term_acc_txt -- ,ri.reference_acc_txt -- ,ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:14:34 Duration: 6s989ms
5 6s664ms 6s664ms 6s664ms 1 6s664ms 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 #5
Day Hour Count Duration Avg duration Dec 13 05 1 6s664ms 6s664ms -
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: 2024-12-13 05:48:54 Duration: 6s664ms Bind query: yes
6 6s489ms 6s489ms 6s489ms 1 6s489ms 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 #6
Day Hour Count Duration Avg duration Dec 13 05 1 6s489ms 6s489ms -
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: 2024-12-13 05:48:47 Duration: 6s489ms Bind query: yes
7 5s773ms 5s773ms 5s773ms 1 5s773ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Dec 13 15 1 5s773ms 5s773ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id;
Date: 2024-12-13 15:06:00 Duration: 5s773ms
8 4s700ms 4s700ms 4s700ms 1 4s700ms 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 #8
Day Hour Count Duration Avg duration Dec 13 05 1 4s700ms 4s700ms [ User: pubeu - Total duration: 4s700ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1301125)) 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: 2024-12-13 05:48:25 Duration: 4s700ms Database: ctddev51 User: pubeu Bind query: yes
9 4s685ms 4s685ms 4s685ms 1 4s685ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Dec 13 15 1 4s685ms 4s685ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:13:49 Duration: 4s685ms
10 4s617ms 4s617ms 4s617ms 1 4s617ms select ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id order by create_tm asc;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Dec 13 15 1 4s617ms 4s617ms -
SELECT ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id order by create_tm asc;
Date: 2024-12-13 15:06:58 Duration: 4s617ms
11 4s82ms 4s82ms 4s82ms 1 4s82ms 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 Dec 13 05 1 4s82ms 4s82ms [ User: pubeu - Total duration: 4s82ms - 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: 2024-12-13 05:47:19 Duration: 4s82ms Database: ctddev51 User: pubeu Bind query: yes
12 3s982ms 3s982ms 3s982ms 1 3s982ms 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 Dec 13 05 1 3s982ms 3s982ms -
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: 2024-12-13 05:47:24 Duration: 3s982ms Bind query: yes
13 3s523ms 3s523ms 3s523ms 1 3s523ms select distinct ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, tr.acc_txt, ia.create_tm from ixn_actor ia inner join ixn i on ia.ixn_id = i.id inner join reference_ixn ri on i.root_id = ri.ixn_id inner join tm_reference tr on ri.reference_acc_txt = tr.acc_txt left outer join tm_reference_term trt on tr.id = trt.tm_reference_id and ia.acc_txt = trt.primary_term_acc_txt and ia.object_type_id = trt.object_type_id where ia.create_tm > ? order by create_tm asc;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Dec 13 15 1 3s523ms 3s523ms -
SELECT distinct ia.acc_txt, trt.primary_term_acc_txt, ri.reference_acc_txt, tr.acc_txt, ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc;
Date: 2024-12-13 15:16:28 Duration: 3s523ms
14 3s519ms 3s519ms 3s519ms 1 3s519ms 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 #14
Day Hour Count Duration Avg duration Dec 13 05 1 3s519ms 3s519ms -
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: 2024-12-13 05:47:32 Duration: 3s519ms Bind query: yes
15 1s97ms 3s534ms 2s316ms 2 4s632ms 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 Dec 13 05 2 4s632ms 2s316ms [ User: pubeu - Total duration: 3s534ms - 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 = '594258' 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: 2024-12-13 05:45:12 Duration: 3s534ms 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 = '1442653' 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: 2024-12-13 05:45:13 Duration: 1s97ms Bind query: yes
16 1s527ms 1s527ms 1s527ms 1 1s527ms select t.nm, t.nm_html nmhtml, t.secondary_nm secondarynm, t.acc_txt acc, ? || t.nm accquerystr, t.has_chems haschems, t.has_diseases hasdiseases, t.has_exposures hasexposures, t.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term t where t.object_type_id = ? and regexp_replace(upper(substring(t.nm, ?, ?)), ?, ?) = ? order by t.nm_sort limit ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Dec 13 05 1 1s527ms 1s527ms -
SELECT /* GeneBrowseTermsDAO */ t.nm, t.nm_html nmHtml, t.secondary_nm secondaryNm, t.acc_txt acc, 'name:' || t.nm accQueryStr, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term t WHERE t.object_type_id = '4' AND REGEXP_REPLACE(UPPER(SUBSTRING(t.nm, 1, 1)), '[^A-Z]', '#') = 'A' ORDER BY t.nm_sort LIMIT 100;
Date: 2024-12-13 05:47:09 Duration: 1s527ms Bind query: yes
17 1s520ms 1s520ms 1s520ms 1 1s520ms 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.taxon_id = any (array ( select dp.descendant_object_id from term t inner join dag_path dp on t.id = dp.ancestor_object_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 Dec 13 05 1 1s520ms 1s520ms -
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.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) 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: 2024-12-13 05:47:28 Duration: 1s520ms Bind query: yes
18 1s450ms 1s450ms 1s450ms 1 1s450ms select coalesce(d.abbr_display, d.nm_display) nm # ?, d.description # ?, coalesce(d.abbr, d.nm) anchor # ?, get_homepage_url (d.id) url # ? from db d # ? where d.id in (# ? select l.db_id # ? from db_link l # ? where l.type_cd = ? # ? and l.object_type_id = ?) # ? order by ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Dec 13 05 1 1s450ms 1s450ms -
SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;
Date: 2024-12-13 05:48:31 Duration: 1s450ms Bind query: yes
19 1s325ms 1s455ms 1s390ms 2 2s780ms 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 Dec 13 05 2 2s780ms 1s390ms -
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: 2024-12-13 05:48:06 Duration: 1s455ms 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: 2024-12-13 05:48:04 Duration: 1s325ms Bind query: yes
20 1s367ms 1s367ms 1s367ms 1 1s367ms select * from ixn_actor where ixn_id not in ( select root_id from ixn) limit ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Dec 13 10 1 1s367ms 1s367ms [ User: edit - Total duration: 1s367ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:2448083 - Total duration: 1s367ms - Times executed: 1 ]
-
select * from ixn_actor where ixn_id not in ( select root_id from ixn) limit 100;
Date: 2024-12-13 10:15:32 Duration: 1s367ms Database: ctddev51 User: edit Application: pgAdmin 4 - CONN:2448083
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,188 Log entries
Events distribution
Key values
- 0 PANIC entries
- 4 FATAL entries
- 32 ERROR entries
- 0 WARNING entries
Most Frequent Errors/Events
Key values
- 10 Max number of times the same event was reported
- 36 Total events found
Rank Times reported Error 1 10 ERROR: column "..." does not exist
Times Reported Most Frequent Error / Event #1
Day Hour Count Dec 13 10 6 15 4 - ERROR: column "reference_acc_txt" does not exist at character 42
- ERROR: column "acc_txt" does not exist at character 42
- ERROR: column "reference_acc_txt" does not exist at character 37
Statement: select * from tm_reference_term where reference_acc_txt in ( '10023788' )
Date: 2024-12-13 10:32:01
Statement: select * from tm_reference_term where acc_txt in ( '10023788' )
Date: 2024-12-13 10:32:16
Statement: select * from tm_reference where reference_acc_txt in ( '10023788' )
Date: 2024-12-13 10:32:31
2 8 LOG: could not receive data from client: Connection timed out
Times Reported Most Frequent Error / Event #2
Day Hour Count Dec 13 18 3 19 5 3 7 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #3
Day Hour Count Dec 13 11 2 12 1 13 1 15 3 - ERROR: syntax error at or near "select" at character 401
- ERROR: syntax error at or near "select" at character 401
- ERROR: syntax error at or near "AND" at character 77
Statement: -- PubTator References Curated select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) -- Distinct chemical, disease, and gene terms in the curated PubTator text-mined articles select distinct trt.object_type_id, trt.primary_term_acc_txt, tr.acc_txt from tm_reference_term trt ,tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN ( 2,3,4 ) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) )
Date: 2024-12-13 11:05:32
Statement: -- PubTator References Curated select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) -- Distinct chemical, disease, and gene terms in the curated PubTator text-mined articles select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt ,tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN ( 2,3,4 ) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) )
Date: 2024-12-13 11:05:45
Statement: select DISTINCT reference_acc_txt from reference_ixn ri AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference )
Date: 2024-12-13 12:46:07 Database: ctddev51 Application: pgAdmin 4 - CONN:842862 User: edit Remote:
4 4 FATAL: password authentication failed for user "..."
Times Reported Most Frequent Error / Event #4
Day Hour Count Dec 13 10 4 - FATAL: password authentication failed for user "load"
Detail: Connection matched pg_hba.conf line 85: "host all all 192.168.201.0/24 md5 "
Date: 2024-12-13 10:08:19
5 3 ERROR: missing FROM-clause entry for table "..."
Times Reported Most Frequent Error / Event #5
Day Hour Count Dec 13 11 1 13 2 - ERROR: missing FROM-clause entry for table "ri" at character 78
- ERROR: missing FROM-clause entry for table "ri" at character 170
- ERROR: missing FROM-clause entry for table "ia" at character 1068
Statement: SELECT DISTINCT ia.object_type_id ,ia.acc_txt ,ri.reference_acc_txt FROM ixn_actor ia ,ixn i ,reference_ixn WHERE ia.OBJECT_type_id IN ( 2,3,4 ) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND ( ia.object_type_id, ia.acc_txt, ri.reference_acc_txt ) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt ,tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN ( 2,3,4 ) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) ) )
Date: 2024-12-13 11:20:16 Database: ctddev51 Application: pgAdmin 4 - CONN:533302 User: edit Remote:
Statement: -- How many PubTator'd chemical, disease, and gene terms were actually curated? -- ANSWER: SELECT DISTINCT ia.object_type_id ,ia.acc_txt ,ri.reference_acc_txt FROM ixn_actor ia ,ixn i ,reference_ixn WHERE ia.OBJECT_type_id IN ( 2,3,4 ) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND ( ia.object_type_id, ia.acc_txt, ri.reference_acc_txt ) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt ,tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN ( 2,3,4 ) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) ) )
Date: 2024-12-13 13:17:30
Statement: select object_type_id, count(*) from ( SELECT DISTINCT ia.object_type_id ,ia.acc_txt ,ri.reference_acc_txt FROM ixn_actor ia ,ixn i ,reference_ixn RI WHERE ia.OBJECT_type_id IN ( 2,3,4 ) AND ia.ixn_id = i.id AND i.root_id = ri.ixn_id AND ( ia.object_type_id, ia.acc_txt, ri.reference_acc_txt ) IN ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt ,tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN ( 2,3,4 ) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) ) ) ) AS test GROUP BY ia.object_type_id
Date: 2024-12-13 13:22:15
6 1 ERROR: subquery in FROM must have an alias
Times Reported Most Frequent Error / Event #6
Day Hour Count Dec 13 13 1 - ERROR: subquery in FROM must have an alias at character 38
Hint: For example, FROM (SELECT ...) [AS] foo.
Statement: select object_type_id. count(*) from ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt ,tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN ( 2,3,4 ) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) ) ) group by object_type_idDate: 2024-12-13 13:05:40 Database: ctddev51 Application: pgAdmin 4 - CONN:3710768 User: edit Remote:
7 1 ERROR: column reference "..." is ambiguous
Times Reported Most Frequent Error / Event #7
Day Hour Count Dec 13 15 1 - ERROR: column reference "create_tm" is ambiguous at character 594
Statement: SELECT distinct ia.acc_txt ,trt.primary_term_acc_txt ,ri.reference_acc_txt ,tr.acc_txt -- ,ia.create_tm FROM ixn_actor ia INNER JOIN ixn i ON ia.ixn_id = i.id INNER JOIN reference_ixn ri ON i.root_id = ri.ixn_id INNER JOIN tm_reference tr ON ri.reference_acc_txt = tr.acc_txt LEFT OUTER JOIN tm_reference_term trt ON tr.id = trt.tm_reference_id AND ia.acc_txt = trt.primary_term_acc_txt AND ia.object_type_id = trt.object_type_id where ia.create_tm > '2024-06-01' order by create_tm asc
Date: 2024-12-13 15:51:52
8 1 ERROR: schema "..." does not exist
Times Reported Most Frequent Error / Event #8
Day Hour Count Dec 13 13 1 - ERROR: schema "object_type_id" does not exist at character 8
Statement: select object_type_id. count(*) from ( select distinct trt.object_type_id, trt.primary_term_acc_txt, tm.acc_txt from tm_reference_term trt ,tm_reference tm where trt.tm_reference_id = tm.id AND trt.OBJECT_type_id IN ( 2,3,4 ) and tm.acc_txt in ( select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ia.OBJECT_type_id IN ( 2,3,4 ) AND ri.reference_acc_txt IN ( SELECT acc_txt FROM tm_reference ) ) ) as test group by object_type_id
Date: 2024-12-13 13:05:49
9 1 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #9
Day Hour Count Dec 13 10 1 - ERROR: canceling statement due to user request
Statement: select DISTINCT reference_acc_txt from reference_ixn ri ,ixn i ,ixn_actor ia WHERE ri.ixn_id = i.root_id AND i.id = ia.ixn_id AND ri.reference_acc_txt IN ( SELECT reference_acc_txt FROM tm_reference )
Date: 2024-12-13 10:33:30