1
124
Details
2m15s
1s14ms
1s281ms
1s92ms
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 #1
Day
Hour
Count
Duration
Avg duration
Apr 27 00 1 1s119ms 1s119ms 01 3 3s233ms 1s77ms 02 2 2s184ms 1s92ms 03 5 5s527ms 1s105ms 04 2 2s159ms 1s79ms 05 2 2s460ms 1s230ms 06 8 8s579ms 1s72ms 07 2 2s232ms 1s116ms 08 6 6s463ms 1s77ms 09 7 7s682ms 1s97ms 10 8 8s791ms 1s98ms 11 6 6s486ms 1s81ms 12 2 2s195ms 1s97ms 13 5 5s382ms 1s76ms 14 3 3s293ms 1s97ms 15 2 2s172ms 1s86ms 16 26 28s818ms 1s108ms 17 8 8s922ms 1s115ms 18 1 1s79ms 1s79ms 19 2 2s166ms 1s83ms 20 5 5s170ms 1s34ms 21 8 8s457ms 1s57ms 22 5 5s447ms 1s89ms 23 5 5s444ms 1s88ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 51s135ms - Times executed: 47 ]
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 = '1427909'
or receptorTerm.id = '1427909'
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-04-27 05:43:41
Duration: 1s281ms
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 = '1425257'
or receptorTerm.id = '1425257'
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-04-27 16:56:21
Duration: 1s191ms
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 = '1425257'
or receptorTerm.id = '1425257'
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-04-27 05:38:40
Duration: 1s179ms
Bind query: yes
x Hide
2
90
Details
5m56s
1s8ms
12s490ms
3s956ms
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 #2
Day
Hour
Count
Duration
Avg duration
Apr 27 00 1 11s912ms 11s912ms 01 1 1s154ms 1s154ms 02 2 3s59ms 1s529ms 03 1 1s67ms 1s67ms 04 3 14s677ms 4s892ms 05 1 2s218ms 2s218ms 09 1 10s454ms 10s454ms 10 4 10s98ms 2s524ms 14 2 4s540ms 2s270ms 15 2 10s676ms 5s338ms 16 46 3m6s 4s45ms 17 22 1m23s 3s794ms 18 2 3s484ms 1s742ms 19 1 11s845ms 11s845ms 23 1 1s385ms 1s385ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m44s - Times executed: 19 ]
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 = '2052624')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-04-27 16:47:37
Duration: 12s490ms
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 = '2052624')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-04-27 00:18:33
Duration: 11s912ms
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 = '2052624')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-04-27 19:37:26
Duration: 11s845ms
Bind query: yes
x Hide
3
78
Details
3m24s
1s15ms
5s214ms
2s627ms
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 #3
Day
Hour
Count
Duration
Avg duration
Apr 27 01 1 3s560ms 3s560ms 02 3 8s479ms 2s826ms 03 1 1s267ms 1s267ms 05 4 13s177ms 3s294ms 06 2 3s985ms 1s992ms 07 2 4s870ms 2s435ms 08 2 6s122ms 3s61ms 09 3 8s766ms 2s922ms 10 1 1s104ms 1s104ms 11 2 7s776ms 3s888ms 12 2 3s914ms 1s957ms 13 1 1s48ms 1s48ms 14 3 7s866ms 2s622ms 15 5 14s875ms 2s975ms 16 20 40s941ms 2s47ms 17 10 27s967ms 2s796ms 18 4 13s563ms 3s390ms 19 4 11s4ms 2s751ms 20 3 8s902ms 2s967ms 21 3 11s180ms 3s726ms 22 2 4s574ms 2s287ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m50s - Times executed: 41 ]
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 = '1381425')
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 = '1381425')
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-04-27 18:26:56
Duration: 5s214ms
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 = '1381425')
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 = '1381425')
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-04-27 17:05:27
Duration: 5s100ms
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 = '1381425')
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 = '1381425')
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-04-27 15:28:02
Duration: 5s14ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
4
46
Details
57s61ms
1s128ms
1s437ms
1s240ms
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 #4
Day
Hour
Count
Duration
Avg duration
Apr 27 01 1 1s189ms 1s189ms 02 3 3s540ms 1s180ms 05 2 2s767ms 1s383ms 07 2 2s344ms 1s172ms 14 1 1s259ms 1s259ms 16 27 34s261ms 1s268ms 18 1 1s196ms 1s196ms 19 2 2s308ms 1s154ms 21 3 3s436ms 1s145ms 23 4 4s757ms 1s189ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 12s341ms - Times executed: 10 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2066417')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2066417')
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-04-27 05:43:42
Duration: 1s437ms
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 = '2053339')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2053339')
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-04-27 16:47:38
Duration: 1s395ms
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 = '2052958')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2052958')
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-04-27 16:39:15
Duration: 1s341ms
Bind query: yes
x Hide
5
35
Details
54s852ms
1s214ms
1s813ms
1s567ms
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 #5
Day
Hour
Count
Duration
Avg duration
Apr 27 01 1 1s240ms 1s240ms 02 1 1s801ms 1s801ms 03 1 1s762ms 1s762ms 04 1 1s786ms 1s786ms 05 3 4s788ms 1s596ms 07 2 3s1ms 1s500ms 08 2 2s999ms 1s499ms 09 1 1s784ms 1s784ms 10 3 5s382ms 1s794ms 11 1 1s797ms 1s797ms 13 2 3s 1s500ms 14 3 4s283ms 1s427ms 16 2 3s67ms 1s533ms 17 3 4s331ms 1s443ms 18 1 1s227ms 1s227ms 19 2 3s6ms 1s503ms 20 1 1s239ms 1s239ms 21 3 4s795ms 1s598ms 22 1 1s766ms 1s766ms 23 1 1s789ms 1s789ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 22s285ms - Times executed: 14 ]
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 = '1381425')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-04-27 10:33:07
Duration: 1s813ms
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 = '1381425')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-04-27 14:23:40
Duration: 1s813ms
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 = '1381425')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-04-27 05:01:48
Duration: 1s806ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
6
34
Details
1m50s
1s301ms
6s171ms
3s255ms
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 #6
Day
Hour
Count
Duration
Avg duration
Apr 27 01 1 3s227ms 3s227ms 06 1 3s202ms 3s202ms 07 1 1s582ms 1s582ms 08 1 3s186ms 3s186ms 09 1 5s905ms 5s905ms 10 2 11s735ms 5s867ms 13 1 1s304ms 1s304ms 16 13 30s496ms 2s345ms 17 8 28s964ms 3s620ms 18 1 5s812ms 5s812ms 20 1 5s901ms 5s901ms 21 3 9s373ms 3s124ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 43s43ms - Times executed: 12 ]
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 = '1381425') ) )
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-04-27 16:29:35
Duration: 6s171ms
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 = '1381425') ) )
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-04-27 17:05:26
Duration: 6s97ms
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 = '1381425') ) )
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-04-27 10:59:29
Duration: 5s913ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
7
29
Details
39s567ms
1s297ms
1s608ms
1s364ms
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 #7
Day
Hour
Count
Duration
Avg duration
Apr 27 03 2 2s683ms 1s341ms 05 2 2s817ms 1s408ms 08 3 4s19ms 1s339ms 13 1 1s335ms 1s335ms 16 1 1s340ms 1s340ms 17 20 27s371ms 1s368ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 8s62ms - 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]', '#') = 'G'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-04-27 17:03:21
Duration: 1s608ms
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]', '#') = 'B'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-04-27 17:03:18
Duration: 1s538ms
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-04-27 05:42:09
Duration: 1s451ms
Bind query: yes
x Hide
8
23
Details
1m48s
1s6ms
5s403ms
4s733ms
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 #8
Day
Hour
Count
Duration
Avg duration
Apr 27 04 2 10s209ms 5s104ms 07 1 4s888ms 4s888ms 10 1 5s57ms 5s57ms 11 1 5s7ms 5s7ms 16 12 54s356ms 4s529ms 17 2 9s792ms 4s896ms 18 1 4s887ms 4s887ms 19 1 4s854ms 4s854ms 20 1 5s29ms 5s29ms 22 1 4s783ms 4s783ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 21s394ms - Times executed: 5 ]
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 = '1314953'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-04-27 16:34:57
Duration: 5s403ms
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 = '1390135'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-04-27 16:40:34
Duration: 5s356ms
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 = '1250837'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-04-27 16:35:19
Duration: 5s335ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
9
20
Details
1m15s
3s655ms
3s989ms
3s752ms
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 #9
Day
Hour
Count
Duration
Avg duration
Apr 27 02 1 3s731ms 3s731ms 04 1 3s725ms 3s725ms 05 1 3s758ms 3s758ms 06 3 11s180ms 3s726ms 07 2 7s423ms 3s711ms 09 2 7s408ms 3s704ms 10 3 11s449ms 3s816ms 13 1 3s688ms 3s688ms 14 2 7s501ms 3s750ms 17 1 3s820ms 3s820ms 18 1 3s960ms 3s960ms 21 1 3s655ms 3s655ms 22 1 3s740ms 3s740ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 33s907ms - Times executed: 9 ]
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 = '1366424')
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 = '1366424')
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-04-27 10:09:14
Duration: 3s989ms
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 = '1283917')
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 = '1283917')
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-04-27 18:18:04
Duration: 3s960ms
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 = '1300432')
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 = '1300432')
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-04-27 14:53:20
Duration: 3s835ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
10
20
Details
24s683ms
1s213ms
1s341ms
1s234ms
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
Apr 27 01 1 1s236ms 1s236ms 05 2 2s483ms 1s241ms 09 8 9s910ms 1s238ms 10 5 6s122ms 1s224ms 16 2 2s454ms 1s227ms 17 1 1s236ms 1s236ms 19 1 1s239ms 1s239ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 6s270ms - Times executed: 5 ]
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-04-27 09:47:30
Duration: 1s341ms
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-04-27 05:43:33
Duration: 1s261ms
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-04-27 10:03:38
Duration: 1s239ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
11
19
Details
2m39s
1s29ms
20s184ms
8s407ms
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 #11
Day
Hour
Count
Duration
Avg duration
Apr 27 01 1 18s49ms 18s49ms 07 1 1s29ms 1s29ms 10 1 3s255ms 3s255ms 16 5 42s978ms 8s595ms 17 9 1m24s 9s438ms 19 1 2s869ms 2s869ms 21 1 6s602ms 6s602ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m36s - Times executed: 9 ]
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 = '1206427')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-04-27 16:30:05
Duration: 20s184ms
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 = '1206427')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-04-27 17:08:33
Duration: 19s97ms
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 = '1206427')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-04-27 17:24:55
Duration: 18s860ms
Bind query: yes
x Hide
12
19
Details
22s746ms
1s110ms
1s415ms
1s197ms
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
Apr 27 00 1 1s206ms 1s206ms 03 9 10s445ms 1s160ms 05 2 2s717ms 1s358ms 08 2 2s389ms 1s194ms 11 1 1s243ms 1s243ms 13 1 1s198ms 1s198ms 16 2 2s389ms 1s194ms 19 1 1s156ms 1s156ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 7s122ms - Times executed: 6 ]
[ User: qaeu - Total duration: 1s415ms - Times executed: 1 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1209014')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1209014')
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-04-27 05:43:39
Duration: 1s415ms
Database: ctdprd51
User: qaeu
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1206430')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1206430')
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-04-27 05:38:38
Duration: 1s302ms
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 = '1205214')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1205214')
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-04-27 11:00:23
Duration: 1s243ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
13
14
Details
35s347ms
1s81ms
6s994ms
2s524ms
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 #13
Day
Hour
Count
Duration
Avg duration
Apr 27 11 1 1s408ms 1s408ms 15 1 6s762ms 6s762ms 16 6 13s847ms 2s307ms 17 6 13s328ms 2s221ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 15s194ms - 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 = '2068141') ) ) )
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-04-27 16:33:18
Duration: 6s994ms
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 = '2068141') ) ) )
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-04-27 15:31:09
Duration: 6s762ms
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 = '2068141') ) ) )
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-04-27 17:06:50
Duration: 6s704ms
Bind query: yes
x Hide
14
13
Details
25s880ms
1s878ms
2s188ms
1s990ms
select
r.id,
r.abbr_authors_txt authors,
r.title,
r.core_citation_txt citation,
r.pub_start_yr yr,
r.acc_txt refacc,
r.has_diseases
or r.has_ixns
or r.has_exposures
or r.has_phenotypes iscurated,
r.has_exposures,
count ( * ) over ( ) fullrowcount
from
reference r
where
r.id in (
select
reference_id
from
term_reference
where
term_id in ( . .. ) )
order by
r.sort_txt
limit ?;
Times Reported Time consuming queries #14
Day
Hour
Count
Duration
Avg duration
Apr 27 16 13 25s880ms 1s990ms
x Hide
Examples
SELECT
/* RefsDAO */
r.id,
r.abbr_authors_txt authors,
r.title,
r.core_citation_txt citation,
r.pub_start_yr yr,
r.acc_txt refAcc,
r.has_diseases
or r.has_ixns
or r.has_exposures
or r.has_phenotypes iscurated,
r.has_exposures,
COUNT ( * ) OVER ( ) fullRowCount
FROM
reference r
WHERE
r.id IN (
select
reference_id
from
term_reference
where
term_id in ( '2038368') )
ORDER BY
r.sort_txt
LIMIT 50 ;
Date: 2024-04-27 16:58:40
Duration: 2s188ms
Bind query: yes
SELECT
/* RefsDAO */
r.id,
r.abbr_authors_txt authors,
r.title,
r.core_citation_txt citation,
r.pub_start_yr yr,
r.acc_txt refAcc,
r.has_diseases
or r.has_ixns
or r.has_exposures
or r.has_phenotypes iscurated,
r.has_exposures,
COUNT ( * ) OVER ( ) fullRowCount
FROM
reference r
WHERE
r.id IN (
select
reference_id
from
term_reference
where
term_id in ( '2031730') )
ORDER BY
r.sort_txt
LIMIT 50 ;
Date: 2024-04-27 16:48:32
Duration: 2s42ms
Bind query: yes
SELECT
/* RefsDAO */
r.id,
r.abbr_authors_txt authors,
r.title,
r.core_citation_txt citation,
r.pub_start_yr yr,
r.acc_txt refAcc,
r.has_diseases
or r.has_ixns
or r.has_exposures
or r.has_phenotypes iscurated,
r.has_exposures,
COUNT ( * ) OVER ( ) fullRowCount
FROM
reference r
WHERE
r.id IN (
select
reference_id
from
term_reference
where
term_id in ( '2027151') )
ORDER BY
r.sort_txt
LIMIT 50 ;
Date: 2024-04-27 16:59:34
Duration: 2s15ms
Bind query: yes
x Hide
15
13
Details
20s226ms
1s91ms
2s718ms
1s555ms
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
diseaseterm.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 #15
Day
Hour
Count
Duration
Avg duration
Apr 27 10 1 1s119ms 1s119ms 16 7 10s643ms 1s520ms 17 5 8s463ms 1s692ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 5s92ms - Times executed: 4 ]
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
diseaseTerm.id IN ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '2051315')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-04-27 17:10:51
Duration: 2s718ms
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
diseaseTerm.id IN ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '2051315')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-04-27 16:31:43
Duration: 2s438ms
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
diseaseTerm.id IN ( select distinct
dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id = '2056570')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-04-27 16:31:22
Duration: 2s25ms
Bind query: yes
x Hide
16
12
Details
1h2m15s
1s452ms
18m5s
5m11s
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 #16
Day
Hour
Count
Duration
Avg duration
Apr 27 04 1 3s165ms 3s165ms 09 1 1s452ms 1s452ms 12 1 8m48s 8m48s 16 3 27m14s 9m4s 17 3 26m 8m40s 18 1 1s663ms 1s663ms 19 2 5s998ms 2s999ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 4s618ms - Times executed: 2 ]
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 = '1230239')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-04-27 16:48:07
Duration: 18m5s
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 = '1230239')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-04-27 17:26:08
Duration: 17m28s
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 = '1248054')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-04-27 16:38:58
Duration: 9m6s
Bind query: yes
x Hide
17
8
Details
10s606ms
1s6ms
2s946ms
1s325ms
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.disease_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
Apr 27 02 1 1s6ms 1s6ms 08 1 1s33ms 1s33ms 12 1 1s68ms 1s68ms 16 3 5s193ms 1s731ms 17 1 1s241ms 1s241ms 21 1 1s63ms 1s63ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 5s257ms - Times executed: 3 ]
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.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2056570')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-04-27 16:31:23
Duration: 2s946ms
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.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2063367')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-04-27 17:09:46
Duration: 1s241ms
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.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2063367')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 ;
Date: 2024-04-27 16:30:36
Duration: 1s132ms
Bind query: yes
x Hide
18
7
Details
51s998ms
1s7ms
15s760ms
7s428ms
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 #18
Day
Hour
Count
Duration
Avg duration
Apr 27 10 1 1s37ms 1s37ms 16 2 16s790ms 8s395ms 17 3 31s832ms 10s610ms 19 1 2s337ms 2s337ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 3s375ms - 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
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1206427') ) )
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-04-27 16:30:01
Duration: 15s760ms
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 = '1206427') ) )
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-04-27 17:08:31
Duration: 15s662ms
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 = '1206427') ) )
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-04-27 17:26:40
Duration: 15s163ms
Bind query: yes
x Hide
19
7
Details
21s88ms
1s212ms
5s375ms
3s12ms
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 #19
Day
Hour
Count
Duration
Avg duration
Apr 27 03 1 1s373ms 1s373ms 08 1 1s238ms 1s238ms 09 1 5s223ms 5s223ms 16 2 6s664ms 3s332ms 17 2 6s588ms 3s294ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 2s611ms - Times executed: 2 ]
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 = '648460'
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 = '648460') OFFSET 0 ) mq
ORDER BY
mq.genesymbolsort
LIMIT 50 ;
Date: 2024-04-27 17:11:29
Duration: 5s375ms
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 = '648460'
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 = '648460') OFFSET 0 ) mq
ORDER BY
mq.genesymbolsort
LIMIT 50 ;
Date: 2024-04-27 16:33:10
Duration: 5s328ms
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 = '648460'
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 = '648460') OFFSET 0 ) mq
ORDER BY
mq.genesymbolsort
LIMIT 50 ;
Date: 2024-04-27 09:57:12
Duration: 5s223ms
Bind query: yes
x Hide
20
7
Details
18s39ms
1s
3s481ms
2s577ms
with recursive sub_node (
object_id,
id,
path,
lvl
) as (
select
n.object_id,
n.id,
array [n.nm_sort],
?
from
dag_node n
where
n.object_id = ?
union all
select
n.object_id,
n.id,
cast ( path || n.nm_sort as varchar ( ?) []) ,
sn.lvl + ?
from
dag_node n
inner join sub_node sn on ( n.parent_id = sn.id) )
select distinct
t.nm prinm,
t.nm_html prinmhtml,
t.secondary_nm secondarynm,
t.acc_db_cd accdbcd,
t.acc_txt termacc,
t.is_leaf isleaf,
t.has_chems haschems,
t.has_diseases hasdiseases,
t.has_exposures hasexposures,
t.has_genes hasgenes,
sn.lvl,
sn.path,
max ( sn.lvl) over ( ) maxlvl,
t.has_phenotypes hasphenotypes
from
sub_node sn
inner join term t on sn.object_id = t.id
where
sn.lvl <= ?
order by
sn.path;
Times Reported Time consuming queries #20
Day
Hour
Count
Duration
Avg duration
Apr 27 05 4 8s694ms 2s173ms 06 1 2s487ms 2s487ms 16 1 3s375ms 3s375ms 17 1 3s481ms 3s481ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 3s343ms - Times executed: 1 ]
[ User: qaeu - Total duration: 3s323ms - Times executed: 1 ]
x Hide
WITH recursive sub_node (
object_id,
id,
path,
lvl
) AS (
SELECT
n.object_id,
n.id,
ARRAY [n.nm_sort],
1
FROM
dag_node n
WHERE
n.object_id = '648460'
UNION ALL
SELECT
n.object_id,
n.id,
CAST ( path || n.nm_sort AS varchar ( 600 ) []) ,
sn.lvl + 1
FROM
dag_node n
INNER JOIN sub_node sn ON ( n.parent_id = sn.id) )
SELECT
/* TreeTermBasicsDAO.getDescendants */
DISTINCT t.nm priNm,
t.nm_html priNmHtml,
t.secondary_nm secondaryNm,
t.acc_db_cd accDbCd,
t.acc_txt termAcc,
t.is_leaf isLeaf,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_genes hasGenes,
sn.lvl,
sn.path,
MAX ( sn.lvl) OVER ( ) maxLvl,
t.has_phenotypes hasPhenotypes
FROM
sub_node sn
INNER JOIN term t ON sn.object_id = t.id
WHERE
sn.lvl <= 4
ORDER BY
sn.path;
Date: 2024-04-27 17:11:28
Duration: 3s481ms
Bind query: yes
WITH recursive sub_node (
object_id,
id,
path,
lvl
) AS (
SELECT
n.object_id,
n.id,
ARRAY [n.nm_sort],
1
FROM
dag_node n
WHERE
n.object_id = '648460'
UNION ALL
SELECT
n.object_id,
n.id,
CAST ( path || n.nm_sort AS varchar ( 600 ) []) ,
sn.lvl + 1
FROM
dag_node n
INNER JOIN sub_node sn ON ( n.parent_id = sn.id) )
SELECT
/* TreeTermBasicsDAO.getDescendants */
DISTINCT t.nm priNm,
t.nm_html priNmHtml,
t.secondary_nm secondaryNm,
t.acc_db_cd accDbCd,
t.acc_txt termAcc,
t.is_leaf isLeaf,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_genes hasGenes,
sn.lvl,
sn.path,
MAX ( sn.lvl) OVER ( ) maxLvl,
t.has_phenotypes hasPhenotypes
FROM
sub_node sn
INNER JOIN term t ON sn.object_id = t.id
WHERE
sn.lvl <= 4
ORDER BY
sn.path;
Date: 2024-04-27 16:33:08
Duration: 3s375ms
Bind query: yes
WITH recursive sub_node (
object_id,
id,
path,
lvl
) AS (
SELECT
n.object_id,
n.id,
ARRAY [n.nm_sort],
1
FROM
dag_node n
WHERE
n.object_id = '588473'
UNION ALL
SELECT
n.object_id,
n.id,
CAST ( path || n.nm_sort AS varchar ( 600 ) []) ,
sn.lvl + 1
FROM
dag_node n
INNER JOIN sub_node sn ON ( n.parent_id = sn.id) )
SELECT
/* TreeTermBasicsDAO.getDescendants */
DISTINCT t.nm priNm,
t.nm_html priNmHtml,
t.secondary_nm secondaryNm,
t.acc_db_cd accDbCd,
t.acc_txt termAcc,
t.is_leaf isLeaf,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_genes hasGenes,
sn.lvl,
sn.path,
MAX ( sn.lvl) OVER ( ) maxLvl,
t.has_phenotypes hasPhenotypes
FROM
sub_node sn
INNER JOIN term t ON sn.object_id = t.id
WHERE
sn.lvl <= 2
ORDER BY
sn.path;
Date: 2024-04-27 05:35:13
Duration: 3s343ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
pgBadger