Times Reported Time consuming queries #1

x Hide

[ User: pubc - Total duration: 9m18s - Times executed: 1 ]

x Hide

[ Application: psql - Total duration: 9m18s - Times executed: 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: 2025-10-09 00:09:20
Duration: 9m18s
Database: ctdprd51
User: pubc
Application: psql

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 2m47s - 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 = 'protein'))) AND gcr.gene_id = ANY (ARRAY (( 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 'DISORDERS OF ENVIRONMENTAL ORIGIN' AND t.object_type_id = 3 AND gd.curated_reference_qty > 0)))) ORDER BY g.nm_sort, g.id LIMIT 50;

Date: 2025-10-09 07:37:59
Duration: 2m47s
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 18s961ms - Times executed: 2 ]

x Hide

SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', '95% & ETHANOL') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', '95% & ETHANOL') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '95%' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ETHANOL')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE '95%' OR upper(l.acc_txt) LIKE 'ETHANOL')) ii GROUP BY ii.cd;

Date: 2025-10-09 04:39:18
Duration: 9s991ms
Bind query: yes

SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', '95%ETHYL & ALCOHOL') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', '95%ETHYL & ALCOHOL') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '95%ETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ALCOHOL')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE '95%ETHYL' OR upper(l.acc_txt) LIKE 'ALCOHOL')) ii GROUP BY ii.cd;

Date: 2025-10-09 04:39:24
Duration: 9s490ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', '95%ETHYL & ALCOHOL') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', '95%ETHYL & ALCOHOL') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '95%ETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ALCOHOL')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE '95%ETHYL' OR upper(l.acc_txt) LIKE 'ALCOHOL')) ii GROUP BY ii.cd;

Date: 2025-10-09 04:39:11
Duration: 9s471ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 52s821ms - Times executed: 2 ]

x Hide

SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1245038') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;

Date: 2025-10-09 15:18:54
Duration: 26s848ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1245038') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;

Date: 2025-10-09 15:18:48
Duration: 25s972ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 29s18ms - Times executed: 5 ]

x Hide

SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1431166' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2025-10-09 18:37:27
Duration: 6s443ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1340246' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2025-10-09 22:06:42
Duration: 5s822ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1370433' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2025-10-09 09:22:43
Duration: 5s690ms
Database: ctdprd51
User: pubeu
Bind query: yes