1
444
Details
22m3s
1s26ms
6s375ms
2s981ms
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
associatedterm.id = any ( array ( (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) )
and ptr.term_object_type_id = ?
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ?;
Times Reported Time consuming queries #1
Day
Hour
Count
Duration
Avg duration
Dec 28 00 15 55s291ms 3s686ms 01 25 1m2s 2s512ms 02 19 1m4s 3s380ms 03 18 55s782ms 3s99ms 04 23 1m7s 2s954ms 05 13 31s480ms 2s421ms 06 25 1m21s 3s260ms 07 24 1m15s 3s142ms 08 15 41s743ms 2s782ms 09 21 47s526ms 2s263ms 10 18 1m2s 3s460ms 11 25 1m37s 3s889ms 12 14 43s807ms 3s129ms 13 12 29s533ms 2s461ms 14 16 41s695ms 2s605ms 15 27 1m9s 2s579ms 16 13 38s624ms 2s971ms 17 19 58s382ms 3s72ms 18 20 1m5s 3s288ms 19 19 49s331ms 2s596ms 20 16 41s332ms 2s583ms 21 15 48s161ms 3s210ms 22 17 49s614ms 2s918ms 23 15 44s449ms 2s963ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 6m17s - Times executed: 135 ]
x Hide
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-12-28 03:10:16
Duration: 6s375ms
Database: ctdprd51
User: pubeu
Bind query: yes
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-12-28 21:37:04
Duration: 6s373ms
Bind query: yes
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-12-28 11:10:48
Duration: 6s350ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
2
353
Details
16m2s
1s9ms
4s888ms
2s725ms
select
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
(
select
string_agg ( distinct stressorterm.nm || ? || (
select
cd
from object_type
where
id = stressorterm.object_type_id) || ? || stressorterm.nm_html || ? || stressorterm.acc_txt || ? || stressorterm.acc_db_cd, ?) ) as stressoragents,
(
select
string_agg ( distinct coalesce ( receptorterm.nm, ?) || ? || coalesce ( (
select
cd
from object_type
where
id = receptorterm.object_type_id) , ?) || ? || coalesce ( receptorterm.nm_html, ?) || ? || coalesce ( receptorterm.acc_txt, ?) || ? || coalesce ( receptorterm.acc_db_cd, ?) || ? || receptor.description, ?) ) as receptors,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
(
select
string_agg ( distinct location.locality_txt, ?) ) as localities,
(
select
string_agg ( distinct event.medium_nm || ? || coalesce ( event.medium_term_acc_txt, ?) , ?) ) as assaymediums,
(
select
string_agg ( distinct exposuremarkerterm.nm || ? || (
select
cd
from object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd, ?) ) as assayedmarkers,
(
select
string_agg ( distinct diseaseterm.nm || ? || (
select
cd
from object_type
where
id = diseaseterm.object_type_id) || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd, ?) ) as diseases,
(
select
string_agg ( distinct phenotypeterm.nm || ? || (
select
cd
from object_type
where
id = phenotypeterm.object_type_id) || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd, ?) ) as phenotypes,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
re.author_summary summary,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join reference r on e.reference_id = r.id
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join exp_event_location location on e.exp_event_id = location.exp_event_id
left outer join country on location.country_id = country.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
inner join reference_exp re on e.reference_id = re.reference_id
left outer join exp_study_factor expstudyfactor on re.id = expstudyfactor.reference_exp_id
where
e.reference_id = any ( array (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like ?)
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressoragents
limit ?;
Times Reported Time consuming queries #2
Day
Hour
Count
Duration
Avg duration
Dec 28 00 12 31s240ms 2s603ms 01 20 59s124ms 2s956ms 02 10 23s267ms 2s326ms 03 13 35s192ms 2s707ms 04 14 34s267ms 2s447ms 05 15 39s898ms 2s659ms 06 14 38s66ms 2s719ms 07 14 33s406ms 2s386ms 08 17 37s320ms 2s195ms 09 10 35s536ms 3s553ms 10 25 1m7s 2s701ms 11 15 38s825ms 2s588ms 12 14 41s717ms 2s979ms 13 8 19s109ms 2s388ms 14 16 50s459ms 3s153ms 15 11 30s951ms 2s813ms 16 11 32s599ms 2s963ms 17 21 56s311ms 2s681ms 18 26 1m11s 2s757ms 19 14 43s235ms 3s88ms 20 11 25s471ms 2s315ms 21 17 46s421ms 2s730ms 22 8 22s449ms 2s806ms 23 17 47s957ms 2s821ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 4m6s - Times executed: 91 ]
x Hide
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1398604')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1398604')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 ;
Date: 2024-12-28 18:38:39
Duration: 4s888ms
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1398604')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1398604')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 ;
Date: 2024-12-28 16:03:40
Duration: 4s754ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1398604')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1398604')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 ;
Date: 2024-12-28 16:19:08
Duration: 4s712ms
Bind query: yes
x Hide
3
146
Details
3m55s
1s223ms
2s401ms
1s614ms
select
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposurereferencecount,
case when cd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
chem_disease_axn a
where
a.chem_id = cd.chem_id
and a.disease_id = cd.disease_id)
else
null
end actiontypes
from
chem_disease cd
inner join term c on cd.chem_id = c.id
inner join term d on cd.disease_id = d.id
where
cd.chem_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
cd.network_score desc nulls last ,
d.nm_sort,
c.nm_sort
limit ?;
Times Reported Time consuming queries #3
Day
Hour
Count
Duration
Avg duration
Dec 28 00 8 13s724ms 1s715ms 01 5 8s265ms 1s653ms 02 4 6s220ms 1s555ms 03 10 16s52ms 1s605ms 04 3 4s405ms 1s468ms 05 5 7s501ms 1s500ms 06 8 13s760ms 1s720ms 07 4 6s217ms 1s554ms 08 7 10s685ms 1s526ms 09 4 6s779ms 1s694ms 10 6 10s515ms 1s752ms 11 3 5s544ms 1s848ms 12 8 12s981ms 1s622ms 13 9 14s294ms 1s588ms 14 6 9s885ms 1s647ms 15 7 10s561ms 1s508ms 16 5 8s32ms 1s606ms 17 4 6s797ms 1s699ms 18 7 10s673ms 1s524ms 19 8 13s576ms 1s697ms 20 5 8s63ms 1s612ms 21 7 11s156ms 1s593ms 22 7 10s687ms 1s526ms 23 6 9s265ms 1s544ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m9s - Times executed: 43 ]
x Hide
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-12-28 19:11:03
Duration: 2s401ms
Bind query: yes
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-12-28 01:45:07
Duration: 2s95ms
Bind query: yes
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-12-28 00:30:15
Duration: 1s934ms
Bind query: yes
x Hide
4
139
Details
2m32s
1s21ms
1s229ms
1s97ms
select distinct
stressorterm.nm as chemnm,
stressorterm.nm_html as chemnmhtml,
stressorterm.nm_sort as chemnmsort,
stressorterm.acc_txt as chemacc,
(
select
string_agg ( distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?) ) as stressorsrctypenm,
stressor.src_details as stressorsrcdetails,
stressor.sample_qty as stressorsampleqty,
stressor.note as stressornote,
receptor.qty as nbrreceptors,
receptor.description as receptors,
receptor.note as receptornotes,
receptorterm.nm || ? || (
select
cd
from
object_type
where
id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms,
(
select
string_agg ( distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?) ) as smokerstatus,
receptor.age as agerange,
receptor.age_uom_nm as ageuomnm,
receptor.age_qualifier_nm as agequalifiernm,
receptor.gender_nm as gendernmsearch,
receptor.id receptorid,
(
select
string_agg ( pct || ? || gender_nm || ? || gender_nm_html, ?)
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderdetails,
(
select
string_agg ( distinct receptorrace.race_nm || ? || receptorrace.pct, ?) ) as receptorrace,
(
select
string_agg ( distinct eventassaymethod.nm, ?) ) as assaymethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumacctxt,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr,
event.detection_limit as detectionlimit,
event.detection_limit_uom as detectionlimituom,
event.detection_freq as detectionfreq,
event.note as eventnote,
(
select
string_agg ( distinct eventlocation.geographic_region_nm, ?) ) as stateorprovince,
(
select
string_agg ( distinct eventlocation.locality_txt, ?) ) as localitytxt,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
exposuremarkerterm.nm || ? || (
select
cd
from
object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers,
event.exp_marker_lvl as assaylevel,
assay_uom as measurement,
assay_measurement_stat as measurementstat,
assay_note as assaynote,
eiot.description as outcomerltnp,
diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield,
outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm,
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrauthorstxt,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
outcome.note as outcomenote,
eventlocation.exp_event_id as eventid,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
inner join reference r on e.reference_id = r.id
left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id
left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id
left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id
left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id
left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
left outer join country on eventlocation.country_id = country.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt
and e.reference_acc_db_id = referenceexp.reference_acc_db_id
left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id
where
exposuremarkerterm.id = ?
or receptorterm.id = ?
group by
chemnm,
chemnmhtml,
chemnmsort,
chemacc,
stressorsrcdetails,
stressorsampleqty,
stressornote,
receptorterms,
medium,
mediumacctxt,
assayedmarkers,
assaylevel,
measurement,
measurementstat,
assaynote,
outcomerltnp,
diseasefield,
phenotypefield,
phenotypeactiondegreetypenm,
ref,
r.abbr_authors_txt,
collectionstartandendyr,
receptorid,
detectionlimit,
detectionlimituom,
detectionfreq,
eventnote,
outcomenote,
eventid
order by
chemnmsort
limit ?;
Times Reported Time consuming queries #4
Day
Hour
Count
Duration
Avg duration
Dec 28 00 2 2s160ms 1s80ms 01 6 6s554ms 1s92ms 02 6 6s567ms 1s94ms 03 8 8s543ms 1s67ms 04 10 10s977ms 1s97ms 05 7 7s984ms 1s140ms 06 6 6s536ms 1s89ms 07 3 3s272ms 1s90ms 08 4 4s427ms 1s106ms 09 4 4s399ms 1s99ms 10 3 3s311ms 1s103ms 11 2 2s259ms 1s129ms 12 5 5s584ms 1s116ms 13 8 8s825ms 1s103ms 14 4 4s505ms 1s126ms 15 8 9s2ms 1s125ms 16 10 10s921ms 1s92ms 17 2 2s148ms 1s74ms 18 8 8s858ms 1s107ms 19 11 11s804ms 1s73ms 20 6 6s416ms 1s69ms 21 4 4s312ms 1s78ms 22 5 5s535ms 1s107ms 23 7 7s629ms 1s89ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 47s283ms - Times executed: 43 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
exposureMarkerTerm.id = '1583515'
or receptorTerm.id = '1583515'
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 18:39:16
Duration: 1s229ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
exposureMarkerTerm.id = '1442960'
or receptorTerm.id = '1442960'
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 05:38:37
Duration: 1s216ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
exposureMarkerTerm.id = '1442960'
or receptorTerm.id = '1442960'
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 05:43:39
Duration: 1s209ms
Bind query: yes
x Hide
5
112
Details
8m39s
1s1ms
5s630ms
4s636ms
select
d.abbr dagabbr,
d.nm dagnm,
gt.level_min_no daglevelmin,
gt.nm gonm,
gt.nm_html gonmhtml,
gt.acc_txt goacc,
gt.object_id goid,
te.corrected_p_val pvalcorrected,
te.raw_p_val pvalraw,
te.target_match_qty targetmatchqty,
te.target_total_qty targettotalqty,
te.background_match_qty backgroundmatchqty,
te.background_total_qty backgroundtotalqty,
count ( * ) over ( ) fullrowcount
from
term_enrichment te
inner join dag_node gt on te.enriched_term_id = gt.object_id
inner join dag d on gt.dag_id = d.id
where
te.term_id = ?
and te.enriched_object_type_id = ?
order by
te.corrected_p_val,
d.abbr,
gt.nm_sort
limit ?;
Times Reported Time consuming queries #5
Day
Hour
Count
Duration
Avg duration
Dec 28 00 4 19s773ms 4s943ms 01 4 19s272ms 4s818ms 02 3 15s398ms 5s132ms 03 6 29s795ms 4s965ms 04 4 19s537ms 4s884ms 05 7 33s979ms 4s854ms 06 7 33s681ms 4s811ms 07 6 29s952ms 4s992ms 08 4 19s599ms 4s899ms 09 8 31s430ms 3s928ms 10 5 23s812ms 4s762ms 11 6 25s77ms 4s179ms 12 2 9s985ms 4s992ms 13 2 9s549ms 4s774ms 14 6 24s938ms 4s156ms 15 5 20s721ms 4s144ms 16 1 4s839ms 4s839ms 17 8 39s28ms 4s878ms 18 3 10s775ms 3s591ms 19 3 14s797ms 4s932ms 20 4 18s969ms 4s742ms 21 6 29s333ms 4s888ms 22 1 5s540ms 5s540ms 23 7 29s485ms 4s212ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 2m33s - Times executed: 33 ]
x Hide
SELECT
/* ChemGODAO */
d.abbr dagAbbr,
d.nm dagNm,
gt.level_min_no dagLevelMin,
gt.nm gonm,
gt.nm_html gonmhtml,
gt.acc_txt goacc,
gt.object_id goid,
te.corrected_p_val pValCorrected,
te.raw_p_val pValRaw,
te.target_match_qty targetmatchqty,
te.target_total_qty targettotalqty,
te.background_match_qty backgroundmatchqty,
te.background_total_qty backgroundtotalqty,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term_enrichment te
INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id
INNER JOIN dag d ON gt.dag_id = d.id
WHERE
te.term_id = '1440387'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-12-28 02:04:10
Duration: 5s630ms
Bind query: yes
SELECT
/* ChemGODAO */
d.abbr dagAbbr,
d.nm dagNm,
gt.level_min_no dagLevelMin,
gt.nm gonm,
gt.nm_html gonmhtml,
gt.acc_txt goacc,
gt.object_id goid,
te.corrected_p_val pValCorrected,
te.raw_p_val pValRaw,
te.target_match_qty targetmatchqty,
te.target_total_qty targettotalqty,
te.background_match_qty backgroundmatchqty,
te.background_total_qty backgroundtotalqty,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term_enrichment te
INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id
INNER JOIN dag d ON gt.dag_id = d.id
WHERE
te.term_id = '1413592'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-12-28 07:05:21
Duration: 5s600ms
Bind query: yes
SELECT
/* ChemGODAO */
d.abbr dagAbbr,
d.nm dagNm,
gt.level_min_no dagLevelMin,
gt.nm gonm,
gt.nm_html gonmhtml,
gt.acc_txt goacc,
gt.object_id goid,
te.corrected_p_val pValCorrected,
te.raw_p_val pValRaw,
te.target_match_qty targetmatchqty,
te.target_total_qty targettotalqty,
te.background_match_qty backgroundmatchqty,
te.background_total_qty backgroundtotalqty,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term_enrichment te
INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id
INNER JOIN dag d ON gt.dag_id = d.id
WHERE
te.term_id = '1436745'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-12-28 03:10:42
Duration: 5s557ms
Bind query: yes
x Hide
6
49
Details
58s852ms
1s110ms
1s792ms
1s201ms
select distinct
stressorterm.nm as chemnm,
stressorterm.nm_html as chemnmhtml,
stressorterm.nm_sort as chemnmsort,
stressorterm.acc_txt as chemacc,
(
select
string_agg ( distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?) ) as stressorsrctypenm,
stressor.src_details as stressorsrcdetails,
stressor.sample_qty as stressorsampleqty,
stressor.note as stressornote,
receptor.qty as nbrreceptors,
receptor.description as receptors,
receptor.note as receptornotes,
receptorterm.nm || ? || (
select
cd
from
object_type
where
id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms,
(
select
string_agg ( distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?) ) as smokerstatus,
receptor.age as agerange,
receptor.age_uom_nm as ageuomnm,
receptor.age_qualifier_nm as agequalifiernm,
receptor.gender_nm as gendernmsearch,
receptor.id receptorid,
(
select
string_agg ( pct || ? || gender_nm || ? || gender_nm_html, ?)
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderdetails,
(
select
string_agg ( distinct receptorrace.race_nm || ? || receptorrace.pct, ?) ) as receptorrace,
(
select
string_agg ( distinct eventassaymethod.nm, ?) ) as assaymethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumacctxt,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr,
event.detection_limit as detectionlimit,
event.detection_limit_uom as detectionlimituom,
event.detection_freq as detectionfreq,
event.note as eventnote,
(
select
string_agg ( distinct eventlocation.geographic_region_nm, ?) ) as stateorprovince,
(
select
string_agg ( distinct eventlocation.locality_txt, ?) ) as localitytxt,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
exposuremarkerterm.nm || ? || (
select
cd
from
object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers,
event.exp_marker_lvl as assaylevel,
assay_uom as measurement,
assay_measurement_stat as measurementstat,
assay_note as assaynote,
eiot.description as outcomerltnp,
diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield,
outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm,
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrauthorstxt,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
outcome.note as outcomenote,
eventlocation.exp_event_id as eventid,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
inner join reference r on e.reference_id = r.id
left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id
left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id
left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id
left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id
left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
left outer join country on eventlocation.country_id = country.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt
and e.reference_acc_db_id = referenceexp.reference_acc_db_id
left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
or receptorterm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
group by
chemnm,
chemnmhtml,
chemnmsort,
chemacc,
stressorsrcdetails,
stressorsampleqty,
stressornote,
receptorterms,
medium,
mediumacctxt,
assayedmarkers,
assaylevel,
measurement,
measurementstat,
assaynote,
outcomerltnp,
diseasefield,
phenotypefield,
phenotypeactiondegreetypenm,
ref,
r.abbr_authors_txt,
collectionstartandendyr,
receptorid,
detectionlimit,
detectionlimituom,
detectionfreq,
eventnote,
outcomenote,
eventid
order by
chemnmsort
limit ?;
Times Reported Time consuming queries #6
Day
Hour
Count
Duration
Avg duration
Dec 28 01 1 1s172ms 1s172ms 02 3 3s580ms 1s193ms 03 1 1s180ms 1s180ms 04 1 1s197ms 1s197ms 05 4 4s888ms 1s222ms 06 2 2s926ms 1s463ms 07 4 4s744ms 1s186ms 10 2 2s360ms 1s180ms 11 1 1s205ms 1s205ms 12 2 2s430ms 1s215ms 13 2 2s342ms 1s171ms 14 3 3s594ms 1s198ms 15 1 1s131ms 1s131ms 16 3 3s553ms 1s184ms 17 3 3s716ms 1s238ms 18 1 1s214ms 1s214ms 19 3 3s559ms 1s186ms 20 3 3s459ms 1s153ms 21 3 3s457ms 1s152ms 22 3 3s626ms 1s208ms 23 3 3s509ms 1s169ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 11s787ms - Times executed: 10 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1236377')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1236377')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 06:53:28
Duration: 1s792ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1221432')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1221432')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 05:43:38
Duration: 1s306ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1239829')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1239829')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 17:59:31
Duration: 1s294ms
Bind query: yes
x Hide
7
40
Details
1m5s
1s1ms
2s512ms
1s632ms
select
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
(
select
string_agg ( distinct stressorterm.nm || ? || (
select
cd
from object_type
where
id = stressorterm.object_type_id) || ? || stressorterm.nm_html || ? || stressorterm.acc_txt || ? || stressorterm.acc_db_cd, ?) ) as stressoragents,
(
select
string_agg ( distinct coalesce ( receptorterm.nm, ?) || ? || coalesce ( (
select
cd
from object_type
where
id = receptorterm.object_type_id) , ?) || ? || coalesce ( receptorterm.nm_html, ?) || ? || coalesce ( receptorterm.acc_txt, ?) || ? || coalesce ( receptorterm.acc_db_cd, ?) || ? || receptor.description, ?) ) as receptors,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
(
select
string_agg ( distinct location.locality_txt, ?) ) as localities,
(
select
string_agg ( distinct event.medium_nm || ? || coalesce ( event.medium_term_acc_txt, ?) , ?) ) as assaymediums,
(
select
string_agg ( distinct exposuremarkerterm.nm || ? || (
select
cd
from object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd, ?) ) as assayedmarkers,
(
select
string_agg ( distinct diseaseterm.nm || ? || (
select
cd
from object_type
where
id = diseaseterm.object_type_id) || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd, ?) ) as diseases,
(
select
string_agg ( distinct phenotypeterm.nm || ? || (
select
cd
from object_type
where
id = phenotypeterm.object_type_id) || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd, ?) ) as phenotypes,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
re.author_summary summary,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join reference r on e.reference_id = r.id
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join exp_event_location location on e.exp_event_id = location.exp_event_id
left outer join country on location.country_id = country.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
inner join reference_exp re on e.reference_id = re.reference_id
left outer join exp_study_factor expstudyfactor on re.id = expstudyfactor.reference_exp_id
where
e.reference_id = any ( array (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like ?)
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressoragents
limit ? offset ?;
Times Reported Time consuming queries #7
Day
Hour
Count
Duration
Avg duration
Dec 28 01 3 3s942ms 1s314ms 02 1 1s1ms 1s1ms 03 1 2s407ms 2s407ms 04 2 3s63ms 1s531ms 05 2 3s917ms 1s958ms 06 2 4s912ms 2s456ms 07 3 4s60ms 1s353ms 08 2 3s449ms 1s724ms 09 1 1s774ms 1s774ms 10 2 2s595ms 1s297ms 11 4 6s258ms 1s564ms 14 1 2s512ms 2s512ms 15 6 9s68ms 1s511ms 16 2 3s299ms 1s649ms 17 1 1s429ms 1s429ms 18 1 1s882ms 1s882ms 20 1 1s124ms 1s124ms 21 1 1s351ms 1s351ms 22 1 2s495ms 2s495ms 23 3 4s754ms 1s584ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 16s505ms - Times executed: 13 ]
x Hide
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436734')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436734')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 OFFSET 350 ;
Date: 2024-12-28 14:03:13
Duration: 2s512ms
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436737')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436737')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 OFFSET 300 ;
Date: 2024-12-28 22:17:50
Duration: 2s495ms
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436737')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436737')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 OFFSET 100 ;
Date: 2024-12-28 15:13:07
Duration: 2s483ms
Bind query: yes
x Hide
8
39
Details
2m12s
1s11ms
9s746ms
3s402ms
select distinct
stressorterm.nm as chemnm,
stressorterm.nm_html as chemnmhtml,
stressorterm.nm_sort as chemnmsort,
stressorterm.acc_txt as chemacc,
(
select
string_agg ( distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?) ) as stressorsrctypenm,
stressor.src_details as stressorsrcdetails,
stressor.sample_qty as stressorsampleqty,
stressor.note as stressornote,
receptor.qty as nbrreceptors,
receptor.description as receptors,
receptor.note as receptornotes,
receptorterm.nm || ? || (
select
cd
from
object_type
where
id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms,
(
select
string_agg ( distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?) ) as smokerstatus,
receptor.age as agerange,
receptor.age_uom_nm as ageuomnm,
receptor.age_qualifier_nm as agequalifiernm,
receptor.gender_nm as gendernmsearch,
receptor.id receptorid,
(
select
string_agg ( pct || ? || gender_nm || ? || gender_nm_html, ?)
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderdetails,
(
select
string_agg ( distinct receptorrace.race_nm || ? || receptorrace.pct, ?) ) as receptorrace,
(
select
string_agg ( distinct eventassaymethod.nm, ?) ) as assaymethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumacctxt,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr,
event.detection_limit as detectionlimit,
event.detection_limit_uom as detectionlimituom,
event.detection_freq as detectionfreq,
event.note as eventnote,
(
select
string_agg ( distinct eventlocation.geographic_region_nm, ?) ) as stateorprovince,
(
select
string_agg ( distinct eventlocation.locality_txt, ?) ) as localitytxt,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
exposuremarkerterm.nm || ? || (
select
cd
from
object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers,
event.exp_marker_lvl as assaylevel,
assay_uom as measurement,
assay_measurement_stat as measurementstat,
assay_note as assaynote,
eiot.description as outcomerltnp,
diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield,
outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm,
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrauthorstxt,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
outcome.note as outcomenote,
eventlocation.exp_event_id as eventid,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
inner join reference r on e.reference_id = r.id
left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id
left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id
left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id
left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id
left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
left outer join country on eventlocation.country_id = country.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt
and e.reference_acc_db_id = referenceexp.reference_acc_db_id
left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id
where
stressorterm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
or exposuremarkerterm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
group by
chemnm,
chemnmhtml,
chemnmsort,
chemacc,
stressorsrcdetails,
stressorsampleqty,
stressornote,
receptorterms,
medium,
mediumacctxt,
assayedmarkers,
assaylevel,
measurement,
measurementstat,
assaynote,
outcomerltnp,
diseasefield,
phenotypefield,
phenotypeactiondegreetypenm,
ref,
r.abbr_authors_txt,
collectionstartandendyr,
receptorid,
detectionlimit,
detectionlimituom,
detectionfreq,
eventnote,
outcomenote,
eventid
order by
chemnmsort
limit ?;
Times Reported Time consuming queries #8
Day
Hour
Count
Duration
Avg duration
Dec 28 01 3 17s379ms 5s793ms 02 3 7s851ms 2s617ms 03 2 2s793ms 1s396ms 05 2 5s766ms 2s883ms 07 3 12s459ms 4s153ms 08 1 5s504ms 5s504ms 10 1 3s776ms 3s776ms 11 2 9s553ms 4s776ms 12 1 4s864ms 4s864ms 13 2 2s834ms 1s417ms 14 1 4s32ms 4s32ms 15 3 9s655ms 3s218ms 16 2 5s448ms 2s724ms 17 2 10s388ms 5s194ms 18 1 1s35ms 1s35ms 19 1 1s286ms 1s286ms 20 3 11s578ms 3s859ms 21 1 1s262ms 1s262ms 22 3 10s511ms 3s503ms 23 2 4s726ms 2s363ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 32s570ms - Times executed: 10 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
stressorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1398604')
or exposureMarkerTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1398604')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 01:42:38
Duration: 9s746ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
stressorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1427841')
or exposureMarkerTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1427841')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 22:05:24
Duration: 6s700ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
stressorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1427841')
or exposureMarkerTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1427841')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 01:47:27
Duration: 6s621ms
Bind query: yes
x Hide
9
34
Details
41s374ms
1s118ms
1s450ms
1s216ms
select distinct
stressorterm.nm as chemnm,
stressorterm.nm_html as chemnmhtml,
stressorterm.nm_sort as chemnmsort,
stressorterm.acc_txt as chemacc,
(
select
string_agg ( distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?) ) as stressorsrctypenm,
stressor.src_details as stressorsrcdetails,
stressor.sample_qty as stressorsampleqty,
stressor.note as stressornote,
receptor.qty as nbrreceptors,
receptor.description as receptors,
receptor.note as receptornotes,
receptorterm.nm || ? || (
select
cd
from
object_type
where
id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms,
(
select
string_agg ( distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?) ) as smokerstatus,
receptor.age as agerange,
receptor.age_uom_nm as ageuomnm,
receptor.age_qualifier_nm as agequalifiernm,
receptor.gender_nm as gendernmsearch,
receptor.id receptorid,
(
select
string_agg ( pct || ? || gender_nm || ? || gender_nm_html, ?)
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderdetails,
(
select
string_agg ( distinct receptorrace.race_nm || ? || receptorrace.pct, ?) ) as receptorrace,
(
select
string_agg ( distinct eventassaymethod.nm, ?) ) as assaymethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumacctxt,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr,
event.detection_limit as detectionlimit,
event.detection_limit_uom as detectionlimituom,
event.detection_freq as detectionfreq,
event.note as eventnote,
(
select
string_agg ( distinct eventlocation.geographic_region_nm, ?) ) as stateorprovince,
(
select
string_agg ( distinct eventlocation.locality_txt, ?) ) as localitytxt,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
exposuremarkerterm.nm || ? || (
select
cd
from
object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers,
event.exp_marker_lvl as assaylevel,
assay_uom as measurement,
assay_measurement_stat as measurementstat,
assay_note as assaynote,
eiot.description as outcomerltnp,
diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield,
outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm,
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrauthorstxt,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
outcome.note as outcomenote,
eventlocation.exp_event_id as eventid,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
inner join reference r on e.reference_id = r.id
left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id
left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id
left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id
left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id
left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
left outer join country on eventlocation.country_id = country.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt
and e.reference_acc_db_id = referenceexp.reference_acc_db_id
left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
or receptorterm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
group by
chemnm,
chemnmhtml,
chemnmsort,
chemacc,
stressorsrcdetails,
stressorsampleqty,
stressornote,
receptorterms,
medium,
mediumacctxt,
assayedmarkers,
assaylevel,
measurement,
measurementstat,
assaynote,
outcomerltnp,
diseasefield,
phenotypefield,
phenotypeactiondegreetypenm,
ref,
r.abbr_authors_txt,
collectionstartandendyr,
receptorid,
detectionlimit,
detectionlimituom,
detectionfreq,
eventnote,
outcomenote,
eventid
order by
chemnmsort
limit ?;
Times Reported Time consuming queries #9
Day
Hour
Count
Duration
Avg duration
Dec 28 01 2 2s449ms 1s224ms 02 1 1s189ms 1s189ms 03 2 2s392ms 1s196ms 05 4 5s14ms 1s253ms 06 3 3s548ms 1s182ms 07 1 1s218ms 1s218ms 08 1 1s301ms 1s301ms 11 2 2s368ms 1s184ms 12 3 3s756ms 1s252ms 13 1 1s313ms 1s313ms 15 2 2s447ms 1s223ms 16 2 2s390ms 1s195ms 17 1 1s222ms 1s222ms 18 1 1s450ms 1s450ms 19 1 1s123ms 1s123ms 20 1 1s121ms 1s121ms 21 4 4s694ms 1s173ms 22 1 1s162ms 1s162ms 23 1 1s210ms 1s210ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 15s336ms - Times executed: 13 ]
[ User: qaeu - Total duration: 1s347ms - Times executed: 1 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2091373')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2091373')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 18:46:22
Duration: 1s450ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2080719')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2080719')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 05:43:41
Duration: 1s347ms
Database: ctdprd51
User: qaeu
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2080719')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2080719')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-12-28 05:38:38
Duration: 1s342ms
Bind query: yes
x Hide
10
33
Details
1m56s
1s44ms
10s368ms
3s544ms
select
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
g.nm genesymbol,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
gd.network_score networkscore,
gd.indirect_chem_qty inferredcount,
gd.reference_qty referencecount,
gd.exposure_reference_qty exposurereferencecount,
case when gd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
gene_disease_axn a
where
a.gene_id = gd.gene_id
and a.disease_id = gd.disease_id)
else
null
end actiontypes
from
gene_disease gd
inner join term g on gd.gene_id = g.id
inner join term d on gd.disease_id = d.id
where
gd.disease_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
gd.network_score desc nulls last ,
g.nm_sort,
d.nm_sort
limit ?;
Times Reported Time consuming queries #10
Day
Hour
Count
Duration
Avg duration
Dec 28 00 3 11s290ms 3s763ms 01 1 1s49ms 1s49ms 02 5 26s313ms 5s262ms 04 2 7s408ms 3s704ms 06 2 13s388ms 6s694ms 08 6 13s754ms 2s292ms 10 2 3s368ms 1s684ms 13 3 11s287ms 3s762ms 14 1 3s114ms 3s114ms 15 1 5s555ms 5s555ms 18 1 2s474ms 2s474ms 19 2 5s584ms 2s792ms 20 1 3s124ms 3s124ms 22 2 7s419ms 3s709ms 23 1 1s840ms 1s840ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 37s740ms - Times executed: 9 ]
x Hide
SELECT
/* DiseaseGeneAssnsDAO */
d.nm diseaseNm,
d.acc_txt diseaseAcc,
d.acc_db_cd diseaseAccDbCd,
d.id diseaseId,
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
gd.network_score networkScore,
gd.indirect_chem_qty inferredCount,
gd.reference_qty referenceCount,
gd.exposure_reference_qty exposureReferenceCount,
CASE WHEN gd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gd.gene_id
AND a.disease_id = gd.disease_id)
ELSE
NULL
END actionTypes
FROM
gene_disease gd
INNER JOIN term g ON gd.gene_id = g.id
INNER JOIN term d ON gd.disease_id = d.id
WHERE
gd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2089378')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-12-28 06:27:09
Duration: 10s368ms
Bind query: yes
SELECT
/* DiseaseGeneAssnsDAO */
d.nm diseaseNm,
d.acc_txt diseaseAcc,
d.acc_db_cd diseaseAccDbCd,
d.id diseaseId,
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
gd.network_score networkScore,
gd.indirect_chem_qty inferredCount,
gd.reference_qty referenceCount,
gd.exposure_reference_qty exposureReferenceCount,
CASE WHEN gd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gd.gene_id
AND a.disease_id = gd.disease_id)
ELSE
NULL
END actionTypes
FROM
gene_disease gd
INNER JOIN term g ON gd.gene_id = g.id
INNER JOIN term d ON gd.disease_id = d.id
WHERE
gd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2089378')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-12-28 02:35:54
Duration: 10s368ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* DiseaseGeneAssnsDAO */
d.nm diseaseNm,
d.acc_txt diseaseAcc,
d.acc_db_cd diseaseAccDbCd,
d.id diseaseId,
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
gd.network_score networkScore,
gd.indirect_chem_qty inferredCount,
gd.reference_qty referenceCount,
gd.exposure_reference_qty exposureReferenceCount,
CASE WHEN gd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gd.gene_id
AND a.disease_id = gd.disease_id)
ELSE
NULL
END actionTypes
FROM
gene_disease gd
INNER JOIN term g ON gd.gene_id = g.id
INNER JOIN term d ON gd.disease_id = d.id
WHERE
gd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2089378')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-12-28 02:30:20
Duration: 10s266ms
Bind query: yes
x Hide
11
31
Details
1m4s
1s308ms
3s352ms
2s70ms
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
associatedterm.id = any ( array ( (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) )
and ptr.term_object_type_id = ?
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ? offset ?;
Times Reported Time consuming queries #11
Day
Hour
Count
Duration
Avg duration
Dec 28 00 1 2s45ms 2s45ms 02 2 3s244ms 1s622ms 03 4 8s198ms 2s49ms 04 1 1s637ms 1s637ms 05 2 4s14ms 2s7ms 06 2 4s217ms 2s108ms 09 1 1s334ms 1s334ms 10 2 5s117ms 2s558ms 11 3 6s240ms 2s80ms 12 1 1s645ms 1s645ms 14 1 2s833ms 2s833ms 15 1 1s865ms 1s865ms 16 1 3s281ms 3s281ms 17 1 2s97ms 2s97ms 18 1 3s352ms 3s352ms 19 2 4s559ms 2s279ms 20 2 4s99ms 2s49ms 22 1 1s382ms 1s382ms 23 2 3s10ms 1s505ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 20s161ms - Times executed: 10 ]
x Hide
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1438258') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 OFFSET 106500 ;
Date: 2024-12-28 18:50:29
Duration: 3s352ms
Bind query: yes
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1438258') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 OFFSET 200 ;
Date: 2024-12-28 16:17:19
Duration: 3s281ms
Bind query: yes
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1438258') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 OFFSET 250 ;
Date: 2024-12-28 10:56:47
Duration: 3s247ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
12
29
Details
1m54s
3s721ms
6s158ms
3s945ms
select
coalesce ( st.alt_nm, t.nm) slimtermnm,
(
select
count ( * )
from
slim_term_mapping stm
inner join chem_disease cd on cd.disease_id = stm.mapped_term_id
where
cd.chem_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
and stm.slim_term_id = st.slim_term_id
and cd.curated_reference_qty > ?) curatedcount,
(
select
count ( * )
from
slim_term_mapping stm
inner join chem_disease cd on cd.disease_id = stm.mapped_term_id
where
cd.chem_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
and stm.slim_term_id = st.slim_term_id
and cd.indirect_gene_qty > ?) inferredcount
from
slim_term st
inner join term t on st.slim_term_id = t.id
where
st.slim_id = ?
order by
?;
Times Reported Time consuming queries #12
Day
Hour
Count
Duration
Avg duration
Dec 28 00 2 7s563ms 3s781ms 03 4 15s447ms 3s861ms 08 1 3s749ms 3s749ms 09 2 7s801ms 3s900ms 10 3 11s231ms 3s743ms 11 2 7s608ms 3s804ms 12 1 3s852ms 3s852ms 13 2 7s930ms 3s965ms 15 3 14s398ms 4s799ms 16 2 7s758ms 3s879ms 17 2 7s837ms 3s918ms 19 1 3s808ms 3s808ms 20 3 11s478ms 3s826ms 22 1 3s949ms 3s949ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 29s370ms - Times executed: 7 ]
x Hide
SELECT
/* ChemDiseasesBySlimDAO */
COALESCE ( st.alt_nm, t.nm) slimTermNm,
(
SELECT
COUNT ( * )
FROM
slim_term_mapping stm
INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1436737')
AND stm.slim_term_id = st.slim_term_id
AND cd.curated_reference_qty > 0 ) curatedCount,
(
SELECT
COUNT ( * )
FROM
slim_term_mapping stm
INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1436737')
AND stm.slim_term_id = st.slim_term_id
AND cd.indirect_gene_qty > 0 ) inferredCount
FROM
slim_term st
INNER JOIN term t ON st.slim_term_id = t.id
WHERE
st.slim_id = 1
ORDER BY
1 ;
Date: 2024-12-28 15:59:09
Duration: 6s158ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemDiseasesBySlimDAO */
COALESCE ( st.alt_nm, t.nm) slimTermNm,
(
SELECT
COUNT ( * )
FROM
slim_term_mapping stm
INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1402395')
AND stm.slim_term_id = st.slim_term_id
AND cd.curated_reference_qty > 0 ) curatedCount,
(
SELECT
COUNT ( * )
FROM
slim_term_mapping stm
INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1402395')
AND stm.slim_term_id = st.slim_term_id
AND cd.indirect_gene_qty > 0 ) inferredCount
FROM
slim_term st
INNER JOIN term t ON st.slim_term_id = t.id
WHERE
st.slim_id = 1
ORDER BY
1 ;
Date: 2024-12-28 15:37:07
Duration: 4s373ms
Bind query: yes
SELECT
/* ChemDiseasesBySlimDAO */
COALESCE ( st.alt_nm, t.nm) slimTermNm,
(
SELECT
COUNT ( * )
FROM
slim_term_mapping stm
INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1415944')
AND stm.slim_term_id = st.slim_term_id
AND cd.curated_reference_qty > 0 ) curatedCount,
(
SELECT
COUNT ( * )
FROM
slim_term_mapping stm
INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1415944')
AND stm.slim_term_id = st.slim_term_id
AND cd.indirect_gene_qty > 0 ) inferredCount
FROM
slim_term st
INNER JOIN term t ON st.slim_term_id = t.id
WHERE
st.slim_id = 1
ORDER BY
1 ;
Date: 2024-12-28 13:32:09
Duration: 4s67ms
Bind query: yes
x Hide
13
26
Details
16m4s
1s913ms
1m24s
37s79ms
select
r.id,
r.abbr_authors_txt authors,
r.title,
r.core_citation_txt citation,
r.pub_start_yr yr,
r.acc_txt refacc,
r.has_diseases
or r.has_ixns
or r.has_exposures
or r.has_phenotypes iscurated,
r.has_exposures,
count ( * ) over ( ) fullrowcount
from
reference r
where
r.id in (
select
reference_id
from
term_reference
where
term_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id = ?) )
order by
r.sort_txt
limit ?;
Times Reported Time consuming queries #13
Day
Hour
Count
Duration
Avg duration
Dec 28 00 1 1m15s 1m15s 01 1 1m10s 1m10s 02 2 1m18s 39s446ms 03 1 1s913ms 1s913ms 04 1 42s927ms 42s927ms 05 1 2s266ms 2s266ms 06 5 1m55s 23s145ms 07 1 1m23s 1m23s 09 1 52s657ms 52s657ms 12 1 1s974ms 1s974ms 14 1 35s263ms 35s263ms 15 1 2s24ms 2s24ms 16 1 2s63ms 2s63ms 17 2 1m57s 58s996ms 18 2 2m1s 1m 19 2 1m58s 59s313ms 20 1 1s942ms 1s942ms 21 1 38s963ms 38s963ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 5m9s - Times executed: 11 ]
x Hide
SELECT
/* RefsDAO */
r.id,
r.abbr_authors_txt authors,
r.title,
r.core_citation_txt citation,
r.pub_start_yr yr,
r.acc_txt refAcc,
r.has_diseases
or r.has_ixns
or r.has_exposures
or r.has_phenotypes iscurated,
r.has_exposures,
COUNT ( * ) OVER ( ) fullRowCount
FROM
reference r
WHERE
r.id IN (
select
reference_id
from
term_reference
where
term_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '1276072') )
ORDER BY
r.sort_txt
LIMIT 50 ;
Date: 2024-12-28 19:42:36
Duration: 1m24s
Bind query: yes
SELECT
/* RefsDAO */
r.id,
r.abbr_authors_txt authors,
r.title,
r.core_citation_txt citation,
r.pub_start_yr yr,
r.acc_txt refAcc,
r.has_diseases
or r.has_ixns
or r.has_exposures
or r.has_phenotypes iscurated,
r.has_exposures,
COUNT ( * ) OVER ( ) fullRowCount
FROM
reference r
WHERE
r.id IN (
select
reference_id
from
term_reference
where
term_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '1276072') )
ORDER BY
r.sort_txt
LIMIT 50 ;
Date: 2024-12-28 07:42:12
Duration: 1m23s
Bind query: yes
SELECT
/* RefsDAO */
r.id,
r.abbr_authors_txt authors,
r.title,
r.core_citation_txt citation,
r.pub_start_yr yr,
r.acc_txt refAcc,
r.has_diseases
or r.has_ixns
or r.has_exposures
or r.has_phenotypes iscurated,
r.has_exposures,
COUNT ( * ) OVER ( ) fullRowCount
FROM
reference r
WHERE
r.id IN (
select
reference_id
from
term_reference
where
term_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '1276072') )
ORDER BY
r.sort_txt
LIMIT 50 ;
Date: 2024-12-28 17:19:55
Duration: 1m21s
Bind query: yes
x Hide
14
19
Details
44s888ms
1s393ms
3s517ms
2s362ms
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
associatedterm.id = any ( array ( (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) )
and ptr.term_object_type_id = ?
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort;
Times Reported Time consuming queries #14
Day
Hour
Count
Duration
Avg duration
Dec 28 01 4 10s490ms 2s622ms 03 2 4s429ms 2s214ms 04 1 1s722ms 1s722ms 05 2 3s889ms 1s944ms 06 2 4s399ms 2s199ms 09 1 1s393ms 1s393ms 11 2 5s367ms 2s683ms 15 2 5s501ms 2s750ms 16 2 5s747ms 2s873ms 21 1 1s946ms 1s946ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 17s187ms - Times executed: 7 ]
x Hide
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1438258') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort;
Date: 2024-12-28 15:41:22
Duration: 3s517ms
Bind query: yes
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1438258') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort;
Date: 2024-12-28 16:18:22
Duration: 3s508ms
Bind query: yes
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
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1438258') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort;
Date: 2024-12-28 01:58:14
Duration: 3s473ms
Bind query: yes
x Hide
15
19
Details
25s135ms
1s268ms
1s443ms
1s322ms
select
t.nm,
t.nm_html nmhtml,
t.secondary_nm secondarynm,
t.acc_txt acc,
? || t.nm accquerystr,
t.has_chems haschems,
t.has_diseases hasdiseases,
t.has_exposures hasexposures,
t.has_phenotypes hasphenotypes,
count ( * ) over ( ) fullrowcount
from
term t
where
t.object_type_id = ?
and regexp_replace ( upper ( substring ( t.nm, ?, ?) ) , ?, ?) = ?
order by
t.nm_sort
limit ?;
Times Reported Time consuming queries #15
Day
Hour
Count
Duration
Avg duration
Dec 28 00 1 1s276ms 1s276ms 01 1 1s443ms 1s443ms 02 1 1s315ms 1s315ms 03 1 1s292ms 1s292ms 04 1 1s410ms 1s410ms 05 2 2s703ms 1s351ms 06 1 1s280ms 1s280ms 07 1 1s285ms 1s285ms 08 2 2s578ms 1s289ms 12 1 1s268ms 1s268ms 13 1 1s291ms 1s291ms 14 3 3s844ms 1s281ms 15 1 1s404ms 1s404ms 22 2 2s737ms 1s368ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 3s856ms - Times executed: 3 ]
x Hide
SELECT
/* GeneBrowseTermsDAO */
t.nm,
t.nm_html nmHtml,
t.secondary_nm secondaryNm,
t.acc_txt acc,
'name:' || t.nm accQueryStr,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term t
WHERE
t.object_type_id = '4'
AND REGEXP_REPLACE ( UPPER ( SUBSTRING ( t.nm, 1 , 1 ) ) , '[^A-Z]', '#') = 'A'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-12-28 01:27:27
Duration: 1s443ms
Bind query: yes
SELECT
/* GeneBrowseTermsDAO */
t.nm,
t.nm_html nmHtml,
t.secondary_nm secondaryNm,
t.acc_txt acc,
'name:' || t.nm accQueryStr,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term t
WHERE
t.object_type_id = '4'
AND REGEXP_REPLACE ( UPPER ( SUBSTRING ( t.nm, 1 , 1 ) ) , '[^A-Z]', '#') = 'A'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-12-28 04:21:45
Duration: 1s410ms
Bind query: yes
SELECT
/* GeneBrowseTermsDAO */
t.nm,
t.nm_html nmHtml,
t.secondary_nm secondaryNm,
t.acc_txt acc,
'name:' || t.nm accQueryStr,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term t
WHERE
t.object_type_id = '4'
AND REGEXP_REPLACE ( UPPER ( SUBSTRING ( t.nm, 1 , 1 ) ) , '[^A-Z]', '#') = 'A'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-12-28 15:08:08
Duration: 1s404ms
Bind query: yes
x Hide
16
17
Details
28s705ms
1s15ms
2s499ms
1s688ms
select
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
(
select
string_agg ( distinct stressorterm.nm || ? || (
select
cd
from object_type
where
id = stressorterm.object_type_id) || ? || stressorterm.nm_html || ? || stressorterm.acc_txt || ? || stressorterm.acc_db_cd, ?) ) as stressoragents,
(
select
string_agg ( distinct coalesce ( receptorterm.nm, ?) || ? || coalesce ( (
select
cd
from object_type
where
id = receptorterm.object_type_id) , ?) || ? || coalesce ( receptorterm.nm_html, ?) || ? || coalesce ( receptorterm.acc_txt, ?) || ? || coalesce ( receptorterm.acc_db_cd, ?) || ? || receptor.description, ?) ) as receptors,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
(
select
string_agg ( distinct location.locality_txt, ?) ) as localities,
(
select
string_agg ( distinct event.medium_nm || ? || coalesce ( event.medium_term_acc_txt, ?) , ?) ) as assaymediums,
(
select
string_agg ( distinct exposuremarkerterm.nm || ? || (
select
cd
from object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd, ?) ) as assayedmarkers,
(
select
string_agg ( distinct diseaseterm.nm || ? || (
select
cd
from object_type
where
id = diseaseterm.object_type_id) || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd, ?) ) as diseases,
(
select
string_agg ( distinct phenotypeterm.nm || ? || (
select
cd
from object_type
where
id = phenotypeterm.object_type_id) || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd, ?) ) as phenotypes,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
re.author_summary summary,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join reference r on e.reference_id = r.id
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join exp_event_location location on e.exp_event_id = location.exp_event_id
left outer join country on location.country_id = country.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
inner join reference_exp re on e.reference_id = re.reference_id
left outer join exp_study_factor expstudyfactor on re.id = expstudyfactor.reference_exp_id
where
e.reference_id = any ( array (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like ?)
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressoragents;
Times Reported Time consuming queries #16
Day
Hour
Count
Duration
Avg duration
Dec 28 00 1 2s499ms 2s499ms 02 1 1s388ms 1s388ms 03 2 3s947ms 1s973ms 04 2 4s821ms 2s410ms 05 3 3s664ms 1s221ms 08 1 2s438ms 2s438ms 11 2 2s950ms 1s475ms 13 1 1s432ms 1s432ms 18 1 1s398ms 1s398ms 20 1 1s214ms 1s214ms 21 2 2s948ms 1s474ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 5s833ms - Times executed: 3 ]
x Hide
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436734')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436734')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents;
Date: 2024-12-28 00:20:18
Duration: 2s499ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436737')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436737')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents;
Date: 2024-12-28 03:11:47
Duration: 2s455ms
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436737')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1436737')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents;
Date: 2024-12-28 04:56:16
Duration: 2s450ms
Bind query: yes
x Hide
17
16
Details
36s620ms
1s126ms
5s941ms
2s288ms
select
g.nm genesymbol,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
i.ixn_prose_txt ixnprose,
i.ixn_prose_html ixnprosehtml,
i.actions_txt ixnactions,
i.id ixnid,
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.chem_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
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.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 #17
Day
Hour
Count
Duration
Avg duration
Dec 28 03 2 4s208ms 2s104ms 06 3 5s511ms 1s837ms 09 1 5s941ms 5s941ms 12 1 2s308ms 2s308ms 14 2 4s377ms 2s188ms 15 2 2s492ms 1s246ms 16 1 1s910ms 1s910ms 18 1 2s308ms 2s308ms 21 1 3s150ms 3s150ms 22 1 2s926ms 2s926ms 23 1 1s481ms 1s481ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 14s399ms - Times executed: 5 ]
x Hide
SELECT
/* ChemGeneIxnsDAO */
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
i.id ixnId,
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.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604')
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.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
g.nm_sort,
c.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-12-28 09:27:13
Duration: 5s941ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemGeneIxnsDAO */
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
i.id ixnId,
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.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1436745')
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.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
g.nm_sort,
c.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-12-28 21:42:18
Duration: 3s150ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemGeneIxnsDAO */
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
i.id ixnId,
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.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1436745')
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.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
g.nm_sort,
c.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-12-28 03:16:41
Duration: 3s82ms
Bind query: yes
x Hide
18
13
Details
43s181ms
1s2ms
8s648ms
3s321ms
select
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposurereferencecount,
case when cd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
chem_disease_axn a
where
a.chem_id = cd.chem_id
and a.disease_id = cd.disease_id)
else
null
end actiontypes
from
chem_disease cd
inner join term c on cd.chem_id = c.id
inner join term d on cd.disease_id = d.id
where
cd.chem_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
cd.network_score desc nulls last ,
d.nm_sort,
c.nm_sort;
Times Reported Time consuming queries #18
Day
Hour
Count
Duration
Avg duration
Dec 28 02 2 3s376ms 1s688ms 06 1 2s341ms 2s341ms 07 2 3s372ms 1s686ms 08 1 4s86ms 4s86ms 10 1 1s681ms 1s681ms 13 1 1s691ms 1s691ms 14 1 4s45ms 4s45ms 16 1 3s956ms 3s956ms 17 1 8s648ms 8s648ms 21 1 1s732ms 1s732ms 22 1 8s248ms 8s248ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 7s408ms - Times executed: 4 ]
x Hide
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1438258')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort;
Date: 2024-12-28 17:50:27
Duration: 8s648ms
Bind query: yes
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1438258')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort;
Date: 2024-12-28 22:51:52
Duration: 8s248ms
Bind query: yes
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1436734')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort;
Date: 2024-12-28 08:47:04
Duration: 4s86ms
Bind query: yes
x Hide
19
12
Details
17s682ms
1s247ms
1s530ms
1s473ms
select
g.nm genesymbol,
g.id geneid,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
count ( gcr.ixn_id) ixncount,
count ( distinct gcr.taxon_id) taxoncount,
count ( * ) over ( ) fullrowcount
from
gene_chem_reference gcr
inner join term g on gcr.gene_id = g.id
where
gcr.chem_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
group by
g.nm,
g.id,
g.acc_txt,
g.acc_db_cd,
g.nm_sort
order by
count ( gcr.ixn_id) desc ,
g.nm_sort
limit ?;
Times Reported Time consuming queries #19
Day
Hour
Count
Duration
Avg duration
Dec 28 02 1 1s522ms 1s522ms 04 1 1s511ms 1s511ms 06 1 1s247ms 1s247ms 07 1 1s437ms 1s437ms 13 1 1s430ms 1s430ms 15 1 1s486ms 1s486ms 16 3 4s543ms 1s514ms 18 2 2s997ms 1s498ms 19 1 1s506ms 1s506ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1s247ms - Times executed: 1 ]
x Hide
SELECT
/* ChemGenesDAO */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
COUNT ( gcr.ixn_id) ixnCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN term g ON gcr.gene_id = g.id
WHERE
gcr.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604')
GROUP BY
g.nm,
g.id,
g.acc_txt,
g.acc_db_cd,
g.nm_sort
ORDER BY
COUNT ( gcr.ixn_id) DESC ,
g.nm_sort
LIMIT 50 ;
Date: 2024-12-28 16:44:21
Duration: 1s530ms
Bind query: yes
SELECT
/* ChemGenesDAO */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
COUNT ( gcr.ixn_id) ixnCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN term g ON gcr.gene_id = g.id
WHERE
gcr.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604')
GROUP BY
g.nm,
g.id,
g.acc_txt,
g.acc_db_cd,
g.nm_sort
ORDER BY
COUNT ( gcr.ixn_id) DESC ,
g.nm_sort
LIMIT 50 ;
Date: 2024-12-28 16:16:24
Duration: 1s527ms
Bind query: yes
SELECT
/* ChemGenesDAO */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
COUNT ( gcr.ixn_id) ixnCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN term g ON gcr.gene_id = g.id
WHERE
gcr.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1398604')
GROUP BY
g.nm,
g.id,
g.acc_txt,
g.acc_db_cd,
g.nm_sort
ORDER BY
COUNT ( gcr.ixn_id) DESC ,
g.nm_sort
LIMIT 50 ;
Date: 2024-12-28 02:09:25
Duration: 1s522ms
Bind query: yes
x Hide
20
11
Details
16s172ms
1s416ms
1s706ms
1s470ms
select
coalesce ( d.abbr_display, d.nm_display) nm # ?,
d.description # ?,
coalesce ( d.abbr, d.nm) anchor # ?,
get_homepage_url ( d.id) url # ?
from
db d # ?
where
d.id in ( # ?
select
l.db_id # ? from db_link l # ?
where
l.type_cd = ? # ?
and l.object_type_id = ?) # ?
order by
?;
Times Reported Time consuming queries #20
Day
Hour
Count
Duration
Avg duration
Dec 28 03 1 1s435ms 1s435ms 05 3 4s327ms 1s442ms 07 1 1s456ms 1s456ms 08 1 1s429ms 1s429ms 16 1 1s432ms 1s432ms 17 1 1s416ms 1s416ms 20 1 1s706ms 1s706ms 21 1 1s473ms 1s473ms 23 1 1s495ms 1s495ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 2s888ms - Times executed: 2 ]
x Hide
SELECT
COALESCE ( d.abbr_display, d.nm_display) nm # 015 ,
d.description # 015 ,
COALESCE ( d.abbr, d.nm) anchor # 015 ,
get_homepage_url ( d.id) url # 015
FROM
db d # 015
WHERE
d.id IN ( # 015
SELECT
l.db_id # 015 FROM db_link l # 015
WHERE
l.type_cd = 'X' # 015
AND l.object_type_id = 4 ) # 015
ORDER BY
1 ;
Date: 2024-12-28 20:19:06
Duration: 1s706ms
Bind query: yes
SELECT
COALESCE ( d.abbr_display, d.nm_display) nm # 015 ,
d.description # 015 ,
COALESCE ( d.abbr, d.nm) anchor # 015 ,
get_homepage_url ( d.id) url # 015
FROM
db d # 015
WHERE
d.id IN ( # 015
SELECT
l.db_id # 015 FROM db_link l # 015
WHERE
l.type_cd = 'X' # 015
AND l.object_type_id = 4 ) # 015
ORDER BY
1 ;
Date: 2024-12-28 23:00:16
Duration: 1s495ms
Bind query: yes
SELECT
COALESCE ( d.abbr_display, d.nm_display) nm # 015 ,
d.description # 015 ,
COALESCE ( d.abbr, d.nm) anchor # 015 ,
get_homepage_url ( d.id) url # 015
FROM
db d # 015
WHERE
d.id IN ( # 015
SELECT
l.db_id # 015 FROM db_link l # 015
WHERE
l.type_cd = 'X' # 015
AND l.object_type_id = 4 ) # 015
ORDER BY
1 ;
Date: 2024-12-28 21:37:03
Duration: 1s473ms
Bind query: yes
x Hide
pgBadger