1
423
Details
5h21m38s
3s83ms
48s875ms
45s623ms
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 ? offset ?;
Times Reported Time consuming queries #1
Day
Hour
Count
Duration
Avg duration
Aug 10 00 77 57m53s 45s114ms 01 76 57m37s 45s489ms 02 75 57m9s 45s731ms 03 75 57m14s 45s787ms 04 75 57m20s 45s872ms 05 45 34m23s 45s856ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 4h26m1s - Times executed: 350 ]
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 = '2077399')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 2402550 ;
Date: 2024-08-10 00:10:23
Duration: 48s875ms
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 = '2077399')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 2419100 ;
Date: 2024-08-10 04:33:43
Duration: 48s334ms
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 = '2077399')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 2402600 ;
Date: 2024-08-10 00:11:13
Duration: 47s698ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
2
69
Details
1m21s
1s87ms
1s429ms
1s176ms
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 #2
Day
Hour
Count
Duration
Avg duration
Aug 10 00 3 3s520ms 1s173ms 01 2 2s295ms 1s147ms 04 1 1s153ms 1s153ms 05 5 6s121ms 1s224ms 06 4 4s742ms 1s185ms 07 4 4s823ms 1s205ms 08 2 2s385ms 1s192ms 09 4 4s784ms 1s196ms 10 3 3s612ms 1s204ms 11 3 3s667ms 1s222ms 12 1 1s185ms 1s185ms 13 3 3s511ms 1s170ms 14 2 2s331ms 1s165ms 15 2 2s332ms 1s166ms 16 3 3s535ms 1s178ms 17 3 3s498ms 1s166ms 18 3 3s514ms 1s171ms 19 5 5s636ms 1s127ms 20 4 4s538ms 1s134ms 21 1 1s143ms 1s143ms 22 5 5s857ms 1s171ms 23 6 7s2ms 1s167ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 56s247ms - Times executed: 48 ]
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 = '1435399'
or receptorTerm.id = '1435399'
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-08-10 05:43:47
Duration: 1s429ms
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 = '1435399'
or receptorTerm.id = '1435399'
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-08-10 05:38:53
Duration: 1s269ms
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 = '1463721'
or receptorTerm.id = '1463721'
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-08-10 11:17:06
Duration: 1s241ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
3
44
Details
2m56s
3s792ms
5s297ms
4s5ms
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 #3
Day
Hour
Count
Duration
Avg duration
Aug 10 01 2 8s170ms 4s85ms 02 4 16s402ms 4s100ms 05 3 13s56ms 4s352ms 06 2 7s840ms 3s920ms 10 1 3s978ms 3s978ms 11 1 4s305ms 4s305ms 12 7 28s13ms 4s1ms 13 21 1m22s 3s930ms 15 2 7s959ms 3s979ms 17 1 3s992ms 3s992ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m27s - Times executed: 22 ]
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 = '1259536')
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 = '1259536')
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-08-10 05:44:31
Duration: 5s297ms
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 = '1392170')
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 = '1392170')
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-08-10 02:54:40
Duration: 4s432ms
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 = '1429165')
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 = '1429165')
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-08-10 12:32:17
Duration: 4s332ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
4
35
Details
1m17s
1s72ms
5s679ms
2s206ms
select
*
from (
select
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
count ( * ) over ( ) fullrowcount
from term g
where g.id in (
select
gt.gene_id
from dag_path dp
inner join gene_taxon gt on dp.descendant_object_id = gt.taxon_id
where dp.ancestor_object_id = ?
union all
select
gcr.gene_id
from dag_path dp
inner join gene_chem_reference gcr on dp.descendant_object_id = gcr.taxon_id
where dp.ancestor_object_id = ?) offset ?) mq
order by
mq.genesymbolsort
limit ?;
Times Reported Time consuming queries #4
Day
Hour
Count
Duration
Avg duration
Aug 10 00 3 6s236ms 2s78ms 02 4 15s356ms 3s839ms 03 2 7s137ms 3s568ms 05 6 10s332ms 1s722ms 06 4 8s381ms 2s95ms 07 2 4s156ms 2s78ms 08 2 4s194ms 2s97ms 09 8 14s737ms 1s842ms 11 1 2s93ms 2s93ms 12 3 4s608ms 1s536ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 34s483ms - Times executed: 16 ]
x Hide
SELECT
/* TaxonGenesDAO */
*
FROM (
SELECT
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
COUNT ( * ) OVER ( ) fullRowCount
FROM term g
WHERE g.id IN (
SELECT
gt.gene_id
FROM dag_path dp
INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id
WHERE dp.ancestor_object_id = '651438'
UNION ALL
SELECT
gcr.gene_id
FROM dag_path dp
INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id
WHERE dp.ancestor_object_id = '651438') OFFSET 0 ) mq
ORDER BY
mq.genesymbolsort
LIMIT 50 ;
Date: 2024-08-10 02:40:16
Duration: 5s679ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* TaxonGenesDAO */
*
FROM (
SELECT
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
COUNT ( * ) OVER ( ) fullRowCount
FROM term g
WHERE g.id IN (
SELECT
gt.gene_id
FROM dag_path dp
INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id
WHERE dp.ancestor_object_id = '651438'
UNION ALL
SELECT
gcr.gene_id
FROM dag_path dp
INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id
WHERE dp.ancestor_object_id = '651438') OFFSET 0 ) mq
ORDER BY
mq.genesymbolsort
LIMIT 50 ;
Date: 2024-08-10 03:59:27
Duration: 5s642ms
Bind query: yes
SELECT
/* TaxonGenesDAO */
*
FROM (
SELECT
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
COUNT ( * ) OVER ( ) fullRowCount
FROM term g
WHERE g.id IN (
SELECT
gt.gene_id
FROM dag_path dp
INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id
WHERE dp.ancestor_object_id = '651112'
UNION ALL
SELECT
gcr.gene_id
FROM dag_path dp
INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id
WHERE dp.ancestor_object_id = '651112') OFFSET 0 ) mq
ORDER BY
mq.genesymbolsort
LIMIT 50 ;
Date: 2024-08-10 02:40:14
Duration: 3s717ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
5
24
Details
1m13s
1s60ms
10s476ms
3s76ms
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 #5
Day
Hour
Count
Duration
Avg duration
Aug 10 00 1 4s937ms 4s937ms 01 2 8s173ms 4s86ms 02 4 10s693ms 2s673ms 04 1 1s191ms 1s191ms 05 1 10s476ms 10s476ms 06 1 3s55ms 3s55ms 07 1 5s671ms 5s671ms 09 3 8s455ms 2s818ms 11 2 4s224ms 2s112ms 12 1 1s60ms 1s60ms 13 4 12s98ms 3s24ms 16 1 1s315ms 1s315ms 19 1 1s227ms 1s227ms 22 1 1s264ms 1s264ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 54s210ms - Times executed: 17 ]
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 = '2084677')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-08-10 05:34:27
Duration: 10s476ms
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 = '2080647')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-08-10 01:21:12
Duration: 6s872ms
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 = '2075879')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-08-10 07:04:30
Duration: 5s671ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
6
22
Details
1m29s
1s8ms
5s448ms
4s62ms
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 #6
Day
Hour
Count
Duration
Avg duration
Aug 10 00 2 10s399ms 5s199ms 01 1 5s448ms 5s448ms 02 1 5s398ms 5s398ms 03 1 1s12ms 1s12ms 05 5 13s624ms 2s724ms 07 2 6s299ms 3s149ms 08 1 1s16ms 1s16ms 09 1 4s884ms 4s884ms 10 3 15s537ms 5s179ms 13 2 10s163ms 5s81ms 14 1 5s411ms 5s411ms 17 1 5s55ms 5s55ms 23 1 5s134ms 5s134ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 48s27ms - Times executed: 10 ]
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 = '1305182'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-08-10 01:52:51
Duration: 5s448ms
Database: ctdprd51
User: pubeu
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 = '1305182'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-08-10 14:03:28
Duration: 5s411ms
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 = '1391044'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-08-10 02:01:46
Duration: 5s398ms
Bind query: yes
x Hide
7
21
Details
1m1s
1s57ms
5s315ms
2s918ms
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 #7
Day
Hour
Count
Duration
Avg duration
Aug 10 00 4 14s861ms 3s715ms 01 1 5s269ms 5s269ms 02 2 6s729ms 3s364ms 05 1 1s320ms 1s320ms 06 1 1s674ms 1s674ms 07 1 1s474ms 1s474ms 08 1 2s598ms 2s598ms 09 1 2s888ms 2s888ms 10 1 5s315ms 5s315ms 11 3 4s805ms 1s601ms 13 5 14s360ms 2s872ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 32s393ms - Times executed: 11 ]
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 = '1391044')
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 = '1391044')
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-08-10 10:44:39
Duration: 5s315ms
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 = '1391044')
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 = '1391044')
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-08-10 13:12:03
Duration: 5s314ms
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 = '1391044')
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 = '1391044')
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-08-10 01:32:10
Duration: 5s269ms
Bind query: yes
x Hide
8
14
Details
13m44s
1s493ms
4m9s
58s916ms
select
phenotypeterm.nm gonm,
phenotypeterm.nm_html gonmhtml,
phenotypeterm.acc_txt goacc,
phenotypeterm.id goid,
diseaseterm.nm diseasenm,
diseaseterm.acc_txt diseaseacc,
diseaseterm.acc_db_cd diseaseaccdbcd,
diseaseterm.id diseaseid,
via_gene_qty genenetworkcount,
via_chem_qty chemnetworkcount,
indirect_reference_qty referencecount,
count ( * ) over ( ) fullrowcount
from
phenotype_term pt
inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id
inner join term diseaseterm on pt.term_id = diseaseterm.id
where
phenotypeterm.id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id = ?)
and diseaseterm.object_type_id = ?
order by
chemnetworkcount desc ,
genenetworkcount desc
limit ?;
Times Reported Time consuming queries #8
Day
Hour
Count
Duration
Avg duration
Aug 10 00 1 1s947ms 1s947ms 01 1 3s12ms 3s12ms 02 1 1s645ms 1s645ms 05 2 5m17s 2m38s 07 2 4m1s 2m 08 1 1s493ms 1s493ms 09 2 4m10s 2m5s 12 2 3s542ms 1s771ms 16 1 1s622ms 1s622ms 17 1 1s924ms 1s924ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 5m28s - Times executed: 8 ]
x Hide
SELECT
/* GoDiseasesDAO */
phenotypeTerm.nm goNm,
phenotypeTerm.nm_html goNmHTML,
phenotypeTerm.acc_txt goAcc,
phenotypeTerm.id goId,
diseaseTerm.nm diseaseNm,
diseaseTerm.acc_txt diseaseAcc,
diseaseTerm.acc_db_cd diseaseAccDBCd,
diseaseTerm.id diseaseId,
via_gene_qty geneNetworkCount,
via_chem_qty chemNetworkCount,
indirect_reference_qty referenceCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
phenotype_term pt
inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id
inner join term diseaseTerm on pt.term_id = diseaseTerm.id
WHERE
phenotypeTerm.id IN ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '1245161')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-08-10 09:01:18
Duration: 4m9s
Bind query: yes
SELECT
/* GoDiseasesDAO */
phenotypeTerm.nm goNm,
phenotypeTerm.nm_html goNmHTML,
phenotypeTerm.acc_txt goAcc,
phenotypeTerm.id goId,
diseaseTerm.nm diseaseNm,
diseaseTerm.acc_txt diseaseAcc,
diseaseTerm.acc_db_cd diseaseAccDBCd,
diseaseTerm.id diseaseId,
via_gene_qty geneNetworkCount,
via_chem_qty chemNetworkCount,
indirect_reference_qty referenceCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
phenotype_term pt
inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id
inner join term diseaseTerm on pt.term_id = diseaseTerm.id
WHERE
phenotypeTerm.id IN ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '1227838')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-08-10 07:03:05
Duration: 3m59s
Bind query: yes
SELECT
/* GoDiseasesDAO */
phenotypeTerm.nm goNm,
phenotypeTerm.nm_html goNmHTML,
phenotypeTerm.acc_txt goAcc,
phenotypeTerm.id goId,
diseaseTerm.nm diseaseNm,
diseaseTerm.acc_txt diseaseAcc,
diseaseTerm.acc_db_cd diseaseAccDBCd,
diseaseTerm.id diseaseId,
via_gene_qty geneNetworkCount,
via_chem_qty chemNetworkCount,
indirect_reference_qty referenceCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
phenotype_term pt
inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id
inner join term diseaseTerm on pt.term_id = diseaseTerm.id
WHERE
phenotypeTerm.id IN ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '1245161')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-08-10 05:05:07
Duration: 3m59s
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
9
14
Details
19s779ms
1s335ms
1s469ms
1s412ms
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 #9
Day
Hour
Count
Duration
Avg duration
Aug 10 01 1 1s386ms 1s386ms 03 1 1s399ms 1s399ms 05 2 2s876ms 1s438ms 06 1 1s382ms 1s382ms 07 1 1s464ms 1s464ms 09 1 1s469ms 1s469ms 10 1 1s335ms 1s335ms 12 2 2s808ms 1s404ms 13 2 2s895ms 1s447ms 17 1 1s394ms 1s394ms 23 1 1s366ms 1s366ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 8s460ms - Times executed: 6 ]
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-08-10 09:32:00
Duration: 1s469ms
Database: ctdprd51
User: pubeu
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]', '#') = 'K'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-08-10 07:43:48
Duration: 1s464ms
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-08-10 05:37:16
Duration: 1s453ms
Bind query: yes
x Hide
10
12
Details
16s651ms
1s369ms
1s432ms
1s387ms
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 #10
Day
Hour
Count
Duration
Avg duration
Aug 10 00 3 4s150ms 1s383ms 02 1 1s369ms 1s369ms 05 4 5s597ms 1s399ms 09 1 1s393ms 1s393ms 10 1 1s370ms 1s370ms 12 2 2s769ms 1s384ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s659ms - Times executed: 7 ]
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-08-10 05:43:39
Duration: 1s432ms
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-08-10 00:06:33
Duration: 1s403ms
Database: ctdprd51
User: pubeu
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-08-10 12:22:57
Duration: 1s397ms
Bind query: yes
x Hide
11
12
Details
16s126ms
1s209ms
1s548ms
1s343ms
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 #11
Day
Hour
Count
Duration
Avg duration
Aug 10 00 1 1s297ms 1s297ms 01 2 2s704ms 1s352ms 02 2 2s641ms 1s320ms 05 2 2s923ms 1s461ms 14 1 1s363ms 1s363ms 15 1 1s293ms 1s293ms 16 1 1s209ms 1s209ms 21 2 2s692ms 1s346ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 10s600ms - Times executed: 8 ]
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 = '2074262')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2074262')
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-08-10 05:43:49
Duration: 1s548ms
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 = '2084677')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2084677')
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-08-10 21:24:26
Duration: 1s453ms
Database: ctdprd51
User: pubeu
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 = '2074344')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2074344')
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-08-10 01:42:16
Duration: 1s422ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
12
12
Details
15s883ms
1s227ms
1s495ms
1s323ms
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 #12
Day
Hour
Count
Duration
Avg duration
Aug 10 01 1 1s495ms 1s495ms 02 1 1s242ms 1s242ms 03 1 1s299ms 1s299ms 05 3 4s32ms 1s344ms 06 1 1s306ms 1s306ms 07 2 2s632ms 1s316ms 08 1 1s289ms 1s289ms 12 1 1s273ms 1s273ms 16 1 1s312ms 1s312ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 7s937ms - Times executed: 6 ]
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 = '1228415')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1228415')
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-08-10 01:45:44
Duration: 1s495ms
Database: ctdprd51
User: pubeu
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 = '1213905')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1213905')
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-08-10 05:43:46
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.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1213905')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1213905')
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-08-10 05:38:52
Duration: 1s354ms
Bind query: yes
x Hide
13
11
Details
33s602ms
1s616ms
6s213ms
3s54ms
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 #13
Day
Hour
Count
Duration
Avg duration
Aug 10 02 2 4s464ms 2s232ms 04 2 4s804ms 2s402ms 08 1 1s984ms 1s984ms 09 2 5s215ms 2s607ms 10 2 8s158ms 4s79ms 11 1 6s98ms 6s98ms 13 1 2s875ms 2s875ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 21s46ms - Times executed: 6 ]
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 = '1391044') ) )
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-08-10 10:04:06
Duration: 6s213ms
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 = '1391044') ) )
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-08-10 11:07:45
Duration: 6s98ms
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 = '1430698') ) )
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-08-10 09:26:10
Duration: 3s317ms
Bind query: yes
x Hide
14
10
Details
1m45s
1s4ms
20s774ms
10s537ms
select
sq.*,
count ( * ) over ( ) fullrowcount
from ( select distinct
gt.nm gonm,
gt.nm_html gonmhtml,
gt.nm_sort gonmsort,
gt.acc_txt goacc,
gt.object_id goid,
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid
from
dag_node gt
inner join gene_go_annot gga on gt.object_id = gga.go_term_id
inner join term g on gga.gene_id = g.id
where
gt.id in (
select
p.descendant_dag_node_id
from
dag_path p
where
p.ancestor_object_id = ?)
and gga.is_not = false ) sq
order by
sq.gonmsort,
sq.genesymbolsort
limit ?;
Times Reported Time consuming queries #14
Day
Hour
Count
Duration
Avg duration
Aug 10 00 2 17s42ms 8s521ms 04 2 21s852ms 10s926ms 08 1 15s711ms 15s711ms 09 1 7s148ms 7s148ms 11 1 19s865ms 19s865ms 13 1 19s312ms 19s312ms 15 1 1s154ms 1s154ms 17 1 3s285ms 3s285ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 43s9ms - Times executed: 6 ]
x Hide
SELECT
/* GoGenesDAO */
sq.*,
COUNT ( * ) OVER ( ) fullRowCount
FROM ( SELECT DISTINCT
gt.nm gonm,
gt.nm_html gonmhtml,
gt.nm_sort gonmsort,
gt.acc_txt goacc,
gt.object_id goid,
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid
FROM
dag_node gt
INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id
INNER JOIN term g ON gga.gene_id = g.id
WHERE
gt.id IN (
SELECT
p.descendant_dag_node_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1213902')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-08-10 04:01:49
Duration: 20s774ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* GoGenesDAO */
sq.*,
COUNT ( * ) OVER ( ) fullRowCount
FROM ( SELECT DISTINCT
gt.nm gonm,
gt.nm_html gonmhtml,
gt.nm_sort gonmsort,
gt.acc_txt goacc,
gt.object_id goid,
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid
FROM
dag_node gt
INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id
INNER JOIN term g ON gga.gene_id = g.id
WHERE
gt.id IN (
SELECT
p.descendant_dag_node_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1213902')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-08-10 11:09:54
Duration: 19s865ms
Bind query: yes
SELECT
/* GoGenesDAO */
sq.*,
COUNT ( * ) OVER ( ) fullRowCount
FROM ( SELECT DISTINCT
gt.nm gonm,
gt.nm_html gonmhtml,
gt.nm_sort gonmsort,
gt.acc_txt goacc,
gt.object_id goid,
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid
FROM
dag_node gt
INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id
INNER JOIN term g ON gga.gene_id = g.id
WHERE
gt.id IN (
SELECT
p.descendant_dag_node_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1213902')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-08-10 13:29:22
Duration: 19s312ms
Bind query: yes
x Hide
15
10
Details
42s545ms
4s153ms
4s546ms
4s254ms
select distinct
associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm,
associatedterm.id associatedtermid,
ptr.ixn_id ixnid,
associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort,
coalesce ( associatedterm.secondary_nm, ?) casrn,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
count ( distinct taxonterm.nm) taxoncount,
i.ixn_prose_html ixnprosehtml,
i.ixn_prose_txt ixnprose,
i.sort_txt ixnsort,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( distinct ptr.reference_id) refcount,
pt.indirect_term_qty inferredcount,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedterm on ptr.term_id = associatedterm.id
inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id
left outer join term taxonterm on ptr.taxon_id = taxonterm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedterm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id
where
ptr.term_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and upper ( baseterm.nm)
like ?) )
and ptr.term_object_type_id = ?
and ptr.phenotype_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and baseterm.id in (
select
object_id
from
db_link l
where
l.acc_txt = ?
and l.type_cd = ?
and l.object_type_id = ?) ) )
and taxonterm.id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and baseterm.id in (
select
object_id
from
db_link l
where
l.acc_txt = ?
and l.type_cd = ?
and l.object_type_id = ?) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = ?
and action_degree_type_nm in ( . .. ) )
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ?;
Times Reported Time consuming queries #15
Day
Hour
Count
Duration
Avg duration
Aug 10 05 2 8s699ms 4s349ms 13 8 33s846ms 4s230ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 8s419ms - Times executed: 2 ]
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
ptr.term_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'ZINC') )
and ptr.term_object_type_id = 2
and ptr.phenotype_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006915'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and taxonTerm.id in (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 1
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = '9606'
AND l.type_cd = 'A'
AND l.object_type_id = 1 ) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = 'phenotype'
and action_degree_type_nm in ( 'increases') )
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-08-10 05:43:54
Duration: 4s546ms
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
ptr.term_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'MERCURY') )
and ptr.term_object_type_id = 2
and ptr.phenotype_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006346'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and taxonTerm.id in (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 1
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = '9606'
AND l.type_cd = 'A'
AND l.object_type_id = 1 ) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = 'phenotype'
and action_degree_type_nm in ( 'increases', 'decreases', 'affects') )
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-08-10 13:37:58
Duration: 4s254ms
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
ptr.term_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'MERCURY') )
and ptr.term_object_type_id = 2
and ptr.phenotype_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006306'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and taxonTerm.id in (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 1
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = '9606'
AND l.type_cd = 'A'
AND l.object_type_id = 1 ) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = 'phenotype'
and action_degree_type_nm in ( 'increases', 'decreases', 'affects') )
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-08-10 13:43:12
Duration: 4s252ms
Bind query: yes
x Hide
16
10
Details
21s886ms
1s137ms
2s528ms
2s188ms
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;
Times Reported Time consuming queries #16
Day
Hour
Count
Duration
Avg duration
Aug 10 05 9 20s748ms 2s305ms 08 1 1s137ms 1s137ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 7s886ms - Times executed: 4 ]
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 = '2078631')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-08-10 05:44:00
Duration: 2s528ms
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 = '2078631')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-08-10 05:43:47
Duration: 2s496ms
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 = '2078631')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-08-10 05:44:31
Duration: 2s429ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
17
10
Details
17s919ms
1s253ms
1s980ms
1s791ms
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 #17
Day
Hour
Count
Duration
Avg duration
Aug 10 00 1 1s857ms 1s857ms 02 1 1s867ms 1s867ms 03 1 1s852ms 1s852ms 04 1 1s980ms 1s980ms 06 1 1s836ms 1s836ms 10 2 3s57ms 1s528ms 11 1 1s815ms 1s815ms 12 1 1s853ms 1s853ms 13 1 1s798ms 1s798ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s229ms - Times executed: 5 ]
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 = '1391044')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-08-10 04:30:43
Duration: 1s980ms
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 = '1391044')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-08-10 02:33:17
Duration: 1s867ms
Database: ctdprd51
User: pubeu
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 = '1391044')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-08-10 00:11:53
Duration: 1s857ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
18
8
Details
21s727ms
1s120ms
6s966ms
2s715ms
select distinct
associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm,
associatedterm.id associatedtermid,
ptr.ixn_id ixnid,
associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort,
coalesce ( associatedterm.secondary_nm, ?) casrn,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
count ( distinct taxonterm.nm) taxoncount,
i.ixn_prose_html ixnprosehtml,
i.ixn_prose_txt ixnprose,
i.sort_txt ixnsort,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( distinct ptr.reference_id) refcount,
pt.indirect_term_qty inferredcount,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedterm on ptr.term_id = associatedterm.id
inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id
left outer join term taxonterm on ptr.taxon_id = taxonterm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedterm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id
where
ptr.ixn_id = any ( array ( (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in (
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 #18
Day
Hour
Count
Duration
Avg duration
Aug 10 02 2 7s994ms 3s997ms 05 2 8s269ms 4s134ms 08 1 1s507ms 1s507ms 10 1 1s496ms 1s496ms 12 1 1s337ms 1s337ms 13 1 1s120ms 1s120ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s502ms - Times executed: 3 ]
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
ptr.ixn_id = ANY ( ARRAY ( (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2089451') ) ) )
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-08-10 05:41:35
Duration: 6s966ms
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
ptr.ixn_id = ANY ( ARRAY ( (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2089451') ) ) )
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-08-10 02:48:00
Duration: 6s864ms
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
ptr.ixn_id = ANY ( ARRAY ( (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2088060') ) ) )
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-08-10 08:55:51
Duration: 1s507ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
19
7
Details
49s535ms
1s23ms
15s881ms
7s76ms
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
phenotypeterm.id = any ( array ( (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) )
and associatedterm.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 #19
Day
Hour
Count
Duration
Avg duration
Aug 10 00 1 4s398ms 4s398ms 02 1 1s99ms 1s99ms 03 2 11s598ms 5s799ms 04 1 15s470ms 15s470ms 05 1 15s881ms 15s881ms 06 1 1s86ms 1s86ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 47s348ms - Times executed: 5 ]
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
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1213902') ) )
and associatedTerm.object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-08-10 05:36:26
Duration: 15s881ms
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
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1213902') ) )
and associatedTerm.object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-08-10 04:51:59
Duration: 15s470ms
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
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1244856') ) )
and associatedTerm.object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-08-10 03:59:18
Duration: 10s575ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
20
7
Details
22s749ms
1s23ms
10s280ms
3s249ms
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 #20
Day
Hour
Count
Duration
Avg duration
Aug 10 04 1 2s384ms 2s384ms 08 1 10s280ms 10s280ms 09 2 5s221ms 2s610ms 11 1 1s246ms 1s246ms 13 2 3s616ms 1s808ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 16s84ms - Times executed: 5 ]
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 = '1391044')
or exposureMarkerTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1391044')
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-08-10 08:39:39
Duration: 10s280ms
Database: ctdprd51
User: pubeu
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 = '1429172')
or exposureMarkerTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1429172')
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-08-10 09:09:17
Duration: 4s71ms
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 = '1269915')
or exposureMarkerTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1269915')
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-08-10 13:35:28
Duration: 2s592ms
Bind query: yes
x Hide
pgBadger