Times Reported Time consuming queries #1

x Hide

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

x Hide

[ Application: psql - Total duration: 9m10s - 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: 2026-01-18 00:09:12
Duration: 9m10s
Database: ctdprd51
User: pubc
Application: psql

x Hide

Times Reported Time consuming queries #2

x Hide

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

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

Date: 2026-01-18 20:33:56
Duration: 39s713ms
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 = '2126896') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2026-01-18 20:33:55
Duration: 39s398ms
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 = '2128651') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;

Date: 2026-01-18 03:34:11
Duration: 27s259ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 1m29s - Times executed: 1 ]

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', '1.1 & 手工获取（推荐，最稳） & CTD & 不适合直接 & API & 抓，最稳的是官网导出 & 操作步骤 & 打开：HTTPS & //CTDBASE.ORG & 搜索 & CADMIUM & 点击 & CADMIUM & → & GENE & INTERACTIONS & 筛选： & INTERACTION & TYPE：EXPRESSION & ORGANISM：HOMO & SAPIENS & EXPORT & → & CSV & 得到文件： & 👉 & CTD_CADMIUM_GENES.CSV') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', '1.1 & 手工获取（推荐，最稳） & CTD & 不适合直接 & API & 抓，最稳的是官网导出 & 操作步骤 & 打开：HTTPS & //CTDBASE.ORG & 搜索 & CADMIUM & 点击 & CADMIUM & → & GENE & INTERACTIONS & 筛选： & INTERACTION & TYPE：EXPRESSION & ORGANISM：HOMO & SAPIENS & EXPORT & → & CSV & 得到文件： & 👉 & CTD_CADMIUM_GENES.CSV') 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 '1.1' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '手工获取（推荐，最稳）' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CTD' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '不适合直接' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'API' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '抓，最稳的是官网导出' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '操作步骤' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '打开：HTTPS' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '//CTDBASE.ORG' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '搜索' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CADMIUM' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '点击' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CADMIUM' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '→' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'GENE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'INTERACTIONS' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '筛选：' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'INTERACTION' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'TYPE：EXPRESSION' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ORGANISM：HOMO' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'SAPIENS' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'EXPORT' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '→' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CSV' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '得到文件：' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '👉' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'CTD_CADMIUM_GENES.CSV')) 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 '1.1' OR upper(l.acc_txt) LIKE '手工获取（推荐，最稳）' OR upper(l.acc_txt) LIKE 'CTD' OR upper(l.acc_txt) LIKE '不适合直接' OR upper(l.acc_txt) LIKE 'API' OR upper(l.acc_txt) LIKE '抓，最稳的是官网导出' OR upper(l.acc_txt) LIKE '操作步骤' OR upper(l.acc_txt) LIKE '打开：HTTPS' OR upper(l.acc_txt) LIKE '//CTDBASE.ORG' OR upper(l.acc_txt) LIKE '搜索' OR upper(l.acc_txt) LIKE 'CADMIUM' OR upper(l.acc_txt) LIKE '点击' OR upper(l.acc_txt) LIKE 'CADMIUM' OR upper(l.acc_txt) LIKE '→' OR upper(l.acc_txt) LIKE 'GENE' OR upper(l.acc_txt) LIKE 'INTERACTIONS' OR upper(l.acc_txt) LIKE '筛选：' OR upper(l.acc_txt) LIKE 'INTERACTION' OR upper(l.acc_txt) LIKE 'TYPE：EXPRESSION' OR upper(l.acc_txt) LIKE 'ORGANISM：HOMO' OR upper(l.acc_txt) LIKE 'SAPIENS' OR upper(l.acc_txt) LIKE 'EXPORT' OR upper(l.acc_txt) LIKE '→' OR upper(l.acc_txt) LIKE 'CSV' OR upper(l.acc_txt) LIKE '得到文件：' OR upper(l.acc_txt) LIKE '👉' OR upper(l.acc_txt) LIKE 'CTD_CADMIUM_GENES.CSV')) ii GROUP BY ii.cd;

Date: 2026-01-18 11:40:45
Duration: 1m29s
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 24s93ms - Times executed: 2 ]

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

Date: 2026-01-18 20:34:05
Duration: 12s386ms
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 = '2126896') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;

Date: 2026-01-18 20:33:59
Duration: 11s706ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 11s937ms - Times executed: 2 ]

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

Date: 2026-01-18 20:41:04
Duration: 6s103ms
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 = '1305189' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2026-01-18 20:40:58
Duration: 5s833ms
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 = '1305189' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;

Date: 2026-01-18 20:41:01
Duration: 5s620ms
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: pubeu - Total duration: 10s989ms - 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.gene_id = ANY (ARRAY (( SELECT /* CIQH.getIxnGeneWhereEquals.Name */ gi.id gene_id FROM term gi WHERE gi.object_type_id = 4 AND UPPER(gi.nm) LIKE 'TP53'))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases' OR gcra.action_degree_type_nm = 'decreases')) 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: 2026-01-18 09:17:18
Duration: 10s989ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

[ User: qaeu - Total duration: 5s368ms - Times executed: 1 ]

[ User: pubeu - Total duration: 5s132ms - Times executed: 1 ]

x Hide

SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort 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 = 1332236)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm 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 AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;

Date: 2026-01-18 05:43:39
Duration: 5s368ms
Database: ctdprd51
User: qaeu
Bind query: yes

SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort 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 = 1332236)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm 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 AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;

Date: 2026-01-18 05:48:49
Duration: 5s132ms
Database: ctdprd51
User: pubeu
Bind query: yes