Times Reported Time consuming queries #1

x Hide

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

x Hide

[ Application: pgAdmin 4 - CONN:7282291 - Total duration: 3m21s - Times executed: 1 ]

x Hide

explain analyze SELECT 'D019216' 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.id in ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = 1376431)) 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-14 13:13:06
Duration: 3m21s
Database: ctddev51
User: pubeu
Application: pgAdmin 4 - CONN:7282291

x Hide

Times Reported Time consuming queries #2

x Hide

WITH sq AS ( SELECT c.id AS chem_id, c.nm AS chem_nm, c.acc_txt AS chem_acc_txt, c.secondary_nm AS casRN, gcr.gene_id, g.nm AS gene_symbol, g.acc_txt AS gene_acc_txt FROM term c JOIN gene_chem_reference gcr ON gcr.chem_id = c.id JOIN term g ON g.id = gcr.gene_id WHERE c.acc_txt = 'D015056' GROUP BY c.id, c.nm, c.acc_txt, c.secondary_nm, gcr.gene_id, g.nm, g.acc_txt ) SELECT 'D015056' AS "Input", sq.chem_nm AS "ChemicalName", sq.chem_acc_txt AS "ChemicalID", sq.casRN AS "CasRN", sq.gene_symbol AS "GeneSymbol", sq.gene_acc_txt AS "GeneID", d.nm AS "Ontology", gt.nm AS "GoTermName", gt.acc_txt AS "GoTermID" FROM sq JOIN gene_go_annot gga ON gga.gene_id = sq.gene_id AND gga.is_not = false JOIN dag_node gt ON gt.object_id = gga.go_term_id JOIN dag d ON d.id = gt.dag_id GROUP BY sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, d.nm, gt.nm, gt.acc_txt ORDER BY sq.chem_nm, sq.gene_symbol, d.nm, gt.nm;

Date: 2026-05-14 13:27:12
Duration: 37s547ms

WITH sq AS ( SELECT c.id AS chem_id, c.nm AS chem_nm, c.acc_txt AS chem_acc_txt, c.secondary_nm AS casRN, gcr.gene_id, g.nm AS gene_symbol, g.acc_txt AS gene_acc_txt FROM term c JOIN gene_chem_reference gcr ON gcr.chem_id = c.id JOIN term g ON g.id = gcr.gene_id WHERE c.acc_txt = 'D015056' GROUP BY c.id, c.nm, c.acc_txt, c.secondary_nm, gcr.gene_id, g.nm, g.acc_txt ) SELECT 'D015056' AS "Input", sq.chem_nm AS "ChemicalName", sq.chem_acc_txt AS "ChemicalID", sq.casRN AS "CasRN", sq.gene_symbol AS "GeneSymbol", sq.gene_acc_txt AS "GeneID", d.nm AS "Ontology", gt.nm AS "GoTermName", gt.acc_txt AS "GoTermID" FROM sq JOIN gene_go_annot gga ON gga.gene_id = sq.gene_id AND gga.is_not = false JOIN dag_node gt ON gt.object_id = gga.go_term_id JOIN dag d ON d.id = gt.dag_id GROUP BY sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, d.nm, gt.nm, gt.acc_txt ORDER BY sq.chem_nm, sq.gene_symbol, d.nm, gt.nm;

Date: 2026-05-14 13:28:04
Duration: 37s505ms

x Hide

Times Reported Time consuming queries #3

x Hide

explain ( analyze, buffers, verbose, settings, wal ) 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-14 13:14:15
Duration: 52s799ms

x Hide

Times Reported Time consuming queries #4

x Hide

explain ( analyze, buffers, verbose, settings, wal ) 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 JOIN gene_chem_reference gcr ON gcr.chem_id = c.id JOIN term g ON g.id = gcr.gene_id WHERE c.acc_txt = 'D015056' ) SELECT DISTINCT 'D015056' AS "Input", sq.chem_nm AS "ChemicalName", sq.chem_acc_txt AS "ChemicalID", sq.casRN AS "CasRN", sq.gene_symbol AS "GeneSymbol", sq.gene_acc_txt AS "GeneID", d.nm AS "Ontology", gt.nm AS "GoTermName", gt.acc_txt AS "GoTermID" FROM sq JOIN gene_go_annot gga ON gga.gene_id = sq.gene_id JOIN dag_node gt ON gt.object_id = gga.go_term_id JOIN dag d ON d.id = gt.dag_id WHERE gga.is_not = false ORDER BY sq.chem_nm, sq.gene_symbol, d.nm, gt.nm;

Date: 2026-05-14 13:20:32
Duration: 44s598ms

x Hide

Times Reported Time consuming queries #5

