Times Reported Time consuming queries #1

x Hide

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

x Hide

[ Application: psql - Total duration: 9m13s - 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-01 00:09:15
Duration: 9m13s
Database: ctdprd51
User: pubc
Application: psql

x Hide

Times Reported Time consuming queries #2

x Hide

/*#015 \pset title 'Avg Query Execution Time'#015 SELECT TO_CHAR(a.query_tm, 'YYYY-MM') "Month"#015 ,ROUND(AVG(a.execution_ms)/ 1000, 2) "Seconds"#015 FROM log_query_archive a#015 WHERE a.execution_ms IS NOT NULL#015 AND a.type_cd NOT LIKE 'venn%'#015 AND a.type_cd NOT LIKE 'myVenn%'#015 AND a.type_cd NOT LIKE 'myGeneVenn%'#015 AND a.type_cd NOT LIKE 'batch%'#015 GROUP BY TO_CHAR(a.query_tm, 'YYYY-MM')#015 ORDER BY 1;#015 */ # 015 # 015 SELECT COALESCE(SUM(i.queries), 0) "Queries" # 015, COUNT(DISTINCT i.session_id) "Query Sessions" # 015, COALESCE(ROUND(SUM(i.queries) / COUNT(DISTINCT i.session_id), 3), 0) "Avg Q/S" # 015 FROM ( SELECT session_id # 015, COALESCE(SUM(submission_qty), 0) queries # 015 FROM log_query_archive # 015 GROUP BY session_id) i;

Date: 2026-01-01 05:56:39
Duration: 53s634ms

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubc - Total duration: 44s305ms - Times executed: 1 ]

x Hide

[ Application: psql - Total duration: 44s305ms - Times executed: 1 ]

x Hide

/*#015 * Cumulative query metrics.#015 *#015 * $Id: cumulative_query_metrics.sql 13065 2013-05-02 13:32:49Z mcr $#015 */ # 015 # 015 # 015 /*#015 \pset title 'Distinct Hosts per Month'#015 SELECT TO_CHAR(dt, 'YYYY-MM') "Month"#015 ,COUNT(DISTINCT i.remote_addr) "Distinct Hosts"#015 FROM (SELECT remote_addr#015 ,DATE_TRUNC('month',query_tm) dt#015 FROM log_query_archive) i#015 GROUP BY TO_CHAR(dt, 'YYYY-MM')#015 ORDER BY 1 DESC;#015 */ # 015 # 015 SELECT TO_CHAR(i.dt, 'YYYY-MM') "Month" # 015, COUNT(DISTINCT i.session_id) "Query Sessions" # 015 FROM ( SELECT session_id # 015, DATE_TRUNC('month', query_tm) dt # 015 FROM log_query_archive) i # 015 GROUP BY TO_CHAR(dt, 'YYYY-MM') # 015 ORDER BY 1 DESC;

Date: 2026-01-01 05:55:45
Duration: 44s305ms
Database: ctdprd51
User: pubc
Application: psql

x Hide

Times Reported Time consuming queries #4

x Hide

SELECT RANK(*) OVER (ORDER BY COUNT(*) DESC, basic_query_txt) "Rank" # 015, basic_query_txt "Query" # 015, COUNT(*) "Count" # 015 FROM log_query_archive # 015 WHERE basic_query_txt IS NOT NULL # 015 AND type_cd = 'gene' # 015 GROUP BY basic_query_txt # 015 ORDER BY 3 DESC # 015, 2 # 015 LIMIT 25;

Date: 2026-01-01 05:57:30
Duration: 12s922ms

SELECT RANK(*) OVER (ORDER BY COUNT(*) DESC, basic_query_txt) "Rank" # 015, basic_query_txt "Query" # 015, COUNT(*) "Count" # 015 FROM log_query_archive # 015 WHERE basic_query_txt IS NOT NULL # 015 AND type_cd = 'chem' # 015 GROUP BY basic_query_txt # 015 ORDER BY 3 DESC # 015, 2 # 015 LIMIT 25;

Date: 2026-01-01 05:57:16
Duration: 6s667ms

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 7s380ms - Times executed: 1 ]

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 ptr.term_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 = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_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 = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and 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 object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) 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-01-01 05:44:18
Duration: 7s435ms
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 ptr.term_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 = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_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 = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and 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 object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) 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-01-01 05:49:09
Duration: 7s380ms
Database: ctdprd51
User: pubeu
Bind query: yes