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-04-23 00:15:44
Duration: 15m43s

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 9m8s - Times executed: 1 ]

x Hide

SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, gcr.gene_id, gcr.taxon_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 = '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 'MAGI2-AS3') 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:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'INTESTINAL NEOPLASMS' AND t.object_type_id = 3 AND gd.curated_reference_qty > 0))) 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));

Date: 2024-04-23 05:11:44
Duration: 9m8s
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 9m7s - Times executed: 1 ]

x Hide

SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, gcr.chem_id, gcr.gene_id, gcr.taxon_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 = '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 'MAGI2-AS3') 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:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'INTESTINAL NEOPLASMS' AND t.object_type_id = 3 AND gd.curated_reference_qty > 0))) 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 'COPPER' 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));

Date: 2024-04-23 05:35:45
Duration: 9m7s
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 9m1s - Times executed: 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 = '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 'MAGI2-AS3') 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:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'INTESTINAL NEOPLASMS' AND t.object_type_id = 3 AND gd.curated_reference_qty > 0))) 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))) ORDER BY g.nm_sort, g.id LIMIT 500;

Date: 2024-04-23 05:20:46
Duration: 9m1s
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 4s621ms - Times executed: 3 ]

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

Date: 2024-04-23 04:24:20
Duration: 8m27s
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 = '1237381') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

Date: 2024-04-23 05:54:13
Duration: 2s330ms
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 = '1237803') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

Date: 2024-04-23 04:51:05
Duration: 1s613ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: pubeu - Total duration: 2m49s - Times executed: 26 ]

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

Date: 2024-04-23 00:18:51
Duration: 11s685ms
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 = '2052624') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;

Date: 2024-04-23 04:50:03
Duration: 11s674ms
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 = '2063367') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;

Date: 2024-04-23 14:34:49
Duration: 11s672ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

[ User: pubeu - Total duration: 2m21s - 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 = '2063367') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2024-04-23 22:49:06
Duration: 36s218ms
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 = '2063367') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2024-04-23 23:33:57
Duration: 35s824ms
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 = '2063367') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2024-04-23 22:48:02
Duration: 35s654ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide