Times Reported Time consuming queries #1

x Hide

[ User: pubeu - Total duration: 12m22s - 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' OR tp.nm = 'gene' OR tp.nm = 'mRNA'))) AND gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterGoWhereEquals.Gene */ ai.gene_id FROM dag_path pi INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id WHERE dbli.acc_txt = 'GO:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( 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 'RENAL INSUFFICIENCY' AND t.object_type_id = 3 AND gd.curated_reference_qty > 0))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Label */ dp.descendant_object_id FROM dag_path dp INNER JOIN term_label l ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'LEAD' AND l.object_type_id = 2))) ORDER BY g.nm_sort, g.id LIMIT 50;

Date: 2024-12-17 04:19:12
Duration: 12m22s
Database: ctdprd51
User: pubeu
Bind query: yes

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' OR tp.nm = 'gene' OR tp.nm = 'mRNA'))) AND gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterGoWhereEquals.Gene */ ai.gene_id FROM dag_path pi INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id WHERE dbli.acc_txt = 'GO:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( 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 'RENAL INSUFFICIENCY' AND t.object_type_id = 3 AND gd.curated_reference_qty > 0))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Label */ dp.descendant_object_id FROM dag_path dp INNER JOIN term_label l ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'LEAD' AND l.object_type_id = 2))) ORDER BY g.nm_sort, g.id LIMIT 50;

Date: 2024-12-17 04:19:54
Duration: 12m4s
Bind query: yes

x Hide

Times Reported Time consuming queries #2

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: 2024-12-17 00:21:34
Duration: 21m32s

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 10m47s - 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 = 'protein'))) 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 '5-HT1B') INTERSECT ( SELECT /* IQH.getMasterGoWhereEquals.Gene */ ai.gene_id FROM dag_path pi INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id WHERE dbli.acc_txt = 'GO:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( SELECT /* IQH.getMasterPathwayWhereEquals.Acc */ tp.term_id FROM term_pathway tp WHERE tp.object_type_id = 4 AND UPPER(tp.pathway_acc_txt) = 'HSA04730' AND tp.pathway_acc_db_cd = 'KEGG'))) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9605' AND n.dag_id = 7)) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (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: 2024-12-17 11:16:53
Duration: 10m47s
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 3m38s - Times executed: 148 ]

x Hide

SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;

Date: 2024-12-17 19:57:28
Duration: 1s573ms
Bind query: yes

SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;

Date: 2024-12-17 13:09:28
Duration: 1s556ms
Bind query: yes

SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;

Date: 2024-12-17 08:10:47
Duration: 1s551ms
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 59s55ms - Times executed: 15 ]

x Hide

SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1291402') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1291402') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;

Date: 2024-12-17 08:48:17
Duration: 4s309ms
Bind query: yes

SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1417966') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1417966') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;

Date: 2024-12-17 08:52:30
Duration: 4s290ms
Bind query: yes

SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1337965') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1337965') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;

Date: 2024-12-17 20:28:27
Duration: 4s179ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: postgres - Total duration: 2m52s - Times executed: 4 ]

x Hide

[ Application: pg_dump - Total duration: 2m52s - Times executed: 4 ]

x Hide

COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;

Date: 2024-12-17 06:05:44
Duration: 43s328ms
Database: ctdprd51
User: postgres
Application: pg_dump

COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;

Date: 2024-12-17 10:05:45
Duration: 43s246ms
Database: ctdprd51
User: postgres
Application: pg_dump

COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout;

Date: 2024-12-17 14:05:44
Duration: 43s79ms
Database: ctdprd51
User: postgres
Application: pg_dump

x Hide