Times Reported Time consuming queries #1

x Hide

[ User: pubeu - Total duration: 26m28s - Times executed: 25 ]

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

Date: 2024-03-20 04:22:49
Duration: 20m23s
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 = '2051475') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2024-03-20 04:02:28
Duration: 15m25s
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 = '2051475') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2024-03-20 05:53:22
Duration: 8m43s
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 18m32s - Times executed: 254 ]

x Hide

SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '646767' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '646767') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50;

Date: 2024-03-20 06:37:26
Duration: 17s446ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '646442' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '646442') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50;

Date: 2024-03-20 06:37:24
Duration: 15s113ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '954270' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '954270') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50;

Date: 2024-03-20 06:37:23
Duration: 14s287ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 11m56s - Times executed: 213 ]

[ User: qaeu - Total duration: 4s530ms - 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 = '646767' 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-03-20 06:37:24
Duration: 15s372ms
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 = '584588' 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-03-20 06:37:23
Duration: 14s682ms
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 = '364932' 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-03-20 06:37:23
Duration: 14s543ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #4

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-03-20 00:15:01
Duration: 15m

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 5m2s - Times executed: 76 ]

x Hide

SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '12033' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '12033') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50 OFFSET 200;

Date: 2024-03-20 06:37:23
Duration: 14s621ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '584586' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '584586') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50 OFFSET 300;

Date: 2024-03-20 06:37:23
Duration: 14s120ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '954270' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '954270') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50 OFFSET 350;

Date: 2024-03-20 06:37:22
Duration: 13s949ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: pubeu - Total duration: 14s969ms - Times executed: 5 ]

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

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

Date: 2024-03-20 01:23:08
Duration: 4m28s
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 = '1204159') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

Date: 2024-03-20 19:09:17
Duration: 3s191ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

[ User: pubeu - Total duration: 5m14s - Times executed: 65 ]

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

Date: 2024-03-20 06:37:26
Duration: 17s253ms
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 = '2048764') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;

Date: 2024-03-20 03:16:02
Duration: 14s847ms
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 = '2049119') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;

Date: 2024-03-20 04:22:55
Duration: 12s434ms
Bind query: yes