VACUUM FULL ANALYZE log_query;

Date: 2025-07-31 00:40:45
Duration: 5s21ms

VACUUM FULL ANALYZE log_query;

Date: 2025-07-31 00:40:43
Duration: 0ms

x Hide

SELECT /* AdvancedQueryDAO.getQueryId */ id FROM pubc.log_query WHERE session_id = $1 AND type_cd = $2 AND server_nm = $3 AND basic_query_txt IS NULL AND gene_txt IS NULL AND gene_query_type IS NULL AND pathway_txt IS NULL AND pathway_query_type IS NULL AND taxon_txt IS NULL AND taxon_query_type IS NULL AND chem_txt IS NULL AND chem_query_type IS NULL AND acc_txt IS NULL AND party_nm_txt = SUBSTR($4, 1, 4000) AND party_query_type = $5 AND action_type_txt IS NULL AND gene_form_type_txt IS NULL AND action_degree_type_txt IS NULL AND go_txt IS NULL AND go_query_type IS NULL AND disease_txt IS NULL AND disease_query_type IS NULL AND from_yr IS NULL AND through_yr IS NULL AND title_abstract_txt IS NULL AND review_status IS NULL;

Date: 2025-07-31 00:40:44

x Hide

UPDATE pubc.log_query SET query_tm = CURRENT_TIMESTAMP, submission_qty = submission_qty + 1 WHERE type_cd = $1 AND session_id = $2 AND server_nm = $3 AND remote_addr = SUBSTR($4, 1, 128) AND results_qty = $5 AND basic_query_txt = SUBSTR($6, 1, 4000) AND batch_input_type_txt = $7 AND results_format_txt = NULLIF ($8, '') AND dag_txt IS NULL AND action_type_txt IS NULL AND action_degree_type_txt IS NULL;

Date: 2025-07-31 00:40:44

x Hide

SELECT /* BasicQueryLoggingDAO.getQueryId */ id FROM pubc.log_query WHERE session_id = $1 AND type_cd = $2 AND basic_query_txt = SUBSTR($3, 1, 1000) AND basic_query_type = $4;

Date: 2025-07-31 00:40:44

x Hide

Times Reported Time consuming queries #1

x Hide

[ User: pubeu - Total duration: 1d59m34s - Times executed: 49678 ]

x Hide

SELECT /* GeneBrowseTermsDAO */ t.nm, t.nm_html nmHtml, t.secondary_nm secondaryNm, t.acc_txt acc, 'name:' || t.nm accQueryStr, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term t WHERE t.object_type_id = '4' AND REGEXP_REPLACE(UPPER(SUBSTRING(t.nm, 1, 1)), '[^A-Z]', '#') = 'A' ORDER BY t.nm_sort LIMIT 100;

Date: 2025-07-31 00:02:32
Duration: 8s593ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* GeneBrowseTermsDAO */ t.nm, t.nm_html nmHtml, t.secondary_nm secondaryNm, t.acc_txt acc, 'name:' || t.nm accQueryStr, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term t WHERE t.object_type_id = '4' AND REGEXP_REPLACE(UPPER(SUBSTRING(t.nm, 1, 1)), '[^A-Z]', '#') = 'A' ORDER BY t.nm_sort LIMIT 100;

Date: 2025-07-31 00:04:39
Duration: 8s499ms
Bind query: yes

SELECT /* GeneBrowseTermsDAO */ t.nm, t.nm_html nmHtml, t.secondary_nm secondaryNm, t.acc_txt acc, 'name:' || t.nm accQueryStr, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term t WHERE t.object_type_id = '4' AND REGEXP_REPLACE(UPPER(SUBSTRING(t.nm, 1, 1)), '[^A-Z]', '#') = 'A' ORDER BY t.nm_sort LIMIT 100;

Date: 2025-07-31 00:02:31
Duration: 8s276ms
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 6h11m9s - Times executed: 87 ]

x Hide

SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1261204') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

Date: 2025-07-31 00:17:50
Duration: 1h21m41s
Bind query: yes

SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1261204') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

Date: 2025-07-31 00:19:42
Duration: 1h20m33s
Bind query: yes

SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1261204') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;

Date: 2025-07-31 00:22:47
Duration: 1h18m31s
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 50m45s - Times executed: 688 ]

[ User: qaeu - Total duration: 6s962ms - Times executed: 2 ]

x Hide

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '2101287')) ORDER BY r.sort_txt LIMIT 50;

Date: 2025-07-31 02:43:17
Duration: 10m35s
Bind query: yes

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1279346')) ORDER BY r.sort_txt LIMIT 50;

Date: 2025-07-31 02:35:47
Duration: 6m59s
Bind query: yes

SELECT /* RefsDAO */ r.id, r.abbr_authors_txt authors, r.title, r.core_citation_txt citation, r.pub_start_yr yr, r.acc_txt refAcc, r.has_diseases or r.has_ixns or r.has_exposures or r.has_phenotypes iscurated, r.has_exposures, COUNT(*) OVER () fullRowCount FROM reference r WHERE r.id IN ( select reference_id from term_reference where term_id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '2100774')) ORDER BY r.sort_txt LIMIT 50;

Date: 2025-07-31 02:44:15
Duration: 6m9s
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 43m58s - Times executed: 1550 ]

x Hide

SELECT /* ChemPathwaysDAO */ ;

Date: 2025-07-31 00:25:40
Duration: 30s808ms
Bind query: yes

SELECT /* RefsDAO */ ;

Date: 2025-07-31 00:15:08
Duration: 13s954ms
Bind query: yes

SELECT /* GeneBrowseTermsDAO */ ;

Date: 2025-07-31 00:09:55
Duration: 7s407ms
Bind query: yes

x Hide