Times Reported Time consuming queries #1

x Hide

SELECT /* Optimized ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd AS associatedTerm, associatedTerm.id AS associatedTermId, ptr.ixn_id AS ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort AS associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') AS casRN, phenotypeTerm.nm || '^' || 'GO' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd AS phenotype, phenotypeTerm.id AS phenotypeId, -- Move aggregation into the main group to avoid N+1 subqueries STRING_AGG(DISTINCT taxonTerm.nm || '^' || 'TAXON' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|') AS taxonTerms, STRING_AGG(DISTINCT anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') AS anatomyTerms, COUNT(DISTINCT taxonTerm.nm) AS taxonCount, i.ixn_prose_html AS ixnProseHtml, i.ixn_prose_txt AS ixnProse, i.sort_txt AS ixnSort, STRING_AGG(DISTINCT r.acc_txt, '|') AS references, COUNT(DISTINCT ptr.reference_id) AS refCount, pt.indirect_term_qty AS inferredCount, COUNT(*) OVER () AS full_count FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt ON ptr.term_id = pt.term_id AND ptr.phenotype_id = pt.phenotype_id INNER JOIN term associatedTerm ON ptr.term_id = associatedTerm.id INNER JOIN term phenotypeTerm ON ptr.phenotype_id = phenotypeTerm.id INNER JOIN reference r ON ptr.reference_id = r.id INNER JOIN ixn i ON ptr.ixn_id = i.id INNER JOIN object_type o ON associatedTerm.object_type_id = o.id LEFT OUTER JOIN term taxonTerm ON ptr.taxon_id = taxonTerm.id LEFT OUTER JOIN ixn_anatomy ia ON ptr.ixn_id = ia.ixn_id LEFT OUTER JOIN term anatomyTerm ON ia.anatomy_id = anatomyTerm.id WHERE taxonTerm.id IN ( -- Replaced ANY ARRAY with a standard IN for better Semi-Join optimization SELECT descendant_object_id FROM dag_path WHERE ancestor_object_id = 179416) GROUP BY associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount;

Date: 2026-05-12 16:01:02
Duration: 39s406ms

SELECT /* Optimized ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd AS associatedTerm, associatedTerm.id AS associatedTermId, ptr.ixn_id AS ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort AS associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') AS casRN, phenotypeTerm.nm || '^' || 'GO' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd AS phenotype, phenotypeTerm.id AS phenotypeId, -- Move aggregation into the main group to avoid N+1 subqueries STRING_AGG(DISTINCT taxonTerm.nm || '^' || 'TAXON' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|') AS taxonTerms, STRING_AGG(DISTINCT anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') AS anatomyTerms, COUNT(DISTINCT taxonTerm.nm) AS taxonCount, i.ixn_prose_html AS ixnProseHtml, i.ixn_prose_txt AS ixnProse, i.sort_txt AS ixnSort, STRING_AGG(DISTINCT r.acc_txt, '|') AS references, COUNT(DISTINCT ptr.reference_id) AS refCount, pt.indirect_term_qty AS inferredCount, COUNT(*) OVER () AS full_count FROM phenotype_term_reference ptr INNER JOIN phenotype_term pt ON ptr.term_id = pt.term_id AND ptr.phenotype_id = pt.phenotype_id INNER JOIN term associatedTerm ON ptr.term_id = associatedTerm.id INNER JOIN term phenotypeTerm ON ptr.phenotype_id = phenotypeTerm.id INNER JOIN reference r ON ptr.reference_id = r.id INNER JOIN ixn i ON ptr.ixn_id = i.id INNER JOIN object_type o ON associatedTerm.object_type_id = o.id LEFT OUTER JOIN term taxonTerm ON ptr.taxon_id = taxonTerm.id LEFT OUTER JOIN ixn_anatomy ia ON ptr.ixn_id = ia.ixn_id LEFT OUTER JOIN term anatomyTerm ON ia.anatomy_id = anatomyTerm.id WHERE taxonTerm.id IN ( -- Replaced ANY ARRAY with a standard IN for better Semi-Join optimization SELECT descendant_object_id FROM dag_path WHERE ancestor_object_id = 179416) GROUP BY associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount;

Date: 2026-05-12 16:12:43
Duration: 38s543ms

x Hide

Times Reported Time consuming queries #2

x Hide

select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'MUS MUSCULUS' AND tl.object_type_id = 1))) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;

Date: 2026-05-12 15:34:26
Duration: 17s223ms
Bind query: yes

select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'MUS MUSCULUS' AND tl.object_type_id = 1))) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;

Date: 2026-05-12 15:20:59
Duration: 17s112ms
Bind query: yes

select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select term_id from term_label tl WHERE UPPER(tl.nm) = 'MUS MUSCULUS' AND tl.object_type_id = 1))) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort asc, pt.indirect_term_qty desc LIMIT 50;

Date: 2026-05-12 14:58:22
Duration: 17s79ms
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pub1 - Total duration: 51s973ms - Times executed: 1 ]

x Hide

[ Application: pgAdmin 4 - CONN:4939229 - Total duration: 51s973ms - Times executed: 1 ]

x Hide

SELECT 'D015056' AS "Input", sqi.chem_nm AS "ChemicalName", sqi.chem_acc_txt AS "ChemicalID", sqi.casRN AS "CasRN", sqi.gene_symbol AS "GeneSymbol", sqi.gene_acc_txt AS "GeneID", sqi.ontology_nm AS "Ontology", sqi.go_term_nm AS "GoTermName", sqi.go_acc_txt AS "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id AS chem_id, c.nm AS chem_nm, c.acc_txt AS chem_acc_txt, c.secondary_nm AS casRN, c.nm_sort AS chem_nm_sort, gcr.gene_id, g.nm AS gene_symbol, g.acc_txt AS gene_acc_txt, g.nm_sort AS gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE c.acc_txt = 'D015056' ) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm AS go_term_nm, gt.acc_txt AS go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm AS ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false /* optional DAG filters */ ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;

Date: 2026-05-12 10:25:39
Duration: 51s973ms
Database: ctddev51
User: pub1
Application: pgAdmin 4 - CONN:4939229

x Hide