Times Reported Time consuming queries #1

x Hide

SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ 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 = 'protein' OR tp.nm = 'gene' OR tp.nm = 'mRNA'))) AND gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Label.Gene */ gd.gene_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(l.nm) LIKE 'RENAL INSUFFICIENCY' AND l.object_type_id = 3)))) ORDER BY g.nm_sort, g.id LIMIT 50;

Date: 2025-01-07 08:10:17
Duration: 9h32m11s
Bind query: yes

SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ 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 = 'protein' OR tp.nm = 'gene' OR tp.nm = 'mRNA'))) AND gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Label.Gene */ gd.gene_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(l.nm) LIKE 'RENAL INSUFFICIENCY' AND l.object_type_id = 3)))) ORDER BY g.nm_sort, g.id LIMIT 50;

Date: 2025-01-07 08:05:39
Duration: 9h28m38s
Bind query: yes

SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ 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 = 'protein' OR tp.nm = 'gene' OR tp.nm = 'mRNA'))) AND gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Label.Gene */ gd.gene_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(l.nm) LIKE 'RENAL INSUFFICIENCY' AND l.object_type_id = 3)))) ORDER BY g.nm_sort, g.id LIMIT 50;

Date: 2025-01-07 08:01:41
Duration: 9h24m34s
Bind query: yes

x Hide

Times Reported Time consuming queries #2

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: 2025-01-07 00:37:23
Duration: 37m21s

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 7m13s - Times executed: 126 ]

x Hide

SELECT /* ChemGenesDAO */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, COUNT(gcr.ixn_id) ixnCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN term g ON gcr.gene_id = g.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1398604') GROUP BY g.nm, g.id, g.acc_txt, g.acc_db_cd, g.nm_sort ORDER BY COUNT(gcr.ixn_id) DESC, g.nm_sort LIMIT 50;

Date: 2025-01-07 11:48:41
Duration: 22s641ms
Bind query: yes

SELECT /* ChemGenesDAO */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, COUNT(gcr.ixn_id) ixnCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN term g ON gcr.gene_id = g.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1398604') GROUP BY g.nm, g.id, g.acc_txt, g.acc_db_cd, g.nm_sort ORDER BY COUNT(gcr.ixn_id) DESC, g.nm_sort LIMIT 50;

Date: 2025-01-07 11:48:56
Duration: 20s600ms
Bind query: yes

SELECT /* ChemGenesDAO */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, COUNT(gcr.ixn_id) ixnCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN term g ON gcr.gene_id = g.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1398604') GROUP BY g.nm, g.id, g.acc_txt, g.acc_db_cd, g.nm_sort ORDER BY COUNT(gcr.ixn_id) DESC, g.nm_sort LIMIT 50;

Date: 2025-01-07 11:48:18
Duration: 19s961ms
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 20m41s - Times executed: 10 ]

x Hide

SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2091134') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2025-01-07 01:16:41
Duration: 13m54s
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 = '2091134') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2025-01-07 01:17:56
Duration: 1m23s
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 = '2091134') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2025-01-07 01:01:16
Duration: 1m5s
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 7m25s - Times executed: 112 ]

x Hide

SELECT /* ChemDiseaseAssnsDAO */ c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemid, d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, cd.network_score networkscore, cd.indirect_gene_qty inferredcount, cd.reference_qty referencecount, cd.exposure_reference_qty exposureReferenceCount, CASE WHEN cd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cd.chem_id AND a.disease_id = cd.disease_id) ELSE NULL END actiontypes FROM chem_disease cd INNER JOIN term c ON cd.chem_id = c.id INNER JOIN term d ON cd.disease_id = d.id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1398604') ORDER BY actionTypes, cd.network_score DESC NULLS LAST, d.nm_sort, c.nm_sort LIMIT 50;

Date: 2025-01-07 13:44:53
Duration: 10s95ms
Bind query: yes

SELECT /* ChemDiseaseAssnsDAO */ c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemid, d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, cd.network_score networkscore, cd.indirect_gene_qty inferredcount, cd.reference_qty referencecount, cd.exposure_reference_qty exposureReferenceCount, CASE WHEN cd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cd.chem_id AND a.disease_id = cd.disease_id) ELSE NULL END actiontypes FROM chem_disease cd INNER JOIN term c ON cd.chem_id = c.id INNER JOIN term d ON cd.disease_id = d.id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1398604') ORDER BY actionTypes, cd.network_score DESC NULLS LAST, d.nm_sort, c.nm_sort LIMIT 50;

Date: 2025-01-07 13:44:44
Duration: 9s852ms
Bind query: yes

SELECT /* ChemDiseaseAssnsDAO */ c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemid, d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, cd.network_score networkscore, cd.indirect_gene_qty inferredcount, cd.reference_qty referencecount, cd.exposure_reference_qty exposureReferenceCount, CASE WHEN cd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cd.chem_id AND a.disease_id = cd.disease_id) ELSE NULL END actiontypes FROM chem_disease cd INNER JOIN term c ON cd.chem_id = c.id INNER JOIN term d ON cd.disease_id = d.id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1398604') ORDER BY actionTypes, cd.network_score DESC NULLS LAST, d.nm_sort, c.nm_sort LIMIT 50;

Date: 2025-01-07 13:44:54
Duration: 9s823ms
Bind query: yes

x Hide

Times Reported Time consuming queries #6

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 = '1245241') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

Date: 2025-01-07 08:13:15
Duration: 20m47s
Bind query: yes

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

Date: 2025-01-07 07:34:18
Duration: 6s921ms
Bind query: yes

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

Date: 2025-01-07 05:52:42
Duration: 1s305ms
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

[ User: pubeu - Total duration: 3m46s - Times executed: 72 ]

[ User: qaeu - Total duration: 8s250ms - Times executed: 3 ]

x Hide

SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2085084');

Date: 2025-01-07 01:16:39
Duration: 5s659ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2091134');

Date: 2025-01-07 00:58:50
Duration: 5s93ms
Bind query: yes

SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2091134');

Date: 2025-01-07 00:48:46
Duration: 5s56ms
Bind query: yes

x Hide

Times Reported Time consuming queries #8

x Hide

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1221429')) ORDER BY r.sort_txt LIMIT 50;

Date: 2025-01-07 00:42:30
Duration: 4m53s
Bind query: yes

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '2083230')) ORDER BY r.sort_txt LIMIT 50;

Date: 2025-01-07 18:42:40
Duration: 50s420ms
Bind query: yes

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1431947')) ORDER BY r.sort_txt LIMIT 50;

Date: 2025-01-07 11:48:32
Duration: 1s238ms
Bind query: yes