Times Reported Time consuming queries #1

x Hide

[ User: pubeu - Total duration: 33s776ms - Times executed: 10 ]

[ User: qaeu - Total duration: 20s320ms - Times executed: 6 ]

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-10-01 13:01:47
Duration: 3s494ms
Database: ctddev51
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-10-01 11:07:42
Duration: 3s476ms
Database: ctddev51
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 = '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-10-01 05:45:12
Duration: 3s415ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: editeu - Total duration: 17s697ms - Times executed: 1 ]

x Hide

[ Application: pgAdmin 4 - CONN:4306379 - Total duration: 17s697ms - Times executed: 1 ]

x Hide

select distinct e.reference_acc_txt, pref.abbr_authors_txt as author, referenceExp.author_summary, ( Select STRING_AGG(distinct eventproject.project_nm, '|')) as associatedStudyTitles, eevent.collection_start_yr, eevent.collection_end_yr, ( Select STRING_AGG(distinct studyFactor.nm, '|')) as studyFactor, ( Select STRING_AGG(distinct stressorSrcType.cd, '|')) as stressorSourceCategory, stressor.chem_term_nm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, ereceptor.qty as nbrReceptors, ereceptor.description, ereceptor.term_nm, ereceptor.term_acc_txt, ereceptor.note as receptor_note, ( Select STRING_AGG(distinct COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ' || tobaccoUse.nm, COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ', tobaccoUse.nm)), '|')) as smokerStatus, ereceptor.age, age_uom.nm, age_qualifier.nm, ( Select STRING_AGG(CAST(pct as int) || '% ' || gender.nm_html, '|') from edit.exp_receptor_gender expgender left outer join edit.gender on expgender.gender_id = gender.id where exp_receptor_id = ereceptor.id) as genderDetails, ( Select STRING_AGG(distinct CAST(receptorRace.pct as int) || '% ' || race.cd, '|')) as receptorRace, ( Select STRING_AGG(distinct eventAssayMethod.nm, '|')) as assayMethods, eevent.detection_limit, eevent.detection_limit_uom, eevent.detection_freq as detectionFreq, medium.nm as medium_nm, eevent.exp_marker_term_nm, eevent.exp_marker_lvl, eevent.assay_uom, eevent.assay_measurement_stat, eevent.assay_note, ( Select STRING_AGG(distinct country.nm, '|')) as country, ( Select STRING_AGG(distinct eventLocation.geographic_region_nm, '|')) as stateOrProvince, ( Select STRING_AGG(distinct eventLocation.locality_txt, '|')) as region, eevent.note as eventNote, eiot.description as outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_action_degree_type_nm, outcome.phenotype_term_nm, ( Select STRING_AGG(distinct expAnatomy.anatomy_term_nm, '|')) as anatomy, outcome.note as outcomeNote from exposure e left outer join reference pref on pref.acc_txt = e.reference_acc_txt left outer join reference_exp referenceExp on referenceExp.reference_acc_txt = e.reference_acc_txt left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id left outer join study_factor studyFactor on studyFactor.id = expStudyFactor.study_factor_id left outer join exp_event_project eventproject on eventproject.exp_event_id = e.exp_event_id inner join exp_stressor stressor on e.exp_stressor_id = stressor.id left outer join exp_receptor ereceptor on e.exp_receptor_id = ereceptor.id left outer join age_uom age_uom on ereceptor.age_uom_id = age_uom.id left outer join age_qualifier age_qualifier on ereceptor.age_qualifier_id = age_qualifier.id left outer join exp_event eevent on e.exp_event_id = eevent.id left outer join medium medium on eevent.medium_id = medium.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 ereceptor.id = receptorTobaccoUse.exp_receptor_id left outer join tobacco_use tobaccoUse on receptorTobaccoUse.tobacco_use_id = tobaccoUse.id left outer join exp_receptor_race receptorRace on ereceptor.id = receptorRace.exp_receptor_id left outer join race race on receptorRace.race_id = race.id left outer join exp_event_location eventLocation on eevent.id = eventLocation.exp_event_id left outer join country on eventLocation.country_id = country.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 exp_anatomy expAnatomy on outcome.id = expAnatomy.exp_outcome_id left outer join exp_event_assay_method eventAssayMethod on eevent.id = eventAssayMethod.exp_event_id where country.cd = 'US' group by e.reference_acc_txt, author, referenceExp.author_summary, pref.abbr_authors_txt, eevent.collection_start_yr, eevent.collection_end_yr, stressor.chem_term_nm, stressorSrcDetails, stressorSampleQty, stressorNote, nbrReceptors, ereceptor.description, ereceptor.term_nm, ereceptor.term_acc_txt, receptor_note, ereceptor.age, age_uom.nm, age_qualifier.nm, eevent.detection_limit, eevent.detection_limit_uom, detectionFreq, medium.nm, eevent.exp_marker_term_nm, eevent.exp_marker_lvl, eevent.assay_uom, eevent.assay_measurement_stat, eevent.assay_note, eventNote, outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_action_degree_type_nm, outcome.phenotype_term_nm, outcomeNote, ereceptor.id;

