Times Reported Time consuming queries #1

x Hide

/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ */ SELECT maint_query_logs_archive ();

Date: 2024-08-26 00:16:31
Duration: 16m29s

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 3m59s - Times executed: 58 ]

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 = '1391044') 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 = '1391044') 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-08-26 12:06:28
Duration: 16s231ms
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 = '1362717') 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 = '1362717') 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-08-26 05:11:36
Duration: 4s768ms
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 = '1309095') 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 = '1309095') 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-08-26 22:12:40
Duration: 4s307ms
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 3m23s - Times executed: 1 ]

x Hide

SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1244203') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

Date: 2024-08-26 16:55:49
Duration: 3m23s
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: postgres - Total duration: 2m15s - Times executed: 4 ]

x Hide

[ Application: pg_dump - Total duration: 2m15s - Times executed: 4 ]

x Hide

COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;

Date: 2024-08-26 10:05:35
Duration: 33s868ms
Database: ctdprd51
User: postgres
Application: pg_dump

COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;

Date: 2024-08-26 18:05:35
Duration: 33s810ms
Database: ctdprd51
User: postgres
Application: pg_dump

COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;

Date: 2024-08-26 06:05:36
Duration: 33s747ms
Database: ctdprd51
User: postgres
Application: pg_dump

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 1m19s - Times executed: 15 ]

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 = '2072716') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2024-08-26 06:32:48
Duration: 14s426ms
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 = '2082631') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2024-08-26 12:12:02
Duration: 8s550ms
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 = '2082631') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2024-08-26 12:11:19
Duration: 8s139ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: pubeu - Total duration: 56s70ms - Times executed: 13 ]

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 = '1309095' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2024-08-26 22:11:15
Duration: 5s695ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1411364' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2024-08-26 19:11:51
Duration: 5s657ms
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 = '1433926' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2024-08-26 18:04:38
Duration: 5s620ms
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

[ User: pubeu - Total duration: 45s351ms - Times executed: 38 ]

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 = '1435399' or receptorTerm.id = '1435399' 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-08-26 05:43:46
Duration: 1s296ms
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 = '1241276' or receptorTerm.id = '1241276' 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-08-26 06:41:12
Duration: 1s265ms
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 = '1435399' or receptorTerm.id = '1435399' 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-08-26 05:38:43
Duration: 1s254ms
Bind query: yes