1
75
Details
4m56s
3s691ms
15s218ms
3s956ms
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 #1
Day
Hour
Count
Duration
Avg duration
Sep 14 01 1 3s727ms 3s727ms 02 2 7s500ms 3s750ms 04 3 11s234ms 3s744ms 06 1 3s795ms 3s795ms 07 2 7s614ms 3s807ms 08 24 1m30s 3s783ms 09 1 3s752ms 3s752ms 10 2 7s521ms 3s760ms 11 2 7s616ms 3s808ms 12 4 14s923ms 3s730ms 13 26 1m51s 4s295ms 14 1 3s736ms 3s736ms 15 3 11s318ms 3s772ms 19 1 3s999ms 3s999ms 22 1 3s788ms 3s788ms 23 1 3s772ms 3s772ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 2m4s - Times executed: 33 ]
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 = '1393242')
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 = '1393242')
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-09-14 13:50:32
Duration: 15s218ms
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 = '1381661')
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 = '1381661')
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-09-14 13:36:34
Duration: 4s49ms
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 = '1399632')
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 = '1399632')
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-09-14 13:51:47
Duration: 4s14ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
2
56
Details
1m3s
1s53ms
1s244ms
1s132ms
select distinct
stressorterm.nm as chemnm,
stressorterm.nm_html as chemnmhtml,
stressorterm.nm_sort as chemnmsort,
stressorterm.acc_txt as chemacc,
(
select
string_agg ( distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?) ) as stressorsrctypenm,
stressor.src_details as stressorsrcdetails,
stressor.sample_qty as stressorsampleqty,
stressor.note as stressornote,
receptor.qty as nbrreceptors,
receptor.description as receptors,
receptor.note as receptornotes,
receptorterm.nm || ? || (
select
cd
from
object_type
where
id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms,
(
select
string_agg ( distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?) ) as smokerstatus,
receptor.age as agerange,
receptor.age_uom_nm as ageuomnm,
receptor.age_qualifier_nm as agequalifiernm,
receptor.gender_nm as gendernmsearch,
receptor.id receptorid,
(
select
string_agg ( pct || ? || gender_nm || ? || gender_nm_html, ?)
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderdetails,
(
select
string_agg ( distinct receptorrace.race_nm || ? || receptorrace.pct, ?) ) as receptorrace,
(
select
string_agg ( distinct eventassaymethod.nm, ?) ) as assaymethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumacctxt,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr,
event.detection_limit as detectionlimit,
event.detection_limit_uom as detectionlimituom,
event.detection_freq as detectionfreq,
event.note as eventnote,
(
select
string_agg ( distinct eventlocation.geographic_region_nm, ?) ) as stateorprovince,
(
select
string_agg ( distinct eventlocation.locality_txt, ?) ) as localitytxt,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
exposuremarkerterm.nm || ? || (
select
cd
from
object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers,
event.exp_marker_lvl as assaylevel,
assay_uom as measurement,
assay_measurement_stat as measurementstat,
assay_note as assaynote,
eiot.description as outcomerltnp,
diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield,
outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm,
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrauthorstxt,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
outcome.note as outcomenote,
eventlocation.exp_event_id as eventid,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
inner join reference r on e.reference_id = r.id
left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id
left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id
left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id
left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id
left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
left outer join country on eventlocation.country_id = country.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt
and e.reference_acc_db_id = referenceexp.reference_acc_db_id
left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id
where
exposuremarkerterm.id = ?
or receptorterm.id = ?
group by
chemnm,
chemnmhtml,
chemnmsort,
chemacc,
stressorsrcdetails,
stressorsampleqty,
stressornote,
receptorterms,
medium,
mediumacctxt,
assayedmarkers,
assaylevel,
measurement,
measurementstat,
assaynote,
outcomerltnp,
diseasefield,
phenotypefield,
phenotypeactiondegreetypenm,
ref,
r.abbr_authors_txt,
collectionstartandendyr,
receptorid,
detectionlimit,
detectionlimituom,
detectionfreq,
eventnote,
outcomenote,
eventid
order by
chemnmsort
limit ?;
Times Reported Time consuming queries #2
Day
Hour
Count
Duration
Avg duration
Sep 14 03 1 1s89ms 1s89ms 04 4 4s590ms 1s147ms 05 3 3s606ms 1s202ms 06 1 1s85ms 1s85ms 07 1 1s88ms 1s88ms 08 3 3s444ms 1s148ms 10 1 1s149ms 1s149ms 11 1 1s137ms 1s137ms 12 1 1s122ms 1s122ms 13 1 1s133ms 1s133ms 14 3 3s386ms 1s128ms 15 2 2s231ms 1s115ms 16 6 6s785ms 1s130ms 17 2 2s241ms 1s120ms 18 5 5s641ms 1s128ms 19 7 7s824ms 1s117ms 20 2 2s154ms 1s77ms 21 5 5s578ms 1s115ms 22 4 4s659ms 1s164ms 23 3 3s466ms 1s155ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 26s - Times executed: 23 ]
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 = '1437597'
or receptorTerm.id = '1437597'
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-09-14 05:38:41
Duration: 1s244ms
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 = '1437597'
or receptorTerm.id = '1437597'
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-09-14 05:43:42
Duration: 1s222ms
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 = '1499563'
or receptorTerm.id = '1499563'
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-09-14 22:35:14
Duration: 1s180ms
Bind query: yes
x Hide
3
29
Details
35s917ms
1s142ms
1s486ms
1s238ms
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 #3
Day
Hour
Count
Duration
Avg duration
Sep 14 00 2 2s426ms 1s213ms 02 2 2s566ms 1s283ms 03 1 1s224ms 1s224ms 05 3 3s803ms 1s267ms 06 1 1s486ms 1s486ms 09 2 2s540ms 1s270ms 12 2 2s434ms 1s217ms 13 2 2s393ms 1s196ms 14 1 1s214ms 1s214ms 15 3 3s570ms 1s190ms 16 1 1s270ms 1s270ms 17 1 1s208ms 1s208ms 18 2 2s454ms 1s227ms 19 1 1s198ms 1s198ms 20 2 2s365ms 1s182ms 22 1 1s219ms 1s219ms 23 2 2s538ms 1s269ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 22s330ms - Times executed: 18 ]
[ User: qaeu - Total duration: 1s272ms - Times executed: 1 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1230580')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1230580')
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-09-14 06:26:01
Duration: 1s486ms
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 = '1216070')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1216070')
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-09-14 05:38:39
Duration: 1s363ms
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 = '1250476')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1250476')
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-09-14 02:57:00
Duration: 1s300ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
4
20
Details
25s33ms
1s140ms
1s393ms
1s251ms
select distinct
stressorterm.nm as chemnm,
stressorterm.nm_html as chemnmhtml,
stressorterm.nm_sort as chemnmsort,
stressorterm.acc_txt as chemacc,
(
select
string_agg ( distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?) ) as stressorsrctypenm,
stressor.src_details as stressorsrcdetails,
stressor.sample_qty as stressorsampleqty,
stressor.note as stressornote,
receptor.qty as nbrreceptors,
receptor.description as receptors,
receptor.note as receptornotes,
receptorterm.nm || ? || (
select
cd
from
object_type
where
id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms,
(
select
string_agg ( distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?) ) as smokerstatus,
receptor.age as agerange,
receptor.age_uom_nm as ageuomnm,
receptor.age_qualifier_nm as agequalifiernm,
receptor.gender_nm as gendernmsearch,
receptor.id receptorid,
(
select
string_agg ( pct || ? || gender_nm || ? || gender_nm_html, ?)
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderdetails,
(
select
string_agg ( distinct receptorrace.race_nm || ? || receptorrace.pct, ?) ) as receptorrace,
(
select
string_agg ( distinct eventassaymethod.nm, ?) ) as assaymethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumacctxt,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr,
event.detection_limit as detectionlimit,
event.detection_limit_uom as detectionlimituom,
event.detection_freq as detectionfreq,
event.note as eventnote,
(
select
string_agg ( distinct eventlocation.geographic_region_nm, ?) ) as stateorprovince,
(
select
string_agg ( distinct eventlocation.locality_txt, ?) ) as localitytxt,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
exposuremarkerterm.nm || ? || (
select
cd
from
object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers,
event.exp_marker_lvl as assaylevel,
assay_uom as measurement,
assay_measurement_stat as measurementstat,
assay_note as assaynote,
eiot.description as outcomerltnp,
diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield,
outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm,
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrauthorstxt,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
outcome.note as outcomenote,
eventlocation.exp_event_id as eventid,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
inner join reference r on e.reference_id = r.id
left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id
left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id
left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id
left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id
left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
left outer join country on eventlocation.country_id = country.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt
and e.reference_acc_db_id = referenceexp.reference_acc_db_id
left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
or receptorterm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
group by
chemnm,
chemnmhtml,
chemnmsort,
chemacc,
stressorsrcdetails,
stressorsampleqty,
stressornote,
receptorterms,
medium,
mediumacctxt,
assayedmarkers,
assaylevel,
measurement,
measurementstat,
assaynote,
outcomerltnp,
diseasefield,
phenotypefield,
phenotypeactiondegreetypenm,
ref,
r.abbr_authors_txt,
collectionstartandendyr,
receptorid,
detectionlimit,
detectionlimituom,
detectionfreq,
eventnote,
outcomenote,
eventid
order by
chemnmsort
limit ?;
Times Reported Time consuming queries #4
Day
Hour
Count
Duration
Avg duration
Sep 14 02 1 1s240ms 1s240ms 05 3 3s984ms 1s328ms 07 2 2s506ms 1s253ms 08 1 1s253ms 1s253ms 10 2 2s485ms 1s242ms 14 5 6s108ms 1s221ms 15 1 1s140ms 1s140ms 16 1 1s191ms 1s191ms 19 2 2s523ms 1s261ms 21 1 1s214ms 1s214ms 22 1 1s384ms 1s384ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 11s235ms - Times executed: 9 ]
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 = '2075714')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2075714')
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-09-14 05:43:43
Duration: 1s393ms
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 = '2076868')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2076868')
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-09-14 22:04:40
Duration: 1s384ms
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 = '2075714')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2075714')
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-09-14 05:38:42
Duration: 1s342ms
Bind query: yes
x Hide
5
15
Details
1m16s
1s7ms
11s156ms
5s110ms
select
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
g.nm genesymbol,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
gd.network_score networkscore,
gd.indirect_chem_qty inferredcount,
gd.reference_qty referencecount,
gd.exposure_reference_qty exposurereferencecount,
case when gd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
gene_disease_axn a
where
a.gene_id = gd.gene_id
and a.disease_id = gd.disease_id)
else
null
end actiontypes
from
gene_disease gd
inner join term g on gd.gene_id = g.id
inner join term d on gd.disease_id = d.id
where
gd.disease_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
gd.network_score desc nulls last ,
g.nm_sort,
d.nm_sort
limit ?;
Times Reported Time consuming queries #5
Day
Hour
Count
Duration
Avg duration
Sep 14 03 5 44s263ms 8s852ms 06 1 1s55ms 1s55ms 07 1 2s340ms 2s340ms 10 1 6s431ms 6s431ms 11 2 4s978ms 2s489ms 13 2 11s188ms 5s594ms 18 1 1s369ms 1s369ms 22 2 5s27ms 2s513ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m - Times executed: 10 ]
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 = '2086129')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-09-14 03:28:06
Duration: 11s156ms
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 = '2086129')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-09-14 03:28:04
Duration: 10s841ms
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 = '2086129')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-09-14 03:28:09
Duration: 10s744ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
6
15
Details
19s675ms
1s268ms
1s357ms
1s311ms
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 #6
Day
Hour
Count
Duration
Avg duration
Sep 14 00 6 7s819ms 1s303ms 01 1 1s357ms 1s357ms 05 2 2s651ms 1s325ms 08 1 1s356ms 1s356ms 09 1 1s299ms 1s299ms 13 1 1s282ms 1s282ms 14 2 2s579ms 1s289ms 17 1 1s330ms 1s330ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 6s594ms - Times executed: 5 ]
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-09-14 01:17:37
Duration: 1s357ms
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-09-14 08:32:37
Duration: 1s356ms
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-09-14 05:42:09
Duration: 1s349ms
Bind query: yes
x Hide
7
11
Details
50s909ms
4s176ms
5s465ms
4s628ms
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 #7
Day
Hour
Count
Duration
Avg duration
Sep 14 03 1 4s490ms 4s490ms 11 4 18s452ms 4s613ms 13 1 4s306ms 4s306ms 18 2 9s885ms 4s942ms 19 1 4s508ms 4s508ms 20 2 9s265ms 4s632ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 18s654ms - Times executed: 4 ]
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 = '1259489'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-09-14 11:44:14
Duration: 5s465ms
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 = '1432893'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-09-14 18:48:17
Duration: 5s462ms
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 = '1294101'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-09-14 20:54:30
Duration: 4s906ms
Bind query: yes
x Hide
8
8
Details
16s290ms
1s25ms
5s145ms
2s36ms
select
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
(
select
string_agg ( distinct stressorterm.nm || ? || (
select
cd
from object_type
where
id = stressorterm.object_type_id) || ? || stressorterm.nm_html || ? || stressorterm.acc_txt || ? || stressorterm.acc_db_cd, ?) ) as stressoragents,
(
select
string_agg ( distinct coalesce ( receptorterm.nm, ?) || ? || coalesce ( (
select
cd
from object_type
where
id = receptorterm.object_type_id) , ?) || ? || coalesce ( receptorterm.nm_html, ?) || ? || coalesce ( receptorterm.acc_txt, ?) || ? || coalesce ( receptorterm.acc_db_cd, ?) || ? || receptor.description, ?) ) as receptors,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
(
select
string_agg ( distinct location.locality_txt, ?) ) as localities,
(
select
string_agg ( distinct event.medium_nm || ? || coalesce ( event.medium_term_acc_txt, ?) , ?) ) as assaymediums,
(
select
string_agg ( distinct exposuremarkerterm.nm || ? || (
select
cd
from object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd, ?) ) as assayedmarkers,
(
select
string_agg ( distinct diseaseterm.nm || ? || (
select
cd
from object_type
where
id = diseaseterm.object_type_id) || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd, ?) ) as diseases,
(
select
string_agg ( distinct phenotypeterm.nm || ? || (
select
cd
from object_type
where
id = phenotypeterm.object_type_id) || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd, ?) ) as phenotypes,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
re.author_summary summary,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join reference r on e.reference_id = r.id
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join exp_event_location location on e.exp_event_id = location.exp_event_id
left outer join country on location.country_id = country.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
inner join reference_exp re on e.reference_id = re.reference_id
left outer join exp_study_factor expstudyfactor on re.id = expstudyfactor.reference_exp_id
where
e.reference_id = any ( array (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like ?)
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressoragents
limit ?;
Times Reported Time consuming queries #8
Day
Hour
Count
Duration
Avg duration
Sep 14 10 1 1s25ms 1s25ms 17 2 2s570ms 1s285ms 19 5 12s693ms 2s538ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 2s288ms - Times executed: 2 ]
x Hide
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1393242')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1393242')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 ;
Date: 2024-09-14 19:21:25
Duration: 5s145ms
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1422479')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1422479')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 ;
Date: 2024-09-14 19:19:59
Duration: 3s639ms
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1394368')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1394368')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 ;
Date: 2024-09-14 19:20:34
Duration: 1s469ms
Bind query: yes
x Hide
9
8
Details
13s223ms
1s619ms
1s693ms
1s652ms
select distinct
associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm,
associatedterm.id associatedtermid,
ptr.ixn_id ixnid,
associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort,
coalesce ( associatedterm.secondary_nm, ?) casrn,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
count ( distinct taxonterm.nm) taxoncount,
i.ixn_prose_html ixnprosehtml,
i.ixn_prose_txt ixnprose,
i.sort_txt ixnsort,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( distinct ptr.reference_id) refcount,
pt.indirect_term_qty inferredcount,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedterm on ptr.term_id = associatedterm.id
inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id
left outer join term taxonterm on ptr.taxon_id = taxonterm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedterm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id
where
associatedterm.id = any ( array ( (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) )
and ptr.term_object_type_id = ?
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ? offset ?;
Times Reported Time consuming queries #9
Day
Hour
Count
Duration
Avg duration
Sep 14 19 8 13s223ms 1s652ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 4s933ms - Times executed: 3 ]
x Hide
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1432893') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 OFFSET 50 ;
Date: 2024-09-14 19:13:30
Duration: 1s693ms
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1432893') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 OFFSET 350 ;
Date: 2024-09-14 19:06:55
Duration: 1s693ms
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1432893') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 OFFSET 250 ;
Date: 2024-09-14 19:11:27
Duration: 1s690ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
10
8
Details
10s401ms
1s271ms
1s325ms
1s300ms
select
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
(
select
string_agg ( distinct stressorterm.nm || ? || (
select
cd
from object_type
where
id = stressorterm.object_type_id) || ? || stressorterm.nm_html || ? || stressorterm.acc_txt || ? || stressorterm.acc_db_cd, ?) ) as stressoragents,
(
select
string_agg ( distinct coalesce ( receptorterm.nm, ?) || ? || coalesce ( (
select
cd
from object_type
where
id = receptorterm.object_type_id) , ?) || ? || coalesce ( receptorterm.nm_html, ?) || ? || coalesce ( receptorterm.acc_txt, ?) || ? || coalesce ( receptorterm.acc_db_cd, ?) || ? || receptor.description, ?) ) as receptors,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
(
select
string_agg ( distinct location.locality_txt, ?) ) as localities,
(
select
string_agg ( distinct event.medium_nm || ? || coalesce ( event.medium_term_acc_txt, ?) , ?) ) as assaymediums,
(
select
string_agg ( distinct exposuremarkerterm.nm || ? || (
select
cd
from object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd, ?) ) as assayedmarkers,
(
select
string_agg ( distinct diseaseterm.nm || ? || (
select
cd
from object_type
where
id = diseaseterm.object_type_id) || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd, ?) ) as diseases,
(
select
string_agg ( distinct phenotypeterm.nm || ? || (
select
cd
from object_type
where
id = phenotypeterm.object_type_id) || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd, ?) ) as phenotypes,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
re.author_summary summary,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join reference r on e.reference_id = r.id
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join exp_event_location location on e.exp_event_id = location.exp_event_id
left outer join country on location.country_id = country.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
inner join reference_exp re on e.reference_id = re.reference_id
left outer join exp_study_factor expstudyfactor on re.id = expstudyfactor.reference_exp_id
where
e.reference_id = any ( array (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like ?)
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressoragents
limit ? offset ?;
Times Reported Time consuming queries #10
Day
Hour
Count
Duration
Avg duration
Sep 14 17 4 5s196ms 1s299ms 18 4 5s204ms 1s301ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1s281ms - Times executed: 1 ]
x Hide
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1432896')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1432896')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 OFFSET 50 ;
Date: 2024-09-14 18:07:00
Duration: 1s325ms
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1432896')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1432896')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 OFFSET 350 ;
Date: 2024-09-14 17:50:44
Duration: 1s321ms
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1432896')
and chem.id = s1.chem_id
and s1.id = e1.exp_stressor_id
union
select
reference_id
from
exposure e1,
term t,
exp_event event1
where
t.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1432896')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 OFFSET 150 ;
Date: 2024-09-14 18:15:54
Duration: 1s314ms
Bind query: yes
x Hide
11
7
Details
31m32s
1s450ms
17m56s
4m30s
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 #11
Day
Hour
Count
Duration
Avg duration
Sep 14 02 2 4m7s 2m3s 16 1 45s559ms 45s559ms 18 2 8m41s 4m20s 19 1 17m56s 17m56s 21 1 1s450ms 1s450ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 22m51s - Times executed: 5 ]
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 = '1239879')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-09-14 19:04:08
Duration: 17m56s
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 = '1257694')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-09-14 18:53:39
Duration: 8m38s
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 = '1235030')
and diseaseTerm.object_type_id = 3
ORDER BY
chemNetworkCount desc ,
geneNetworkCount desc
LIMIT 50 ;
Date: 2024-09-14 02:08:10
Duration: 4m4s
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
12
7
Details
1m28s
1s194ms
21s109ms
12s637ms
select
sq.*,
count ( * ) over ( ) fullrowcount
from ( select distinct
gt.nm gonm,
gt.nm_html gonmhtml,
gt.nm_sort gonmsort,
gt.acc_txt goacc,
gt.object_id goid,
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid
from
dag_node gt
inner join gene_go_annot gga on gt.object_id = gga.go_term_id
inner join term g on gga.gene_id = g.id
where
gt.id in (
select
p.descendant_dag_node_id
from
dag_path p
where
p.ancestor_object_id = ?)
and gga.is_not = false ) sq
order by
sq.gonmsort,
sq.genesymbolsort
limit ?;
Times Reported Time consuming queries #12
Day
Hour
Count
Duration
Avg duration
Sep 14 02 2 41s165ms 20s582ms 18 5 47s294ms 9s458ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m8s - Times executed: 4 ]
x Hide
SELECT
/* GoGenesDAO */
sq.*,
COUNT ( * ) OVER ( ) fullRowCount
FROM ( SELECT DISTINCT
gt.nm gonm,
gt.nm_html gonmhtml,
gt.nm_sort gonmsort,
gt.acc_txt goacc,
gt.object_id goid,
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid
FROM
dag_node gt
INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id
INNER JOIN term g ON gga.gene_id = g.id
WHERE
gt.id IN (
SELECT
p.descendant_dag_node_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1216067')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-09-14 02:20:15
Duration: 21s109ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* GoGenesDAO */
sq.*,
COUNT ( * ) OVER ( ) fullRowCount
FROM ( SELECT DISTINCT
gt.nm gonm,
gt.nm_html gonmhtml,
gt.nm_sort gonmsort,
gt.acc_txt goacc,
gt.object_id goid,
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid
FROM
dag_node gt
INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id
INNER JOIN term g ON gga.gene_id = g.id
WHERE
gt.id IN (
SELECT
p.descendant_dag_node_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1216067')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-09-14 02:20:17
Duration: 20s56ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* GoGenesDAO */
sq.*,
COUNT ( * ) OVER ( ) fullRowCount
FROM ( SELECT DISTINCT
gt.nm gonm,
gt.nm_html gonmhtml,
gt.nm_sort gonmsort,
gt.acc_txt goacc,
gt.object_id goid,
g.nm genesymbol,
g.nm_sort genesymbolsort,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid
FROM
dag_node gt
INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id
INNER JOIN term g ON gga.gene_id = g.id
WHERE
gt.id IN (
SELECT
p.descendant_dag_node_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1216067')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 ;
Date: 2024-09-14 18:43:53
Duration: 19s982ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
13
7
Details
17s865ms
1s315ms
6s117ms
2s552ms
select distinct
associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm,
associatedterm.id associatedtermid,
ptr.ixn_id ixnid,
associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort,
coalesce ( associatedterm.secondary_nm, ?) casrn,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
count ( distinct taxonterm.nm) taxoncount,
i.ixn_prose_html ixnprosehtml,
i.ixn_prose_txt ixnprose,
i.sort_txt ixnsort,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( distinct ptr.reference_id) refcount,
pt.indirect_term_qty inferredcount,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedterm on ptr.term_id = associatedterm.id
inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id
left outer join term taxonterm on ptr.taxon_id = taxonterm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedterm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id
where
associatedterm.id = any ( array ( (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) )
and ptr.term_object_type_id = ?
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ?;
Times Reported Time consuming queries #13
Day
Hour
Count
Duration
Avg duration
Sep 14 01 1 1s713ms 1s713ms 17 3 6s830ms 2s276ms 19 3 9s321ms 3s107ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 5s259ms - Times executed: 3 ]
x Hide
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1393242') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 19:23:04
Duration: 6s117ms
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1432896') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 17:28:39
Duration: 3s306ms
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
associatedTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1422479') ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 19:18:49
Duration: 1s888ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
14
7
Details
17s275ms
1s14ms
5s344ms
2s467ms
select
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
g.nm genesymbol,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
gd.network_score networkscore,
gd.indirect_chem_qty inferredcount,
gd.reference_qty referencecount,
gd.exposure_reference_qty exposurereferencecount,
case when gd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
gene_disease_axn a
where
a.gene_id = gd.gene_id
and a.disease_id = gd.disease_id)
else
null
end actiontypes
from
gene_disease gd
inner join term g on gd.gene_id = g.id
inner join term d on gd.disease_id = d.id
where
gd.disease_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
gd.network_score desc nulls last ,
g.nm_sort,
d.nm_sort
limit ? offset ?;
Times Reported Time consuming queries #14
Day
Hour
Count
Duration
Avg duration
Sep 14 04 1 5s344ms 5s344ms 07 1 1s14ms 1s14ms 11 1 2s565ms 2s565ms 15 1 4s514ms 4s514ms 17 1 1s593ms 1s593ms 18 1 1s129ms 1s129ms 21 1 1s112ms 1s112ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 7s471ms - Times executed: 3 ]
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 = '2079317')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 50 ;
Date: 2024-09-14 04:36:41
Duration: 5s344ms
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 = '2085868')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 150 ;
Date: 2024-09-14 15:12:43
Duration: 4s514ms
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 = '2084083')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 50 ;
Date: 2024-09-14 11:57:01
Duration: 2s565ms
Bind query: yes
x Hide
15
7
Details
9s459ms
1s340ms
1s359ms
1s351ms
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 #15
Day
Hour
Count
Duration
Avg duration
Sep 14 04 1 1s351ms 1s351ms 05 2 2s705ms 1s352ms 08 1 1s351ms 1s351ms 13 1 1s340ms 1s340ms 22 2 2s709ms 1s354ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 4s68ms - Times executed: 3 ]
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-09-14 05:38:33
Duration: 1s359ms
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-09-14 22:32:18
Duration: 1s357ms
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-09-14 22:36:45
Duration: 1s351ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
16
6
Details
1m43s
5s596ms
32s74ms
17s212ms
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 desc ,
gd.network_score desc nulls last ,
g.nm_sort,
d.nm_sort
limit ? offset ?;
Times Reported Time consuming queries #16
Day
Hour
Count
Duration
Avg duration
Sep 14 03 5 1m37s 19s536ms 23 1 5s596ms 5s596ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1m37s - Times executed: 5 ]
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 = '2086129')
ORDER BY
actionTypes DESC ,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 3825250 ;
Date: 2024-09-14 03:30:05
Duration: 32s74ms
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 = '2086129')
ORDER BY
actionTypes DESC ,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 3825250 ;
Date: 2024-09-14 03:29:59
Duration: 30s530ms
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 = '2086129')
ORDER BY
actionTypes DESC ,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 300 ;
Date: 2024-09-14 03:29:55
Duration: 12s145ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
17
6
Details
1m11s
10s605ms
12s434ms
11s840ms
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 desc ,
gd.network_score desc nulls last ,
g.nm_sort,
d.nm_sort
limit ?;
Times Reported Time consuming queries #17
Day
Hour
Count
Duration
Avg duration
Sep 14 03 6 1m11s 11s840ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 12s395ms - Times executed: 1 ]
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 = '2086129')
ORDER BY
actionTypes DESC ,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-09-14 03:29:24
Duration: 12s434ms
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 = '2086129')
ORDER BY
actionTypes DESC ,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-09-14 03:29:25
Duration: 12s395ms
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 = '2086129')
ORDER BY
actionTypes DESC ,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-09-14 03:29:22
Duration: 11s928ms
Bind query: yes
x Hide
18
6
Details
25s299ms
1s2ms
15s557ms
4s216ms
select distinct
associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm,
associatedterm.id associatedtermid,
ptr.ixn_id ixnid,
associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort,
coalesce ( associatedterm.secondary_nm, ?) casrn,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
count ( distinct taxonterm.nm) taxoncount,
i.ixn_prose_html ixnprosehtml,
i.ixn_prose_txt ixnprose,
i.sort_txt ixnsort,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( distinct ptr.reference_id) refcount,
pt.indirect_term_qty inferredcount,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedterm on ptr.term_id = associatedterm.id
inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id
left outer join term taxonterm on ptr.taxon_id = taxonterm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedterm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id
where
phenotypeterm.id = any ( array ( (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) )
and associatedterm.object_type_id = ?
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ?;
Times Reported Time consuming queries #18
Day
Hour
Count
Duration
Avg duration
Sep 14 11 2 2s377ms 1s188ms 13 1 5s347ms 5s347ms 18 3 17s575ms 5s858ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 16s908ms - Times executed: 2 ]
x Hide
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1216067') ) )
and associatedTerm.object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 18:44:18
Duration: 15s557ms
Database: ctdprd51
User: pubeu
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1242626') ) )
and associatedTerm.object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 13:45:57
Duration: 5s347ms
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1212440') ) )
and associatedTerm.object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 11:03:45
Duration: 1s350ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
19
6
Details
13s721ms
1s127ms
6s882ms
2s286ms
select distinct
associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm,
associatedterm.id associatedtermid,
ptr.ixn_id ixnid,
associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort,
coalesce ( associatedterm.secondary_nm, ?) casrn,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
count ( distinct taxonterm.nm) taxoncount,
i.ixn_prose_html ixnprosehtml,
i.ixn_prose_txt ixnprose,
i.sort_txt ixnsort,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( distinct ptr.reference_id) refcount,
pt.indirect_term_qty inferredcount,
count ( * ) over ( ) fullrowcount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedterm on ptr.term_id = associatedterm.id
inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id
left outer join term taxonterm on ptr.taxon_id = taxonterm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedterm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id
where
ptr.ixn_id = any ( array ( (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) ) )
and ptr.term_object_type_id = ?
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort
limit ?;
Times Reported Time consuming queries #19
Day
Hour
Count
Duration
Avg duration
Sep 14 23 6 13s721ms 2s286ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 5s710ms - Times executed: 4 ]
x Hide
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
ptr.ixn_id = ANY ( ARRAY ( (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2090903') ) ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 23:49:57
Duration: 6s882ms
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
ptr.ixn_id = ANY ( ARRAY ( (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2091184') ) ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 23:49:12
Duration: 1s551ms
Database: ctdprd51
User: pubeu
Bind query: yes
select distinct
/* ChemPhenotypesAssnsDAO */
associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm,
associatedTerm.id associatedTermId,
ptr.ixn_id ixnId,
associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort,
COALESCE ( associatedTerm.secondary_nm, '') casRN,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype,
phenotypeTerm.id phenotypeId,
(
SELECT
STRING_AGG ( distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE ( taxonTerm.secondary_nm, '') , '|') ) as taxonTerms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
COUNT ( DISTINCT taxonTerm.nm) taxonCount,
i.ixn_prose_html ixnProseHtml,
i.ixn_prose_txt ixnProse,
i.sort_txt ixnSort,
(
SELECT
STRING_AGG ( distinct r.acc_txt, '|') ) as references,
COUNT ( DISTINCT ptr.reference_id) refCount,
pt.indirect_term_qty inferredCount,
COUNT ( * ) OVER ( ) fullRowCount
from
phenotype_term_reference ptr
inner join phenotype_term pt on ptr.term_id = pt.term_id
and ptr.phenotype_id = pt.phenotype_id
inner join term associatedTerm on ptr.term_id = associatedTerm.id
inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id
left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id
inner join reference r on ptr.reference_id = r.id
inner join ixn i on ptr.ixn_id = i.id
inner join object_type o on associatedTerm.object_type_id = o.id
left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id
left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id
where
ptr.ixn_id = ANY ( ARRAY ( (
select
ixn_id
from
ixn_anatomy
where
anatomy_id in (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2089512') ) ) )
and ptr.term_object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort
LIMIT 50 ;
Date: 2024-09-14 23:50:24
Duration: 1s478ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
20
5
Details
10s745ms
1s799ms
2s990ms
2s149ms
select
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposurereferencecount,
case when cd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
chem_disease_axn a
where
a.chem_id = cd.chem_id
and a.disease_id = cd.disease_id)
else
null
end actiontypes
from
chem_disease cd
inner join term c on cd.chem_id = c.id
inner join term d on cd.disease_id = d.id
where
cd.chem_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
cd.network_score desc nulls last ,
d.nm_sort,
c.nm_sort
limit ? offset ?;
Times Reported Time consuming queries #20
Day
Hour
Count
Duration
Avg duration
Sep 14 13 4 7s754ms 1s938ms 20 1 2s990ms 2s990ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 4s813ms - Times executed: 2 ]
x Hide
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1432893')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 OFFSET 533700 ;
Date: 2024-09-14 20:07:05
Duration: 2s990ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1393242')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 OFFSET 14950 ;
Date: 2024-09-14 13:50:59
Duration: 2s246ms
Bind query: yes
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1393242')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 OFFSET 1450 ;
Date: 2024-09-14 13:50:53
Duration: 1s886ms
Bind query: yes
x Hide
pgBadger