Date: 2024-10-01 11:42:35
Duration: 17s697ms
Database: ctddev51
User: editeu
Application: pgAdmin 4 - CONN:4306379

select distinct e.reference_acc_txt, pref.abbr_authors_txt as author, referenceExp.author_summary, ( Select STRING_AGG(distinct eventproject.project_nm, '|')) as associatedStudyTitles, eevent.collection_start_yr, eevent.collection_end_yr, ( Select STRING_AGG(distinct studyFactor.nm, '|')) as studyFactor, ( Select STRING_AGG(distinct stressorSrcType.cd, '|')) as stressorSourceCategory, stressor.chem_term_nm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, ereceptor.qty as nbrReceptors, ereceptor.description, ereceptor.term_nm, ereceptor.term_acc_txt, ereceptor.note as receptor_note, ( Select STRING_AGG(distinct COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ' || tobaccoUse.nm, COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ', tobaccoUse.nm)), '|')) as smokerStatus, ereceptor.age, age_uom.nm, age_qualifier.nm, ( Select STRING_AGG(CAST(pct as int) || '% ' || gender.nm_html, '|') from edit.exp_receptor_gender expgender left outer join edit.gender on expgender.gender_id = gender.id where exp_receptor_id = ereceptor.id) as genderDetails, ( Select STRING_AGG(distinct CAST(receptorRace.pct as int) || '% ' || race.cd, '|')) as receptorRace, ( Select STRING_AGG(distinct eventAssayMethod.nm, '|')) as assayMethods, eevent.detection_limit, eevent.detection_limit_uom, eevent.detection_freq as detectionFreq, medium.nm as medium_nm, eevent.exp_marker_term_nm, eevent.exp_marker_lvl, eevent.assay_uom, eevent.assay_measurement_stat, eevent.assay_note, ( Select STRING_AGG(distinct country.nm, '|')) as country, ( Select STRING_AGG(distinct eventLocation.geographic_region_nm, '|')) as stateOrProvince, ( Select STRING_AGG(distinct eventLocation.locality_txt, '|')) as region, eevent.note as eventNote, eiot.description as outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_action_degree_type_nm, outcome.phenotype_term_nm, ( Select STRING_AGG(distinct expAnatomy.anatomy_term_nm, '|')) as anatomy, outcome.note as outcomeNote from exposure e left outer join reference pref on pref.acc_txt = e.reference_acc_txt left outer join reference_exp referenceExp on referenceExp.reference_acc_txt = e.reference_acc_txt left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id left outer join study_factor studyFactor on studyFactor.id = expStudyFactor.study_factor_id left outer join exp_event_project eventproject on eventproject.exp_event_id = e.exp_event_id inner join exp_stressor stressor on e.exp_stressor_id = stressor.id left outer join exp_receptor ereceptor on e.exp_receptor_id = ereceptor.id left outer join age_uom age_uom on ereceptor.age_uom_id = age_uom.id left outer join age_qualifier age_qualifier on ereceptor.age_qualifier_id = age_qualifier.id left outer join exp_event eevent on e.exp_event_id = eevent.id left outer join medium medium on eevent.medium_id = medium.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 ereceptor.id = receptorTobaccoUse.exp_receptor_id left outer join tobacco_use tobaccoUse on receptorTobaccoUse.tobacco_use_id = tobaccoUse.id left outer join exp_receptor_race receptorRace on ereceptor.id = receptorRace.exp_receptor_id left outer join race race on receptorRace.race_id = race.id left outer join exp_event_location eventLocation on eevent.id = eventLocation.exp_event_id left outer join country on eventLocation.country_id = country.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 exp_anatomy expAnatomy on outcome.id = expAnatomy.exp_outcome_id left outer join exp_event_assay_method eventAssayMethod on eevent.id = eventAssayMethod.exp_event_id where country.cd = 'CN' group by e.reference_acc_txt, author, referenceExp.author_summary, pref.abbr_authors_txt, eevent.collection_start_yr, eevent.collection_end_yr, stressor.chem_term_nm, stressorSrcDetails, stressorSampleQty, stressorNote, nbrReceptors, ereceptor.description, ereceptor.term_nm, ereceptor.term_acc_txt, receptor_note, ereceptor.age, age_uom.nm, age_qualifier.nm, eevent.detection_limit, eevent.detection_limit_uom, detectionFreq, medium.nm, eevent.exp_marker_term_nm, eevent.exp_marker_lvl, eevent.assay_uom, eevent.assay_measurement_stat, eevent.assay_note, eventNote, outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_action_degree_type_nm, outcome.phenotype_term_nm, outcomeNote, ereceptor.id;

