Times Reported Time consuming queries #1

x Hide

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

x Hide

[ Application: psql - Total duration: 9m8s - 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-11-30 00:09:11
Duration: 9m8s
Database: ctdprd51
User: pubc
Application: psql

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 1m13s - Times executed: 13 ]

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 = '1292466' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2025-11-30 23:36:47
Duration: 6s11ms
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 = '1392805' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2025-11-30 22:20:36
Duration: 5s924ms
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 = '1440361' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2025-11-30 15:21:03
Duration: 5s918ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 27s25ms - Times executed: 3 ]

x Hide

SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id 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.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 'BISPHENOL A' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt LIMIT 50;

Date: 2025-11-30 00:38:12
Duration: 9s233ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id 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.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 'BISPHENOL A' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt LIMIT 50;

Date: 2025-11-30 00:47:40
Duration: 9s200ms
Bind query: yes

SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id 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.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 'BISPHENOL A' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt LIMIT 50;

Date: 2025-11-30 00:33:48
Duration: 8s994ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

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

x Hide

SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id 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.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 'BISPHENOL A' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt;

Date: 2025-11-30 00:34:53
Duration: 9s337ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id 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 = 'mRNA'))) 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 'BISPHENOL A' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY c.nm_sort, g.nm_sort, i.sort_txt;

Date: 2025-11-30 01:08:18
Duration: 8s963ms
Bind query: yes

x Hide