1
47
Details
54s690ms
1s99ms
1s239ms
1s163ms
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
Jul 27 00 2 2s306ms 1s153ms 01 2 2s327ms 1s163ms 02 1 1s112ms 1s112ms 03 5 5s719ms 1s143ms 05 4 4s797ms 1s199ms 06 1 1s159ms 1s159ms 07 2 2s338ms 1s169ms 08 2 2s314ms 1s157ms 09 2 2s317ms 1s158ms 10 2 2s333ms 1s166ms 12 3 3s481ms 1s160ms 13 2 2s318ms 1s159ms 16 2 2s299ms 1s149ms 17 1 1s164ms 1s164ms 18 3 3s379ms 1s126ms 19 2 2s211ms 1s105ms 20 3 3s573ms 1s191ms 21 2 2s399ms 1s199ms 22 3 3s568ms 1s189ms 23 3 3s566ms 1s188ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 35s890ms - Times executed: 31 ]
[ User: qaeu - Total duration: 1s239ms - 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
exposureMarkerTerm.id = '1433030'
or receptorTerm.id = '1433030'
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-07-27 05:43:40
Duration: 1s239ms
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
exposureMarkerTerm.id = '1433030'
or receptorTerm.id = '1433030'
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-07-27 05:38:39
Duration: 1s237ms
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 = '1630689'
or receptorTerm.id = '1630689'
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-07-27 21:55:00
Duration: 1s202ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
2
29
Details
2m7s
3s874ms
15s891ms
4s413ms
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 #2
Day
Hour
Count
Duration
Avg duration
Jul 27 03 2 7s816ms 3s908ms 04 1 4s341ms 4s341ms 05 1 4s180ms 4s180ms 07 2 7s808ms 3s904ms 09 1 3s954ms 3s954ms 10 4 27s914ms 6s978ms 11 1 4s40ms 4s40ms 12 1 3s945ms 3s945ms 14 1 3s946ms 3s946ms 15 1 4s4ms 4s4ms 16 7 27s996ms 3s999ms 19 3 11s827ms 3s942ms 21 2 8s302ms 4s151ms 22 2 7s916ms 3s958ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m27s - Times executed: 19 ]
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 = '1388675')
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 = '1388675')
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-07-27 10:45:57
Duration: 15s891ms
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 = '1281487')
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 = '1281487')
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-07-27 04:48:21
Duration: 4s341ms
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 = '1391318')
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 = '1391318')
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-07-27 05:20:12
Duration: 4s180ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
3
14
Details
1m
1s5ms
5s384ms
4s319ms
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 #3
Day
Hour
Count
Duration
Avg duration
Jul 27 08 3 15s781ms 5s260ms 11 4 20s952ms 5s238ms 12 2 6s42ms 3s21ms 14 1 5s154ms 5s154ms 15 2 6s196ms 3s98ms 18 1 5s334ms 5s334ms 19 1 1s17ms 1s17ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 28s206ms - Times executed: 7 ]
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 = '1317730'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-07-27 08:36:39
Duration: 5s384ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemGODAO */
d.abbr dagAbbr,
d.nm dagNm,
gt.level_min_no dagLevelMin,
gt.nm gonm,
gt.nm_html gonmhtml,
gt.acc_txt goacc,
gt.object_id goid,
te.corrected_p_val pValCorrected,
te.raw_p_val pValRaw,
te.target_match_qty targetmatchqty,
te.target_total_qty targettotalqty,
te.background_match_qty backgroundmatchqty,
te.background_total_qty backgroundtotalqty,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term_enrichment te
INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id
INNER JOIN dag d ON gt.dag_id = d.id
WHERE
te.term_id = '1255985'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-07-27 11:59:03
Duration: 5s368ms
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 = '1408037'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-07-27 11:59:23
Duration: 5s352ms
Bind query: yes
x Hide
4
13
Details
16s529ms
1s153ms
1s494ms
1s271ms
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 #4
Day
Hour
Count
Duration
Avg duration
Jul 27 02 1 1s268ms 1s268ms 05 2 2s731ms 1s365ms 07 1 1s494ms 1s494ms 11 1 1s239ms 1s239ms 15 1 1s227ms 1s227ms 16 3 3s733ms 1s244ms 19 2 2s318ms 1s159ms 20 1 1s277ms 1s277ms 22 1 1s239ms 1s239ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 12s570ms - Times executed: 10 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1226052')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1226052')
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-07-27 07:28:23
Duration: 1s494ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1211542')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1211542')
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-07-27 05:38:37
Duration: 1s377ms
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 = '1211542')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1211542')
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-07-27 05:43:39
Duration: 1s353ms
Bind query: yes
x Hide
5
12
Details
15s871ms
1s251ms
1s395ms
1s322ms
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 #5
Day
Hour
Count
Duration
Avg duration
Jul 27 01 1 1s251ms 1s251ms 04 2 2s653ms 1s326ms 05 2 2s781ms 1s390ms 06 1 1s264ms 1s264ms 08 2 2s643ms 1s321ms 11 1 1s295ms 1s295ms 12 2 2s622ms 1s311ms 22 1 1s359ms 1s359ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 10s560ms - Times executed: 8 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2071900')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2071900')
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-07-27 05:38:40
Duration: 1s395ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2071900')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2071900')
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-07-27 05:43:41
Duration: 1s385ms
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 = '2073499')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2073499')
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-07-27 22:35:08
Duration: 1s359ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
6
11
Details
29s96ms
1s98ms
10s967ms
2s645ms
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 #6
Day
Hour
Count
Duration
Avg duration
Jul 27 01 2 3s498ms 1s749ms 02 1 1s360ms 1s360ms 03 1 2s609ms 2s609ms 09 1 2s562ms 2s562ms 12 5 17s874ms 3s574ms 18 1 1s190ms 1s190ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s938ms - Times executed: 6 ]
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 = '2080559')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-07-27 12:24:58
Duration: 10s967ms
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 = '2080269')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-07-27 03:18:11
Duration: 2s609ms
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 = '2080269')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-07-27 09:23:19
Duration: 2s562ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
7
10
Details
21s115ms
1s2ms
3s306ms
2s111ms
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 #7
Day
Hour
Count
Duration
Avg duration
Jul 27 05 4 8s626ms 2s156ms 13 1 1s881ms 1s881ms 15 4 8s503ms 2s125ms 18 1 2s104ms 2s104ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s258ms - Times executed: 4 ]
[ User: qaeu - Total duration: 3s306ms - 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 = '589967'
UNION ALL
SELECT
n.object_id,
n.id,
CAST ( path || n.nm_sort AS varchar ( 600 ) []) ,
sn.lvl + 1
FROM
dag_node n
INNER JOIN sub_node sn ON ( n.parent_id = sn.id) )
SELECT
/* TreeTermBasicsDAO.getDescendants */
DISTINCT t.nm priNm,
t.nm_html priNmHtml,
t.secondary_nm secondaryNm,
t.acc_db_cd accDbCd,
t.acc_txt termAcc,
t.is_leaf isLeaf,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_genes hasGenes,
sn.lvl,
sn.path,
MAX ( sn.lvl) OVER ( ) maxLvl,
t.has_phenotypes hasPhenotypes
FROM
sub_node sn
INNER JOIN term t ON sn.object_id = t.id
WHERE
sn.lvl <= 2
ORDER BY
sn.path;
Date: 2024-07-27 05:40:12
Duration: 3s306ms
Database: ctdprd51
User: qaeu
Bind query: yes
WITH recursive sub_node (
object_id,
id,
path,
lvl
) AS (
SELECT
n.object_id,
n.id,
ARRAY [n.nm_sort],
1
FROM
dag_node n
WHERE
n.object_id = '589967'
UNION ALL
SELECT
n.object_id,
n.id,
CAST ( path || n.nm_sort AS varchar ( 600 ) []) ,
sn.lvl + 1
FROM
dag_node n
INNER JOIN sub_node sn ON ( n.parent_id = sn.id) )
SELECT
/* TreeTermBasicsDAO.getDescendants */
DISTINCT t.nm priNm,
t.nm_html priNmHtml,
t.secondary_nm secondaryNm,
t.acc_db_cd accDbCd,
t.acc_txt termAcc,
t.is_leaf isLeaf,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_genes hasGenes,
sn.lvl,
sn.path,
MAX ( sn.lvl) OVER ( ) maxLvl,
t.has_phenotypes hasPhenotypes
FROM
sub_node sn
INNER JOIN term t ON sn.object_id = t.id
WHERE
sn.lvl <= 2
ORDER BY
sn.path;
Date: 2024-07-27 05:35:13
Duration: 3s301ms
Database: ctdprd51
User: pubeu
Bind query: yes
WITH recursive sub_node (
object_id,
id,
path,
lvl
) AS (
SELECT
n.object_id,
n.id,
ARRAY [n.nm_sort],
1
FROM
dag_node n
WHERE
n.object_id = '649871'
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-07-27 15:58:25
Duration: 2s761ms
Bind query: yes
x Hide
8
10
Details
13s906ms
1s365ms
1s409ms
1s390ms
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 #8
Day
Hour
Count
Duration
Avg duration
Jul 27 02 2 2s780ms 1s390ms 03 1 1s403ms 1s403ms 05 3 4s197ms 1s399ms 07 1 1s375ms 1s375ms 08 1 1s365ms 1s365ms 09 2 2s783ms 1s391ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 8s346ms - Times executed: 6 ]
x Hide
SELECT
/* GeneBrowseTermsDAO */
t.nm,
t.nm_html nmHtml,
t.secondary_nm secondaryNm,
t.acc_txt acc,
'name:' || t.nm accQueryStr,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term t
WHERE
t.object_type_id = '4'
AND REGEXP_REPLACE ( UPPER ( SUBSTRING ( t.nm, 1 , 1 ) ) , '[^A-Z]', '#') = 'A'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-07-27 02:44:30
Duration: 1s409ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* GeneBrowseTermsDAO */
t.nm,
t.nm_html nmHtml,
t.secondary_nm secondaryNm,
t.acc_txt acc,
'name:' || t.nm accQueryStr,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term t
WHERE
t.object_type_id = '4'
AND REGEXP_REPLACE ( UPPER ( SUBSTRING ( t.nm, 1 , 1 ) ) , '[^A-Z]', '#') = 'A'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-07-27 03:02:16
Duration: 1s403ms
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-07-27 05:42:09
Duration: 1s403ms
Bind query: yes
x Hide
9
9
Details
12s152ms
1s337ms
1s381ms
1s350ms
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 #9
Day
Hour
Count
Duration
Avg duration
Jul 27 05 2 2s690ms 1s345ms 06 1 1s363ms 1s363ms 10 1 1s349ms 1s349ms 14 1 1s337ms 1s337ms 18 2 2s720ms 1s360ms 21 1 1s346ms 1s346ms 22 1 1s344ms 1s344ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 6s736ms - 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-07-27 18:45:45
Duration: 1s381ms
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-07-27 06:45:47
Duration: 1s363ms
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-07-27 10:42:41
Duration: 1s349ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
10
6
Details
11s595ms
1s135ms
3s230ms
1s932ms
select
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
g.nm genesymbol,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
gd.network_score networkscore,
gd.indirect_chem_qty inferredcount,
gd.reference_qty referencecount,
gd.exposure_reference_qty exposurereferencecount,
case when gd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
gene_disease_axn a
where
a.gene_id = gd.gene_id
and a.disease_id = gd.disease_id)
else
null
end actiontypes
from
gene_disease gd
inner join term g on gd.gene_id = g.id
inner join term d on gd.disease_id = d.id
where
gd.disease_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
gd.network_score desc nulls last ,
g.nm_sort,
d.nm_sort;
Times Reported Time consuming queries #10
Day
Hour
Count
Duration
Avg duration
Jul 27 11 2 3s208ms 1s604ms 12 2 3s151ms 1s575ms 13 1 2s4ms 2s4ms 20 1 3s230ms 3s230ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 7s518ms - Times executed: 4 ]
x Hide
SELECT
/* DiseaseGeneAssnsDAO */
d.nm diseaseNm,
d.acc_txt diseaseAcc,
d.acc_db_cd diseaseAccDbCd,
d.id diseaseId,
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
gd.network_score networkScore,
gd.indirect_chem_qty inferredCount,
gd.reference_qty referenceCount,
gd.exposure_reference_qty exposureReferenceCount,
CASE WHEN gd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gd.gene_id
AND a.disease_id = gd.disease_id)
ELSE
NULL
END actionTypes
FROM
gene_disease gd
INNER JOIN term g ON gd.gene_id = g.id
INNER JOIN term d ON gd.disease_id = d.id
WHERE
gd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2072513')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-07-27 20:52:25
Duration: 3s230ms
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 = '2076269')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-07-27 11:43:27
Duration: 2s72ms
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 = '2072176')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-07-27 13:08:29
Duration: 2s4ms
Bind query: yes
x Hide
11
4
Details
5s584ms
1s315ms
1s473ms
1s396ms
select
fg.nm fromgenesymbol,
fg.acc_txt fromgeneacc,
tg.nm togenesymbol,
tg.acc_txt togeneacc,
ft.nm fromtaxonnm,
ft.secondary_nm fromtaxoncommonnm,
ft.acc_txt fromtaxonacc,
tt.nm totaxonnm,
tt.secondary_nm totaxoncommonnm,
tt.acc_txt totaxonacc,
ggr.experimental_sys_nm,
ggr.experimental_sys_type,
(
select
string_agg ( ggt.throughput_txt, ? order by ggt.throughput_txt)
from
gene_gene_ref_throughput ggt
where
ggt.gene_gene_reference_id = ggr.id) throughput,
count ( * ) over ( ) fullrowcount
from
gene_gene_reference ggr
inner join term fg on ggr.from_gene_id = fg.id
inner join term tg on ggr.to_gene_id = tg.id
inner join term ft on ggr.from_taxon_id = ft.id
inner join term tt on ggr.to_taxon_id = tt.id
where
ggr.reference_id = ?
order by
fg.nm_sort,
tg.nm_sort
limit ?;
Times Reported Time consuming queries #11
Day
Hour
Count
Duration
Avg duration
Jul 27 05 4 5s584ms 1s396ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1s434ms - Times executed: 1 ]
[ User: qaeu - Total duration: 1s315ms - Times executed: 1 ]
x Hide
SELECT
/* ReferenceGeneGeneIxnsDAO */
fg.nm fromGeneSymbol,
fg.acc_txt fromGeneAcc,
tg.nm toGeneSymbol,
tg.acc_txt toGeneAcc,
ft.nm fromTaxonNm,
ft.secondary_nm fromTaxonCommonNm,
ft.acc_txt fromTaxonAcc,
tt.nm toTaxonNm,
tt.secondary_nm toTaxonCommonNm,
tt.acc_txt toTaxonAcc,
ggr.experimental_sys_nm,
ggr.experimental_sys_type,
(
SELECT
STRING_AGG ( ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt)
FROM
gene_gene_ref_throughput ggt
WHERE
ggt.gene_gene_reference_id = ggr.id) throughput,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_gene_reference ggr
INNER JOIN term fg ON ggr.from_gene_id = fg.id
INNER JOIN term tg ON ggr.to_gene_id = tg.id
INNER JOIN term ft ON ggr.from_taxon_id = ft.id
INNER JOIN term tt ON ggr.to_taxon_id = tt.id
WHERE
ggr.reference_id = '111363'
ORDER BY
fg.nm_sort,
tg.nm_sort
LIMIT 50 ;
Date: 2024-07-27 05:38:06
Duration: 1s473ms
Bind query: yes
SELECT
/* ReferenceGeneGeneIxnsDAO */
fg.nm fromGeneSymbol,
fg.acc_txt fromGeneAcc,
tg.nm toGeneSymbol,
tg.acc_txt toGeneAcc,
ft.nm fromTaxonNm,
ft.secondary_nm fromTaxonCommonNm,
ft.acc_txt fromTaxonAcc,
tt.nm toTaxonNm,
tt.secondary_nm toTaxonCommonNm,
tt.acc_txt toTaxonAcc,
ggr.experimental_sys_nm,
ggr.experimental_sys_type,
(
SELECT
STRING_AGG ( ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt)
FROM
gene_gene_ref_throughput ggt
WHERE
ggt.gene_gene_reference_id = ggr.id) throughput,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_gene_reference ggr
INNER JOIN term fg ON ggr.from_gene_id = fg.id
INNER JOIN term tg ON ggr.to_gene_id = tg.id
INNER JOIN term ft ON ggr.from_taxon_id = ft.id
INNER JOIN term tt ON ggr.to_taxon_id = tt.id
WHERE
ggr.reference_id = '111363'
ORDER BY
fg.nm_sort,
tg.nm_sort
LIMIT 50 ;
Date: 2024-07-27 05:38:05
Duration: 1s434ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ReferenceGeneGeneIxnsDAO */
fg.nm fromGeneSymbol,
fg.acc_txt fromGeneAcc,
tg.nm toGeneSymbol,
tg.acc_txt toGeneAcc,
ft.nm fromTaxonNm,
ft.secondary_nm fromTaxonCommonNm,
ft.acc_txt fromTaxonAcc,
tt.nm toTaxonNm,
tt.secondary_nm toTaxonCommonNm,
tt.acc_txt toTaxonAcc,
ggr.experimental_sys_nm,
ggr.experimental_sys_type,
(
SELECT
STRING_AGG ( ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt)
FROM
gene_gene_ref_throughput ggt
WHERE
ggt.gene_gene_reference_id = ggr.id) throughput,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_gene_reference ggr
INNER JOIN term fg ON ggr.from_gene_id = fg.id
INNER JOIN term tg ON ggr.to_gene_id = tg.id
INNER JOIN term ft ON ggr.from_taxon_id = ft.id
INNER JOIN term tt ON ggr.to_taxon_id = tt.id
WHERE
ggr.reference_id = '111363'
ORDER BY
fg.nm_sort,
tg.nm_sort
LIMIT 50 ;
Date: 2024-07-27 05:43:08
Duration: 1s360ms
Bind query: yes
x Hide
12
3
Details
7m36s
2s384ms
3m57s
2m32s
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 #12
Day
Hour
Count
Duration
Avg duration
Jul 27 01 1 2s384ms 2s384ms 18 1 3m57s 3m57s 23 1 3m37s 3m37s
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 7m36s - Times executed: 3 ]
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 = '1242798')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-07-27 18:16:34
Duration: 3m57s
Database: ctdprd51
User: pubeu
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 = '1242889')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-07-27 23:50:11
Duration: 3m37s
Database: ctdprd51
User: pubeu
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 = '1242493')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-07-27 01:42:18
Duration: 2s384ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
13
2
Details
8s323ms
4s132ms
4s191ms
4s161ms
select distinct
associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm,
associatedterm.id associatedtermid,
ptr.ixn_id ixnid,
associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort,
coalesce ( associatedterm.secondary_nm, ?) casrn,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
count ( distinct taxonterm.nm) taxoncount,
i.ixn_prose_html ixnprosehtml,
i.ixn_prose_txt ixnprose,
i.sort_txt ixnsort,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( distinct ptr.reference_id) refcount,
pt.indirect_term_qty inferredcount,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedterm on ptr.term_id = associatedterm.id
inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id
left outer join term taxonterm on ptr.taxon_id = taxonterm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedterm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id
where
ptr.term_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and upper ( baseterm.nm)
like ?) )
and ptr.term_object_type_id = ?
and ptr.phenotype_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and baseterm.id in (
select
object_id
from
db_link l
where
l.acc_txt = ?
and l.type_cd = ?
and l.object_type_id = ?) ) )
and taxonterm.id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and baseterm.id in (
select
object_id
from
db_link l
where
l.acc_txt = ?
and l.type_cd = ?
and l.object_type_id = ?) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = ?
and action_degree_type_nm in ( . .. ) )
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ?;
Times Reported Time consuming queries #13
Day
Hour
Count
Duration
Avg duration
Jul 27 05 2 8s323ms 4s161ms
x Hide
Examples
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
ptr.term_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'ZINC') )
and ptr.term_object_type_id = 2
and ptr.phenotype_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006915'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and taxonTerm.id in (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 1
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = '9606'
AND l.type_cd = 'A'
AND l.object_type_id = 1 ) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = 'phenotype'
and action_degree_type_nm in ( 'increases') )
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-07-27 05:43:46
Duration: 4s191ms
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
ptr.term_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'ZINC') )
and ptr.term_object_type_id = 2
and ptr.phenotype_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006915'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and taxonTerm.id in (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 1
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = '9606'
AND l.type_cd = 'A'
AND l.object_type_id = 1 ) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = 'phenotype'
and action_degree_type_nm in ( 'increases') )
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-07-27 05:38:45
Duration: 4s132ms
Bind query: yes
x Hide
14
2
Details
8s293ms
4s142ms
4s151ms
4s146ms
select
? "Input",
sqi.chem_nm "ChemicalName",
sqi.chem_acc_txt "ChemicalID",
sqi.casrn "CasRN",
sqi.gene_symbol "GeneSymbol",
sqi.gene_acc_txt "GeneID",
sqi.ontology_nm "Ontology",
sqi.go_term_nm "GoTermName",
sqi.go_acc_txt "GoTermID"
from ( with sq as (
select distinct
c.id chem_id,
c.nm chem_nm,
c.acc_txt chem_acc_txt,
c.secondary_nm casrn,
c.nm_sort chem_nm_sort,
gcr.gene_id,
g.nm gene_symbol,
g.acc_txt gene_acc_txt,
g.nm_sort gene_symbol_sort
from
term c
inner join gene_chem_reference gcr on c.id = gcr.chem_id
inner join term g on gcr.gene_id = g.id
where ( c.id = ?) )
select distinct
sq.chem_nm,
sq.chem_acc_txt,
sq.casrn,
sq.gene_symbol,
sq.gene_acc_txt,
gt.nm go_term_nm,
gt.acc_txt go_acc_txt,
sq.chem_nm_sort,
sq.gene_symbol_sort,
gt.nm_sort,
d.nm ontology_nm
from
sq
inner join gene_go_annot gga on sq.gene_id = gga.gene_id
inner join dag_node gt on gga.go_term_id = gt.object_id
inner join dag d on gt.dag_id = d.id
where
gga.is_not = false
and ( d.id = ?
or d.id = ?)
order by
sq.chem_nm_sort,
sq.gene_symbol_sort,
d.nm,
gt.nm_sort) sqi;
Times Reported Time consuming queries #14
Day
Hour
Count
Duration
Avg duration
Jul 27 05 2 8s293ms 4s146ms
x Hide
Examples User(s) involved
[ User: qaeu - Total duration: 4s142ms - Times executed: 1 ]
x Hide
SELECT
/* BatchChemGODAO */
'ddt' "Input",
sqi.chem_nm "ChemicalName",
sqi.chem_acc_txt "ChemicalID",
sqi.casRN "CasRN",
sqi.gene_symbol "GeneSymbol",
sqi.gene_acc_txt "GeneID",
sqi.ontology_nm "Ontology",
sqi.go_term_nm "GoTermName",
sqi.go_acc_txt "GoTermID"
FROM ( WITH sq AS (
SELECT DISTINCT
c.id chem_id,
c.nm chem_nm,
c.acc_txt chem_acc_txt,
c.secondary_nm casRN,
c.nm_sort chem_nm_sort,
gcr.gene_id,
g.nm gene_symbol,
g.acc_txt gene_acc_txt,
g.nm_sort gene_symbol_sort
FROM
term c
INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id
INNER JOIN term g ON gcr.gene_id = g.id
WHERE ( c.id = 1291235 ) )
SELECT DISTINCT
sq.chem_nm,
sq.chem_acc_txt,
sq.casRN,
sq.gene_symbol,
sq.gene_acc_txt,
gt.nm go_term_nm,
gt.acc_txt go_acc_txt,
sq.chem_nm_sort,
sq.gene_symbol_sort,
gt.nm_sort,
d.nm ontology_nm
FROM
sq
INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id
INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id
INNER JOIN dag d ON gt.dag_id = d.id
WHERE
gga.is_not = false
AND ( d.id = 5
OR d.id = 4 )
ORDER BY
sq.chem_nm_sort,
sq.gene_symbol_sort,
d.nm,
gt.nm_sort) sqi;
Date: 2024-07-27 05:38:24
Duration: 4s151ms
Bind query: yes
SELECT
/* BatchChemGODAO */
'ddt' "Input",
sqi.chem_nm "ChemicalName",
sqi.chem_acc_txt "ChemicalID",
sqi.casRN "CasRN",
sqi.gene_symbol "GeneSymbol",
sqi.gene_acc_txt "GeneID",
sqi.ontology_nm "Ontology",
sqi.go_term_nm "GoTermName",
sqi.go_acc_txt "GoTermID"
FROM ( WITH sq AS (
SELECT DISTINCT
c.id chem_id,
c.nm chem_nm,
c.acc_txt chem_acc_txt,
c.secondary_nm casRN,
c.nm_sort chem_nm_sort,
gcr.gene_id,
g.nm gene_symbol,
g.acc_txt gene_acc_txt,
g.nm_sort gene_symbol_sort
FROM
term c
INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id
INNER JOIN term g ON gcr.gene_id = g.id
WHERE ( c.id = 1291235 ) )
SELECT DISTINCT
sq.chem_nm,
sq.chem_acc_txt,
sq.casRN,
sq.gene_symbol,
sq.gene_acc_txt,
gt.nm go_term_nm,
gt.acc_txt go_acc_txt,
sq.chem_nm_sort,
sq.gene_symbol_sort,
gt.nm_sort,
d.nm ontology_nm
FROM
sq
INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id
INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id
INNER JOIN dag d ON gt.dag_id = d.id
WHERE
gga.is_not = false
AND ( d.id = 5
OR d.id = 4 )
ORDER BY
sq.chem_nm_sort,
sq.gene_symbol_sort,
d.nm,
gt.nm_sort) sqi;
Date: 2024-07-27 05:43:26
Duration: 4s142ms
Database: ctdprd51
User: qaeu
Bind query: yes
x Hide
15
2
Details
8s276ms
4s123ms
4s153ms
4s138ms
select distinct
associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm,
associatedterm.id associatedtermid,
ptr.ixn_id ixnid,
associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort,
coalesce ( associatedterm.secondary_nm, ?) casrn,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
count ( distinct taxonterm.nm) taxoncount,
i.ixn_prose_html ixnprosehtml,
i.ixn_prose_txt ixnprose,
i.sort_txt ixnsort,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( distinct ptr.reference_id) refcount,
pt.indirect_term_qty inferredcount,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedterm on ptr.term_id = associatedterm.id
inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id
left outer join term taxonterm on ptr.taxon_id = taxonterm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedterm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id
where
ptr.term_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and upper ( baseterm.nm)
like ?) )
and ptr.term_object_type_id = ?
and ptr.phenotype_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and baseterm.id in (
select
object_id
from
db_link l
where
l.acc_txt = ?
and l.type_cd = ?
and l.object_type_id = ?) ) )
and i.id in (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and upper ( baseterm.nm)
like ?) ) )
and taxonterm.id in ( select distinct
dp.descendant_object_id
from
dag_path dp
where
dp.ancestor_object_id in ( select distinct
id
from
term baseterm
where
object_type_id = ?
and baseterm.id in (
select
object_id
from
db_link l
where
l.acc_txt = ?
and l.type_cd = ?
and l.object_type_id = ?) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = ?
and action_degree_type_nm in ( . .. ) )
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ?;
Times Reported Time consuming queries #15
Day
Hour
Count
Duration
Avg duration
Jul 27 05 2 8s276ms 4s138ms
x Hide
Examples
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
ptr.term_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'ACETYLCYSTEINE') )
and ptr.term_object_type_id = 2
and ptr.phenotype_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006979'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and i.id in (
select
ixn_id
from
ixn_anatomy
where
anatomy_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 10
and upper ( baseTerm.nm)
LIKE 'CARDIOVASCULAR SYSTEM') ) )
and taxonTerm.id in (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 1
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = '9605'
AND l.type_cd = 'A'
AND l.object_type_id = 1 ) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = 'phenotype'
and action_degree_type_nm in ( 'increases') )
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-07-27 05:43:50
Duration: 4s153ms
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
ptr.term_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'ACETYLCYSTEINE') )
and ptr.term_object_type_id = 2
and ptr.phenotype_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006979'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and i.id in (
select
ixn_id
from
ixn_anatomy
where
anatomy_id IN (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 10
and upper ( baseTerm.nm)
LIKE 'CARDIOVASCULAR SYSTEM') ) )
and taxonTerm.id in (
select
/* DBConstants.getDAGTermSQL */
distinct dp.descendant_object_id
from
dag_path dp
WHERE
dp.ancestor_object_id in ( select distinct
id
from
term baseTerm
where
object_type_id = 1
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = '9605'
AND l.type_cd = 'A'
AND l.object_type_id = 1 ) ) )
and i.id in (
select
ixn_id
from
ixn_axn
where
action_type_nm = 'phenotype'
and action_degree_type_nm in ( 'increases') )
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-07-27 05:38:49
Duration: 4s123ms
Bind query: yes
x Hide
16
2
Details
7s963ms
3s964ms
3s999ms
3s981ms
select
gcr.ixn_id,
null ,
null ,
null
from
gene_chem_reference gcr
where
gcr.gene_id = any ( array ( (
select
gd.gene_id
from
term t
inner join dag_path dp on t.id = dp.ancestor_object_id
inner join gene_disease gd on dp.descendant_object_id = gd.disease_id
where
upper ( t.nm)
like ?
and t.object_type_id = ?) ) )
and gcr.id in (
select
gcra.gene_chem_reference_id
from
gene_chem_reference_axn gcra
where ( gcra.action_degree_type_nm = ?) ) ;
Times Reported Time consuming queries #16
Day
Hour
Count
Duration
Avg duration
Jul 27 05 2 7s963ms 3s981ms
x Hide
Examples User(s) involved
[ User: qaeu - Total duration: 3s999ms - Times executed: 1 ]
[ User: pubeu - Total duration: 3s964ms - Times executed: 1 ]
x Hide
SELECT
/* CIQH.getIxnCacheQuery */
gcr.ixn_id,
NULL ,
NULL ,
NULL
FROM
gene_chem_reference gcr
WHERE
/* CIQH.getIxnWhereCore */
gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* IQH.getMasterDiseaseWhereEquals.Name.Gene */
gd.gene_id
FROM
term t
INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id
INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id
WHERE
UPPER ( t.nm)
LIKE 'ASTHMA'
AND t.object_type_id = 3 ) ) )
AND gcr.id IN (
SELECT
gcra.gene_chem_reference_id
FROM
gene_chem_reference_axn gcra
WHERE ( gcra.action_degree_type_nm = 'increases') ) ;
Date: 2024-07-27 05:42:20
Duration: 3s999ms
Database: ctdprd51
User: qaeu
Bind query: yes
SELECT
/* CIQH.getIxnCacheQuery */
gcr.ixn_id,
NULL ,
NULL ,
NULL
FROM
gene_chem_reference gcr
WHERE
/* CIQH.getIxnWhereCore */
gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* IQH.getMasterDiseaseWhereEquals.Name.Gene */
gd.gene_id
FROM
term t
INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id
INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id
WHERE
UPPER ( t.nm)
LIKE 'ASTHMA'
AND t.object_type_id = 3 ) ) )
AND gcr.id IN (
SELECT
gcra.gene_chem_reference_id
FROM
gene_chem_reference_axn gcra
WHERE ( gcra.action_degree_type_nm = 'increases') ) ;
Date: 2024-07-27 05:37:19
Duration: 3s964ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
17
2
Details
7s779ms
3s882ms
3s897ms
3s889ms
select
g.id geneid,
g.acc_txt acc,
g.nm nm,
g.nm nmhtml,
g.secondary_nm secondarynm,
g.has_chems haschems,
g.has_diseases hasdiseases,
g.has_exposures hasexposures,
g.has_phenotypes hasphenotypes,
count ( * ) over ( ) fullrowcount
from
term g
where
g.id in (
select
gcr.gene_id
from
gene_chem_reference gcr
where
gcr.gene_id = any ( array ( (
select
gd.gene_id
from
term t
inner join dag_path dp on t.id = dp.ancestor_object_id
inner join gene_disease gd on dp.descendant_object_id = gd.disease_id
where
upper ( t.nm)
like ?
and t.object_type_id = ?) ) )
and gcr.id in (
select
gcra.gene_chem_reference_id
from
gene_chem_reference_axn gcra
where ( gcra.action_degree_type_nm = ?) ) )
order by
g.nm_sort,
g.id
limit ?;
Times Reported Time consuming queries #17
Day
Hour
Count
Duration
Avg duration
Jul 27 05 2 7s779ms 3s889ms
x Hide
Examples
SELECT
/* AdvancedGeneQueryDAO.getData */
g.id geneId,
g.acc_txt acc,
g.nm nm,
g.nm nmHtml,
g.secondary_nm secondaryNm,
g.has_chems hasChems,
g.has_diseases hasDiseases,
g.has_exposures hasExposures,
g.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term g
WHERE
g.id IN (
SELECT
gcr.gene_id
FROM
gene_chem_reference gcr
WHERE
/* CIQH.getIxnWhereCore */
gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* IQH.getMasterDiseaseWhereEquals.Name.Gene */
gd.gene_id
FROM
term t
INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id
INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id
WHERE
UPPER ( t.nm)
LIKE 'ASTHMA'
AND t.object_type_id = 3 ) ) )
AND gcr.id IN (
SELECT
gcra.gene_chem_reference_id
FROM
gene_chem_reference_axn gcra
WHERE ( gcra.action_degree_type_nm = 'increases') ) )
ORDER BY
g.nm_sort,
g.id
LIMIT 50 ;
Date: 2024-07-27 05:42:24
Duration: 3s897ms
Bind query: yes
SELECT
/* AdvancedGeneQueryDAO.getData */
g.id geneId,
g.acc_txt acc,
g.nm nm,
g.nm nmHtml,
g.secondary_nm secondaryNm,
g.has_chems hasChems,
g.has_diseases hasDiseases,
g.has_exposures hasExposures,
g.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term g
WHERE
g.id IN (
SELECT
gcr.gene_id
FROM
gene_chem_reference gcr
WHERE
/* CIQH.getIxnWhereCore */
gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* IQH.getMasterDiseaseWhereEquals.Name.Gene */
gd.gene_id
FROM
term t
INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id
INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id
WHERE
UPPER ( t.nm)
LIKE 'ASTHMA'
AND t.object_type_id = 3 ) ) )
AND gcr.id IN (
SELECT
gcra.gene_chem_reference_id
FROM
gene_chem_reference_axn gcra
WHERE ( gcra.action_degree_type_nm = 'increases') ) )
ORDER BY
g.nm_sort,
g.id
LIMIT 50 ;
Date: 2024-07-27 05:37:24
Duration: 3s882ms
Bind query: yes
x Hide
18
2
Details
6s800ms
3s395ms
3s404ms
3s400ms
select
g.nm genesymbol,
g.id geneid,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
i.id ixnid,
i.ixn_prose_txt ixnprose,
i.ixn_prose_html ixnprosehtml,
i.actions_txt ixnactions,
count ( distinct gcr.reference_id) refcount,
count ( distinct gcr.taxon_id) taxoncount,
count ( * ) over ( ) fullrowcount
from
gene_chem_reference gcr
inner join ixn i on gcr.ixn_id = i.id
inner join term g on gcr.gene_id = g.id
inner join term c on gcr.chem_id = c.id
where
gcr.gene_id = any ( array ( (
select
tp.term_id
from
term_pathway tp
where
upper ( tp.pathway_nm)
like ?
and tp.object_type_id = ?) ) )
and gcr.id in (
select
gcra.gene_chem_reference_id
from
gene_chem_reference_axn gcra
where ( gcra.action_degree_type_nm = ?) )
group by
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
order by
g.nm_sort,
c.nm_sort,
i.sort_txt
limit ?;
Times Reported Time consuming queries #18
Day
Hour
Count
Duration
Avg duration
Jul 27 05 2 6s800ms 3s400ms
x Hide
Examples
SELECT
/* AdvancedIxnQueryDAO.getData */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
c.nm chemNm,
c.nm_html chemNmhtml,
c.acc_txt chemAcc,
c.secondary_nm casRN,
c.id chemId,
i.id ixnId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
COUNT ( DISTINCT gcr.reference_id) refCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN ixn i ON gcr.ixn_id = i.id
INNER JOIN term g ON gcr.gene_id = g.id
INNER JOIN term c ON gcr.chem_id = c.id
WHERE
/* CIQH.getIxnWhereCore */
gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* IQH.getMasterPathwayWhereEquals.Name */
tp.term_id
FROM
term_pathway tp
WHERE
UPPER ( tp.pathway_nm)
LIKE 'METABOLISM'
AND tp.object_type_id = 4 ) ) )
AND gcr.id IN (
SELECT
gcra.gene_chem_reference_id
FROM
gene_chem_reference_axn gcra
WHERE ( gcra.action_degree_type_nm = 'increases') )
GROUP BY
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
g.nm_sort,
c.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-07-27 05:37:31
Duration: 3s404ms
Bind query: yes
SELECT
/* AdvancedIxnQueryDAO.getData */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
c.nm chemNm,
c.nm_html chemNmhtml,
c.acc_txt chemAcc,
c.secondary_nm casRN,
c.id chemId,
i.id ixnId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
COUNT ( DISTINCT gcr.reference_id) refCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN ixn i ON gcr.ixn_id = i.id
INNER JOIN term g ON gcr.gene_id = g.id
INNER JOIN term c ON gcr.chem_id = c.id
WHERE
/* CIQH.getIxnWhereCore */
gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* IQH.getMasterPathwayWhereEquals.Name */
tp.term_id
FROM
term_pathway tp
WHERE
UPPER ( tp.pathway_nm)
LIKE 'METABOLISM'
AND tp.object_type_id = 4 ) ) )
AND gcr.id IN (
SELECT
gcra.gene_chem_reference_id
FROM
gene_chem_reference_axn gcra
WHERE ( gcra.action_degree_type_nm = 'increases') )
GROUP BY
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
g.nm_sort,
c.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-07-27 05:42:32
Duration: 3s395ms
Bind query: yes
x Hide
19
2
Details
4s365ms
2s181ms
2s183ms
2s182ms
select distinct
phenotypeterm.nm as gonm,
phenotypeterm.nm_html as gonmhtml,
phenotypeterm.acc_txt as goacc,
diseaseterm.nm as diseasenm,
diseaseterm.nm_html as diseasenmhtml,
diseaseterm.acc_txt as diseaseacc,
diseaseterm.acc_db_cd as diseaseaccdbcd,
chemterm.nm as chemnm,
chemterm.nm_html as chemnmhtml,
chemterm.acc_txt as chemacc,
geneterm.nm as genesymbol,
geneterm.nm_html as genesymbolhtml,
geneterm.acc_txt as geneacc,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference viachemptr,
phenotype_term_reference viageneptr,
term phenotypeterm,
term diseaseterm,
term geneterm,
term chemterm
where
viachemptr.via_term_id in (
select
baseterm.id
from
term baseterm
where
baseterm.object_type_id = ?
and upper ( baseterm.nm)
like ?)
and viageneptr.via_term_id in (
select
baseterm.id
from
term baseterm
where
baseterm.object_type_id = ?
and upper ( baseterm.nm)
like ?)
and phenotypeterm.id = any ( array (
select
baseterm.id
from
term baseterm
where
baseterm.object_type_id = ?
and baseterm.id in (
select
object_id
from
db_link l
where
l.acc_txt = ?
and l.type_cd = ?
and l.object_type_id = ?) ) )
and viachemptr.phenotype_id = phenotypeterm.id
and viachemptr.term_object_type_id = ?
and viachemptr.term_id = diseaseterm.id
and viachemptr.via_term_object_type_id = ?
and viachemptr.via_term_id = chemterm.id
and viachemptr.term_id = viageneptr.term_id
and viachemptr.phenotype_id = viageneptr.phenotype_id
and viageneptr.via_term_object_type_id = ?
and viageneptr.via_term_id = geneterm.id
and exists (
select
?
from
gene_chem_reference
where
gene_id = geneterm.id
and chem_id = chemterm.id)
group by
phenotypeterm.nm,
phenotypeterm.nm_html,
phenotypeterm.acc_txt,
diseaseterm.nm,
diseaseterm.nm_html,
diseaseterm.acc_txt,
diseaseterm.acc_db_cd,
chemterm.nm,
chemterm.nm_html,
chemterm.acc_txt,
geneterm.nm,
geneterm.nm_html,
geneterm.acc_txt
order by
chemterm.nm
limit ?;
Times Reported Time consuming queries #19
Day
Hour
Count
Duration
Avg duration
Jul 27 05 2 4s365ms 2s182ms
x Hide
Examples
select distinct
phenotypeTerm.nm AS goNm,
phenotypeTerm.nm_html AS goNmHtml,
phenotypeTerm.acc_txt AS goAcc,
diseaseTerm.nm AS diseaseNm,
diseaseTerm.nm_html AS diseaseNmHtml,
diseaseTerm.acc_txt AS diseaseAcc,
diseaseTerm.acc_db_cd AS diseaseAccDbCd,
chemTerm.nm AS chemNm,
chemTerm.nm_html AS chemNmHtml,
chemTerm.acc_txt AS chemAcc,
geneTerm.nm AS geneSymbol,
geneTerm.nm_html AS geneSymbolHtml,
geneTerm.acc_txt AS geneAcc,
COUNT ( * ) OVER ( ) fullRowCount
from
PHENOTYPE_TERM_REFERENCE viaChemPTR,
PHENOTYPE_TERM_REFERENCE viaGenePTR,
TERM phenotypeTerm,
TERM diseaseTerm,
TERM geneTerm,
TERM chemTerm
where
viaChemPTR.via_term_id IN (
select
baseTerm.id
from
term baseTerm
WHERE
baseTerm.object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'ZINC')
and viaGenePTR.via_term_id IN (
select
baseTerm.id
from
term baseTerm
WHERE
baseTerm.object_type_id = 4
and upper ( baseTerm.nm)
LIKE 'BCL2')
and phenotypeTerm.id = ANY ( ARRAY (
select
baseTerm.id
from
term baseTerm
WHERE
baseTerm.object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006915'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and viaChemPTR.phenotype_id = phenotypeTerm.id
and viaChemPTR.term_object_type_id = 3
and viaChemPTR.term_id = diseaseTerm.id
and viaChemPTR.via_term_object_type_id = 2
and viaChemPTR.via_term_id = chemTerm.id
and viaChemPTR.term_id = viaGenePTR.term_id
and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id
and viaGenePTR.via_term_object_type_id = 4
and viaGenePTR.via_term_id = geneTerm.id
and exists (
select
1
from
gene_chem_reference
where
gene_id = geneTerm.id
and chem_id = chemTerm.id)
GROUP BY
phenotypeTerm.nm,
phenotypeTerm.nm_html,
phenotypeTerm.acc_txt,
diseaseTerm.nm,
diseaseTerm.nm_html,
diseaseTerm.acc_txt,
diseaseTerm.acc_db_cd,
chemTerm.nm,
chemTerm.nm_html,
chemTerm.acc_txt,
geneTerm.nm,
geneTerm.nm_html,
geneTerm.acc_txt
order by
chemTerm.nm
LIMIT 50 ;
Date: 2024-07-27 05:43:54
Duration: 2s183ms
Bind query: yes
select distinct
phenotypeTerm.nm AS goNm,
phenotypeTerm.nm_html AS goNmHtml,
phenotypeTerm.acc_txt AS goAcc,
diseaseTerm.nm AS diseaseNm,
diseaseTerm.nm_html AS diseaseNmHtml,
diseaseTerm.acc_txt AS diseaseAcc,
diseaseTerm.acc_db_cd AS diseaseAccDbCd,
chemTerm.nm AS chemNm,
chemTerm.nm_html AS chemNmHtml,
chemTerm.acc_txt AS chemAcc,
geneTerm.nm AS geneSymbol,
geneTerm.nm_html AS geneSymbolHtml,
geneTerm.acc_txt AS geneAcc,
COUNT ( * ) OVER ( ) fullRowCount
from
PHENOTYPE_TERM_REFERENCE viaChemPTR,
PHENOTYPE_TERM_REFERENCE viaGenePTR,
TERM phenotypeTerm,
TERM diseaseTerm,
TERM geneTerm,
TERM chemTerm
where
viaChemPTR.via_term_id IN (
select
baseTerm.id
from
term baseTerm
WHERE
baseTerm.object_type_id = 2
and upper ( baseTerm.nm)
LIKE 'ZINC')
and viaGenePTR.via_term_id IN (
select
baseTerm.id
from
term baseTerm
WHERE
baseTerm.object_type_id = 4
and upper ( baseTerm.nm)
LIKE 'BCL2')
and phenotypeTerm.id = ANY ( ARRAY (
select
baseTerm.id
from
term baseTerm
WHERE
baseTerm.object_type_id = 5
and baseTerm.id in (
select
object_id
from
db_link l
where
l.acc_txt = 'GO:0006915'
AND l.type_cd = 'A'
AND l.object_type_id = 5 ) ) )
and viaChemPTR.phenotype_id = phenotypeTerm.id
and viaChemPTR.term_object_type_id = 3
and viaChemPTR.term_id = diseaseTerm.id
and viaChemPTR.via_term_object_type_id = 2
and viaChemPTR.via_term_id = chemTerm.id
and viaChemPTR.term_id = viaGenePTR.term_id
and viaChemPTR.phenotype_id = viaGenePTR.phenotype_id
and viaGenePTR.via_term_object_type_id = 4
and viaGenePTR.via_term_id = geneTerm.id
and exists (
select
1
from
gene_chem_reference
where
gene_id = geneTerm.id
and chem_id = chemTerm.id)
GROUP BY
phenotypeTerm.nm,
phenotypeTerm.nm_html,
phenotypeTerm.acc_txt,
diseaseTerm.nm,
diseaseTerm.nm_html,
diseaseTerm.acc_txt,
diseaseTerm.acc_db_cd,
chemTerm.nm,
chemTerm.nm_html,
chemTerm.acc_txt,
geneTerm.nm,
geneTerm.nm_html,
geneTerm.acc_txt
order by
chemTerm.nm
LIMIT 50 ;
Date: 2024-07-27 05:38:53
Duration: 2s181ms
Bind query: yes
x Hide
20
2
Details
2s388ms
1s192ms
1s195ms
1s194ms
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
abbrauthorstxt
limit ?;
Times Reported Time consuming queries #20
Day
Hour
Count
Duration
Avg duration
Jul 27 21 2 2s388ms 1s194ms
x Hide
Examples
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 = '1630689'
or receptorTerm.id = '1630689'
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
abbrAuthorsTxt
LIMIT 50 ;
Date: 2024-07-27 21:55:06
Duration: 1s195ms
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 = '1630689'
or receptorTerm.id = '1630689'
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
abbrAuthorsTxt
LIMIT 50 ;
Date: 2024-07-27 21:55:07
Duration: 1s192ms
Bind query: yes
x Hide
pgBadger