x Hide

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 JOIN gene_chem_reference gcr ON gcr.chem_id = c.id JOIN term g ON g.id = gcr.gene_id WHERE c.acc_txt = 'D015056' ) SELECT DISTINCT 'D015056' AS "Input", sq.chem_nm AS "ChemicalName", sq.chem_acc_txt AS "ChemicalID", sq.casRN AS "CasRN", sq.gene_symbol AS "GeneSymbol", sq.gene_acc_txt AS "GeneID", d.nm AS "Ontology", gt.nm AS "GoTermName", gt.acc_txt AS "GoTermID" FROM sq JOIN gene_go_annot gga ON gga.gene_id = sq.gene_id JOIN dag_node gt ON gt.object_id = gga.go_term_id JOIN dag d ON d.id = gt.dag_id WHERE gga.is_not = false ORDER BY sq.chem_nm, sq.gene_symbol, d.nm, gt.nm;

Date: 2026-05-14 13:26:12
Duration: 39s439ms

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: pubeu - Total duration: 23s61ms - Times executed: 1 ]

x Hide

[ Application: pgAdmin 4 - CONN:7474459 - Total duration: 23s61ms - Times executed: 1 ]

x Hide

WITH descendant_terms AS MATERIALIZED ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = 179416 ), filtered_ptr AS MATERIALIZED ( SELECT ptr.* FROM phenotype_term_reference ptr JOIN descendant_terms dt ON ptr.taxon_id = dt.descendant_object_id ) SELECT DISTINCT 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, 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 filtered_ptr ptr JOIN phenotype_term pt ON pt.term_id = ptr.term_id AND pt.phenotype_id = ptr.phenotype_id JOIN term associatedTerm ON associatedTerm.id = ptr.term_id JOIN term phenotypeTerm ON phenotypeTerm.id = ptr.phenotype_id LEFT JOIN term taxonTerm ON taxonTerm.id = ptr.taxon_id JOIN reference r ON r.id = ptr.reference_id JOIN ixn i ON i.id = ptr.ixn_id JOIN object_type o ON o.id = associatedTerm.object_type_id LEFT JOIN ixn_anatomy ia ON ia.ixn_id = ptr.ixn_id LEFT JOIN term anatomyTerm ON anatomyTerm.id = ia.anatomy_id GROUP BY associatedTerm, associatedTermNmSort, phenotype, casRN, ptr.ixn_id, i.ixn_prose_html, i.ixn_prose_txt, i.sort_txt, associatedTerm.id, phenotypeTerm.id, pt.indirect_term_qty;

Date: 2026-05-14 12:38:25
Duration: 23s61ms
Database: ctddev51
User: pubeu
Application: pgAdmin 4 - CONN:7474459

x Hide

Times Reported Time consuming queries #1

x Hide

WITH sq AS ( SELECT c.id AS chem_id, c.nm AS chem_nm, c.acc_txt AS chem_acc_txt, c.secondary_nm AS casRN, gcr.gene_id, g.nm AS gene_symbol, g.acc_txt AS gene_acc_txt FROM term c JOIN gene_chem_reference gcr ON gcr.chem_id = c.id JOIN term g ON g.id = gcr.gene_id WHERE c.acc_txt = 'D015056' GROUP BY c.id, c.nm, c.acc_txt, c.secondary_nm, gcr.gene_id, g.nm, g.acc_txt ) SELECT 'D015056' AS "Input", sq.chem_nm AS "ChemicalName", sq.chem_acc_txt AS "ChemicalID", sq.casRN AS "CasRN", sq.gene_symbol AS "GeneSymbol", sq.gene_acc_txt AS "GeneID", d.nm AS "Ontology", gt.nm AS "GoTermName", gt.acc_txt AS "GoTermID" FROM sq JOIN gene_go_annot gga ON gga.gene_id = sq.gene_id AND gga.is_not = false JOIN dag_node gt ON gt.object_id = gga.go_term_id JOIN dag d ON d.id = gt.dag_id GROUP BY sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, d.nm, gt.nm, gt.acc_txt ORDER BY sq.chem_nm, sq.gene_symbol, d.nm, gt.nm;

Date: 2026-05-14 13:27:12
Duration: 37s547ms

WITH sq AS ( SELECT c.id AS chem_id, c.nm AS chem_nm, c.acc_txt AS chem_acc_txt, c.secondary_nm AS casRN, gcr.gene_id, g.nm AS gene_symbol, g.acc_txt AS gene_acc_txt FROM term c JOIN gene_chem_reference gcr ON gcr.chem_id = c.id JOIN term g ON g.id = gcr.gene_id WHERE c.acc_txt = 'D015056' GROUP BY c.id, c.nm, c.acc_txt, c.secondary_nm, gcr.gene_id, g.nm, g.acc_txt ) SELECT 'D015056' AS "Input", sq.chem_nm AS "ChemicalName", sq.chem_acc_txt AS "ChemicalID", sq.casRN AS "CasRN", sq.gene_symbol AS "GeneSymbol", sq.gene_acc_txt AS "GeneID", d.nm AS "Ontology", gt.nm AS "GoTermName", gt.acc_txt AS "GoTermID" FROM sq JOIN gene_go_annot gga ON gga.gene_id = sq.gene_id AND gga.is_not = false JOIN dag_node gt ON gt.object_id = gga.go_term_id JOIN dag d ON d.id = gt.dag_id GROUP BY sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, d.nm, gt.nm, gt.acc_txt ORDER BY sq.chem_nm, sq.gene_symbol, d.nm, gt.nm;