Date: 2024-10-01 11:46:36
Duration: 6s63ms

select distinct e.reference_acc_txt, pref.abbr_authors_txt as author, referenceExp.author_summary, ( Select STRING_AGG(distinct eventproject.project_nm, '|')) as associatedStudyTitles, eevent.collection_start_yr, eevent.collection_end_yr, ( Select STRING_AGG(distinct studyFactor.nm, '|')) as studyFactor, ( Select STRING_AGG(distinct stressorSrcType.cd, '|')) as stressorSourceCategory, stressor.chem_term_nm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, ereceptor.qty as nbrReceptors, ereceptor.description, ereceptor.term_nm, ereceptor.term_acc_txt, ereceptor.note as receptor_note, ( Select STRING_AGG(distinct COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ' || tobaccoUse.nm, COALESCE(COALESCE(NULLIF (CAST(receptorTobaccoUse.pct as int), 0)) || '% ', tobaccoUse.nm)), '|')) as smokerStatus, ereceptor.age, age_uom.nm, age_qualifier.nm, ( Select STRING_AGG(CAST(pct as int) || '% ' || gender.nm_html, '|') from edit.exp_receptor_gender expgender left outer join edit.gender on expgender.gender_id = gender.id where exp_receptor_id = ereceptor.id) as genderDetails, ( Select STRING_AGG(distinct CAST(receptorRace.pct as int) || '% ' || race.cd, '|')) as receptorRace, ( Select STRING_AGG(distinct eventAssayMethod.nm, '|')) as assayMethods, eevent.detection_limit, eevent.detection_limit_uom, eevent.detection_freq as detectionFreq, medium.nm as medium_nm, eevent.exp_marker_term_nm, eevent.exp_marker_lvl, eevent.assay_uom, eevent.assay_measurement_stat, eevent.assay_note, ( Select STRING_AGG(distinct country.nm, '|')) as country, ( Select STRING_AGG(distinct eventLocation.geographic_region_nm, '|')) as stateOrProvince, ( Select STRING_AGG(distinct eventLocation.locality_txt, '|')) as region, eevent.note as eventNote, eiot.description as outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_action_degree_type_nm, outcome.phenotype_term_nm, ( Select STRING_AGG(distinct expAnatomy.anatomy_term_nm, '|')) as anatomy, outcome.note as outcomeNote from exposure e left outer join reference pref on pref.acc_txt = e.reference_acc_txt left outer join reference_exp referenceExp on referenceExp.reference_acc_txt = e.reference_acc_txt left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id left outer join study_factor studyFactor on studyFactor.id = expStudyFactor.study_factor_id left outer join exp_event_project eventproject on eventproject.exp_event_id = e.exp_event_id inner join exp_stressor stressor on e.exp_stressor_id = stressor.id left outer join exp_receptor ereceptor on e.exp_receptor_id = ereceptor.id left outer join age_uom age_uom on ereceptor.age_uom_id = age_uom.id left outer join age_qualifier age_qualifier on ereceptor.age_qualifier_id = age_qualifier.id left outer join exp_event eevent on e.exp_event_id = eevent.id left outer join medium medium on eevent.medium_id = medium.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 ereceptor.id = receptorTobaccoUse.exp_receptor_id left outer join tobacco_use tobaccoUse on receptorTobaccoUse.tobacco_use_id = tobaccoUse.id left outer join exp_receptor_race receptorRace on ereceptor.id = receptorRace.exp_receptor_id left outer join race race on receptorRace.race_id = race.id left outer join exp_event_location eventLocation on eevent.id = eventLocation.exp_event_id left outer join country on eventLocation.country_id = country.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 exp_anatomy expAnatomy on outcome.id = expAnatomy.exp_outcome_id left outer join exp_event_assay_method eventAssayMethod on eevent.id = eventAssayMethod.exp_event_id where country.cd = 'BD' group by e.reference_acc_txt, author, referenceExp.author_summary, pref.abbr_authors_txt, eevent.collection_start_yr, eevent.collection_end_yr, stressor.chem_term_nm, stressorSrcDetails, stressorSampleQty, stressorNote, nbrReceptors, ereceptor.description, ereceptor.term_nm, ereceptor.term_acc_txt, receptor_note, ereceptor.age, age_uom.nm, age_qualifier.nm, eevent.detection_limit, eevent.detection_limit_uom, detectionFreq, medium.nm, eevent.exp_marker_term_nm, eevent.exp_marker_lvl, eevent.assay_uom, eevent.assay_measurement_stat, eevent.assay_note, eventNote, outcomeRelationship, outcome.disease_term_nm, outcome.phenotype_action_degree_type_nm, outcome.phenotype_term_nm, outcomeNote, ereceptor.id;

Date: 2024-10-01 11:49:52
Duration: 1s219ms