pgBadger-
Global information
- Generated on Wed Sep 18 04:10:04 2024
- Log file: /project/archive/log/postgres/dbdev51/postgresql.log-20240917
- Parsed 15,572 log entries in 3s
- Log start from 2024-09-17 00:01:15 to 2024-09-17 23:58:45
-
Overview
Global Stats
- 37 Number of unique normalized queries
- 112 Number of queries
- 54m14s Total query duration
- 2024-09-17 05:45:12 First query
- 2024-09-17 16:12:05 Last query
- 1 queries/s at 2024-09-17 09:59:29 Query peak
- 54m14s Total query duration
- 0ms Prepare/parse total duration
- 2s50ms Bind total duration
- 54m12s Execute total duration
- 26 Number of events
- 4 Number of unique normalized events
- 20 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,902 Total number of sessions
- 43 sessions at 2024-09-17 09:58:30 Session peak
- 40d6h28m44s Total duration of sessions
- 30m29s Average duration of sessions
- 0 Average queries per session
- 1s710ms Average queries duration per session
- 30m27s Average idle time per session
- 1,902 Total number of connections
- 5 connections/s at 2024-09-17 05:45:08 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 1 queries/s Query Peak
- 2024-09-17 09:59:29 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2024-09-17 09:59:29 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2024-09-17 05:45:13 Date
Queries duration
Key values
- 54m14s Total query duration
Prepared queries ratio
Key values
- 0.00 Ratio of bind vs prepare
- 0.00 % Ratio between prepared and "usual" statements
General Activity
↑ Back to the top of the General Activity tableDay Hour Count Min duration Max duration Avg duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Sep 17 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 20 0ms 4s158ms 2s209ms 0ms 14s909ms 24s754ms 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 4 0ms 52s733ms 15s911ms 0ms 0ms 1m3s 10 1 0ms 2m32s 2m32s 0ms 0ms 2m32s 11 0 0ms 0ms 0ms 0ms 0ms 0ms 12 4 0ms 57s659ms 17s662ms 0ms 12s991ms 57s659ms 13 34 0ms 3m3s 30s729ms 1m33s 2m34s 3m3s 14 24 0ms 2m34s 35s111ms 2m27s 2m30s 2m34s 15 13 0ms 5m12s 44s762ms 13s921ms 2m31s 5m12s 16 12 0ms 2m36s 37s623ms 1m4s 2m34s 2m36s 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) Sep 17 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 18 0 2s203ms 0ms 0ms 24s754ms 06 0 0 0ms 0ms 0ms 0ms 07 0 0 0ms 0ms 0ms 0ms 08 0 0 0ms 0ms 0ms 0ms 09 4 0 15s911ms 0ms 0ms 1m3s 10 1 0 2m32s 0ms 0ms 2m32s 11 0 0 0ms 0ms 0ms 0ms 12 4 0 17s662ms 0ms 0ms 57s659ms 13 34 0 30s729ms 56s554ms 1m33s 3m3s 14 24 0 35s111ms 13s985ms 2m27s 2m34s 15 13 0 44s762ms 6s782ms 13s921ms 5m12s 16 12 0 37s623ms 1s568ms 1m4s 2m36s 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) Sep 17 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 Sep 17 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 20 20.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 4 4.00 0.00% 10 0 1 1.00 0.00% 11 0 0 0.00 0.00% 12 0 4 4.00 0.00% 13 0 34 34.00 0.00% 14 0 24 24.00 0.00% 15 0 13 13.00 0.00% 16 0 12 12.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 Sep 17 00 71 0.02/s 01 78 0.02/s 02 75 0.02/s 03 80 0.02/s 04 80 0.02/s 05 85 0.02/s 06 80 0.02/s 07 78 0.02/s 08 80 0.02/s 09 83 0.02/s 10 80 0.02/s 11 79 0.02/s 12 77 0.02/s 13 87 0.02/s 14 84 0.02/s 15 83 0.02/s 16 82 0.02/s 17 78 0.02/s 18 76 0.02/s 19 77 0.02/s 20 80 0.02/s 21 78 0.02/s 22 80 0.02/s 23 71 0.02/s Day Hour Count Average Duration Average idle time Sep 17 00 71 30m40s 30m40s 01 78 30m41s 30m41s 02 75 30m38s 30m38s 03 80 30m40s 30m40s 04 80 30m40s 30m40s 05 85 28m42s 28m42s 06 80 30m40s 30m40s 07 78 30m39s 30m39s 08 80 30m38s 30m38s 09 80 30m42s 30m41s 10 81 30m19s 30m17s 11 81 33m10s 33m10s 12 77 30m19s 30m18s 13 85 27m20s 27m7s 14 85 28m55s 28m45s 15 83 29m16s 29m9s 16 82 30m9s 30m3s 17 78 30m39s 30m39s 18 77 34m7s 34m7s 19 77 30m38s 30m38s 20 80 30m39s 30m39s 21 78 30m41s 30m41s 22 80 30m38s 30m38s 23 71 30m40s 30m40s -
Connections
Established Connections
Key values
- 5 connections Connection Peak
- 2024-09-17 05:45:08 Date
Connections per database
Key values
- ctddev51 Main Database
- 1,902 connections Total
Connections per user
Key values
- editeu Main User
- 1,902 connections Total
-
Sessions
Simultaneous sessions
Key values
- 43 sessions Session Peak
- 2024-09-17 09:58:30 Date
Histogram of session times
Key values
- 1,863 1800000-3600000ms duration
Sessions per database
Key values
- ctddev51 Main Database
- 1,902 sessions Total
Sessions per user
Key values
- editeu Main User
- 1,902 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 1,902 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 69 buffers Checkpoint Peak
- 2024-09-17 06:11:26 Date
- 7.004 seconds Highest write time
- 0.001 seconds Sync time
Checkpoints Wal files
Key values
- 0 files Wal files usage Peak
- 2024-09-17 15:11:21 Date
Checkpoints distance
Key values
- 0.81 Mo Distance Peak
- 2024-09-17 06:11:26 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Sep 17 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 69 7.004s 0.001s 7.019s 07 0 0s 0s 0s 08 0 0s 0s 0s 09 0 0s 0s 0s 10 0 0s 0s 0s 11 0 0s 0s 0s 12 0 0s 0s 0s 13 6 0.694s 0.001s 0.71s 14 10 1.099s 0.001s 1.115s 15 13 1.479s 0.002s 1.509s 16 6 0.686s 0.001s 0.702s 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 Sep 17 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 0 0s 0s 11 0 0 0 0 0s 0s 12 0 0 0 0 0s 0s 13 0 0 0 6 0.001s 0.001s 14 0 0 0 7 0.001s 0.001s 15 0 0 0 12 0.001s 0.002s 16 0 0 0 6 0.001s 0.001s 17 0 0 0 0 0s 0s 18 0 0 0 0 0s 0s 19 0 0 0 0 0s 0s 20 0 0 0 0 0s 0s 21 0 0 0 0 0s 0s 22 0 0 0 0 0s 0s 23 0 0 0 0 0s 0s Day Hour Count Avg time (sec) Sep 17 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 Sep 17 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 416.00 kB 429.00 kB 07 0.00 kB 0.00 kB 08 0.00 kB 0.00 kB 09 0.00 kB 0.00 kB 10 0.00 kB 0.00 kB 11 0.00 kB 0.00 kB 12 0.00 kB 0.00 kB 13 20.00 kB 388.00 kB 14 38.00 kB 353.00 kB 15 22.50 kB 304.50 kB 16 21.00 kB 263.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 Sep 17 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 Sep 17 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
- 110 Total read queries
- 2 Total write queries
Queries by database
Key values
- unknown Main database
- 81 Requests
- 44m18s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 102 Requests
User Request type Count Duration pub1 Total 21 5m24s select 21 5m24s pubeu Total 17 6m17s cte 2 6s928ms select 15 6m10s unknown Total 102 49m53s cte 2 2s119ms select 100 49m50s Duration by user
Key values
- 49m53s (unknown) Main time consuming user
User Request type Count Duration pub1 Total 21 5m24s select 21 5m24s pubeu Total 17 6m17s cte 2 6s928ms select 15 6m10s unknown Total 102 49m53s cte 2 2s119ms select 100 49m50s Queries by host
Key values
- unknown Main host
- 140 Requests
- 1h1m35s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 112 Requests
- 54m12s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2024-09-17 18:20:19 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 83 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 5m12s select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where exists ( 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 'CHEMICALS') and viaChemPTR.via_term_id = dp.descendant_object_id) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;[ Date: 2024-09-17 15:29:31 - Database: ctddev51 - User: pubeu - Bind query: yes ]
2 3m3s SELECT distinct viaChemPTR.via_term_id -- chem #015 ,viaGenePTR.via_term_id -- gene #015 ,viaChemPTR.phenotype_id -- phenotype #015 ,viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR#015 ,pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR#015 WHERE viaChemPTR.term_id = viaGenePTR.term_id #015 AND viaChemPTR.term_object_type_id = 3#015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id #015 AND viaChemPTR.via_term_object_type_id = 2#015 AND viaGenePTR.via_term_object_type_id = 4#015;[ Date: 2024-09-17 13:05:20 - Bind query: yes ]
3 2m36s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 16:06:41 - Bind query: yes ]
4 2m34s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 14:07:53 - Bind query: yes ]
5 2m34s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 13:43:54 - Bind query: yes ]
6 2m34s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 16:12:05 - Bind query: yes ]
7 2m33s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 13:39:26 - Bind query: yes ]
8 2m32s SELECT distinct viaChemPTR.via_term_id -- chem #015 ,viaGenePTR.via_term_id -- gene #015 ,viaChemPTR.phenotype_id -- phenotype #015 ,viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR#015 ,pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR#015 WHERE viaChemPTR.term_id = viaGenePTR.term_id #015 AND viaChemPTR.term_object_type_id = 3#015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id #015 AND viaChemPTR.via_term_object_type_id = 2#015 AND viaGenePTR.via_term_object_type_id = 4#015;[ Date: 2024-09-17 10:02:15 - Bind query: yes ]
9 2m31s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 15:21:02 - Bind query: yes ]
10 2m30s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 14:57:06 - Bind query: yes ]
11 2m30s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 13:48:45 - Bind query: yes ]
12 2m28s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 14:02:29 - Bind query: yes ]
13 2m27s SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;[ Date: 2024-09-17 14:46:31 - Bind query: yes ]
14 1m30s SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;[ Date: 2024-09-17 13:01:45 - Database: ctddev51 - User: pub1 - Bind query: yes ]
15 1m5s SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;[ Date: 2024-09-17 13:36:26 - Bind query: yes ]
16 1m SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;[ Date: 2024-09-17 13:45:45 - Bind query: yes ]
17 57s916ms SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;[ Date: 2024-09-17 16:09:04 - Bind query: yes ]
18 57s659ms SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;[ Date: 2024-09-17 12:54:58 - Database: ctddev51 - User: pub1 - Bind query: yes ]
19 56s554ms SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;[ Date: 2024-09-17 13:40:53 - Bind query: yes ]
20 55s298ms SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;[ Date: 2024-09-17 14:43:37 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 17m40s 7 2m27s 2m34s 2m31s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub1.phenotype_term_reference viachemptr # ?, pub1.phenotype_term_reference viageneptr # ? where viachemptr.term_id = viageneptr.term_id # ? and viachemptr.term_object_type_id = ? # ? and viachemptr.phenotype_id = viageneptr.phenotype_id # ? and viachemptr.via_term_object_type_id = ? # ? and viageneptr.via_term_object_type_id = ? # ?;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 17 13 3 7m38s 2m32s 14 4 10m1s 2m30s -
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 14:07:53 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 13:43:54 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 13:39:26 Duration: 2m33s Bind query: yes
2 10m2s 10 52s733ms 1m30s 1m select gene_id # ?, chem_id # ?, reference_id # ? from pub1.gene_chem_reference # ? # ?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 17 09 1 52s733ms 52s733ms 12 1 57s659ms 57s659ms 13 5 5m26s 1m5s 14 3 2m45s 55s90ms [ User: pub1 - Total duration: 3m20s - Times executed: 3 ]
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:01:45 Duration: 1m30s Database: ctddev51 User: pub1 Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:36:26 Duration: 1m5s Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:45:45 Duration: 1m Bind query: yes
3 7m42s 3 2m31s 2m36s 2m34s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub2.phenotype_term_reference viachemptr # ?, pub2.phenotype_term_reference viageneptr # ? where viachemptr.term_id = viageneptr.term_id # ? and viachemptr.term_object_type_id = ? # ? and viachemptr.phenotype_id = viageneptr.phenotype_id # ? and viachemptr.via_term_object_type_id = ? # ? and viageneptr.via_term_object_type_id = ? # ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 17 15 1 2m31s 2m31s 16 2 5m10s 2m35s -
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 16:06:41 Duration: 2m36s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 16:12:05 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 15:21:02 Duration: 2m31s Bind query: yes
4 5m36s 2 2m32s 3m3s 2m48s select distinct viachemptr.via_term_id -- chem #? ,viageneptr.via_term_id -- gene #? ,viachemptr.phenotype_id -- phenotype #? ,viachemptr.term_id -- disease #? from pub1.phenotype_term_reference viachemptr#? ,pub1.phenotype_term_reference viageneptr#? where viachemptr.term_id = viageneptr.term_id #? and viachemptr.term_object_type_id = ?#? and viachemptr.phenotype_id = viageneptr.phenotype_id #? and viachemptr.via_term_object_type_id = ?#? and viageneptr.via_term_object_type_id = ?#?;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 17 10 1 2m32s 2m32s 13 1 3m3s 3m3s -
SELECT distinct viaChemPTR.via_term_id -- chem #015 ,viaGenePTR.via_term_id -- gene #015 ,viaChemPTR.phenotype_id -- phenotype #015 ,viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR#015 ,pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR#015 WHERE viaChemPTR.term_id = viaGenePTR.term_id #015 AND viaChemPTR.term_object_type_id = 3#015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id #015 AND viaChemPTR.via_term_object_type_id = 2#015 AND viaGenePTR.via_term_object_type_id = 4#015;
Date: 2024-09-17 13:05:20 Duration: 3m3s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id -- chem #015 ,viaGenePTR.via_term_id -- gene #015 ,viaChemPTR.phenotype_id -- phenotype #015 ,viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR#015 ,pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR#015 WHERE viaChemPTR.term_id = viaGenePTR.term_id #015 AND viaChemPTR.term_object_type_id = 3#015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id #015 AND viaChemPTR.via_term_object_type_id = 2#015 AND viaGenePTR.via_term_object_type_id = 4#015;
Date: 2024-09-17 10:02:15 Duration: 2m32s Bind query: yes
5 5m12s 1 5m12s 5m12s 5m12s select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where exists ( 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 viachemptr.via_term_id = dp.descendant_object_id) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 17 15 1 5m12s 5m12s [ User: pubeu - Total duration: 5m12s - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where exists ( 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 'CHEMICALS') and viaChemPTR.via_term_id = dp.descendant_object_id) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:29:31 Duration: 5m12s Database: ctddev51 User: pubeu Bind query: yes
6 2m45s 3 53s433ms 57s916ms 55s268ms select gene_id # ?, chem_id # ?, reference_id # ? from pub2.gene_chem_reference # ? # ?;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 17 15 1 53s433ms 53s433ms 16 2 1m52s 56s185ms -
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 16:09:04 Duration: 57s916ms Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 16:03:37 Duration: 54s454ms Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 15:18:03 Duration: 53s433ms Bind query: yes
7 1m6s 10 6s186ms 7s996ms 6s634ms select phenotype_id # ?, term_id # ?, reference_id # ? from pub1.phenotype_term_reference # ? where source_cd = ? and term_object_type_id = ? # ?;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 17 09 1 6s476ms 6s476ms 12 1 6s849ms 6s849ms 13 5 32s115ms 6s423ms 14 3 20s904ms 6s968ms -
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 14:54:23 Duration: 7s996ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 12:55:14 Duration: 6s849ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 13:46:01 Duration: 6s688ms Bind query: yes
8 41s790ms 3 13s883ms 13s985ms 13s930ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Sep 17 14 1 13s985ms 13s985ms 15 2 27s805ms 13s902ms [ User: pubeu - Total duration: 13s985ms - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 14:19:04 Duration: 13s985ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:15:10 Duration: 13s921ms Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:04:22 Duration: 13s883ms Bind query: yes
9 33s324ms 5 6s387ms 6s848ms 6s664ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viageneptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and upper(baseterm.nm) like ?) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Sep 17 14 2 13s38ms 6s519ms 15 3 20s286ms 6s762ms [ User: pubeu - Total duration: 20s18ms - Times executed: 3 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'PTEN') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:05:36 Duration: 6s848ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'PTEN') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:13:17 Duration: 6s782ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'MAPK1') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:03:30 Duration: 6s655ms Bind query: yes
10 30s811ms 10 2s623ms 3s841ms 3s81ms select chem_id # ?, disease_id # ?, reference_id # ? from pub1.chem_disease_reference # ? where source_cd = ? # ?;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Sep 17 09 1 3s24ms 3s24ms 12 1 3s841ms 3s841ms 13 5 15s269ms 3s53ms 14 3 8s676ms 2s892ms -
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 12:55:05 Duration: 3s841ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 13:45:52 Duration: 3s302ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 13:01:52 Duration: 3s181ms Bind query: yes
11 22s884ms 14 1s411ms 2s51ms 1s634ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub1.term t where object_type_id = ? and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct chem_id from pub1.chem_disease_reference r where source_cd = ?);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Sep 17 09 1 1s411ms 1s411ms 13 10 16s753ms 1s675ms 14 3 4s719ms 1s573ms [ User: pub1 - Total duration: 21s473ms - Times executed: 13 ]
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:44:44 Duration: 2s51ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:39:57 Duration: 1s955ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:58:42 Duration: 1s788ms Database: ctddev51 User: pub1 Bind query: yes
12 21s282ms 9 2s57ms 3s605ms 2s364ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub1.term t where object_type_id = ? and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct gene_id from pub1.gene_disease_reference r where source_cd in (...));Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Sep 17 12 1 2s301ms 2s301ms 13 5 12s169ms 2s433ms 14 3 6s811ms 2s270ms -
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 13:02:10 Duration: 3s605ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 14:54:30 Duration: 2s492ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 12:55:21 Duration: 2s301ms Bind query: yes
13 20s141ms 3 6s416ms 6s897ms 6s713ms select phenotype_id # ?, term_id # ?, reference_id # ? from pub2.phenotype_term_reference # ? where source_cd = ? and term_object_type_id = ? # ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Sep 17 15 1 6s416ms 6s416ms 16 2 13s724ms 6s862ms -
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 16:03:52 Duration: 6s897ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 16:09:19 Duration: 6s827ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 15:18:18 Duration: 6s416ms Bind query: yes
14 10s105ms 3 3s257ms 3s518ms 3s368ms select chem_id # ?, disease_id # ?, reference_id # ? from pub2.chem_disease_reference # ? where source_cd = ? # ?;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Sep 17 15 1 3s257ms 3s257ms 16 2 6s847ms 3s423ms -
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 16:09:11 Duration: 3s518ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 16:03:43 Duration: 3s329ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 15:18:10 Duration: 3s257ms Bind query: yes
15 6s951ms 3 2s82ms 2s734ms 2s317ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub2.term t where object_type_id = ? and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct gene_id from pub2.gene_disease_reference r where source_cd in (...));Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Sep 17 15 1 2s135ms 2s135ms 16 2 4s816ms 2s408ms -
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 16:04:00 Duration: 2s734ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 15:18:24 Duration: 2s135ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 16:09:26 Duration: 2s82ms Bind query: yes
16 6s312ms 1 6s312ms 6s312ms 6s312ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viageneptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Sep 17 14 1 6s312ms 6s312ms [ User: pubeu - Total duration: 6s312ms - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'ADRB2' AND tl.object_type_id = 4)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 14:18:44 Duration: 6s312ms Database: ctddev51 User: pubeu Bind query: yes
17 4s643ms 3 1s459ms 1s615ms 1s547ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub2.term t where object_type_id = ? and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct chem_id from pub2.chem_disease_reference r where source_cd = ?);Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Sep 17 15 1 1s459ms 1s459ms 16 2 3s183ms 1s591ms [ User: pub1 - Total duration: 4s643ms - Times executed: 3 ]
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 16:02:42 Duration: 1s615ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 16:08:06 Duration: 1s568ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 15:17:09 Duration: 1s459ms Database: ctddev51 User: pub1 Bind query: yes
18 4s530ms 2 1s67ms 3s463ms 2s265ms 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 #18
Day Hour Count Duration Avg duration Sep 17 05 2 4s530ms 2s265ms [ User: pubeu - Total duration: 3s463ms - 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 = '589967' 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-09-17 05:45:12 Duration: 3s463ms 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 = '1432724' 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-09-17 05:45:13 Duration: 1s67ms Bind query: yes
19 4s158ms 1 4s158ms 4s158ms 4s158ms 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 #19
Day Hour Count Duration Avg duration Sep 17 05 1 4s158ms 4s158ms -
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 = 1291235)) 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-09-17 05:48:27 Duration: 4s158ms Bind query: yes
20 3s997ms 1 3s997ms 3s997ms 3s997ms 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 #20
Day Hour Count Duration Avg duration Sep 17 05 1 3s997ms 3s997ms -
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-09-17 05:48:48 Duration: 3s997ms Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 14 22s884ms 1s411ms 2s51ms 1s634ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub1.term t where object_type_id = ? and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct chem_id from pub1.chem_disease_reference r where source_cd = ?);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 17 09 1 1s411ms 1s411ms 13 10 16s753ms 1s675ms 14 3 4s719ms 1s573ms [ User: pub1 - Total duration: 21s473ms - Times executed: 13 ]
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:44:44 Duration: 2s51ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:39:57 Duration: 1s955ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:58:42 Duration: 1s788ms Database: ctddev51 User: pub1 Bind query: yes
2 10 10m2s 52s733ms 1m30s 1m select gene_id # ?, chem_id # ?, reference_id # ? from pub1.gene_chem_reference # ? # ?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 17 09 1 52s733ms 52s733ms 12 1 57s659ms 57s659ms 13 5 5m26s 1m5s 14 3 2m45s 55s90ms [ User: pub1 - Total duration: 3m20s - Times executed: 3 ]
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:01:45 Duration: 1m30s Database: ctddev51 User: pub1 Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:36:26 Duration: 1m5s Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:45:45 Duration: 1m Bind query: yes
3 10 1m6s 6s186ms 7s996ms 6s634ms select phenotype_id # ?, term_id # ?, reference_id # ? from pub1.phenotype_term_reference # ? where source_cd = ? and term_object_type_id = ? # ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 17 09 1 6s476ms 6s476ms 12 1 6s849ms 6s849ms 13 5 32s115ms 6s423ms 14 3 20s904ms 6s968ms -
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 14:54:23 Duration: 7s996ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 12:55:14 Duration: 6s849ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 13:46:01 Duration: 6s688ms Bind query: yes
4 10 30s811ms 2s623ms 3s841ms 3s81ms select chem_id # ?, disease_id # ?, reference_id # ? from pub1.chem_disease_reference # ? where source_cd = ? # ?;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 17 09 1 3s24ms 3s24ms 12 1 3s841ms 3s841ms 13 5 15s269ms 3s53ms 14 3 8s676ms 2s892ms -
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 12:55:05 Duration: 3s841ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 13:45:52 Duration: 3s302ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 13:01:52 Duration: 3s181ms Bind query: yes
5 9 21s282ms 2s57ms 3s605ms 2s364ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub1.term t where object_type_id = ? and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct gene_id from pub1.gene_disease_reference r where source_cd in (...));Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 17 12 1 2s301ms 2s301ms 13 5 12s169ms 2s433ms 14 3 6s811ms 2s270ms -
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 13:02:10 Duration: 3s605ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 14:54:30 Duration: 2s492ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 12:55:21 Duration: 2s301ms Bind query: yes
6 7 17m40s 2m27s 2m34s 2m31s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub1.phenotype_term_reference viachemptr # ?, pub1.phenotype_term_reference viageneptr # ? where viachemptr.term_id = viageneptr.term_id # ? and viachemptr.term_object_type_id = ? # ? and viachemptr.phenotype_id = viageneptr.phenotype_id # ? and viachemptr.via_term_object_type_id = ? # ? and viageneptr.via_term_object_type_id = ? # ?;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 17 13 3 7m38s 2m32s 14 4 10m1s 2m30s -
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 14:07:53 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 13:43:54 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 13:39:26 Duration: 2m33s Bind query: yes
7 5 33s324ms 6s387ms 6s848ms 6s664ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viageneptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and upper(baseterm.nm) like ?) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 17 14 2 13s38ms 6s519ms 15 3 20s286ms 6s762ms [ User: pubeu - Total duration: 20s18ms - Times executed: 3 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'PTEN') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:05:36 Duration: 6s848ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'PTEN') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:13:17 Duration: 6s782ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'MAPK1') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:03:30 Duration: 6s655ms Bind query: yes
8 3 7m42s 2m31s 2m36s 2m34s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub2.phenotype_term_reference viachemptr # ?, pub2.phenotype_term_reference viageneptr # ? where viachemptr.term_id = viageneptr.term_id # ? and viachemptr.term_object_type_id = ? # ? and viachemptr.phenotype_id = viageneptr.phenotype_id # ? and viachemptr.via_term_object_type_id = ? # ? and viageneptr.via_term_object_type_id = ? # ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Sep 17 15 1 2m31s 2m31s 16 2 5m10s 2m35s -
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 16:06:41 Duration: 2m36s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 16:12:05 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 15:21:02 Duration: 2m31s Bind query: yes
9 3 2m45s 53s433ms 57s916ms 55s268ms select gene_id # ?, chem_id # ?, reference_id # ? from pub2.gene_chem_reference # ? # ?;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Sep 17 15 1 53s433ms 53s433ms 16 2 1m52s 56s185ms -
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 16:09:04 Duration: 57s916ms Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 16:03:37 Duration: 54s454ms Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 15:18:03 Duration: 53s433ms Bind query: yes
10 3 41s790ms 13s883ms 13s985ms 13s930ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Sep 17 14 1 13s985ms 13s985ms 15 2 27s805ms 13s902ms [ User: pubeu - Total duration: 13s985ms - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 14:19:04 Duration: 13s985ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:15:10 Duration: 13s921ms Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:04:22 Duration: 13s883ms Bind query: yes
11 3 20s141ms 6s416ms 6s897ms 6s713ms select phenotype_id # ?, term_id # ?, reference_id # ? from pub2.phenotype_term_reference # ? where source_cd = ? and term_object_type_id = ? # ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Sep 17 15 1 6s416ms 6s416ms 16 2 13s724ms 6s862ms -
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 16:03:52 Duration: 6s897ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 16:09:19 Duration: 6s827ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 15:18:18 Duration: 6s416ms Bind query: yes
12 3 10s105ms 3s257ms 3s518ms 3s368ms select chem_id # ?, disease_id # ?, reference_id # ? from pub2.chem_disease_reference # ? where source_cd = ? # ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Sep 17 15 1 3s257ms 3s257ms 16 2 6s847ms 3s423ms -
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 16:09:11 Duration: 3s518ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 16:03:43 Duration: 3s329ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 15:18:10 Duration: 3s257ms Bind query: yes
13 3 6s951ms 2s82ms 2s734ms 2s317ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub2.term t where object_type_id = ? and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct gene_id from pub2.gene_disease_reference r where source_cd in (...));Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Sep 17 15 1 2s135ms 2s135ms 16 2 4s816ms 2s408ms -
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 16:04:00 Duration: 2s734ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 15:18:24 Duration: 2s135ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 16:09:26 Duration: 2s82ms Bind query: yes
14 3 4s643ms 1s459ms 1s615ms 1s547ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub2.term t where object_type_id = ? and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct chem_id from pub2.chem_disease_reference r where source_cd = ?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Sep 17 15 1 1s459ms 1s459ms 16 2 3s183ms 1s591ms [ User: pub1 - Total duration: 4s643ms - Times executed: 3 ]
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 16:02:42 Duration: 1s615ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 16:08:06 Duration: 1s568ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 15:17:09 Duration: 1s459ms Database: ctddev51 User: pub1 Bind query: yes
15 2 5m36s 2m32s 3m3s 2m48s select distinct viachemptr.via_term_id -- chem #? ,viageneptr.via_term_id -- gene #? ,viachemptr.phenotype_id -- phenotype #? ,viachemptr.term_id -- disease #? from pub1.phenotype_term_reference viachemptr#? ,pub1.phenotype_term_reference viageneptr#? where viachemptr.term_id = viageneptr.term_id #? and viachemptr.term_object_type_id = ?#? and viachemptr.phenotype_id = viageneptr.phenotype_id #? and viachemptr.via_term_object_type_id = ?#? and viageneptr.via_term_object_type_id = ?#?;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Sep 17 10 1 2m32s 2m32s 13 1 3m3s 3m3s -
SELECT distinct viaChemPTR.via_term_id -- chem #015 ,viaGenePTR.via_term_id -- gene #015 ,viaChemPTR.phenotype_id -- phenotype #015 ,viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR#015 ,pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR#015 WHERE viaChemPTR.term_id = viaGenePTR.term_id #015 AND viaChemPTR.term_object_type_id = 3#015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id #015 AND viaChemPTR.via_term_object_type_id = 2#015 AND viaGenePTR.via_term_object_type_id = 4#015;
Date: 2024-09-17 13:05:20 Duration: 3m3s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id -- chem #015 ,viaGenePTR.via_term_id -- gene #015 ,viaChemPTR.phenotype_id -- phenotype #015 ,viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR#015 ,pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR#015 WHERE viaChemPTR.term_id = viaGenePTR.term_id #015 AND viaChemPTR.term_object_type_id = 3#015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id #015 AND viaChemPTR.via_term_object_type_id = 2#015 AND viaGenePTR.via_term_object_type_id = 4#015;
Date: 2024-09-17 10:02:15 Duration: 2m32s Bind query: yes
16 2 4s530ms 1s67ms 3s463ms 2s265ms 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 #16
Day Hour Count Duration Avg duration Sep 17 05 2 4s530ms 2s265ms [ User: pubeu - Total duration: 3s463ms - 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 = '589967' 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-09-17 05:45:12 Duration: 3s463ms 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 = '1432724' 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-09-17 05:45:13 Duration: 1s67ms Bind query: yes
17 2 2s930ms 1s277ms 1s653ms 1s465ms 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 #17
Day Hour Count Duration Avg duration Sep 17 05 2 2s930ms 1s465ms -
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-09-17 05:48:09 Duration: 1s653ms 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-09-17 05:48:07 Duration: 1s277ms Bind query: yes
18 1 5m12s 5m12s 5m12s 5m12s select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where exists ( 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 viachemptr.via_term_id = dp.descendant_object_id) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Sep 17 15 1 5m12s 5m12s [ User: pubeu - Total duration: 5m12s - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where exists ( 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 'CHEMICALS') and viaChemPTR.via_term_id = dp.descendant_object_id) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:29:31 Duration: 5m12s Database: ctddev51 User: pubeu Bind query: yes
19 1 6s312ms 6s312ms 6s312ms 6s312ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viageneptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Sep 17 14 1 6s312ms 6s312ms [ User: pubeu - Total duration: 6s312ms - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'ADRB2' AND tl.object_type_id = 4)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 14:18:44 Duration: 6s312ms Database: ctddev51 User: pubeu Bind query: yes
20 1 4s158ms 4s158ms 4s158ms 4s158ms 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 #20
Day Hour Count Duration Avg duration Sep 17 05 1 4s158ms 4s158ms -
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 = 1291235)) 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-09-17 05:48:27 Duration: 4s158ms Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 5m12s 5m12s 5m12s 1 5m12s select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where exists ( 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 viachemptr.via_term_id = dp.descendant_object_id) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 17 15 1 5m12s 5m12s [ User: pubeu - Total duration: 5m12s - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where exists ( 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 'CHEMICALS') and viaChemPTR.via_term_id = dp.descendant_object_id) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:29:31 Duration: 5m12s Database: ctddev51 User: pubeu Bind query: yes
2 2m32s 3m3s 2m48s 2 5m36s select distinct viachemptr.via_term_id -- chem #? ,viageneptr.via_term_id -- gene #? ,viachemptr.phenotype_id -- phenotype #? ,viachemptr.term_id -- disease #? from pub1.phenotype_term_reference viachemptr#? ,pub1.phenotype_term_reference viageneptr#? where viachemptr.term_id = viageneptr.term_id #? and viachemptr.term_object_type_id = ?#? and viachemptr.phenotype_id = viageneptr.phenotype_id #? and viachemptr.via_term_object_type_id = ?#? and viageneptr.via_term_object_type_id = ?#?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 17 10 1 2m32s 2m32s 13 1 3m3s 3m3s -
SELECT distinct viaChemPTR.via_term_id -- chem #015 ,viaGenePTR.via_term_id -- gene #015 ,viaChemPTR.phenotype_id -- phenotype #015 ,viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR#015 ,pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR#015 WHERE viaChemPTR.term_id = viaGenePTR.term_id #015 AND viaChemPTR.term_object_type_id = 3#015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id #015 AND viaChemPTR.via_term_object_type_id = 2#015 AND viaGenePTR.via_term_object_type_id = 4#015;
Date: 2024-09-17 13:05:20 Duration: 3m3s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id -- chem #015 ,viaGenePTR.via_term_id -- gene #015 ,viaChemPTR.phenotype_id -- phenotype #015 ,viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR#015 ,pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR#015 WHERE viaChemPTR.term_id = viaGenePTR.term_id #015 AND viaChemPTR.term_object_type_id = 3#015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id #015 AND viaChemPTR.via_term_object_type_id = 2#015 AND viaGenePTR.via_term_object_type_id = 4#015;
Date: 2024-09-17 10:02:15 Duration: 2m32s Bind query: yes
3 2m31s 2m36s 2m34s 3 7m42s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub2.phenotype_term_reference viachemptr # ?, pub2.phenotype_term_reference viageneptr # ? where viachemptr.term_id = viageneptr.term_id # ? and viachemptr.term_object_type_id = ? # ? and viachemptr.phenotype_id = viageneptr.phenotype_id # ? and viachemptr.via_term_object_type_id = ? # ? and viageneptr.via_term_object_type_id = ? # ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 17 15 1 2m31s 2m31s 16 2 5m10s 2m35s -
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 16:06:41 Duration: 2m36s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 16:12:05 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub2.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub2.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 15:21:02 Duration: 2m31s Bind query: yes
4 2m27s 2m34s 2m31s 7 17m40s select distinct viachemptr.via_term_id, viageneptr.via_term_id, viachemptr.phenotype_id, viachemptr.term_id from pub1.phenotype_term_reference viachemptr # ?, pub1.phenotype_term_reference viageneptr # ? where viachemptr.term_id = viageneptr.term_id # ? and viachemptr.term_object_type_id = ? # ? and viachemptr.phenotype_id = viageneptr.phenotype_id # ? and viachemptr.via_term_object_type_id = ? # ? and viageneptr.via_term_object_type_id = ? # ?;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 17 13 3 7m38s 2m32s 14 4 10m1s 2m30s -
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 14:07:53 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 13:43:54 Duration: 2m34s Bind query: yes
-
SELECT distinct viaChemPTR.via_term_id, -- chem #015 viaGenePTR.via_term_id, -- gene #015 viaChemPTR.phenotype_id, -- phenotype #015 viaChemPTR.term_id -- disease #015 FROM pub1.PHENOTYPE_TERM_REFERENCE viaChemPTR # 015, pub1.PHENOTYPE_TERM_REFERENCE viaGenePTR # 015 WHERE viaChemPTR.term_id = viaGenePTR.term_id # 015 AND viaChemPTR.term_object_type_id = 3 # 015 AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id # 015 AND viaChemPTR.via_term_object_type_id = 2 # 015 AND viaGenePTR.via_term_object_type_id = 4 # 015;
Date: 2024-09-17 13:39:26 Duration: 2m33s Bind query: yes
5 52s733ms 1m30s 1m 10 10m2s select gene_id # ?, chem_id # ?, reference_id # ? from pub1.gene_chem_reference # ? # ?;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 17 09 1 52s733ms 52s733ms 12 1 57s659ms 57s659ms 13 5 5m26s 1m5s 14 3 2m45s 55s90ms [ User: pub1 - Total duration: 3m20s - Times executed: 3 ]
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:01:45 Duration: 1m30s Database: ctddev51 User: pub1 Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:36:26 Duration: 1m5s Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub1.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 13:45:45 Duration: 1m Bind query: yes
6 53s433ms 57s916ms 55s268ms 3 2m45s select gene_id # ?, chem_id # ?, reference_id # ? from pub2.gene_chem_reference # ? # ?;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 17 15 1 53s433ms 53s433ms 16 2 1m52s 56s185ms -
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 16:09:04 Duration: 57s916ms Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 16:03:37 Duration: 54s454ms Bind query: yes
-
SELECT gene_id # 015, chem_id # 015, reference_id # 015 FROM pub2.GENE_CHEM_REFERENCE # 015 # 015;
Date: 2024-09-17 15:18:03 Duration: 53s433ms Bind query: yes
7 13s883ms 13s985ms 13s930ms 3 41s790ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 17 14 1 13s985ms 13s985ms 15 2 27s805ms 13s902ms [ User: pubeu - Total duration: 13s985ms - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 14:19:04 Duration: 13s985ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:15:10 Duration: 13s921ms Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:04:22 Duration: 13s883ms Bind query: yes
8 6s416ms 6s897ms 6s713ms 3 20s141ms select phenotype_id # ?, term_id # ?, reference_id # ? from pub2.phenotype_term_reference # ? where source_cd = ? and term_object_type_id = ? # ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Sep 17 15 1 6s416ms 6s416ms 16 2 13s724ms 6s862ms -
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 16:03:52 Duration: 6s897ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 16:09:19 Duration: 6s827ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub2.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 15:18:18 Duration: 6s416ms Bind query: yes
9 6s387ms 6s848ms 6s664ms 5 33s324ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viageneptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and upper(baseterm.nm) like ?) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Sep 17 14 2 13s38ms 6s519ms 15 3 20s286ms 6s762ms [ User: pubeu - Total duration: 20s18ms - Times executed: 3 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'PTEN') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:05:36 Duration: 6s848ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'PTEN') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:13:17 Duration: 6s782ms Database: ctddev51 User: pubeu Bind query: yes
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and upper(baseTerm.nm) LIKE 'MAPK1') and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 15:03:30 Duration: 6s655ms Bind query: yes
10 6s186ms 7s996ms 6s634ms 10 1m6s select phenotype_id # ?, term_id # ?, reference_id # ? from pub1.phenotype_term_reference # ? where source_cd = ? and term_object_type_id = ? # ?;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Sep 17 09 1 6s476ms 6s476ms 12 1 6s849ms 6s849ms 13 5 32s115ms 6s423ms 14 3 20s904ms 6s968ms -
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 14:54:23 Duration: 7s996ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 12:55:14 Duration: 6s849ms Bind query: yes
-
SELECT phenotype_id # 015, term_id # 015, reference_id # 015 FROM pub1.PHENOTYPE_TERM_REFERENCE # 015 where source_cd = 'C' and term_object_type_id = 2 # 015;
Date: 2024-09-17 13:46:01 Duration: 6s688ms Bind query: yes
11 6s312ms 6s312ms 6s312ms 1 6s312ms select distinct phenotypeterm.nm as gonm, phenotypeterm.nm_html as gonmhtml, phenotypeterm.acc_txt as goacc, diseaseterm.nm as diseasenm, diseaseterm.nm_html as diseasenmhtml, diseaseterm.acc_txt as diseaseacc, diseaseterm.acc_db_cd as diseaseaccdbcd, chemterm.nm as chemnm, chemterm.nm_html as chemnmhtml, chemterm.acc_txt as chemacc, geneterm.nm as genesymbol, geneterm.nm_html as genesymbolhtml, geneterm.acc_txt as geneacc, count(*) over () fullrowcount from phenotype_term_reference viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viageneptr.via_term_id in ( select baseterm.id from term baseterm where baseterm.object_type_id = ? and baseterm.id in ( select term_id from term_label tl where upper(tl.nm) = ? and tl.object_type_id = ?)) and viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_object_type_id = ? and viachemptr.term_id = diseaseterm.id and viachemptr.via_term_object_type_id = ? and viachemptr.via_term_id = chemterm.id and viachemptr.term_id = viageneptr.term_id and viachemptr.phenotype_id = viageneptr.phenotype_id and viageneptr.via_term_object_type_id = ? and viageneptr.via_term_id = geneterm.id and exists ( select ? from gene_chem_reference where gene_id = geneterm.id and chem_id = chemterm.id) group by phenotypeterm.nm, phenotypeterm.nm_html, phenotypeterm.acc_txt, diseaseterm.nm, diseaseterm.nm_html, diseaseterm.acc_txt, diseaseterm.acc_db_cd, chemterm.nm, chemterm.nm_html, chemterm.acc_txt, geneterm.nm, geneterm.nm_html, geneterm.acc_txt order by chemterm.nm limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Sep 17 14 1 6s312ms 6s312ms [ User: pubeu - Total duration: 6s312ms - Times executed: 1 ]
-
select distinct phenotypeTerm.nm AS goNm, phenotypeTerm.nm_html AS goNmHtml, phenotypeTerm.acc_txt AS goAcc, diseaseTerm.nm AS diseaseNm, diseaseTerm.nm_html AS diseaseNmHtml, diseaseTerm.acc_txt AS diseaseAcc, diseaseTerm.acc_db_cd AS diseaseAccDbCd, chemTerm.nm AS chemNm, chemTerm.nm_html AS chemNmHtml, chemTerm.acc_txt AS chemAcc, geneTerm.nm AS geneSymbol, geneTerm.nm_html AS geneSymbolHtml, geneTerm.acc_txt AS geneAcc, COUNT(*) OVER () fullRowCount from PHENOTYPE_TERM_REFERENCE viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 2 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'QUERCETIN' AND tl.object_type_id = 2)) and viaGenePTR.via_term_id IN ( select baseTerm.id from term baseTerm WHERE baseTerm.object_type_id = 4 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'ADRB2' AND tl.object_type_id = 4)) and viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_object_type_id = 3 and viaChemPTR.term_id = diseaseTerm.id and viaChemPTR.via_term_object_type_id = 2 and viaChemPTR.via_term_id = chemTerm.id and viaChemPTR.term_id = viaGenePTR.term_id and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id and viaGenePTR.via_term_object_type_id = 4 and viaGenePTR.via_term_id = geneTerm.id and exists ( select 1 from gene_chem_reference where gene_id = geneTerm.id and chem_id = chemTerm.id) GROUP BY phenotypeTerm.nm, phenotypeTerm.nm_html, phenotypeTerm.acc_txt, diseaseTerm.nm, diseaseTerm.nm_html, diseaseTerm.acc_txt, diseaseTerm.acc_db_cd, chemTerm.nm, chemTerm.nm_html, chemTerm.acc_txt, geneTerm.nm, geneTerm.nm_html, geneTerm.acc_txt order by chemTerm.nm LIMIT 50;
Date: 2024-09-17 14:18:44 Duration: 6s312ms Database: ctddev51 User: pubeu Bind query: yes
12 4s158ms 4s158ms 4s158ms 1 4s158ms 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 #12
Day Hour Count Duration Avg duration Sep 17 05 1 4s158ms 4s158ms -
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 = 1291235)) 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-09-17 05:48:27 Duration: 4s158ms Bind query: yes
13 3s997ms 3s997ms 3s997ms 1 3s997ms 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 #13
Day Hour Count Duration Avg duration Sep 17 05 1 3s997ms 3s997ms -
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-09-17 05:48:48 Duration: 3s997ms Bind query: yes
14 3s257ms 3s518ms 3s368ms 3 10s105ms select chem_id # ?, disease_id # ?, reference_id # ? from pub2.chem_disease_reference # ? where source_cd = ? # ?;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Sep 17 15 1 3s257ms 3s257ms 16 2 6s847ms 3s423ms -
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 16:09:11 Duration: 3s518ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 16:03:43 Duration: 3s329ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub2.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 15:18:10 Duration: 3s257ms Bind query: yes
15 2s623ms 3s841ms 3s81ms 10 30s811ms select chem_id # ?, disease_id # ?, reference_id # ? from pub1.chem_disease_reference # ? where source_cd = ? # ?;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Sep 17 09 1 3s24ms 3s24ms 12 1 3s841ms 3s841ms 13 5 15s269ms 3s53ms 14 3 8s676ms 2s892ms -
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 12:55:05 Duration: 3s841ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 13:45:52 Duration: 3s302ms Bind query: yes
-
SELECT chem_id # 015, disease_id # 015, reference_id # 015 FROM pub1.CHEM_DISEASE_REFERENCE # 015 where source_cd = 'C' # 015;
Date: 2024-09-17 13:01:52 Duration: 3s181ms Bind query: yes
16 2s57ms 3s605ms 2s364ms 9 21s282ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub1.term t where object_type_id = ? and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct gene_id from pub1.gene_disease_reference r where source_cd in (...));Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Sep 17 12 1 2s301ms 2s301ms 13 5 12s169ms 2s433ms 14 3 6s811ms 2s270ms -
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 13:02:10 Duration: 3s605ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 14:54:30 Duration: 2s492ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub1.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 12:55:21 Duration: 2s301ms Bind query: yes
17 2s82ms 2s734ms 2s317ms 3 6s951ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub2.term t where object_type_id = ? and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct gene_id from pub2.gene_disease_reference r where source_cd in (...));Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Sep 17 15 1 2s135ms 2s135ms 16 2 4s816ms 2s408ms -
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 16:04:00 Duration: 2s734ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 15:18:24 Duration: 2s135ms Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 4 and t.id in ( select distinct gene_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct gene_id from pub2.gene_disease_reference r where source_cd in ('C', 'O'));
Date: 2024-09-17 16:09:26 Duration: 2s82ms Bind query: yes
18 1s67ms 3s463ms 2s265ms 2 4s530ms 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 #18
Day Hour Count Duration Avg duration Sep 17 05 2 4s530ms 2s265ms [ User: pubeu - Total duration: 3s463ms - 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 = '589967' 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-09-17 05:45:12 Duration: 3s463ms 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 = '1432724' 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-09-17 05:45:13 Duration: 1s67ms Bind query: yes
19 1s411ms 2s51ms 1s634ms 14 22s884ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub1.term t where object_type_id = ? and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub1.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct chem_id from pub1.chem_disease_reference r where source_cd = ?);Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Sep 17 09 1 1s411ms 1s411ms 13 10 16s753ms 1s675ms 14 3 4s719ms 1s573ms [ User: pub1 - Total duration: 21s473ms - Times executed: 13 ]
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:44:44 Duration: 2s51ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:39:57 Duration: 1s955ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub1.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub1.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub1.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 13:58:42 Duration: 1s788ms Database: ctddev51 User: pub1 Bind query: yes
20 1s459ms 1s615ms 1s547ms 3 4s643ms select id, object_type_id, acc_txt, t.acc_db_cd, nm, nm_sort, secondary_nm, description, note from pub2.term t where object_type_id = ? and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id not in ( select gene_chem_reference_id from pub2.gene_chem_reference_axn where action_degree_type_nm = ?) union select distinct chem_id from pub2.chem_disease_reference r where source_cd = ?);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Sep 17 15 1 1s459ms 1s459ms 16 2 3s183ms 1s591ms [ User: pub1 - Total duration: 4s643ms - Times executed: 3 ]
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 16:02:42 Duration: 1s615ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 16:08:06 Duration: 1s568ms Database: ctddev51 User: pub1 Bind query: yes
-
select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub2.term t where object_type_id = 2 and t.id in ( select distinct chem_id from pub2.gene_chem_reference r where r.id NOT in ( SELECT gene_chem_reference_id FROM pub2.GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect') union select distinct chem_id from pub2.chem_disease_reference r where source_cd = 'C');
Date: 2024-09-17 15:17:09 Duration: 1s459ms Database: ctddev51 User: pub1 Bind query: yes
Time consuming prepare
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
Time consuming bind
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 3s71ms 3 1s21ms 1s28ms 1s23ms SELECT distinct viaChemPTR.via_term_id -- chem #015;Times Reported Time consuming bind #1
Day Hour Count Duration Avg duration Sep 17 16 1 1s21ms 1s21ms Sep 17 10 1 1s21ms 1s21ms 13 1 1s28ms 1s28ms -
SELECT distinct viaChemPTR.via_term_id -- chem #015;
Date: 2024-09-17 13:02:16 Duration: 1s28ms Database: postgres
-
SELECT distinct viaChemPTR.via_term_id -- chem #015;
Date: 2024-09-16 16:05:17 Duration: 1s21ms Database: postgres
-
SELECT distinct viaChemPTR.via_term_id -- chem #015;
Date: 2024-09-17 09:59:42 Duration: 1s21ms Database: postgres
-
Events
Log levels
Key values
- 7,774 Log entries
Events distribution
Key values
- 0 PANIC entries
- 0 FATAL entries
- 26 ERROR entries
- 0 WARNING entries
Most Frequent Errors/Events
Key values
- 20 Max number of times the same event was reported
- 26 Total events found
Rank Times reported Error 1 20 LOG: could not receive data from client: Connection reset by peer
Times Reported Most Frequent Error / Event #1
Day Hour Count Sep 17 10 1 12 1 13 10 14 4 15 2 16 2 2 3 LOG: could not receive data from client: Connection timed out
Times Reported Most Frequent Error / Event #2
Day Hour Count Sep 17 11 2 18 1 3 2 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #3
Day Hour Count Sep 17 15 2 - ERROR: syntax error at or near "source_cd" at character 88
- ERROR: syntax error at or near "source_cd" at character 93
Statement: select gene_id ,disease_id ,reference_id from gene_disease_reference whee source_cd = 'O' limit 100
Date: 2024-09-17 15:47:55
Statement: select gene_id ,disease_id ,reference_id from pub2.gene_disease_reference whee source_cd = 'O' limit 100
Date: 2024-09-17 15:48:05
4 1 ERROR: relation "..." does not exist
Times Reported Most Frequent Error / Event #4
Day Hour Count Sep 17 13 1 - ERROR: relation "pub1.chem_reference" does not exist at character 15
Statement: select * from pub1.chem_reference where id = 1382365
Date: 2024-09-17 13:20:03 Database: ctddev51 Application: pgAdmin 4 - CONN:7433093 User: pub1 Remote: