1
80
Details
5m21s
3s896ms
4s374ms
4s18ms
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
Jul 06 00 2 8s57ms 4s28ms 01 1 3s942ms 3s942ms 02 3 11s786ms 3s928ms 03 7 27s606ms 3s943ms 04 4 15s720ms 3s930ms 05 1 3s961ms 3s961ms 06 2 8s39ms 4s19ms 08 2 7s957ms 3s978ms 10 2 8s335ms 4s167ms 12 1 3s979ms 3s979ms 13 2 7s849ms 3s924ms 14 15 1m 4s14ms 15 36 2m25s 4s51ms 16 1 3s921ms 3s921ms 19 1 4s268ms 4s268ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 2m32s - Times executed: 38 ]
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 = '1396826')
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 = '1396826')
AND stm.slim_term_id = st.slim_term_id
AND cd.indirect_gene_qty > 0 ) inferredCount
FROM
slim_term st
INNER JOIN term t ON st.slim_term_id = t.id
WHERE
st.slim_id = 1
ORDER BY
1 ;
Date: 2024-07-06 10:36:17
Duration: 4s374ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemDiseasesBySlimDAO */
COALESCE ( st.alt_nm, t.nm) slimTermNm,
(
SELECT
COUNT ( * )
FROM
slim_term_mapping stm
INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id
WHERE
cd.chem_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1272482')
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 = '1272482')
AND stm.slim_term_id = st.slim_term_id
AND cd.indirect_gene_qty > 0 ) inferredCount
FROM
slim_term st
INNER JOIN term t ON st.slim_term_id = t.id
WHERE
st.slim_id = 1
ORDER BY
1 ;
Date: 2024-07-06 15:38:36
Duration: 4s326ms
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 = '1396826')
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 = '1396826')
AND stm.slim_term_id = st.slim_term_id
AND cd.indirect_gene_qty > 0 ) inferredCount
FROM
slim_term st
INNER JOIN term t ON st.slim_term_id = t.id
WHERE
st.slim_id = 1
ORDER BY
1 ;
Date: 2024-07-06 19:23:56
Duration: 4s268ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
2
62
Details
1m14s
1s134ms
1s352ms
1s195ms
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
Jul 06 00 3 3s590ms 1s196ms 01 3 3s519ms 1s173ms 02 3 3s591ms 1s197ms 03 1 1s138ms 1s138ms 04 6 7s287ms 1s214ms 05 7 8s534ms 1s219ms 06 3 3s602ms 1s200ms 07 4 4s813ms 1s203ms 08 8 9s497ms 1s187ms 09 3 3s638ms 1s212ms 10 4 4s736ms 1s184ms 11 6 7s129ms 1s188ms 12 2 2s413ms 1s206ms 13 1 1s206ms 1s206ms 14 2 2s347ms 1s173ms 18 1 1s200ms 1s200ms 19 1 1s158ms 1s158ms 20 1 1s154ms 1s154ms 21 1 1s134ms 1s134ms 22 2 2s407ms 1s203ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 47s530ms - Times executed: 40 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
exposureMarkerTerm.id = '1433030'
or receptorTerm.id = '1433030'
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 05:38:39
Duration: 1s352ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
exposureMarkerTerm.id = '1433030'
or receptorTerm.id = '1433030'
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 05:43:39
Duration: 1s253ms
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 = '1864936'
or receptorTerm.id = '1864936'
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 04:41:42
Duration: 1s235ms
Bind query: yes
x Hide
3
22
Details
1m33s
1s2ms
5s377ms
4s265ms
select
d.abbr dagabbr,
d.nm dagnm,
gt.level_min_no daglevelmin,
gt.nm gonm,
gt.nm_html gonmhtml,
gt.acc_txt goacc,
gt.object_id goid,
te.corrected_p_val pvalcorrected,
te.raw_p_val pvalraw,
te.target_match_qty targetmatchqty,
te.target_total_qty targettotalqty,
te.background_match_qty backgroundmatchqty,
te.background_total_qty backgroundtotalqty,
count ( * ) over ( ) fullrowcount
from
term_enrichment te
inner join dag_node gt on te.enriched_term_id = gt.object_id
inner join dag d on gt.dag_id = d.id
where
te.term_id = ?
and te.enriched_object_type_id = ?
order by
te.corrected_p_val,
d.abbr,
gt.nm_sort
limit ?;
Times Reported Time consuming queries #3
Day
Hour
Count
Duration
Avg duration
Jul 06 00 1 5s133ms 5s133ms 02 4 12s357ms 3s89ms 03 1 5s195ms 5s195ms 06 6 31s944ms 5s324ms 07 1 5s116ms 5s116ms 08 2 2s51ms 1s25ms 09 1 1s8ms 1s8ms 10 1 5s65ms 5s65ms 11 1 5s73ms 5s73ms 12 1 5s273ms 5s273ms 17 1 5s172ms 5s172ms 20 1 5s257ms 5s257ms 21 1 5s199ms 5s199ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 39s568ms - Times executed: 10 ]
x Hide
SELECT
/* ChemGODAO */
d.abbr dagAbbr,
d.nm dagNm,
gt.level_min_no dagLevelMin,
gt.nm gonm,
gt.nm_html gonmhtml,
gt.acc_txt goacc,
gt.object_id goid,
te.corrected_p_val pValCorrected,
te.raw_p_val pValRaw,
te.target_match_qty targetmatchqty,
te.target_total_qty targettotalqty,
te.background_match_qty backgroundmatchqty,
te.background_total_qty backgroundtotalqty,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term_enrichment te
INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id
INNER JOIN dag d ON gt.dag_id = d.id
WHERE
te.term_id = '1333288'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-07-06 06:17:59
Duration: 5s377ms
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 = '1359883'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-07-06 06:41:16
Duration: 5s345ms
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 = '1359883'
AND te.enriched_object_type_id = 5
ORDER BY
te.corrected_p_val,
d.abbr,
gt.nm_sort
LIMIT 50 ;
Date: 2024-07-06 06:41:17
Duration: 5s320ms
Bind query: yes
x Hide
4
21
Details
28s229ms
1s266ms
1s487ms
1s344ms
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
Jul 06 00 1 1s299ms 1s299ms 01 1 1s280ms 1s280ms 02 2 2s551ms 1s275ms 05 2 2s848ms 1s424ms 06 1 1s303ms 1s303ms 07 1 1s283ms 1s283ms 08 1 1s460ms 1s460ms 10 3 3s950ms 1s316ms 11 1 1s343ms 1s343ms 13 1 1s361ms 1s361ms 14 2 2s663ms 1s331ms 15 1 1s369ms 1s369ms 16 1 1s328ms 1s328ms 17 1 1s347ms 1s347ms 22 1 1s487ms 1s487ms 23 1 1s349ms 1s349ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s472ms - Times executed: 7 ]
[ User: qaeu - Total duration: 1s379ms - Times executed: 1 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2071982')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2071982')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 22:47:01
Duration: 1s487ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.disease_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2071900')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2071900')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 05:38:41
Duration: 1s468ms
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 = '2070528')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '2070528')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 08:54:56
Duration: 1s460ms
Bind query: yes
x Hide
5
14
Details
18s313ms
1s236ms
1s404ms
1s308ms
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 #5
Day
Hour
Count
Duration
Avg duration
Jul 06 00 1 1s322ms 1s322ms 02 2 2s595ms 1s297ms 03 1 1s404ms 1s404ms 05 2 2s728ms 1s364ms 06 1 1s291ms 1s291ms 07 1 1s294ms 1s294ms 08 1 1s294ms 1s294ms 09 1 1s269ms 1s269ms 13 1 1s254ms 1s254ms 14 1 1s305ms 1s305ms 19 1 1s236ms 1s236ms 22 1 1s315ms 1s315ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s222ms - Times executed: 7 ]
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 = '1253166')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1253166')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 03:52:17
Duration: 1s404ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1211542')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1211542')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 05:38:38
Duration: 1s387ms
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
inner join reference r ON e.reference_id = r.id
left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id
left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id
left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id
left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id
left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
left outer join country ON eventLocation.country_id = country.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt
and e.reference_acc_db_id = referenceExp.reference_acc_db_id
left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id
where
outcome.phenotype_id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1211542')
or receptorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1211542')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2024-07-06 05:43:38
Duration: 1s340ms
Bind query: yes
x Hide
6
9
Details
21s497ms
1s57ms
6s469ms
2s388ms
select
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
g.nm genesymbol,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
gd.network_score networkscore,
gd.indirect_chem_qty inferredcount,
gd.reference_qty referencecount,
gd.exposure_reference_qty exposurereferencecount,
case when gd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
gene_disease_axn a
where
a.gene_id = gd.gene_id
and a.disease_id = gd.disease_id)
else
null
end actiontypes
from
gene_disease gd
inner join term g on gd.gene_id = g.id
inner join term d on gd.disease_id = d.id
where
gd.disease_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
gd.network_score desc nulls last ,
g.nm_sort,
d.nm_sort
limit ?;
Times Reported Time consuming queries #6
Day
Hour
Count
Duration
Avg duration
Jul 06 02 1 6s469ms 6s469ms 04 1 1s998ms 1s998ms 06 1 2s350ms 2s350ms 08 1 1s352ms 1s352ms 09 1 2s212ms 2s212ms 12 2 3s683ms 1s841ms 15 1 1s57ms 1s57ms 18 1 2s373ms 2s373ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s227ms - Times executed: 4 ]
x Hide
SELECT
/* DiseaseGeneAssnsDAO */
d.nm diseaseNm,
d.acc_txt diseaseAcc,
d.acc_db_cd diseaseAccDbCd,
d.id diseaseId,
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
gd.network_score networkScore,
gd.indirect_chem_qty inferredCount,
gd.reference_qty referenceCount,
gd.exposure_reference_qty exposureReferenceCount,
CASE WHEN gd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gd.gene_id
AND a.disease_id = gd.disease_id)
ELSE
NULL
END actionTypes
FROM
gene_disease gd
INNER JOIN term g ON gd.gene_id = g.id
INNER JOIN term d ON gd.disease_id = d.id
WHERE
gd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2078285')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-07-06 02:38:42
Duration: 6s469ms
Bind query: yes
SELECT
/* DiseaseGeneAssnsDAO */
d.nm diseaseNm,
d.acc_txt diseaseAcc,
d.acc_db_cd diseaseAccDbCd,
d.id diseaseId,
g.nm geneSymbol,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
g.id geneId,
gd.network_score networkScore,
gd.indirect_chem_qty inferredCount,
gd.reference_qty referenceCount,
gd.exposure_reference_qty exposureReferenceCount,
CASE WHEN gd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gd.gene_id
AND a.disease_id = gd.disease_id)
ELSE
NULL
END actionTypes
FROM
gene_disease gd
INNER JOIN term g ON gd.gene_id = g.id
INNER JOIN term d ON gd.disease_id = d.id
WHERE
gd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2080269')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-07-06 12:40:12
Duration: 2s504ms
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 = '2071921')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 ;
Date: 2024-07-06 18:46:04
Duration: 2s373ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
7
9
Details
12s580ms
1s352ms
1s435ms
1s397ms
select
t.nm,
t.nm_html nmhtml,
t.secondary_nm secondarynm,
t.acc_txt acc,
? || t.nm accquerystr,
t.has_chems haschems,
t.has_diseases hasdiseases,
t.has_exposures hasexposures,
t.has_phenotypes hasphenotypes,
count ( * ) over ( ) fullrowcount
from
term t
where
t.object_type_id = ?
and regexp_replace ( upper ( substring ( t.nm, ?, ?) ) , ?, ?) = ?
order by
t.nm_sort
limit ?;
Times Reported Time consuming queries #7
Day
Hour
Count
Duration
Avg duration
Jul 06 02 2 2s861ms 1s430ms 04 1 1s372ms 1s372ms 05 4 5s604ms 1s401ms 13 2 2s741ms 1s370ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 4s188ms - Times executed: 3 ]
x Hide
SELECT
/* GeneBrowseTermsDAO */
t.nm,
t.nm_html nmHtml,
t.secondary_nm secondaryNm,
t.acc_txt acc,
'name:' || t.nm accQueryStr,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term t
WHERE
t.object_type_id = '4'
AND REGEXP_REPLACE ( UPPER ( SUBSTRING ( t.nm, 1 , 1 ) ) , '[^A-Z]', '#') = 'T'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-07-06 05:57:06
Duration: 1s435ms
Bind query: yes
SELECT
/* GeneBrowseTermsDAO */
t.nm,
t.nm_html nmHtml,
t.secondary_nm secondaryNm,
t.acc_txt acc,
'name:' || t.nm accQueryStr,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term t
WHERE
t.object_type_id = '4'
AND REGEXP_REPLACE ( UPPER ( SUBSTRING ( t.nm, 1 , 1 ) ) , '[^A-Z]', '#') = 'A'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-07-06 02:58:26
Duration: 1s432ms
Bind query: yes
SELECT
/* GeneBrowseTermsDAO */
t.nm,
t.nm_html nmHtml,
t.secondary_nm secondaryNm,
t.acc_txt acc,
'name:' || t.nm accQueryStr,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_phenotypes hasPhenotypes,
COUNT ( * ) OVER ( ) fullRowCount
FROM
term t
WHERE
t.object_type_id = '4'
AND REGEXP_REPLACE ( UPPER ( SUBSTRING ( t.nm, 1 , 1 ) ) , '[^A-Z]', '#') = 'A'
ORDER BY
t.nm_sort
LIMIT 100 ;
Date: 2024-07-06 02:58:28
Duration: 1s429ms
Bind query: yes
x Hide
8
8
Details
42s922ms
1s16ms
22s483ms
5s365ms
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 #8
Day
Hour
Count
Duration
Avg duration
Jul 06 00 1 1s273ms 1s273ms 02 1 2s724ms 2s724ms 03 1 22s483ms 22s483ms 07 1 1s16ms 1s16ms 11 2 6s62ms 3s31ms 12 1 8s116ms 8s116ms 15 1 1s245ms 1s245ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 16s410ms - 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 = '2071921')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 3666000 ;
Date: 2024-07-06 03:20:50
Duration: 22s483ms
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 = '2073988')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 50 ;
Date: 2024-07-06 12:11:53
Duration: 8s116ms
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 = '2082528')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort
LIMIT 50 OFFSET 448850 ;
Date: 2024-07-06 11:35:29
Duration: 3s49ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
9
8
Details
11s140ms
1s349ms
1s450ms
1s392ms
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 ? offset ?;
Times Reported Time consuming queries #9
Day
Hour
Count
Duration
Avg duration
Jul 06 05 3 4s159ms 1s386ms 06 1 1s388ms 1s388ms 13 4 5s592ms 1s398ms
x Hide
Examples
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]', '#') = 'T'
ORDER BY
t.nm_sort
LIMIT 100 OFFSET 100 ;
Date: 2024-07-06 05:59:14
Duration: 1s450ms
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 OFFSET 1000 ;
Date: 2024-07-06 13:45:50
Duration: 1s435ms
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 OFFSET 1300 ;
Date: 2024-07-06 13:45:57
Duration: 1s417ms
Bind query: yes
x Hide
10
6
Details
14s544ms
1s42ms
3s377ms
2s424ms
with recursive sub_node (
object_id,
id,
path,
lvl
) as (
select
n.object_id,
n.id,
array [n.nm_sort],
?
from
dag_node n
where
n.object_id = ?
union all
select
n.object_id,
n.id,
cast ( path || n.nm_sort as varchar ( ?) []) ,
sn.lvl + ?
from
dag_node n
inner join sub_node sn on ( n.parent_id = sn.id) )
select distinct
t.nm prinm,
t.nm_html prinmhtml,
t.secondary_nm secondarynm,
t.acc_db_cd accdbcd,
t.acc_txt termacc,
t.is_leaf isleaf,
t.has_chems haschems,
t.has_diseases hasdiseases,
t.has_exposures hasexposures,
t.has_genes hasgenes,
sn.lvl,
sn.path,
max ( sn.lvl) over ( ) maxlvl,
t.has_phenotypes hasphenotypes
from
sub_node sn
inner join term t on sn.object_id = t.id
where
sn.lvl <= ?
order by
sn.path;
Times Reported Time consuming queries #10
Day
Hour
Count
Duration
Avg duration
Jul 06 04 1 2s466ms 2s466ms 05 5 12s77ms 2s415ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 6s604ms - Times executed: 2 ]
[ User: qaeu - Total duration: 3s377ms - Times executed: 1 ]
x Hide
WITH recursive sub_node (
object_id,
id,
path,
lvl
) AS (
SELECT
n.object_id,
n.id,
ARRAY [n.nm_sort],
1
FROM
dag_node n
WHERE
n.object_id = '589967'
UNION ALL
SELECT
n.object_id,
n.id,
CAST ( path || n.nm_sort AS varchar ( 600 ) []) ,
sn.lvl + 1
FROM
dag_node n
INNER JOIN sub_node sn ON ( n.parent_id = sn.id) )
SELECT
/* TreeTermBasicsDAO.getDescendants */
DISTINCT t.nm priNm,
t.nm_html priNmHtml,
t.secondary_nm secondaryNm,
t.acc_db_cd accDbCd,
t.acc_txt termAcc,
t.is_leaf isLeaf,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_genes hasGenes,
sn.lvl,
sn.path,
MAX ( sn.lvl) OVER ( ) maxLvl,
t.has_phenotypes hasPhenotypes
FROM
sub_node sn
INNER JOIN term t ON sn.object_id = t.id
WHERE
sn.lvl <= 2
ORDER BY
sn.path;
Date: 2024-07-06 05:40:12
Duration: 3s377ms
Database: ctdprd51
User: qaeu
Bind query: yes
WITH recursive sub_node (
object_id,
id,
path,
lvl
) AS (
SELECT
n.object_id,
n.id,
ARRAY [n.nm_sort],
1
FROM
dag_node n
WHERE
n.object_id = '589967'
UNION ALL
SELECT
n.object_id,
n.id,
CAST ( path || n.nm_sort AS varchar ( 600 ) []) ,
sn.lvl + 1
FROM
dag_node n
INNER JOIN sub_node sn ON ( n.parent_id = sn.id) )
SELECT
/* TreeTermBasicsDAO.getDescendants */
DISTINCT t.nm priNm,
t.nm_html priNmHtml,
t.secondary_nm secondaryNm,
t.acc_db_cd accDbCd,
t.acc_txt termAcc,
t.is_leaf isLeaf,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_genes hasGenes,
sn.lvl,
sn.path,
MAX ( sn.lvl) OVER ( ) maxLvl,
t.has_phenotypes hasPhenotypes
FROM
sub_node sn
INNER JOIN term t ON sn.object_id = t.id
WHERE
sn.lvl <= 2
ORDER BY
sn.path;
Date: 2024-07-06 05:35:12
Duration: 3s319ms
Database: ctdprd51
User: pubeu
Bind query: yes
WITH recursive sub_node (
object_id,
id,
path,
lvl
) AS (
SELECT
n.object_id,
n.id,
ARRAY [n.nm_sort],
1
FROM
dag_node n
WHERE
n.object_id = '650196'
UNION ALL
SELECT
n.object_id,
n.id,
CAST ( path || n.nm_sort AS varchar ( 600 ) []) ,
sn.lvl + 1
FROM
dag_node n
INNER JOIN sub_node sn ON ( n.parent_id = sn.id) )
SELECT
/* TreeTermBasicsDAO.getDescendants */
DISTINCT t.nm priNm,
t.nm_html priNmHtml,
t.secondary_nm secondaryNm,
t.acc_db_cd accDbCd,
t.acc_txt termAcc,
t.is_leaf isLeaf,
t.has_chems hasChems,
t.has_diseases hasDiseases,
t.has_exposures hasExposures,
t.has_genes hasGenes,
sn.lvl,
sn.path,
MAX ( sn.lvl) OVER ( ) maxLvl,
t.has_phenotypes hasPhenotypes
FROM
sub_node sn
INNER JOIN term t ON sn.object_id = t.id
WHERE
sn.lvl <= 4
ORDER BY
sn.path;
Date: 2024-07-06 05:06:23
Duration: 3s285ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
11
5
Details
14s306ms
2s374ms
3s864ms
2s861ms
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 #11
Day
Hour
Count
Duration
Avg duration
Jul 06 00 1 3s864ms 3s864ms 05 1 2s413ms 2s413ms 06 1 2s374ms 2s374ms 11 1 2s924ms 2s924ms 23 1 2s728ms 2s728ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 9s163ms - Times executed: 3 ]
x Hide
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1417912')
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 = '1417912')
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 200 ;
Date: 2024-07-06 00:57:35
Duration: 3s864ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1426808')
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 = '1426808')
and t.id = event1.exp_marker_term_id
and event1.exp_marker_type_id in (
select
id
from
exp_marker_type
where
nm like 'chem%')
and event1.id = e1.exp_event_id) )
group by
e.reference_acc_txt,
r.abbr_authors_txt,
pub_start_yr,
re.author_summary
order by
stressorAgents
LIMIT 50 OFFSET 100 ;
Date: 2024-07-06 11:33:19
Duration: 2s924ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1426808')
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 = '1426808')
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-07-06 23:02:58
Duration: 2s728ms
Bind query: yes
x Hide
12
4
Details
5s46ms
1s42ms
1s422ms
1s261ms
select
fg.nm fromgenesymbol,
fg.acc_txt fromgeneacc,
tg.nm togenesymbol,
tg.acc_txt togeneacc,
ft.nm fromtaxonnm,
ft.secondary_nm fromtaxoncommonnm,
ft.acc_txt fromtaxonacc,
tt.nm totaxonnm,
tt.secondary_nm totaxoncommonnm,
tt.acc_txt totaxonacc,
ggr.experimental_sys_nm,
ggr.experimental_sys_type,
(
select
string_agg ( ggt.throughput_txt, ? order by ggt.throughput_txt)
from
gene_gene_ref_throughput ggt
where
ggt.gene_gene_reference_id = ggr.id) throughput,
count ( * ) over ( ) fullrowcount
from
gene_gene_reference ggr
inner join term fg on ggr.from_gene_id = fg.id
inner join term tg on ggr.to_gene_id = tg.id
inner join term ft on ggr.from_taxon_id = ft.id
inner join term tt on ggr.to_taxon_id = tt.id
where
ggr.reference_id = ?
order by
fg.nm_sort,
tg.nm_sort
limit ?;
Times Reported Time consuming queries #12
Day
Hour
Count
Duration
Avg duration
Jul 06 05 4 5s46ms 1s261ms
x Hide
Examples User(s) involved
[ User: qaeu - Total duration: 1s356ms - Times executed: 1 ]
x Hide
SELECT
/* ReferenceGeneGeneIxnsDAO */
fg.nm fromGeneSymbol,
fg.acc_txt fromGeneAcc,
tg.nm toGeneSymbol,
tg.acc_txt toGeneAcc,
ft.nm fromTaxonNm,
ft.secondary_nm fromTaxonCommonNm,
ft.acc_txt fromTaxonAcc,
tt.nm toTaxonNm,
tt.secondary_nm toTaxonCommonNm,
tt.acc_txt toTaxonAcc,
ggr.experimental_sys_nm,
ggr.experimental_sys_type,
(
SELECT
STRING_AGG ( ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt)
FROM
gene_gene_ref_throughput ggt
WHERE
ggt.gene_gene_reference_id = ggr.id) throughput,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_gene_reference ggr
INNER JOIN term fg ON ggr.from_gene_id = fg.id
INNER JOIN term tg ON ggr.to_gene_id = tg.id
INNER JOIN term ft ON ggr.from_taxon_id = ft.id
INNER JOIN term tt ON ggr.to_taxon_id = tt.id
WHERE
ggr.reference_id = '111363'
ORDER BY
fg.nm_sort,
tg.nm_sort
LIMIT 50 ;
Date: 2024-07-06 05:38:06
Duration: 1s422ms
Bind query: yes
SELECT
/* ReferenceGeneGeneIxnsDAO */
fg.nm fromGeneSymbol,
fg.acc_txt fromGeneAcc,
tg.nm toGeneSymbol,
tg.acc_txt toGeneAcc,
ft.nm fromTaxonNm,
ft.secondary_nm fromTaxonCommonNm,
ft.acc_txt fromTaxonAcc,
tt.nm toTaxonNm,
tt.secondary_nm toTaxonCommonNm,
tt.acc_txt toTaxonAcc,
ggr.experimental_sys_nm,
ggr.experimental_sys_type,
(
SELECT
STRING_AGG ( ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt)
FROM
gene_gene_ref_throughput ggt
WHERE
ggt.gene_gene_reference_id = ggr.id) throughput,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_gene_reference ggr
INNER JOIN term fg ON ggr.from_gene_id = fg.id
INNER JOIN term tg ON ggr.to_gene_id = tg.id
INNER JOIN term ft ON ggr.from_taxon_id = ft.id
INNER JOIN term tt ON ggr.to_taxon_id = tt.id
WHERE
ggr.reference_id = '111363'
ORDER BY
fg.nm_sort,
tg.nm_sort
LIMIT 50 ;
Date: 2024-07-06 05:43:06
Duration: 1s356ms
Database: ctdprd51
User: qaeu
Bind query: yes
SELECT
/* ReferenceGeneGeneIxnsDAO */
fg.nm fromGeneSymbol,
fg.acc_txt fromGeneAcc,
tg.nm toGeneSymbol,
tg.acc_txt toGeneAcc,
ft.nm fromTaxonNm,
ft.secondary_nm fromTaxonCommonNm,
ft.acc_txt fromTaxonAcc,
tt.nm toTaxonNm,
tt.secondary_nm toTaxonCommonNm,
tt.acc_txt toTaxonAcc,
ggr.experimental_sys_nm,
ggr.experimental_sys_type,
(
SELECT
STRING_AGG ( ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt)
FROM
gene_gene_ref_throughput ggt
WHERE
ggt.gene_gene_reference_id = ggr.id) throughput,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_gene_reference ggr
INNER JOIN term fg ON ggr.from_gene_id = fg.id
INNER JOIN term tg ON ggr.to_gene_id = tg.id
INNER JOIN term ft ON ggr.from_taxon_id = ft.id
INNER JOIN term tt ON ggr.to_taxon_id = tt.id
WHERE
ggr.reference_id = '111363'
ORDER BY
fg.nm_sort,
tg.nm_sort
LIMIT 50 ;
Date: 2024-07-06 05:38:05
Duration: 1s224ms
Bind query: yes
x Hide
13
4
Details
4s416ms
1s65ms
1s210ms
1s104ms
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 #13
Day
Hour
Count
Duration
Avg duration
Jul 06 03 1 1s210ms 1s210ms 07 1 1s69ms 1s69ms 10 1 1s65ms 1s65ms 17 1 1s69ms 1s69ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1s69ms - 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 = '1336418')
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 = '1336418')
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-07-06 03:35:38
Duration: 1s210ms
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 = '1268145')
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 = '1268145')
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-07-06 17:28:47
Duration: 1s69ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemExposureStudiesAssnsDAO */
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
(
SELECT
STRING_AGG ( distinct stressorTerm.nm || '^' || (
select
cd
from object_type
where
id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|') ) as stressorAgents,
(
SELECT
STRING_AGG ( distinct COALESCE ( receptorTerm.nm, '') || '^' || COALESCE ( (
select
cd
from object_type
where
id = receptorTerm.object_type_id) , '') || '^' || COALESCE ( receptorTerm.nm_html, '') || '^' || COALESCE ( receptorTerm.acc_txt, '') || '^' || COALESCE ( receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|') ) as receptors,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
(
SELECT
STRING_AGG ( distinct location.locality_txt, ' | ') ) as localities,
(
SELECT
STRING_AGG ( distinct event.medium_nm || '^' || COALESCE ( event.medium_term_acc_txt, '') , ' | ') ) as assayMediums,
(
SELECT
STRING_AGG ( distinct exposureMarkerTerm.nm || '^' || (
select
cd
from object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|') ) as assayedMarkers,
(
SELECT
STRING_AGG ( distinct diseaseTerm.nm || '^' || (
select
cd
from object_type
where
id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|') ) as diseases,
(
SELECT
STRING_AGG ( distinct phenotypeTerm.nm || '^' || (
select
cd
from object_type
where
id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|') ) as phenotypes,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
re.author_summary summary,
COUNT ( * ) OVER ( ) fullRowCount
FROM
exposure e
inner join reference r ON e.reference_id = r.id
inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id
left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id
left outer join term receptorTerm ON receptor.term_id = receptorTerm.id
left outer join exp_event event ON e.exp_event_id = event.id
left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id
left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id
left outer join country ON location.country_id = country.id
left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id
left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id
left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id
left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id
inner join term stressorTerm ON stressor.chem_id = stressorTerm.id
left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id
Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id
inner join reference_exp re ON e.reference_id = re.reference_id
left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id
where
e.reference_id = ANY ( ARRAY (
select
reference_id
from
exposure e1,
term chem,
exp_stressor s1
where
chem.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1306732')
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 = '1306732')
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-07-06 07:48:03
Duration: 1s69ms
Bind query: yes
x Hide
14
3
Details
17s488ms
1s186ms
9s386ms
5s829ms
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 ? offset ?;
Times Reported Time consuming queries #14
Day
Hour
Count
Duration
Avg duration
Jul 06 04 1 9s386ms 9s386ms 21 1 6s914ms 6s914ms 22 1 1s186ms 1s186ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 17s488ms - Times executed: 3 ]
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 = '1235351')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 OFFSET 350 ;
Date: 2024-07-06 04:06:14
Duration: 9s386ms
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 = '1253166')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 OFFSET 200 ;
Date: 2024-07-06 21:17:18
Duration: 6s914ms
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 = '1220833')
AND gga.is_not = false ) sq
ORDER BY
sq.gonmsort,
sq.genesymbolsort
LIMIT 50 OFFSET 17050 ;
Date: 2024-07-06 22:56:40
Duration: 1s186ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
15
3
Details
5s80ms
1s36ms
2s104ms
1s693ms
select
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
g.nm genesymbol,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
g.id geneid,
gd.network_score networkscore,
gd.indirect_chem_qty inferredcount,
gd.reference_qty referencecount,
gd.exposure_reference_qty exposurereferencecount,
case when gd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
gene_disease_axn a
where
a.gene_id = gd.gene_id
and a.disease_id = gd.disease_id)
else
null
end actiontypes
from
gene_disease gd
inner join term g on gd.gene_id = g.id
inner join term d on gd.disease_id = d.id
where
gd.disease_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
gd.network_score desc nulls last ,
g.nm_sort,
d.nm_sort;
Times Reported Time consuming queries #15
Day
Hour
Count
Duration
Avg duration
Jul 06 08 1 1s36ms 1s36ms 17 1 2s104ms 2s104ms 22 1 1s939ms 1s939ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 4s44ms - Times executed: 2 ]
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 = '2076269')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-07-06 17:22:06
Duration: 2s104ms
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 = '2073754')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-07-06 22:33:09
Duration: 1s939ms
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 = '2072323')
ORDER BY
actionTypes,
gd.network_score DESC NULLS LAST ,
g.nm_sort,
d.nm_sort;
Date: 2024-07-06 08:13:16
Duration: 1s36ms
Bind query: yes
x Hide
16
3
Details
4s286ms
1s27ms
1s709ms
1s428ms
select
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposurereferencecount,
case when cd.curated_reference_qty > ? then
(
select
string_agg ( a.action_type_cd || ? || a.action_type_nm, ?)
from
chem_disease_axn a
where
a.chem_id = cd.chem_id
and a.disease_id = cd.disease_id)
else
null
end actiontypes
from
chem_disease cd
inner join term c on cd.chem_id = c.id
inner join term d on cd.disease_id = d.id
where
cd.disease_id in (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?)
order by
actiontypes,
cd.network_score desc nulls last ,
d.nm_sort,
c.nm_sort
limit ? offset ?;
Times Reported Time consuming queries #16
Day
Hour
Count
Duration
Avg duration
Jul 06 03 1 1s709ms 1s709ms 07 1 1s27ms 1s27ms 16 1 1s549ms 1s549ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1s549ms - Times executed: 1 ]
x Hide
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2080922')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 OFFSET 227500 ;
Date: 2024-07-06 03:47:51
Duration: 1s709ms
Bind query: yes
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2081293')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 OFFSET 217200 ;
Date: 2024-07-06 16:30:56
Duration: 1s549ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* ChemDiseaseAssnsDAO */
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casRN,
c.id chemid,
d.nm diseasenm,
d.acc_txt diseaseacc,
d.acc_db_cd diseaseaccdbcd,
d.id diseaseid,
cd.network_score networkscore,
cd.indirect_gene_qty inferredcount,
cd.reference_qty referencecount,
cd.exposure_reference_qty exposureReferenceCount,
CASE WHEN cd.curated_reference_qty > 0 THEN
(
SELECT
STRING_AGG ( a.action_type_cd || '^' || a.action_type_nm, '|')
FROM
chem_disease_axn a
WHERE
a.chem_id = cd.chem_id
AND a.disease_id = cd.disease_id)
ELSE
NULL
END actiontypes
FROM
chem_disease cd
INNER JOIN term c ON cd.chem_id = c.id
INNER JOIN term d ON cd.disease_id = d.id
WHERE
cd.disease_id IN (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '2080559')
ORDER BY
actionTypes,
cd.network_score DESC NULLS LAST ,
d.nm_sort,
c.nm_sort
LIMIT 50 OFFSET 150 ;
Date: 2024-07-06 07:48:45
Duration: 1s27ms
Bind query: yes
x Hide
17
3
Details
4s44ms
1s341ms
1s356ms
1s348ms
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 #17
Day
Hour
Count
Duration
Avg duration
Jul 06 05 2 2s697ms 1s348ms 10 1 1s347ms 1s347ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 1s347ms - Times executed: 1 ]
x Hide
SELECT
COALESCE ( d.abbr_display, d.nm_display) nm # 015 ,
d.description # 015 ,
COALESCE ( d.abbr, d.nm) anchor # 015 ,
get_homepage_url ( d.id) url # 015
FROM
db d # 015
WHERE
d.id IN ( # 015
SELECT
l.db_id # 015 FROM db_link l # 015
WHERE
l.type_cd = 'X' # 015
AND l.object_type_id = 4 ) # 015
ORDER BY
1 ;
Date: 2024-07-06 05:38:31
Duration: 1s356ms
Bind query: yes
SELECT
COALESCE ( d.abbr_display, d.nm_display) nm # 015 ,
d.description # 015 ,
COALESCE ( d.abbr, d.nm) anchor # 015 ,
get_homepage_url ( d.id) url # 015
FROM
db d # 015
WHERE
d.id IN ( # 015
SELECT
l.db_id # 015 FROM db_link l # 015
WHERE
l.type_cd = 'X' # 015
AND l.object_type_id = 4 ) # 015
ORDER BY
1 ;
Date: 2024-07-06 10:25:24
Duration: 1s347ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
COALESCE ( d.abbr_display, d.nm_display) nm # 015 ,
d.description # 015 ,
COALESCE ( d.abbr, d.nm) anchor # 015 ,
get_homepage_url ( d.id) url # 015
FROM
db d # 015
WHERE
d.id IN ( # 015
SELECT
l.db_id # 015 FROM db_link l # 015
WHERE
l.type_cd = 'X' # 015
AND l.object_type_id = 4 ) # 015
ORDER BY
1 ;
Date: 2024-07-06 05:43:31
Duration: 1s341ms
Bind query: yes
x Hide
18
3
Details
3s591ms
1s159ms
1s222ms
1s197ms
select distinct
stressorterm.nm as chemnm,
stressorterm.nm_html as chemnmhtml,
stressorterm.nm_sort as chemnmsort,
stressorterm.acc_txt as chemacc,
(
select
string_agg ( distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?) ) as stressorsrctypenm,
stressor.src_details as stressorsrcdetails,
stressor.sample_qty as stressorsampleqty,
stressor.note as stressornote,
receptor.qty as nbrreceptors,
receptor.description as receptors,
receptor.note as receptornotes,
receptorterm.nm || ? || (
select
cd
from
object_type
where
id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms,
(
select
string_agg ( distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?) ) as smokerstatus,
receptor.age as agerange,
receptor.age_uom_nm as ageuomnm,
receptor.age_qualifier_nm as agequalifiernm,
receptor.gender_nm as gendernmsearch,
receptor.id receptorid,
(
select
string_agg ( pct || ? || gender_nm || ? || gender_nm_html, ?)
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderdetails,
(
select
string_agg ( distinct receptorrace.race_nm || ? || receptorrace.pct, ?) ) as receptorrace,
(
select
string_agg ( distinct eventassaymethod.nm, ?) ) as assaymethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumacctxt,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr,
event.detection_limit as detectionlimit,
event.detection_limit_uom as detectionlimituom,
event.detection_freq as detectionfreq,
event.note as eventnote,
(
select
string_agg ( distinct eventlocation.geographic_region_nm, ?) ) as stateorprovince,
(
select
string_agg ( distinct eventlocation.locality_txt, ?) ) as localitytxt,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
exposuremarkerterm.nm || ? || (
select
cd
from
object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers,
event.exp_marker_lvl as assaylevel,
assay_uom as measurement,
assay_measurement_stat as measurementstat,
assay_note as assaynote,
eiot.description as outcomerltnp,
diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield,
outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm,
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrauthorstxt,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
outcome.note as outcomenote,
eventlocation.exp_event_id as eventid,
count ( * ) over ( ) fullrowcount
from
exposure e
inner join exp_stressor stressor on e.exp_stressor_id = stressor.id
inner join term stressorterm on stressor.chem_id = stressorterm.id
left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id
left outer join exp_event event on e.exp_event_id = event.id
left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id
left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id
left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id
left outer join term diseaseterm on outcome.disease_id = diseaseterm.id
left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id
left outer join term receptorterm on receptor.term_id = receptorterm.id
inner join reference r on e.reference_id = r.id
left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id
left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id
left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id
left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id
left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id
left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id
left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id
left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id
left outer join country on eventlocation.country_id = country.id
left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id
left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt
and e.reference_acc_db_id = referenceexp.reference_acc_db_id
left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id
where
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;
Times Reported Time consuming queries #18
Day
Hour
Count
Duration
Avg duration
Jul 06 09 1 1s222ms 1s222ms 10 1 1s210ms 1s210ms 19 1 1s159ms 1s159ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 2s369ms - Times executed: 2 ]
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 = '2027876'
or receptorTerm.id = '2027876'
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;
Date: 2024-07-06 09:01:16
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 = '2048010'
or receptorTerm.id = '2048010'
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;
Date: 2024-07-06 10:12:42
Duration: 1s210ms
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
exposureMarkerTerm.id = '1435564'
or receptorTerm.id = '1435564'
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;
Date: 2024-07-06 19:53:03
Duration: 1s159ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
19
2
Details
41s472ms
20s671ms
20s800ms
20s736ms
select
g.nm genesymbol,
g.id geneid,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
i.id ixnid,
i.ixn_prose_txt ixnprose,
i.ixn_prose_html ixnprosehtml,
i.actions_txt ixnactions,
count ( distinct gcr.reference_id) refcount,
count ( distinct gcr.taxon_id) taxoncount,
count ( * ) over ( ) fullrowcount
from
gene_chem_reference gcr
inner join ixn i on gcr.ixn_id = i.id
inner join term g on gcr.gene_id = g.id
inner join term c on gcr.chem_id = c.id
where
exists (
select
?
from
gene_chem_ref_gene_form gf
where
gf.gene_chem_reference_id = gcr.id
and gf.gene_id = gcr.gene_id
and gf.actor_form_type_nm in (
select
tc.nm
from
actor_form_type tp,
actor_form_type tc
where
tc.subset_left_no between tp.subset_left_no and tp.subset_right_no
and ( tp.nm = ?) ) )
and gcr.gene_id = any ( array ( (
select
gi.id gene_id
from
term gi
where
gi.object_type_id = ?
and upper ( gi.nm)
like ?)
intersect (
select
ai.gene_id
from
dag_path pi
inner join gene_go_annot ai on pi.descendant_object_id = ai.go_term_id
inner join db_link dbli on dbli.object_id = pi.ancestor_object_id
where
dbli.acc_txt = ?
and dbli.type_cd = ?
and dbli.object_type_id = ?) ) )
and gcr.chem_id = any ( array (
select
dp.descendant_object_id
from
dag_path dp
inner join term t on t.id = dp.ancestor_object_id
where
upper ( t.nm)
like ?
and t.object_type_id = ?) )
and gcr.taxon_id = any ( array (
select
dp.descendant_object_id
from
dag_path dp
inner join dag_node n on n.id = dp.ancestor_dag_node_id
where
n.acc_txt = ?
and n.dag_id = ?) )
group by
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
order by
c.nm_sort,
g.nm_sort,
i.sort_txt
limit ?;
Times Reported Time consuming queries #19
Day
Hour
Count
Duration
Avg duration
Jul 06 13 2 41s472ms 20s736ms
x Hide
Examples
SELECT
/* AdvancedIxnQueryDAO.getData */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
c.nm chemNm,
c.nm_html chemNmhtml,
c.acc_txt chemAcc,
c.secondary_nm casRN,
c.id chemId,
i.id ixnId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
COUNT ( DISTINCT gcr.reference_id) refCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN ixn i ON gcr.ixn_id = i.id
INNER JOIN term g ON gcr.gene_id = g.id
INNER JOIN term c ON gcr.chem_id = c.id
WHERE
/* CIQH.getIxnWhereCore */
EXISTS (
SELECT
/* CIQH.getIxnGeneFormTypeWhere */
1
FROM
gene_chem_ref_gene_form gf
WHERE
gf.gene_chem_reference_id = gcr.id
AND gf.gene_id = gcr.gene_id
AND gf.actor_form_type_nm IN (
SELECT
tc.nm
FROM
actor_form_type tp,
actor_form_type tc
WHERE
tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no
AND ( tp.nm = 'gene') ) )
AND gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* CIQH.getIxnGeneWhereEquals.Name */
gi.id gene_id
FROM
term gi
WHERE
gi.object_type_id = 4
AND UPPER ( gi.nm)
LIKE 'IL6')
INTERSECT (
SELECT
/* IQH.getMasterGoWhereEquals.Gene */
ai.gene_id
FROM
dag_path pi
INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id
INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id
WHERE
dbli.acc_txt = 'GO:0005488'
AND dbli.type_cd = 'A'
AND dbli.object_type_id = 5 ) ) )
AND gcr.chem_id = ANY ( ARRAY (
SELECT
/* CIQH.getIxnChemWhereEquals.Name */
dp.descendant_object_id
FROM
dag_path dp
INNER JOIN term t ON t.id = dp.ancestor_object_id
WHERE
UPPER ( t.nm)
LIKE 'SPINETORAM'
AND t.object_type_id = 2 ) )
AND gcr.taxon_id = ANY ( ARRAY (
SELECT
/* CIQH.getIxnTaxonWhereEquals.Acc */
dp.descendant_object_id
FROM
dag_path dp
INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id
WHERE
n.acc_txt = '9606'
AND n.dag_id = 7 ) )
GROUP BY
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
c.nm_sort,
g.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-07-06 13:44:04
Duration: 20s800ms
Bind query: yes
SELECT
/* AdvancedIxnQueryDAO.getData */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
c.nm chemNm,
c.nm_html chemNmhtml,
c.acc_txt chemAcc,
c.secondary_nm casRN,
c.id chemId,
i.id ixnId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
COUNT ( DISTINCT gcr.reference_id) refCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN ixn i ON gcr.ixn_id = i.id
INNER JOIN term g ON gcr.gene_id = g.id
INNER JOIN term c ON gcr.chem_id = c.id
WHERE
/* CIQH.getIxnWhereCore */
EXISTS (
SELECT
/* CIQH.getIxnGeneFormTypeWhere */
1
FROM
gene_chem_ref_gene_form gf
WHERE
gf.gene_chem_reference_id = gcr.id
AND gf.gene_id = gcr.gene_id
AND gf.actor_form_type_nm IN (
SELECT
tc.nm
FROM
actor_form_type tp,
actor_form_type tc
WHERE
tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no
AND ( tp.nm = 'gene') ) )
AND gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* CIQH.getIxnGeneWhereEquals.Name */
gi.id gene_id
FROM
term gi
WHERE
gi.object_type_id = 4
AND UPPER ( gi.nm)
LIKE 'CAT')
INTERSECT (
SELECT
/* IQH.getMasterGoWhereEquals.Gene */
ai.gene_id
FROM
dag_path pi
INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id
INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id
WHERE
dbli.acc_txt = 'GO:0005488'
AND dbli.type_cd = 'A'
AND dbli.object_type_id = 5 ) ) )
AND gcr.chem_id = ANY ( ARRAY (
SELECT
/* CIQH.getIxnChemWhereEquals.Name */
dp.descendant_object_id
FROM
dag_path dp
INNER JOIN term t ON t.id = dp.ancestor_object_id
WHERE
UPPER ( t.nm)
LIKE 'SPINETORAM'
AND t.object_type_id = 2 ) )
AND gcr.taxon_id = ANY ( ARRAY (
SELECT
/* CIQH.getIxnTaxonWhereEquals.Acc */
dp.descendant_object_id
FROM
dag_path dp
INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id
WHERE
n.acc_txt = '9606'
AND n.dag_id = 7 ) )
GROUP BY
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
c.nm_sort,
g.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-07-06 13:47:23
Duration: 20s671ms
Bind query: yes
x Hide
20
2
Details
35s960ms
17s737ms
18s223ms
17s980ms
select
g.nm genesymbol,
g.id geneid,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
i.id ixnid,
i.ixn_prose_txt ixnprose,
i.ixn_prose_html ixnprosehtml,
i.actions_txt ixnactions,
count ( distinct gcr.reference_id) refcount,
count ( distinct gcr.taxon_id) taxoncount,
count ( * ) over ( ) fullrowcount
from
gene_chem_reference gcr
inner join ixn i on gcr.ixn_id = i.id
inner join term g on gcr.gene_id = g.id
inner join term c on gcr.chem_id = c.id
where
exists (
select
?
from
gene_chem_ref_gene_form gf
where
gf.gene_chem_reference_id = gcr.id
and gf.gene_id = gcr.gene_id
and gf.actor_form_type_nm in (
select
tc.nm
from
actor_form_type tp,
actor_form_type tc
where
tc.subset_left_no between tp.subset_left_no and tp.subset_right_no
and ( tp.nm = ?) ) )
and gcr.gene_id = any ( array ( (
select
gi.id gene_id
from
term gi
where
gi.object_type_id = ?
and upper ( gi.nm)
like ?) ) )
group by
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
order by
c.nm_sort,
g.nm_sort,
i.sort_txt
limit ?;
Times Reported Time consuming queries #20
Day
Hour
Count
Duration
Avg duration
Jul 06 00 1 17s737ms 17s737ms 01 1 18s223ms 18s223ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 18s223ms - Times executed: 1 ]
x Hide
SELECT
/* AdvancedIxnQueryDAO.getData */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
c.nm chemNm,
c.nm_html chemNmhtml,
c.acc_txt chemAcc,
c.secondary_nm casRN,
c.id chemId,
i.id ixnId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
COUNT ( DISTINCT gcr.reference_id) refCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN ixn i ON gcr.ixn_id = i.id
INNER JOIN term g ON gcr.gene_id = g.id
INNER JOIN term c ON gcr.chem_id = c.id
WHERE
/* CIQH.getIxnWhereCore */
EXISTS (
SELECT
/* CIQH.getIxnGeneFormTypeWhere */
1
FROM
gene_chem_ref_gene_form gf
WHERE
gf.gene_chem_reference_id = gcr.id
AND gf.gene_id = gcr.gene_id
AND gf.actor_form_type_nm IN (
SELECT
tc.nm
FROM
actor_form_type tp,
actor_form_type tc
WHERE
tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no
AND ( tp.nm = 'gene') ) )
AND gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* CIQH.getIxnGeneWhereEquals.Name */
gi.id gene_id
FROM
term gi
WHERE
gi.object_type_id = 4
AND UPPER ( gi.nm)
LIKE 'BCL2') ) )
GROUP BY
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
c.nm_sort,
g.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-07-06 01:35:55
Duration: 18s223ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* AdvancedIxnQueryDAO.getData */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
c.nm chemNm,
c.nm_html chemNmhtml,
c.acc_txt chemAcc,
c.secondary_nm casRN,
c.id chemId,
i.id ixnId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
COUNT ( DISTINCT gcr.reference_id) refCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN ixn i ON gcr.ixn_id = i.id
INNER JOIN term g ON gcr.gene_id = g.id
INNER JOIN term c ON gcr.chem_id = c.id
WHERE
/* CIQH.getIxnWhereCore */
EXISTS (
SELECT
/* CIQH.getIxnGeneFormTypeWhere */
1
FROM
gene_chem_ref_gene_form gf
WHERE
gf.gene_chem_reference_id = gcr.id
AND gf.gene_id = gcr.gene_id
AND gf.actor_form_type_nm IN (
SELECT
tc.nm
FROM
actor_form_type tp,
actor_form_type tc
WHERE
tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no
AND ( tp.nm = 'gene') ) )
AND gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* CIQH.getIxnGeneWhereEquals.Name */
gi.id gene_id
FROM
term gi
WHERE
gi.object_type_id = 4
AND UPPER ( gi.nm)
LIKE 'BCL2') ) )
GROUP BY
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
c.nm_sort,
g.nm_sort,
i.sort_txt
LIMIT 50 ;
Date: 2024-07-06 00:50:57
Duration: 17s737ms
Bind query: yes
x Hide
pgBadger