pgBadger-
Global information
- Generated on Thu Aug 22 04:10:03 2024
- Log file: /project/archive/log/postgres/dbdev51/postgresql.log-20240821
- Parsed 17,028 log entries in 2s
- Log start from 2024-08-21 00:00:29 to 2024-08-21 23:58:24
-
Overview
Global Stats
- 36 Number of unique normalized queries
- 80 Number of queries
- 3d33m45s Total query duration
- 2024-08-21 05:45:13 First query
- 2024-08-21 23:23:49 Last query
- 2 queries/s at 2024-08-21 10:43:18 Query peak
- 3d33m45s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 3d33m45s Execute total duration
- 19 Number of events
- 8 Number of unique normalized events
- 6 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
- 17 Number temporary file
- 1.00 GiB Max size of temporary file
- 965.29 MiB Average size of temporary file
- 1,965 Total number of sessions
- 43 sessions at 2024-08-21 22:27:32 Session peak
- 43d5h15m1s Total duration of sessions
- 31m40s Average duration of sessions
- 0 Average queries per session
- 2m12s Average queries duration per session
- 29m27s Average idle time per session
- 1,969 Total number of connections
- 9 connections/s at 2024-08-21 10:43:16 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 2 queries/s Query Peak
- 2024-08-21 10:43:18 Date
SELECT Traffic
Key values
- 2 queries/s Query Peak
- 2024-08-21 10:43:18 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2024-08-21 17:24:47 Date
Queries duration
Key values
- 3d33m45s 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) Aug 21 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 34 0ms 4s194ms 2s353ms 18s355ms 20s739ms 36s417ms 06 0 0ms 0ms 0ms 0ms 0ms 0ms 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 0 0ms 0ms 0ms 0ms 0ms 0ms 09 0 0ms 0ms 0ms 0ms 0ms 0ms 10 16 0ms 9s606ms 3s503ms 11s991ms 15s20ms 16s862ms 11 8 0ms 5s933ms 3s155ms 5s933ms 7s370ms 11s942ms 12 0 0ms 0ms 0ms 0ms 0ms 0ms 13 0 0ms 0ms 0ms 0ms 0ms 0ms 14 3 0ms 3s70ms 2s388ms 0ms 2s65ms 3s70ms 15 1 0ms 42s719ms 42s719ms 0ms 0ms 42s719ms 16 6 0ms 9m9s 1m46s 5s779ms 49s232ms 9m9s 17 4 0ms 3s626ms 2s335ms 0ms 4s597ms 4s744ms 18 6 0ms 3s557ms 2s300ms 0ms 4s576ms 9s225ms 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 1 0ms 1d11h38m15s 1d11h38m15s 0ms 0ms 1d11h38m15s 23 1 0ms 1d12h40m56s 1d12h40m56s 0ms 0ms 1d12h40m56s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 21 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 32 0 2s359ms 0ms 18s355ms 36s417ms 06 0 0 0ms 0ms 0ms 0ms 07 0 0 0ms 0ms 0ms 0ms 08 0 0 0ms 0ms 0ms 0ms 09 0 0 0ms 0ms 0ms 0ms 10 16 0 3s503ms 0ms 11s991ms 16s862ms 11 8 0 3s155ms 0ms 5s933ms 11s942ms 12 0 0 0ms 0ms 0ms 0ms 13 0 0 0ms 0ms 0ms 0ms 14 1 0 2s65ms 0ms 0ms 2s65ms 15 1 0 42s719ms 0ms 0ms 42s719ms 16 6 0 1m46s 0ms 5s779ms 9m9s 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 1 0 1d11h38m15s 0ms 0ms 1d11h38m15s 23 1 0 1d12h40m56s 0ms 0ms 1d12h40m56s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Aug 21 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 Aug 21 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 34 34.00 0.00% 06 0 0 0.00 0.00% 07 0 0 0.00 0.00% 08 0 0 0.00 0.00% 09 0 0 0.00 0.00% 10 0 16 16.00 0.00% 11 0 8 8.00 0.00% 12 0 0 0.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 0 0.00 0.00% 17 0 4 4.00 0.00% 18 0 6 6.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 Aug 21 00 79 0.02/s 01 80 0.02/s 02 77 0.02/s 03 80 0.02/s 04 79 0.02/s 05 83 0.02/s 06 77 0.02/s 07 72 0.02/s 08 78 0.02/s 09 77 0.02/s 10 116 0.03/s 11 92 0.03/s 12 76 0.02/s 13 78 0.02/s 14 81 0.02/s 15 82 0.02/s 16 80 0.02/s 17 87 0.02/s 18 104 0.03/s 19 79 0.02/s 20 80 0.02/s 21 80 0.02/s 22 79 0.02/s 23 73 0.02/s Day Hour Count Average Duration Average idle time Aug 21 00 79 30m39s 30m39s 01 80 30m39s 30m39s 02 77 30m40s 30m40s 03 80 30m40s 30m40s 04 79 30m39s 30m39s 05 83 28m36s 28m35s 06 77 30m37s 30m37s 07 72 30m41s 30m41s 08 78 30m40s 30m40s 09 77 30m39s 30m39s 10 116 22m29s 22m29s 11 92 24m48s 24m47s 12 76 31m11s 31m11s 13 78 31m56s 31m56s 14 79 30m39s 30m39s 15 80 30m39s 30m38s 16 77 30m42s 30m34s 17 88 28m48s 28m47s 18 104 23m57s 23m57s 19 79 30m39s 30m39s 20 80 30m40s 30m40s 21 80 30m39s 30m39s 22 80 57m26s 30m43s 23 74 1h34s 30m50s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2024-08-21 10:43:16 Date
Connections per database
Key values
- ctddev51 Main Database
- 1,969 connections Total
Connections per user
Key values
- editeu Main User
- 1,969 connections Total
-
Sessions
Simultaneous sessions
Key values
- 43 sessions Session Peak
- 2024-08-21 22:27:32 Date
Histogram of session times
Key values
- 1,844 1800000-3600000ms duration
Sessions per database
Key values
- ctddev51 Main Database
- 1,965 sessions Total
Sessions per user
Key values
- editeu Main User
- 1,965 sessions Total
Sessions per host
Key values
- 10.12.5.56 Main Host
- 1,965 sessions Total
Sessions per application
Key values
- unknown Main Application
- 1,965 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 76 buffers Checkpoint Peak
- 2024-08-21 06:10:11 Date
- 7.713 seconds Highest write time
- 0.001 seconds Sync time
Checkpoints Wal files
Key values
- 0 files Wal files usage Peak
- 2024-08-21 06:10:11 Date
Checkpoints distance
Key values
- 0.70 Mo Distance Peak
- 2024-08-21 06:10:11 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Aug 21 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 76 7.713s 0.001s 7.73s 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 0 0s 0s 0s 14 0 0s 0s 0s 15 0 0s 0s 0s 16 0 0s 0s 0s 17 0 0s 0s 0s 18 0 0s 0s 0s 19 0 0s 0s 0s 20 0 0s 0s 0s 21 0 0s 0s 0s 22 0 0s 0s 0s 23 0 0s 0s 0s Day Hour Added Removed Recycled Synced files Longest sync Average sync Aug 21 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 20 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 0 0s 0s 14 0 0 0 0 0s 0s 15 0 0 0 0 0s 0s 16 0 0 0 0 0s 0s 17 0 0 0 0 0s 0s 18 0 0 0 0 0s 0s 19 0 0 0 0 0s 0s 20 0 0 0 0 0s 0s 21 0 0 0 0 0s 0s 22 0 0 0 0 0s 0s 23 0 0 0 0 0s 0s Day Hour Count Avg time (sec) Aug 21 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 Aug 21 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 356.00 kB 541.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 0.00 kB 0.00 kB 14 0.00 kB 0.00 kB 15 0.00 kB 0.00 kB 16 0.00 kB 0.00 kB 17 0.00 kB 0.00 kB 18 0.00 kB 0.00 kB 19 0.00 kB 0.00 kB 20 0.00 kB 0.00 kB 21 0.00 kB 0.00 kB 22 0.00 kB 0.00 kB 23 0.00 kB 0.00 kB -
Temporary Files
Size of temporary files
Key values
- 12.00 GiB Temp Files size Peak
- 2024-08-21 16:45:13 Date
Number of temporary files
Key values
- 12 per second Temp Files Peak
- 2024-08-21 16:45:13 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Aug 21 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 17 16.03 GiB 965.29 MiB 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 Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 17 16.03 GiB 25.95 MiB 1.00 GiB 965.29 MiB select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id) as tetramercount;-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 16:45:14 Duration: 9m9s
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 16:45:13 Duration: 0ms Database: ctddev51 User: pub2 Application: pgAdmin 4 - CONN:5020715
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 - Database: ctddev51 - User: pub2 - Application: pgAdmin 4 - CONN:5020715 ]
2 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
3 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
4 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
5 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
6 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
7 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
8 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
9 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
10 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
11 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
12 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:13 ]
13 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:14 ]
14 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:14 ]
15 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:14 ]
16 1.00 GiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:14 ]
17 25.95 MiB SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:14 ]
-
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 Aug 21 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
- 66 Total read queries
- 14 Total write queries
Queries by database
Key values
- unknown Main database
- 45 Requests
- 3d31m22s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 70 Requests
User Request type Count Duration editeu Total 24 1m21s select 24 1m21s pub2 Total 2 37s128ms select 2 37s128ms pubeu Total 17 59s495ms cte 7 24s358ms select 10 35s137ms unknown Total 70 3d42m28s cte 11 36s34ms select 59 3d41m52s Duration by user
Key values
- 3d42m28s (unknown) Main time consuming user
User Request type Count Duration editeu Total 24 1m21s select 24 1m21s pub2 Total 2 37s128ms select 2 37s128ms pubeu Total 17 59s495ms cte 7 24s358ms select 10 35s137ms unknown Total 70 3d42m28s cte 11 36s34ms select 59 3d41m52s Queries by host
Key values
- unknown Main host
- 113 Requests
- 3d45m26s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 79 Requests
- 3d33m15s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2024-08-21 15:36:55 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 74 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 1d12h40m56s select count(*) from ( select chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm from pub2.term chemTerm, pub2.term geneTerm, pub2.term phenotypeTerm, pub2.term diseaseTerm WHERE (chemTerm.id, geneTerm.id, phenotypeTerm.id, diseaseTerm.id) IN ( SELECT DISTINCT gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id FROM pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.GENE_GO_ANNOT g2g -- ,pub2.term chemTerm -- WHERE chemTerm.nm = 'Zinc' -- AND object_type_id = 2 -- chemical -- AND chemTerm.id = gcr.chem_id -- AND gcr.chem_id = cdr.chem_id where gcr.chem_id = cdr.chem_id AND cdr.source_cd = 'C' AND gcr.chem_id = ptr.term_id AND ptr.source_cd IN ('C') AND gcr.gene_id = gdr.gene_id AND gdr.source_cd IN ('C', 'O') AND gcr.gene_id = g2g.gene_id AND g2g.is_not IS FALSE AND cdr.disease_id = gdr.disease_id AND ptr.phenotype_id = g2g.go_term_id)) as test;[ Date: 2024-08-21 23:23:49 ]
2 1d11h38m15s select count(*) from ( SELECT DISTINCT gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id FROM pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.GENE_GO_ANNOT g2g -- ,pub2.term chemTerm -- WHERE chemTerm.nm = 'Zinc' -- AND object_type_id = 2 -- chemical -- AND chemTerm.id = gcr.chem_id -- AND gcr.chem_id = cdr.chem_id where gcr.chem_id = cdr.chem_id AND cdr.source_cd = 'C' AND gcr.chem_id = ptr.term_id AND ptr.source_cd IN ('C') AND gcr.gene_id = gdr.gene_id AND gdr.source_cd IN ('C', 'O') AND gcr.gene_id = g2g.gene_id AND g2g.is_not IS FALSE AND cdr.disease_id = gdr.disease_id AND ptr.phenotype_id = g2g.go_term_id) as test;[ Date: 2024-08-21 22:27:32 ]
3 9m9s SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;[ Date: 2024-08-21 16:45:14 ]
4 49s232ms select count(*) from ( SELECT distinct viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) as ptrTestCount;[ Date: 2024-08-21 16:05:06 ]
5 42s719ms select count(*) from ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) as ptrTestCount;[ Date: 2024-08-21 15:55:34 ]
6 30s219ms SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops', 'NEURON') AND phenotypeTerm.object_type_id = 5) AS tetramerCount;[ Date: 2024-08-21 16:14:03 - Database: ctddev51 - User: pub2 - Application: pgAdmin 4 - CONN:4504232 ]
7 9s606ms select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;[ Date: 2024-08-21 10:41:45 - Database: ctddev51 - User: editeu - Bind query: yes ]
8 8s913ms select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;[ Date: 2024-08-21 10:58:32 - Database: ctddev51 - User: editeu - Bind query: yes ]
9 5s933ms select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;[ Date: 2024-08-21 11:09:01 - Database: ctddev51 - User: editeu - Bind query: yes ]
10 5s779ms -- My re-write of EDB re-write of Problematic Query 1 Rewrite suggestion by EDB (EDB version below) select count(*) from ( WITH viaChemPTR AS ( SELECT phenotype_id, viaChemPTR.term_id, viaChemPTR.via_term_id FROM PHENOTYPE_TERM_REFERENCE viaChemPTR -- ,TERM phenotypeTerm -- ,TERM diseaseTerm -- ,TERM chemTerm -- WHERE viaChemPTR.phenotype_id = phenotypeTerm.id -- AND viaChemPTR.term_id = diseaseTerm.id -- AND viaChemPTR.via_term_id = chemTerm.id WHERE viaChemPTR.term_object_type_id = 3 AND viaChemPTR.via_term_object_type_id = 2 AND exists ( SELECT t.id FROM term t WHERE t.nm_fts @@ to_tsquery('common.english_nostops', 'NEURON') AND t.object_type_id = 5 AND viaChemPTR.phenotype_id = t.id -- AND phenotypeTerm.id = t.id )) 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 viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_id = diseaseTerm.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 = 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.id, diseaseTerm.id, chemTerm.id, geneTerm.id ORDER BY chemTerm.nm) as test;[ Date: 2024-08-21 16:19:20 ]
11 5s386ms select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;[ Date: 2024-08-21 10:43:22 - Database: ctddev51 - User: editeu - Bind query: yes ]
12 5s105ms select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;[ Date: 2024-08-21 11:01:11 - Database: ctddev51 - User: editeu - Bind query: yes ]
13 5s82ms select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;[ Date: 2024-08-21 10:50:11 - Database: ctddev51 - User: editeu - Bind query: yes ]
14 4s329ms select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 1 and t.id = l.TERM_ID;[ Date: 2024-08-21 10:41:40 - Database: ctddev51 - User: editeu - Bind query: yes ]
15 4s194ms 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-08-21 05:48:49 - Bind query: yes ]
16 4s97ms select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 1 and t.id = l.TERM_ID;[ Date: 2024-08-21 11:08:59 - Database: ctddev51 - User: editeu - Bind query: yes ]
17 3s929ms SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));[ Date: 2024-08-21 05:47:19 - Database: ctddev51 - User: pubeu - Bind query: yes ]
18 3s908ms SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;[ Date: 2024-08-21 05:47:30 - Bind query: yes ]
19 3s901ms SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;[ Date: 2024-08-21 05:47:23 - Bind query: yes ]
20 3s626ms 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-08-21 17:24:47 - Database: ctddev51 - User: pubeu - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 1d12h40m56s 1 1d12h40m56s 1d12h40m56s 1d12h40m56s select count(*) from ( select chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from pub2.term chemterm, pub2.term geneterm, pub2.term phenotypeterm, pub2.term diseaseterm where (chemterm.id, geneterm.id, phenotypeterm.id, diseaseterm.id) in ( select distinct gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.gene_go_annot g2g where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.chem_id = ptr.term_id and ptr.source_cd in (...) and gcr.gene_id = gdr.gene_id and gdr.source_cd in (...) and gcr.gene_id = g2g.gene_id and g2g.is_not is false and cdr.disease_id = gdr.disease_id and ptr.phenotype_id = g2g.go_term_id)) as test;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 21 23 1 1d12h40m56s 1d12h40m56s -
select count(*) from ( select chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm from pub2.term chemTerm, pub2.term geneTerm, pub2.term phenotypeTerm, pub2.term diseaseTerm WHERE (chemTerm.id, geneTerm.id, phenotypeTerm.id, diseaseTerm.id) IN ( SELECT DISTINCT gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id FROM pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.GENE_GO_ANNOT g2g -- ,pub2.term chemTerm -- WHERE chemTerm.nm = 'Zinc' -- AND object_type_id = 2 -- chemical -- AND chemTerm.id = gcr.chem_id -- AND gcr.chem_id = cdr.chem_id where gcr.chem_id = cdr.chem_id AND cdr.source_cd = 'C' AND gcr.chem_id = ptr.term_id AND ptr.source_cd IN ('C') AND gcr.gene_id = gdr.gene_id AND gdr.source_cd IN ('C', 'O') AND gcr.gene_id = g2g.gene_id AND g2g.is_not IS FALSE AND cdr.disease_id = gdr.disease_id AND ptr.phenotype_id = g2g.go_term_id)) as test;
Date: 2024-08-21 23:23:49 Duration: 1d12h40m56s
2 1d11h38m15s 1 1d11h38m15s 1d11h38m15s 1d11h38m15s select count(*) from ( select distinct gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.gene_go_annot g2g where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.chem_id = ptr.term_id and ptr.source_cd in (...) and gcr.gene_id = gdr.gene_id and gdr.source_cd in (...) and gcr.gene_id = g2g.gene_id and g2g.is_not is false and cdr.disease_id = gdr.disease_id and ptr.phenotype_id = g2g.go_term_id) as test;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Aug 21 22 1 1d11h38m15s 1d11h38m15s -
select count(*) from ( SELECT DISTINCT gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id FROM pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.GENE_GO_ANNOT g2g -- ,pub2.term chemTerm -- WHERE chemTerm.nm = 'Zinc' -- AND object_type_id = 2 -- chemical -- AND chemTerm.id = gcr.chem_id -- AND gcr.chem_id = cdr.chem_id where gcr.chem_id = cdr.chem_id AND cdr.source_cd = 'C' AND gcr.chem_id = ptr.term_id AND ptr.source_cd IN ('C') AND gcr.gene_id = gdr.gene_id AND gdr.source_cd IN ('C', 'O') AND gcr.gene_id = g2g.gene_id AND g2g.is_not IS FALSE AND cdr.disease_id = gdr.disease_id AND ptr.phenotype_id = g2g.go_term_id) as test;
Date: 2024-08-21 22:27:32 Duration: 1d11h38m15s
3 9m9s 1 9m9s 9m9s 9m9s select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id) as tetramercount;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 21 16 1 9m9s 9m9s -
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 16:45:14 Duration: 9m9s
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 16:45:13 Duration: 0ms Database: ctddev51 User: pub2 Application: pgAdmin 4 - CONN:5020715
4 1m21s 24 1s216ms 9s606ms 3s387ms select t.id, t.object_type_id, t.acc_txt, t.acc_db_cd, t.nm, t.nm_sort, t.secondary_nm, t.description, t.note, l.nm from pub2.term t, pub2.term_label l where t.object_type_id = ? and t.id = l.term_id;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 21 10 16 56s48ms 3s503ms 11 8 25s247ms 3s155ms [ User: editeu - Total duration: 1m21s - Times executed: 24 ]
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 10:41:45 Duration: 9s606ms Database: ctddev51 User: editeu Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 10:58:32 Duration: 8s913ms Database: ctddev51 User: editeu Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 11:09:01 Duration: 5s933ms Database: ctddev51 User: editeu Bind query: yes
5 49s232ms 1 49s232ms 49s232ms 49s232ms select count(*) from ( select distinct viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ?) as ptrtestcount;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 21 16 1 49s232ms 49s232ms -
select count(*) from ( SELECT distinct viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) as ptrTestCount;
Date: 2024-08-21 16:05:06 Duration: 49s232ms
6 42s719ms 1 42s719ms 42s719ms 42s719ms select count(*) from ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ?) as ptrtestcount;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 21 15 1 42s719ms 42s719ms -
select count(*) from ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) as ptrTestCount;
Date: 2024-08-21 15:55:34 Duration: 42s719ms
7 30s219ms 1 30s219ms 30s219ms 30s219ms select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id and exists ( select ? from gene_chem_reference gcr where initialtetramerset.gene_id = gcr.gene_id and initialtetramerset.chem_id = gcr.chem_id) and exists ( select ? from phenotype_term_reference ptr where initialtetramerset.chem_id = ptr.term_id and initialtetramerset.phenotype_id = ptr.phenotype_id and ptr.source_cd in (...)) and exists ( select ? from gene_go_annot gga where initialtetramerset.gene_id = gga.gene_id and initialtetramerset.phenotype_id = gga.go_term_id and gga.is_not is false) and phenotypeterm.nm_fts @@ to_tsquery(?, ?) and phenotypeterm.object_type_id = ?) as tetramercount;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Aug 21 16 1 30s219ms 30s219ms [ User: pub2 - Total duration: 30s219ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:4504232 - Total duration: 30s219ms - Times executed: 1 ]
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops', 'NEURON') AND phenotypeTerm.object_type_id = 5) AS tetramerCount;
Date: 2024-08-21 16:14:03 Duration: 30s219ms Database: ctddev51 User: pub2 Application: pgAdmin 4 - CONN:4504232
8 27s649ms 12 1s82ms 3s626ms 2s304ms 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 #8
Day Hour Count Duration Avg duration Aug 21 05 2 4s505ms 2s252ms 17 4 9s341ms 2s335ms 18 6 13s802ms 2s300ms [ User: pubeu - Total duration: 20s977ms - Times executed: 6 ]
-
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-08-21 17:24:47 Duration: 3s626ms 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 = '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-08-21 18:23:54 Duration: 3s557ms 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 = '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-08-21 18:22:45 Duration: 3s494ms Database: ctddev51 User: pubeu Bind query: yes
9 16s57ms 8 1s976ms 2s45ms 2s7ms select count(*) from gene_disease gd where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 21 05 8 16s57ms 2s7ms [ User: pubeu - Total duration: 1s983ms - Times executed: 1 ]
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:14 Duration: 2s45ms Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:03 Duration: 2s40ms Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:05 Duration: 2s27ms Bind query: yes
10 5s779ms 1 5s779ms 5s779ms 5s779ms select count(*) from ( with viachemptr as ( select phenotype_id, viachemptr.term_id, viachemptr.via_term_id from phenotype_term_reference viachemptr where viachemptr.term_object_type_id = ? and viachemptr.via_term_object_type_id = ? and exists ( select t.id from term t where t.nm_fts @@ to_tsquery(?, ?) and t.object_type_id = ? and viachemptr.phenotype_id = t.id)) 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 viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_id = diseaseterm.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.id, diseaseterm.id, chemterm.id, geneterm.id order by chemterm.nm) as test;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Aug 21 16 1 5s779ms 5s779ms -
-- My re-write of EDB re-write of Problematic Query 1 Rewrite suggestion by EDB (EDB version below) select count(*) from ( WITH viaChemPTR AS ( SELECT phenotype_id, viaChemPTR.term_id, viaChemPTR.via_term_id FROM PHENOTYPE_TERM_REFERENCE viaChemPTR -- ,TERM phenotypeTerm -- ,TERM diseaseTerm -- ,TERM chemTerm -- WHERE viaChemPTR.phenotype_id = phenotypeTerm.id -- AND viaChemPTR.term_id = diseaseTerm.id -- AND viaChemPTR.via_term_id = chemTerm.id WHERE viaChemPTR.term_object_type_id = 3 AND viaChemPTR.via_term_object_type_id = 2 AND exists ( SELECT t.id FROM term t WHERE t.nm_fts @@ to_tsquery('common.english_nostops', 'NEURON') AND t.object_type_id = 5 AND viaChemPTR.phenotype_id = t.id -- AND phenotypeTerm.id = t.id )) 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 viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_id = diseaseTerm.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 = 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.id, diseaseTerm.id, chemTerm.id, geneTerm.id ORDER BY chemTerm.nm) as test;
Date: 2024-08-21 16:19:20 Duration: 5s779ms
11 5s642ms 2 2s771ms 2s871ms 2s821ms 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 #11
Day Hour Count Duration Avg duration Aug 21 05 2 5s642ms 2s821ms -
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-08-21 05:48:21 Duration: 2s871ms 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-08-21 05:48:24 Duration: 2s771ms Bind query: yes
12 4s366ms 2 2s65ms 2s301ms 2s183ms select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id and exists ( select ? from gene_chem_reference gcr where initialtetramerset.gene_id = gcr.gene_id and initialtetramerset.chem_id = gcr.chem_id) and exists ( select ? from phenotype_term_reference ptr where initialtetramerset.chem_id = ptr.term_id and initialtetramerset.phenotype_id = ptr.phenotype_id and ptr.source_cd in (...)) and exists ( select ? from gene_go_annot gga where initialtetramerset.gene_id = gga.gene_id and initialtetramerset.phenotype_id = gga.go_term_id and gga.is_not is false) and chemterm.nm = ? and chemterm.object_type_id = ?) as tetramercount;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 21 14 1 2s65ms 2s65ms 16 1 2s301ms 2s301ms -
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND chemTerm.object_type_id = 2 -- chemical -- AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops','NEURON') -- AND phenotypeTerm.object_type_id = 5 ) AS tetramerCount;
Date: 2024-08-21 16:29:18 Duration: 2s301ms
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 14:55:04 Duration: 2s65ms
13 4s194ms 1 4s194ms 4s194ms 4s194ms 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 #13
Day Hour Count Duration Avg duration Aug 21 05 1 4s194ms 4s194ms -
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-08-21 05:48:49 Duration: 4s194ms Bind query: yes
14 3s929ms 1 3s929ms 3s929ms 3s929ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Aug 21 05 1 3s929ms 3s929ms [ User: pubeu - Total duration: 3s929ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2024-08-21 05:47:19 Duration: 3s929ms Database: ctddev51 User: pubeu Bind query: yes
15 3s908ms 1 3s908ms 3s908ms 3s908ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.taxon_id = any (array ( select dp.descendant_object_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Aug 21 05 1 3s908ms 3s908ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2024-08-21 05:47:30 Duration: 3s908ms Bind query: yes
16 3s901ms 1 3s901ms 3s901ms 3s901ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Aug 21 05 1 3s901ms 3s901ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2024-08-21 05:47:23 Duration: 3s901ms Bind query: yes
17 3s445ms 1 3s445ms 3s445ms 3s445ms 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 #17
Day Hour Count Duration Avg duration Aug 21 05 1 3s445ms 3s445ms -
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-08-21 05:49:15 Duration: 3s445ms Bind query: yes
18 3s416ms 1 3s416ms 3s416ms 3s416ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.id ixnid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id where gcr.gene_id = any (array (( select tp.term_id from term_pathway tp where upper(tp.pathway_nm) like ? and tp.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?)) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by g.nm_sort, c.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Aug 21 05 1 3s416ms 3s416ms -
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterPathwayWhereEquals.Name */ tp.term_id FROM term_pathway tp WHERE UPPER(tp.pathway_nm) LIKE 'METABOLISM' AND tp.object_type_id = 4))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases')) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2024-08-21 05:47:34 Duration: 3s416ms Bind query: yes
19 3s392ms 1 3s392ms 3s392ms 3s392ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 21 05 1 3s392ms 3s392ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2024-08-21 05:49:19 Duration: 3s392ms Bind query: yes
20 3s287ms 1 3s287ms 3s287ms 3s287ms 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 tg.nm_sort, fg.nm_sort limit ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 21 05 1 3s287ms 3s287ms -
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 tg.nm_sort, fg.nm_sort LIMIT 50;
Date: 2024-08-21 05:48:27 Duration: 3s287ms Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 24 1m21s 1s216ms 9s606ms 3s387ms select t.id, t.object_type_id, t.acc_txt, t.acc_db_cd, t.nm, t.nm_sort, t.secondary_nm, t.description, t.note, l.nm from pub2.term t, pub2.term_label l where t.object_type_id = ? and t.id = l.term_id;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 21 10 16 56s48ms 3s503ms 11 8 25s247ms 3s155ms [ User: editeu - Total duration: 1m21s - Times executed: 24 ]
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 10:41:45 Duration: 9s606ms Database: ctddev51 User: editeu Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 10:58:32 Duration: 8s913ms Database: ctddev51 User: editeu Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 11:09:01 Duration: 5s933ms Database: ctddev51 User: editeu Bind query: yes
2 12 27s649ms 1s82ms 3s626ms 2s304ms with recursive sub_node ( object_id, id, path, lvl ) as ( select n.object_id, n.id, array[n.nm_sort], ? from dag_node n where n.object_id = ? union all select n.object_id, n.id, cast(path || n.nm_sort as varchar(?)[]), sn.lvl + ? from dag_node n inner join sub_node sn on (n.parent_id = sn.id)) select distinct t.nm prinm, t.nm_html prinmhtml, t.secondary_nm secondarynm, t.acc_db_cd accdbcd, t.acc_txt termacc, t.is_leaf isleaf, t.has_chems haschems, t.has_diseases hasdiseases, t.has_exposures hasexposures, t.has_genes hasgenes, sn.lvl, sn.path, max(sn.lvl) over () maxlvl, t.has_phenotypes hasphenotypes from sub_node sn inner join term t on sn.object_id = t.id where sn.lvl <= ? order by sn.path;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Aug 21 05 2 4s505ms 2s252ms 17 4 9s341ms 2s335ms 18 6 13s802ms 2s300ms [ User: pubeu - Total duration: 20s977ms - Times executed: 6 ]
-
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-08-21 17:24:47 Duration: 3s626ms 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 = '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-08-21 18:23:54 Duration: 3s557ms 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 = '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-08-21 18:22:45 Duration: 3s494ms Database: ctddev51 User: pubeu Bind query: yes
3 8 16s57ms 1s976ms 2s45ms 2s7ms select count(*) from gene_disease gd where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 21 05 8 16s57ms 2s7ms [ User: pubeu - Total duration: 1s983ms - Times executed: 1 ]
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:14 Duration: 2s45ms Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:03 Duration: 2s40ms Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:05 Duration: 2s27ms Bind query: yes
4 2 5s642ms 2s771ms 2s871ms 2s821ms 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 #4
Day Hour Count Duration Avg duration Aug 21 05 2 5s642ms 2s821ms -
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-08-21 05:48:21 Duration: 2s871ms 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-08-21 05:48:24 Duration: 2s771ms Bind query: yes
5 2 4s366ms 2s65ms 2s301ms 2s183ms select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id and exists ( select ? from gene_chem_reference gcr where initialtetramerset.gene_id = gcr.gene_id and initialtetramerset.chem_id = gcr.chem_id) and exists ( select ? from phenotype_term_reference ptr where initialtetramerset.chem_id = ptr.term_id and initialtetramerset.phenotype_id = ptr.phenotype_id and ptr.source_cd in (...)) and exists ( select ? from gene_go_annot gga where initialtetramerset.gene_id = gga.gene_id and initialtetramerset.phenotype_id = gga.go_term_id and gga.is_not is false) and chemterm.nm = ? and chemterm.object_type_id = ?) as tetramercount;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 21 14 1 2s65ms 2s65ms 16 1 2s301ms 2s301ms -
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND chemTerm.object_type_id = 2 -- chemical -- AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops','NEURON') -- AND phenotypeTerm.object_type_id = 5 ) AS tetramerCount;
Date: 2024-08-21 16:29:18 Duration: 2s301ms
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 14:55:04 Duration: 2s65ms
6 2 2s416ms 1s119ms 1s296ms 1s208ms select t.nm, t.nm_sort nmsort, t.acc_txt acc, t.acc_db_cd accdbcd, ? objecttypecd, null accdisplay from term t where t.id in ( select gga.go_term_id from gene_chem_reference gcr inner join gene_go_annot gga on gcr.gene_id = gga.gene_id inner join dag_node n on gga.go_term_id = n.object_id where gcr.chem_id = ? and gga.is_not = false and n.dag_id = ?) order by t.nm_sort;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 21 05 2 2s416ms 1s208ms -
SELECT /* VennDAO.getTermQuerySQL */ t.nm, t.nm_sort nmsort, t.acc_txt acc, t.acc_db_cd accdbcd, 'go' objectTypeCd, NULL accDisplay FROM term t WHERE t.id IN ( SELECT /* VennChemGODAO */ gga.go_term_id FROM gene_chem_reference gcr INNER JOIN gene_go_annot gga ON gcr.gene_id = gga.gene_id INNER JOIN dag_node n ON gga.go_term_id = n.object_id where gcr.chem_id = 1291235 AND gga.is_not = false AND n.dag_id = 5) ORDER BY t.nm_sort;
Date: 2024-08-21 05:48:35 Duration: 1s296ms Bind query: yes
-
SELECT /* VennDAO.getTermQuerySQL */ t.nm, t.nm_sort nmsort, t.acc_txt acc, t.acc_db_cd accdbcd, 'go' objectTypeCd, NULL accDisplay FROM term t WHERE t.id IN ( SELECT /* VennChemGODAO */ gga.go_term_id FROM gene_chem_reference gcr INNER JOIN gene_go_annot gga ON gcr.gene_id = gga.gene_id INNER JOIN dag_node n ON gga.go_term_id = n.object_id where gcr.chem_id = 1403714 AND gga.is_not = false AND n.dag_id = 5) ORDER BY t.nm_sort;
Date: 2024-08-21 05:48:36 Duration: 1s119ms Bind query: yes
7 1 1d12h40m56s 1d12h40m56s 1d12h40m56s 1d12h40m56s select count(*) from ( select chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from pub2.term chemterm, pub2.term geneterm, pub2.term phenotypeterm, pub2.term diseaseterm where (chemterm.id, geneterm.id, phenotypeterm.id, diseaseterm.id) in ( select distinct gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.gene_go_annot g2g where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.chem_id = ptr.term_id and ptr.source_cd in (...) and gcr.gene_id = gdr.gene_id and gdr.source_cd in (...) and gcr.gene_id = g2g.gene_id and g2g.is_not is false and cdr.disease_id = gdr.disease_id and ptr.phenotype_id = g2g.go_term_id)) as test;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Aug 21 23 1 1d12h40m56s 1d12h40m56s -
select count(*) from ( select chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm from pub2.term chemTerm, pub2.term geneTerm, pub2.term phenotypeTerm, pub2.term diseaseTerm WHERE (chemTerm.id, geneTerm.id, phenotypeTerm.id, diseaseTerm.id) IN ( SELECT DISTINCT gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id FROM pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.GENE_GO_ANNOT g2g -- ,pub2.term chemTerm -- WHERE chemTerm.nm = 'Zinc' -- AND object_type_id = 2 -- chemical -- AND chemTerm.id = gcr.chem_id -- AND gcr.chem_id = cdr.chem_id where gcr.chem_id = cdr.chem_id AND cdr.source_cd = 'C' AND gcr.chem_id = ptr.term_id AND ptr.source_cd IN ('C') AND gcr.gene_id = gdr.gene_id AND gdr.source_cd IN ('C', 'O') AND gcr.gene_id = g2g.gene_id AND g2g.is_not IS FALSE AND cdr.disease_id = gdr.disease_id AND ptr.phenotype_id = g2g.go_term_id)) as test;
Date: 2024-08-21 23:23:49 Duration: 1d12h40m56s
8 1 1d11h38m15s 1d11h38m15s 1d11h38m15s 1d11h38m15s select count(*) from ( select distinct gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.gene_go_annot g2g where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.chem_id = ptr.term_id and ptr.source_cd in (...) and gcr.gene_id = gdr.gene_id and gdr.source_cd in (...) and gcr.gene_id = g2g.gene_id and g2g.is_not is false and cdr.disease_id = gdr.disease_id and ptr.phenotype_id = g2g.go_term_id) as test;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Aug 21 22 1 1d11h38m15s 1d11h38m15s -
select count(*) from ( SELECT DISTINCT gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id FROM pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.GENE_GO_ANNOT g2g -- ,pub2.term chemTerm -- WHERE chemTerm.nm = 'Zinc' -- AND object_type_id = 2 -- chemical -- AND chemTerm.id = gcr.chem_id -- AND gcr.chem_id = cdr.chem_id where gcr.chem_id = cdr.chem_id AND cdr.source_cd = 'C' AND gcr.chem_id = ptr.term_id AND ptr.source_cd IN ('C') AND gcr.gene_id = gdr.gene_id AND gdr.source_cd IN ('C', 'O') AND gcr.gene_id = g2g.gene_id AND g2g.is_not IS FALSE AND cdr.disease_id = gdr.disease_id AND ptr.phenotype_id = g2g.go_term_id) as test;
Date: 2024-08-21 22:27:32 Duration: 1d11h38m15s
9 1 9m9s 9m9s 9m9s 9m9s select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id) as tetramercount;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 21 16 1 9m9s 9m9s -
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 16:45:14 Duration: 9m9s
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 16:45:13 Duration: 0ms Database: ctddev51 User: pub2 Application: pgAdmin 4 - CONN:5020715
10 1 49s232ms 49s232ms 49s232ms 49s232ms select count(*) from ( select distinct viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ?) as ptrtestcount;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Aug 21 16 1 49s232ms 49s232ms -
select count(*) from ( SELECT distinct viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) as ptrTestCount;
Date: 2024-08-21 16:05:06 Duration: 49s232ms
11 1 42s719ms 42s719ms 42s719ms 42s719ms select count(*) from ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ?) as ptrtestcount;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 21 15 1 42s719ms 42s719ms -
select count(*) from ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) as ptrTestCount;
Date: 2024-08-21 15:55:34 Duration: 42s719ms
12 1 30s219ms 30s219ms 30s219ms 30s219ms select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id and exists ( select ? from gene_chem_reference gcr where initialtetramerset.gene_id = gcr.gene_id and initialtetramerset.chem_id = gcr.chem_id) and exists ( select ? from phenotype_term_reference ptr where initialtetramerset.chem_id = ptr.term_id and initialtetramerset.phenotype_id = ptr.phenotype_id and ptr.source_cd in (...)) and exists ( select ? from gene_go_annot gga where initialtetramerset.gene_id = gga.gene_id and initialtetramerset.phenotype_id = gga.go_term_id and gga.is_not is false) and phenotypeterm.nm_fts @@ to_tsquery(?, ?) and phenotypeterm.object_type_id = ?) as tetramercount;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 21 16 1 30s219ms 30s219ms [ User: pub2 - Total duration: 30s219ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:4504232 - Total duration: 30s219ms - Times executed: 1 ]
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops', 'NEURON') AND phenotypeTerm.object_type_id = 5) AS tetramerCount;
Date: 2024-08-21 16:14:03 Duration: 30s219ms Database: ctddev51 User: pub2 Application: pgAdmin 4 - CONN:4504232
13 1 5s779ms 5s779ms 5s779ms 5s779ms select count(*) from ( with viachemptr as ( select phenotype_id, viachemptr.term_id, viachemptr.via_term_id from phenotype_term_reference viachemptr where viachemptr.term_object_type_id = ? and viachemptr.via_term_object_type_id = ? and exists ( select t.id from term t where t.nm_fts @@ to_tsquery(?, ?) and t.object_type_id = ? and viachemptr.phenotype_id = t.id)) 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 viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_id = diseaseterm.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.id, diseaseterm.id, chemterm.id, geneterm.id order by chemterm.nm) as test;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Aug 21 16 1 5s779ms 5s779ms -
-- My re-write of EDB re-write of Problematic Query 1 Rewrite suggestion by EDB (EDB version below) select count(*) from ( WITH viaChemPTR AS ( SELECT phenotype_id, viaChemPTR.term_id, viaChemPTR.via_term_id FROM PHENOTYPE_TERM_REFERENCE viaChemPTR -- ,TERM phenotypeTerm -- ,TERM diseaseTerm -- ,TERM chemTerm -- WHERE viaChemPTR.phenotype_id = phenotypeTerm.id -- AND viaChemPTR.term_id = diseaseTerm.id -- AND viaChemPTR.via_term_id = chemTerm.id WHERE viaChemPTR.term_object_type_id = 3 AND viaChemPTR.via_term_object_type_id = 2 AND exists ( SELECT t.id FROM term t WHERE t.nm_fts @@ to_tsquery('common.english_nostops', 'NEURON') AND t.object_type_id = 5 AND viaChemPTR.phenotype_id = t.id -- AND phenotypeTerm.id = t.id )) 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 viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_id = diseaseTerm.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 = 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.id, diseaseTerm.id, chemTerm.id, geneTerm.id ORDER BY chemTerm.nm) as test;
Date: 2024-08-21 16:19:20 Duration: 5s779ms
14 1 4s194ms 4s194ms 4s194ms 4s194ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Aug 21 05 1 4s194ms 4s194ms -
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-08-21 05:48:49 Duration: 4s194ms Bind query: yes
15 1 3s929ms 3s929ms 3s929ms 3s929ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Aug 21 05 1 3s929ms 3s929ms [ User: pubeu - Total duration: 3s929ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2024-08-21 05:47:19 Duration: 3s929ms Database: ctddev51 User: pubeu Bind query: yes
16 1 3s908ms 3s908ms 3s908ms 3s908ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.taxon_id = any (array ( select dp.descendant_object_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Aug 21 05 1 3s908ms 3s908ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2024-08-21 05:47:30 Duration: 3s908ms Bind query: yes
17 1 3s901ms 3s901ms 3s901ms 3s901ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Aug 21 05 1 3s901ms 3s901ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2024-08-21 05:47:23 Duration: 3s901ms Bind query: yes
18 1 3s445ms 3s445ms 3s445ms 3s445ms 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 #18
Day Hour Count Duration Avg duration Aug 21 05 1 3s445ms 3s445ms -
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-08-21 05:49:15 Duration: 3s445ms Bind query: yes
19 1 3s416ms 3s416ms 3s416ms 3s416ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.id ixnid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id where gcr.gene_id = any (array (( select tp.term_id from term_pathway tp where upper(tp.pathway_nm) like ? and tp.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?)) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by g.nm_sort, c.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 21 05 1 3s416ms 3s416ms -
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterPathwayWhereEquals.Name */ tp.term_id FROM term_pathway tp WHERE UPPER(tp.pathway_nm) LIKE 'METABOLISM' AND tp.object_type_id = 4))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases')) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2024-08-21 05:47:34 Duration: 3s416ms Bind query: yes
20 1 3s392ms 3s392ms 3s392ms 3s392ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 21 05 1 3s392ms 3s392ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2024-08-21 05:49:19 Duration: 3s392ms Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 1d12h40m56s 1d12h40m56s 1d12h40m56s 1 1d12h40m56s select count(*) from ( select chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from pub2.term chemterm, pub2.term geneterm, pub2.term phenotypeterm, pub2.term diseaseterm where (chemterm.id, geneterm.id, phenotypeterm.id, diseaseterm.id) in ( select distinct gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.gene_go_annot g2g where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.chem_id = ptr.term_id and ptr.source_cd in (...) and gcr.gene_id = gdr.gene_id and gdr.source_cd in (...) and gcr.gene_id = g2g.gene_id and g2g.is_not is false and cdr.disease_id = gdr.disease_id and ptr.phenotype_id = g2g.go_term_id)) as test;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Aug 21 23 1 1d12h40m56s 1d12h40m56s -
select count(*) from ( select chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm from pub2.term chemTerm, pub2.term geneTerm, pub2.term phenotypeTerm, pub2.term diseaseTerm WHERE (chemTerm.id, geneTerm.id, phenotypeTerm.id, diseaseTerm.id) IN ( SELECT DISTINCT gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id FROM pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.GENE_GO_ANNOT g2g -- ,pub2.term chemTerm -- WHERE chemTerm.nm = 'Zinc' -- AND object_type_id = 2 -- chemical -- AND chemTerm.id = gcr.chem_id -- AND gcr.chem_id = cdr.chem_id where gcr.chem_id = cdr.chem_id AND cdr.source_cd = 'C' AND gcr.chem_id = ptr.term_id AND ptr.source_cd IN ('C') AND gcr.gene_id = gdr.gene_id AND gdr.source_cd IN ('C', 'O') AND gcr.gene_id = g2g.gene_id AND g2g.is_not IS FALSE AND cdr.disease_id = gdr.disease_id AND ptr.phenotype_id = g2g.go_term_id)) as test;
Date: 2024-08-21 23:23:49 Duration: 1d12h40m56s
2 1d11h38m15s 1d11h38m15s 1d11h38m15s 1 1d11h38m15s select count(*) from ( select distinct gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id from pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.gene_go_annot g2g where gcr.chem_id = cdr.chem_id and cdr.source_cd = ? and gcr.chem_id = ptr.term_id and ptr.source_cd in (...) and gcr.gene_id = gdr.gene_id and gdr.source_cd in (...) and gcr.gene_id = g2g.gene_id and g2g.is_not is false and cdr.disease_id = gdr.disease_id and ptr.phenotype_id = g2g.go_term_id) as test;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Aug 21 22 1 1d11h38m15s 1d11h38m15s -
select count(*) from ( SELECT DISTINCT gcr.chem_id, gcr.gene_id, ptr.phenotype_id, cdr.disease_id FROM pub2.gene_chem_reference gcr, pub2.chem_disease_reference cdr, pub2.gene_disease_reference gdr, pub2.phenotype_term_reference ptr, pub2.GENE_GO_ANNOT g2g -- ,pub2.term chemTerm -- WHERE chemTerm.nm = 'Zinc' -- AND object_type_id = 2 -- chemical -- AND chemTerm.id = gcr.chem_id -- AND gcr.chem_id = cdr.chem_id where gcr.chem_id = cdr.chem_id AND cdr.source_cd = 'C' AND gcr.chem_id = ptr.term_id AND ptr.source_cd IN ('C') AND gcr.gene_id = gdr.gene_id AND gdr.source_cd IN ('C', 'O') AND gcr.gene_id = g2g.gene_id AND g2g.is_not IS FALSE AND cdr.disease_id = gdr.disease_id AND ptr.phenotype_id = g2g.go_term_id) as test;
Date: 2024-08-21 22:27:32 Duration: 1d11h38m15s
3 9m9s 9m9s 9m9s 1 9m9s select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id) as tetramercount;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Aug 21 16 1 9m9s 9m9s -
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 16:45:14 Duration: 9m9s
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id -- AND EXISTS -- G-C -- ( -- select 1 -- from gene_chem_reference gcr -- where initialTetramerSet.gene_id = gcr.gene_id -- and initialTetramerSet.chem_id = gcr.chem_id -- ) -- AND EXISTS -- C-P -- ( -- select 1 -- from phenotype_term_reference ptr -- WHERE initialTetramerSet.chem_id = ptr.term_id -- and initialTetramerSet.phenotype_id = ptr.phenotype_id -- AND ptr.source_cd IN ( 'C' ) -- ) -- AND EXISTS -- G-P -- ( -- select 1 -- from gene_go_annot gga -- WHERE initialTetramerSet.gene_id = gga.gene_id -- and initialTetramerSet.phenotype_id = gga.go_term_id -- -- AND gga.is_not IS FALSE -- ) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 16:45:13 Duration: 0ms Database: ctddev51 User: pub2 Application: pgAdmin 4 - CONN:5020715
4 49s232ms 49s232ms 49s232ms 1 49s232ms select count(*) from ( select distinct viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ?) as ptrtestcount;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Aug 21 16 1 49s232ms 49s232ms -
select count(*) from ( SELECT distinct viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) as ptrTestCount;
Date: 2024-08-21 16:05:06 Duration: 49s232ms
5 42s719ms 42s719ms 42s719ms 1 42s719ms select count(*) from ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ?) as ptrtestcount;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Aug 21 15 1 42s719ms 42s719ms -
select count(*) from ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) as ptrTestCount;
Date: 2024-08-21 15:55:34 Duration: 42s719ms
6 30s219ms 30s219ms 30s219ms 1 30s219ms select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id and exists ( select ? from gene_chem_reference gcr where initialtetramerset.gene_id = gcr.gene_id and initialtetramerset.chem_id = gcr.chem_id) and exists ( select ? from phenotype_term_reference ptr where initialtetramerset.chem_id = ptr.term_id and initialtetramerset.phenotype_id = ptr.phenotype_id and ptr.source_cd in (...)) and exists ( select ? from gene_go_annot gga where initialtetramerset.gene_id = gga.gene_id and initialtetramerset.phenotype_id = gga.go_term_id and gga.is_not is false) and phenotypeterm.nm_fts @@ to_tsquery(?, ?) and phenotypeterm.object_type_id = ?) as tetramercount;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Aug 21 16 1 30s219ms 30s219ms [ User: pub2 - Total duration: 30s219ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:4504232 - Total duration: 30s219ms - Times executed: 1 ]
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- -- AND chemTerm.nm = 'Zinc' -- AND chemTerm.object_type_id = 2 -- chemical AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops', 'NEURON') AND phenotypeTerm.object_type_id = 5) AS tetramerCount;
Date: 2024-08-21 16:14:03 Duration: 30s219ms Database: ctddev51 User: pub2 Application: pgAdmin 4 - CONN:4504232
7 5s779ms 5s779ms 5s779ms 1 5s779ms select count(*) from ( with viachemptr as ( select phenotype_id, viachemptr.term_id, viachemptr.via_term_id from phenotype_term_reference viachemptr where viachemptr.term_object_type_id = ? and viachemptr.via_term_object_type_id = ? and exists ( select t.id from term t where t.nm_fts @@ to_tsquery(?, ?) and t.object_type_id = ? and viachemptr.phenotype_id = t.id)) 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 viachemptr, phenotype_term_reference viageneptr, term phenotypeterm, term diseaseterm, term geneterm, term chemterm where viachemptr.phenotype_id = phenotypeterm.id and viachemptr.term_id = diseaseterm.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.id, diseaseterm.id, chemterm.id, geneterm.id order by chemterm.nm) as test;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Aug 21 16 1 5s779ms 5s779ms -
-- My re-write of EDB re-write of Problematic Query 1 Rewrite suggestion by EDB (EDB version below) select count(*) from ( WITH viaChemPTR AS ( SELECT phenotype_id, viaChemPTR.term_id, viaChemPTR.via_term_id FROM PHENOTYPE_TERM_REFERENCE viaChemPTR -- ,TERM phenotypeTerm -- ,TERM diseaseTerm -- ,TERM chemTerm -- WHERE viaChemPTR.phenotype_id = phenotypeTerm.id -- AND viaChemPTR.term_id = diseaseTerm.id -- AND viaChemPTR.via_term_id = chemTerm.id WHERE viaChemPTR.term_object_type_id = 3 AND viaChemPTR.via_term_object_type_id = 2 AND exists ( SELECT t.id FROM term t WHERE t.nm_fts @@ to_tsquery('common.english_nostops', 'NEURON') AND t.object_type_id = 5 AND viaChemPTR.phenotype_id = t.id -- AND phenotypeTerm.id = t.id )) 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 viaChemPTR, PHENOTYPE_TERM_REFERENCE viaGenePTR, TERM phenotypeTerm, TERM diseaseTerm, TERM geneTerm, TERM chemTerm where viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_id = diseaseTerm.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 = 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.id, diseaseTerm.id, chemTerm.id, geneTerm.id ORDER BY chemTerm.nm) as test;
Date: 2024-08-21 16:19:20 Duration: 5s779ms
8 4s194ms 4s194ms 4s194ms 1 4s194ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Aug 21 05 1 4s194ms 4s194ms -
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-08-21 05:48:49 Duration: 4s194ms Bind query: yes
9 3s929ms 3s929ms 3s929ms 1 3s929ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Aug 21 05 1 3s929ms 3s929ms [ User: pubeu - Total duration: 3s929ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2024-08-21 05:47:19 Duration: 3s929ms Database: ctddev51 User: pubeu Bind query: yes
10 3s908ms 3s908ms 3s908ms 1 3s908ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.taxon_id = any (array ( select dp.descendant_object_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Aug 21 05 1 3s908ms 3s908ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2024-08-21 05:47:30 Duration: 3s908ms Bind query: yes
11 3s901ms 3s901ms 3s901ms 1 3s901ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Aug 21 05 1 3s901ms 3s901ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2024-08-21 05:47:23 Duration: 3s901ms Bind query: yes
12 3s445ms 3s445ms 3s445ms 1 3s445ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Aug 21 05 1 3s445ms 3s445ms -
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-08-21 05:49:15 Duration: 3s445ms Bind query: yes
13 3s416ms 3s416ms 3s416ms 1 3s416ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, i.id ixnid, i.ixn_prose_txt ixnprose, i.ixn_prose_html ixnprosehtml, i.actions_txt ixnactions, count(distinct gcr.reference_id) refcount, count(distinct gcr.taxon_id) taxoncount, count(*) over () fullrowcount from gene_chem_reference gcr inner join ixn i on gcr.ixn_id = i.id inner join term g on gcr.gene_id = g.id inner join term c on gcr.chem_id = c.id where gcr.gene_id = any (array (( select tp.term_id from term_pathway tp where upper(tp.pathway_nm) like ? and tp.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?)) group by g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id order by g.nm_sort, c.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Aug 21 05 1 3s416ms 3s416ms -
SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterPathwayWhereEquals.Name */ tp.term_id FROM term_pathway tp WHERE UPPER(tp.pathway_nm) LIKE 'METABOLISM' AND tp.object_type_id = 4))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases')) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;
Date: 2024-08-21 05:47:34 Duration: 3s416ms Bind query: yes
14 3s392ms 3s392ms 3s392ms 1 3s392ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Aug 21 05 1 3s392ms 3s392ms -
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2024-08-21 05:49:19 Duration: 3s392ms Bind query: yes
15 1s216ms 9s606ms 3s387ms 24 1m21s select t.id, t.object_type_id, t.acc_txt, t.acc_db_cd, t.nm, t.nm_sort, t.secondary_nm, t.description, t.note, l.nm from pub2.term t, pub2.term_label l where t.object_type_id = ? and t.id = l.term_id;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Aug 21 10 16 56s48ms 3s503ms 11 8 25s247ms 3s155ms [ User: editeu - Total duration: 1m21s - Times executed: 24 ]
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 10:41:45 Duration: 9s606ms Database: ctddev51 User: editeu Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 10:58:32 Duration: 8s913ms Database: ctddev51 User: editeu Bind query: yes
-
select t.ID, t.OBJECT_TYPE_ID, t.ACC_TXT, t.ACC_DB_CD, t.NM, t.NM_SORT, t.SECONDARY_NM, t.DESCRIPTION, t.NOTE, l.NM from pub2.TERM t, pub2.TERM_LABEL l where t.OBJECT_TYPE_ID = 4 and t.id = l.TERM_ID;
Date: 2024-08-21 11:09:01 Duration: 5s933ms Database: ctddev51 User: editeu Bind query: yes
16 3s287ms 3s287ms 3s287ms 1 3s287ms 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 tg.nm_sort, fg.nm_sort limit ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Aug 21 05 1 3s287ms 3s287ms -
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 tg.nm_sort, fg.nm_sort LIMIT 50;
Date: 2024-08-21 05:48:27 Duration: 3s287ms Bind query: yes
17 2s771ms 2s871ms 2s821ms 2 5s642ms 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 Aug 21 05 2 5s642ms 2s821ms -
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-08-21 05:48:21 Duration: 2s871ms 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-08-21 05:48:24 Duration: 2s771ms Bind query: yes
18 1s82ms 3s626ms 2s304ms 12 27s649ms 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 Aug 21 05 2 4s505ms 2s252ms 17 4 9s341ms 2s335ms 18 6 13s802ms 2s300ms [ User: pubeu - Total duration: 20s977ms - Times executed: 6 ]
-
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-08-21 17:24:47 Duration: 3s626ms 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 = '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-08-21 18:23:54 Duration: 3s557ms 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 = '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-08-21 18:22:45 Duration: 3s494ms Database: ctddev51 User: pubeu Bind query: yes
19 2s65ms 2s301ms 2s183ms 2 4s366ms select count(*) from ( with initialtetramerset as ( select viachemptr.via_term_id as chem_id, viageneptr.via_term_id as gene_id, viachemptr.phenotype_id as phenotype_id, viachemptr.term_id as disease_id from phenotype_term_reference viachemptr, 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 = ? ) select distinct chemterm.nm, geneterm.nm, phenotypeterm.nm, diseaseterm.nm from initialtetramerset, term chemterm, term geneterm, term phenotypeterm, term diseaseterm where chemterm.id = initialtetramerset.chem_id and geneterm.id = initialtetramerset.gene_id and phenotypeterm.id = initialtetramerset.phenotype_id and diseaseterm.id = initialtetramerset.disease_id and exists ( select ? from gene_chem_reference gcr where initialtetramerset.gene_id = gcr.gene_id and initialtetramerset.chem_id = gcr.chem_id) and exists ( select ? from phenotype_term_reference ptr where initialtetramerset.chem_id = ptr.term_id and initialtetramerset.phenotype_id = ptr.phenotype_id and ptr.source_cd in (...)) and exists ( select ? from gene_go_annot gga where initialtetramerset.gene_id = gga.gene_id and initialtetramerset.phenotype_id = gga.go_term_id and gga.is_not is false) and chemterm.nm = ? and chemterm.object_type_id = ?) as tetramercount;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Aug 21 14 1 2s65ms 2s65ms 16 1 2s301ms 2s301ms -
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND chemTerm.object_type_id = 2 -- chemical -- AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops','NEURON') -- AND phenotypeTerm.object_type_id = 5 ) AS tetramerCount;
Date: 2024-08-21 16:29:18 Duration: 2s301ms
-
SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id, -- chem viaGenePTR.via_term_id AS gene_id, -- gene viaChemPTR.phenotype_id AS phenotype_id, viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR, phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm, geneTerm.nm, phenotypeTerm.nm, diseaseTerm.nm FROM initialTetramerSet, term chemTerm, term geneTerm, term phenotypeTerm, term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ('C')) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND chemTerm.object_type_id = 2 -- chemical ) AS tetramerCount;
Date: 2024-08-21 14:55:04 Duration: 2s65ms
20 1s976ms 2s45ms 2s7ms 8 16s57ms select count(*) from gene_disease gd where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?);Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Aug 21 05 8 16s57ms 2s7ms [ User: pubeu - Total duration: 1s983ms - Times executed: 1 ]
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:14 Duration: 2s45ms Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:03 Duration: 2s40ms Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');
Date: 2024-08-21 05:48:05 Duration: 2s27ms Bind query: yes
Time consuming prepare
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
Time consuming bind
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
-
Events
Log levels
Key values
- 8,058 Log entries
Events distribution
Key values
- 0 PANIC entries
- 2 FATAL entries
- 17 ERROR entries
- 0 WARNING entries
Most Frequent Errors/Events
Key values
- 6 Max number of times the same event was reported
- 19 Total events found
Rank Times reported Error 1 6 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #1
Day Hour Count Aug 21 14 2 16 4 - ERROR: syntax error at or near "select" at character 333
- ERROR: syntax error at or near "and" at character 1431
- ERROR: syntax error at or near ")" at character 1259
Statement: select t.relname ,l.locktype,page ,virtualtransaction ,pid,mode ,granted from pg_locks l ,pg_stat_all_tables t where l.relation = t.relid order by relation asc; select distinct( term_object_type_id, via_term_object_type_id ) from phenotype_term_reference ptr where via_term_id is not null select * from phenotype_term_reference ptr limit 100 select * from phenotype_term ptr limit 100
Date: 2024-08-21 14:44:37 Database: ctddev51 Application: pgAdmin 4 - CONN:3534603 User: pub2 Remote:
Statement: WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm ,geneTerm.nm ,phenotypeTerm.nm ,diseaseTerm.nm -- ,initialTetramerSet.gene_id FROM initialTetramerSet ,term chemTerm ,term geneTerm ,term phenotypeTerm ,term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id ) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr and initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = gga.phenotype_id AND ptr.source_cd IN ( 'C' ) ) AND EXISTS -- G-P ( select 1 from gene_go_annot gga and initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.phenotype_id AND gga.is_not IS FALSE ) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND object_type_id = 2 -- chemical -- AND chemTerm.id = initialTetramerSet.chem_id limit 1
Date: 2024-08-21 14:45:02 Database: ctddev51 Application: pgAdmin 4 - CONN:1424743 User: pub2 Remote:
Statement: -- My re-write of EDB re-write of Problematic Query 1 Rewrite suggestion by EDB (EDB version below) select count(*) from ( WITH viaChemPTR AS ( SELECT phenotype_id ,viaChemPTR.term_id ,viaChemPTR.via_term_id FROM PHENOTYPE_TERM_REFERENCE viaChemPTR -- ,TERM phenotypeTerm -- ,TERM diseaseTerm -- ,TERM chemTerm -- WHERE viaChemPTR.phenotype_id = phenotypeTerm.id -- AND viaChemPTR.term_id = diseaseTerm.id -- AND viaChemPTR.via_term_id = chemTerm.id WHERE viaChemPTR.term_object_type_id = 3 AND viaChemPTR.via_term_object_type_id = 2 AND exists ( SELECT t.id FROM term t WHERE t.nm_fts @@ to_tsquery('common.english_nostops','NEURON') AND t.object_type_id = 5 AND viaChemPTR.phenotype_id = t.id -- AND phenotypeTerm.id = t.id ) ) )
Date: 2024-08-21 16:21:03
2 3 ERROR: subquery in FROM must have an alias
Times Reported Most Frequent Error / Event #2
Day Hour Count Aug 21 14 1 15 1 16 1 - ERROR: subquery in FROM must have an alias at character 22
- ERROR: subquery in FROM must have an alias at character 22
- ERROR: subquery in FROM must have an alias at character 122
Hint: For example, FROM (SELECT ...) [AS] foo.
Statement: SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm ,geneTerm.nm ,phenotypeTerm.nm ,diseaseTerm.nm FROM initialTetramerSet ,term chemTerm ,term geneTerm ,term phenotypeTerm ,term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id ) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ( 'C' ) ) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE ) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND object_type_id = 2 -- chemical )Date: 2024-08-21 14:54:25
Hint: For example, FROM (SELECT ...) [AS] foo.
Statement: select count(*) from ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene )Date: 2024-08-21 15:54:39 Database: ctddev51 Application: pgAdmin 4 - CONN:3308362 User: pub2 Remote:
Hint: For example, FROM (SELECT ...) [AS] foo.
Statement: -- My re-write of EDB re-write of Problematic Query 1 Rewrite suggestion by EDB (EDB version below) select count(*) from ( WITH viaChemPTR AS ( SELECT phenotype_id ,viaChemPTR.term_id ,viaChemPTR.via_term_id FROM PHENOTYPE_TERM_REFERENCE viaChemPTR -- ,TERM phenotypeTerm -- ,TERM diseaseTerm -- ,TERM chemTerm -- WHERE viaChemPTR.phenotype_id = phenotypeTerm.id -- AND viaChemPTR.term_id = diseaseTerm.id -- AND viaChemPTR.via_term_id = chemTerm.id WHERE viaChemPTR.term_object_type_id = 3 AND viaChemPTR.via_term_object_type_id = 2 AND exists ( SELECT t.id FROM term t WHERE t.nm_fts @@ to_tsquery('common.english_nostops','NEURON') AND t.object_type_id = 5 AND viaChemPTR.phenotype_id = t.id -- AND phenotypeTerm.id = t.id ) ) 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 viaChemPTR ,PHENOTYPE_TERM_REFERENCE viaGenePTR ,TERM phenotypeTerm ,TERM diseaseTerm ,TERM geneTerm ,TERM chemTerm where viaChemPTR.phenotype_id = phenotypeTerm.id and viaChemPTR.term_id = diseaseTerm.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 = 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.id, diseaseTerm.id, chemTerm.id, geneTerm.id ORDER BY chemTerm.nm )Date: 2024-08-21 16:19:05
3 2 ERROR: column reference "..." is ambiguous
Times Reported Most Frequent Error / Event #3
Day Hour Count Aug 21 14 2 - ERROR: column reference "object_type_id" is ambiguous at character 1893
- ERROR: column reference "object_type_id" is ambiguous at character 1870
Statement: WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm ,geneTerm.nm ,phenotypeTerm.nm ,diseaseTerm.nm -- ,initialTetramerSet.gene_id FROM initialTetramerSet ,term chemTerm ,term geneTerm ,term phenotypeTerm ,term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id ) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ( 'C' ) ) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE ) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND object_type_id = 2 -- chemical -- AND chemTerm.id = initialTetramerSet.chem_id limit 1
Date: 2024-08-21 14:46:51
Statement: SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm ,geneTerm.nm ,phenotypeTerm.nm ,diseaseTerm.nm FROM initialTetramerSet ,term chemTerm ,term geneTerm ,term phenotypeTerm ,term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id ) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ( 'C' ) ) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE ) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND object_type_id = 2 -- chemical ) AS tetramerCount
Date: 2024-08-21 14:54:42
4 2 LOG: could not send data to client: Connection timed out
Times Reported Most Frequent Error / Event #4
Day Hour Count Aug 21 22 1 23 1 5 2 ERROR: missing FROM-clause entry for table "..."
Times Reported Most Frequent Error / Event #5
Day Hour Count Aug 21 14 1 16 1 - ERROR: missing FROM-clause entry for table "gga" at character 1528
- ERROR: missing FROM-clause entry for table "chemterm" at character 2185
Statement: WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm ,geneTerm.nm ,phenotypeTerm.nm ,diseaseTerm.nm -- ,initialTetramerSet.gene_id FROM initialTetramerSet ,term chemTerm ,term geneTerm ,term phenotypeTerm ,term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id ) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = gga.phenotype_id AND ptr.source_cd IN ( 'C' ) ) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.phenotype_id AND gga.is_not IS FALSE ) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND object_type_id = 2 -- chemical -- AND chemTerm.id = initialTetramerSet.chem_id limit 1
Date: 2024-08-21 14:45:52
Statement: SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease ,chemTerm.nm AS chemTerm ,geneTerm.nm AS geneTerm ,phenotypeTerm.nm AS phenotypeTerm ,diseaseTerm.nm AS diseaseTerm FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR ,term chemTerm ,term geneTerm ,term phenotypeTerm ,term diseaseTerm WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ------------------------------------------------------- AND chemTerm.id = viaChemPTR.via_term_id AND geneTerm.id = viaGenePTR.via_term_id AND phenotypeTerm.id = viaChemPTR.phenotype_id AND diseaseTerm.id = viaChemPTR.term_id ) SELECT DISTINCT initialTetramerSet.chemTerm ,initialTetramerSet.geneTerm ,initialTetramerSet.phenotypeTerm ,initialTetramerSet.diseaseTerm FROM initialTetramerSet WHERE EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id ) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ( 'C' ) ) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE ) -- ------------------------------------------------- -- this takes seconds: AND chemTerm.nm = 'Zinc' AND chemTerm.object_type_id = 2 -- chemical -- ------------------------------------------------- -- this takes seconds: -- ------------------------------------------------- -- AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops','NEURON') -- AND phenotypeTerm.object_type_id = 5 ) AS tetramerCount
Date: 2024-08-21 16:49:51
6 2 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #6
Day Hour Count Aug 21 22 1 23 1 7 1 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #7
Day Hour Count Aug 21 17 1 - ERROR: canceling statement due to user request
Statement: SELECT count(*) FROM ( WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease ,chemTerm.nm AS chemTerm ,geneTerm.nm AS geneTerm ,phenotypeTerm.nm AS phenotypeTerm ,diseaseTerm.nm AS diseaseTerm FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR ,term chemTerm ,term geneTerm ,term phenotypeTerm ,term diseaseTerm WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ------------------------------------------------------- AND chemTerm.id = viaChemPTR.via_term_id AND geneTerm.id = viaGenePTR.via_term_id AND phenotypeTerm.id = viaChemPTR.phenotype_id AND diseaseTerm.id = viaChemPTR.term_id ) SELECT DISTINCT initialTetramerSet.chemTerm ,initialTetramerSet.geneTerm ,initialTetramerSet.phenotypeTerm ,initialTetramerSet.diseaseTerm FROM initialTetramerSet WHERE EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id ) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ( 'C' ) ) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.go_term_id AND gga.is_not IS FALSE ) -- ------------------------------------------------- -- this takes seconds: -- AND initialTetramerSet.chemTerm = 'Zinc' -- ------------------------------------------------- -- this takes seconds: -- ------------------------------------------------- -- AND phenotypeTerm.nm_fts @@ to_tsquery('common.english_nostops','NEURON') -- AND phenotypeTerm.object_type_id = 5 ) AS tetramerCount
Date: 2024-08-21 17:41:15
8 1 ERROR: column "..." does not exist
Times Reported Most Frequent Error / Event #8
Day Hour Count Aug 21 14 1 - ERROR: column gga.phenotype_id does not exist at character 1763
Statement: WITH initialTetramerSet AS ( SELECT viaChemPTR.via_term_id AS chem_id -- chem ,viaGenePTR.via_term_id AS gene_id -- gene ,viaChemPTR.phenotype_id AS phenotype_id ,viaChemPTR.term_id AS disease_id -- disease FROM phenotype_term_reference viaChemPTR ,phenotype_term_reference viaGenePTR WHERE viaChemPTR.term_id = viaGenePTR.term_id AND viaChemPTR.term_object_type_id = 3 -- disease AND viaChemPTR.phenotype_id = viaGenePTR.phenotype_id AND viaChemPTR.via_term_object_type_id = 2 -- chem AND viaGenePTR.via_term_object_type_id = 4 -- gene ) SELECT DISTINCT chemTerm.nm ,geneTerm.nm ,phenotypeTerm.nm ,diseaseTerm.nm -- ,initialTetramerSet.gene_id FROM initialTetramerSet ,term chemTerm ,term geneTerm ,term phenotypeTerm ,term diseaseTerm WHERE chemTerm.id = initialTetramerSet.chem_id AND geneTerm.id = initialTetramerSet.gene_id AND phenotypeTerm.id = initialTetramerSet.phenotype_id AND diseaseTerm.id = initialTetramerSet.disease_id AND EXISTS -- G-C ( select 1 from gene_chem_reference gcr where initialTetramerSet.gene_id = gcr.gene_id and initialTetramerSet.chem_id = gcr.chem_id ) AND EXISTS -- C-P ( select 1 from phenotype_term_reference ptr WHERE initialTetramerSet.chem_id = ptr.term_id and initialTetramerSet.phenotype_id = ptr.phenotype_id AND ptr.source_cd IN ( 'C' ) ) AND EXISTS -- G-P ( select 1 from gene_go_annot gga WHERE initialTetramerSet.gene_id = gga.gene_id and initialTetramerSet.phenotype_id = gga.phenotype_id AND gga.is_not IS FALSE ) -- ------------------------------------------------- AND chemTerm.nm = 'Zinc' AND object_type_id = 2 -- chemical -- AND chemTerm.id = initialTetramerSet.chem_id limit 1
Date: 2024-08-21 14:46:29