Date: 2026-05-14 13:28:04
Duration: 37s505ms

x Hide

Times Reported Time consuming queries #2

x Hide

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

x Hide

[ Application: pgAdmin 4 - CONN:7282291 - Total duration: 3m21s - Times executed: 1 ]

x Hide

explain analyze SELECT 'D019216' 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.id in ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = 1376431)) 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-14 13:13:06
Duration: 3m21s
Database: ctddev51
User: pubeu
Application: pgAdmin 4 - CONN:7282291

x Hide

Times Reported Time consuming queries #3

x Hide

explain ( analyze, buffers, verbose, settings, wal ) 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-14 13:14:15
Duration: 52s799ms

x Hide

Times Reported Time consuming queries #4

x Hide

explain ( analyze, buffers, verbose, settings, wal ) 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 JOIN gene_chem_reference gcr ON gcr.chem_id = c.id JOIN term g ON g.id = gcr.gene_id WHERE c.acc_txt = 'D015056' ) SELECT DISTINCT 'D015056' AS "Input", sq.chem_nm AS "ChemicalName", sq.chem_acc_txt AS "ChemicalID", sq.casRN AS "CasRN", sq.gene_symbol AS "GeneSymbol", sq.gene_acc_txt AS "GeneID", d.nm AS "Ontology", gt.nm AS "GoTermName", gt.acc_txt AS "GoTermID" FROM sq JOIN gene_go_annot gga ON gga.gene_id = sq.gene_id JOIN dag_node gt ON gt.object_id = gga.go_term_id JOIN dag d ON d.id = gt.dag_id WHERE gga.is_not = false ORDER BY sq.chem_nm, sq.gene_symbol, d.nm, gt.nm;

Date: 2026-05-14 13:20:32
Duration: 44s598ms

x Hide

Times Reported Time consuming queries #5

x Hide

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 JOIN gene_chem_reference gcr ON gcr.chem_id = c.id JOIN term g ON g.id = gcr.gene_id WHERE c.acc_txt = 'D015056' ) SELECT DISTINCT 'D015056' AS "Input", sq.chem_nm AS "ChemicalName", sq.chem_acc_txt AS "ChemicalID", sq.casRN AS "CasRN", sq.gene_symbol AS "GeneSymbol", sq.gene_acc_txt AS "GeneID", d.nm AS "Ontology", gt.nm AS "GoTermName", gt.acc_txt AS "GoTermID" FROM sq JOIN gene_go_annot gga ON gga.gene_id = sq.gene_id JOIN dag_node gt ON gt.object_id = gga.go_term_id JOIN dag d ON d.id = gt.dag_id WHERE gga.is_not = false ORDER BY sq.chem_nm, sq.gene_symbol, d.nm, gt.nm;

Date: 2026-05-14 13:26:12
Duration: 39s439ms

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: pubeu - Total duration: 23s61ms - Times executed: 1 ]

x Hide

[ Application: pgAdmin 4 - CONN:7474459 - Total duration: 23s61ms - Times executed: 1 ]

x Hide

WITH descendant_terms AS MATERIALIZED ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = 179416 ), filtered_ptr AS MATERIALIZED ( SELECT ptr.* FROM phenotype_term_reference ptr JOIN descendant_terms dt ON ptr.taxon_id = dt.descendant_object_id ) SELECT DISTINCT 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, 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 filtered_ptr ptr JOIN phenotype_term pt ON pt.term_id = ptr.term_id AND pt.phenotype_id = ptr.phenotype_id JOIN term associatedTerm ON associatedTerm.id = ptr.term_id JOIN term phenotypeTerm ON phenotypeTerm.id = ptr.phenotype_id LEFT JOIN term taxonTerm ON taxonTerm.id = ptr.taxon_id JOIN reference r ON r.id = ptr.reference_id JOIN ixn i ON i.id = ptr.ixn_id JOIN object_type o ON o.id = associatedTerm.object_type_id LEFT JOIN ixn_anatomy ia ON ia.ixn_id = ptr.ixn_id LEFT JOIN term anatomyTerm ON anatomyTerm.id = ia.anatomy_id GROUP BY associatedTerm, associatedTermNmSort, phenotype, casRN, ptr.ixn_id, i.ixn_prose_html, i.ixn_prose_txt, i.sort_txt, associatedTerm.id, phenotypeTerm.id, pt.indirect_term_qty;

Date: 2026-05-14 12:38:25
Duration: 23s61ms
Database: ctddev51
User: pubeu
Application: pgAdmin 4 - CONN:7474459

x Hide

Times Reported Time consuming queries #1

x Hide

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

x Hide

[ Application: pgAdmin 4 - CONN:7282291 - Total duration: 3m21s - Times executed: 1 ]

x Hide

explain analyze SELECT 'D019216' 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.id in ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = 1376431)) 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-14 13:13:06
Duration: 3m21s
Database: ctddev51
User: pubeu
Application: pgAdmin 4 - CONN:7282291

x Hide