Times Reported Time consuming queries #1

x Hide

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

x Hide

[ Application: psql - Total duration: 9m15s - 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-03-04 00:09:16
Duration: 9m15s
Database: ctdprd51
User: pubc
Application: psql

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: postgres - Total duration: 7m23s - Times executed: 4 ]

x Hide

[ Application: pg_dump - Total duration: 7m23s - 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: 2026-03-04 06:06:52
Duration: 1m51s
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: 2026-03-04 14:06:54
Duration: 1m50s
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: 2026-03-04 18:06:52
Duration: 1m50s
Database: ctdprd51
User: postgres
Application: pg_dump

x Hide

Times Reported Time consuming queries #3

x Hide

COPY pubc.log_query_bots (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, 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: 2026-03-04 18:07:16
Duration: 23s971ms

COPY pubc.log_query_bots (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, 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: 2026-03-04 06:07:17
Duration: 23s919ms

COPY pubc.log_query_bots (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, 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: 2026-03-04 14:07:18
Duration: 23s770ms

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: postgres - Total duration: 1m14s - Times executed: 4 ]

x Hide

[ Application: pg_dump - Total duration: 1m14s - Times executed: 4 ]

x Hide

COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 18:00:20
Duration: 18s802ms
Database: ctdprd51
User: postgres
Application: pg_dump

COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 06:00:20
Duration: 18s759ms
Database: ctdprd51
User: postgres
Application: pg_dump

COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 14:00:20
Duration: 18s707ms
Database: ctdprd51
User: postgres
Application: pg_dump

x Hide

Times Reported Time consuming queries #5

x Hide

COPY pubc.log_query_bots_original (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, 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, user_agent_pattern) TO stdout;

Date: 2026-03-04 06:07:32
Duration: 15s506ms

COPY pubc.log_query_bots_original (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, 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, user_agent_pattern) TO stdout;

Date: 2026-03-04 18:07:31
Duration: 15s445ms

COPY pubc.log_query_bots_original (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, 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, user_agent_pattern) TO stdout;

Date: 2026-03-04 14:07:33
Duration: 15s359ms

x Hide

Times Reported Time consuming queries #6

x Hide

COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 06:00:51
Duration: 14s737ms

COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 14:00:50
Duration: 14s660ms

COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 10:00:51
Duration: 14s616ms

x Hide

Times Reported Time consuming queries #7

x Hide

COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 06:01:06
Duration: 14s509ms

COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 14:01:05
Duration: 14s463ms

COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 10:01:05
Duration: 14s411ms

x Hide

Times Reported Time consuming queries #8

x Hide

[ User: pubeu - Total duration: 49s544ms - Times executed: 2 ]

x Hide

SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1255550') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;

Date: 2026-03-04 15:45:04
Duration: 25s178ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1255550') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;

Date: 2026-03-04 15:45:10
Duration: 24s365ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #9

x Hide

[ User: pubeu - Total duration: 18s962ms - Times executed: 2 ]

x Hide

SELECT /* BatchChemDiseaseAssnsDAO */ 'cardiovascular diseases' "Input", c.nm "ChemicalName", c.acc_txt "ChemicalID", c.secondary_nm "CasRN", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs", CASE WHEN cdr.via_gene_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cdr.chem_id AND a.disease_id = cdr.disease_id) ELSE NULL END "DirectEvidence", g.nm "InferenceGeneSymbol", cdr.network_score "InferenceScore" FROM chem_disease_reference cdr INNER JOIN term c ON cdr.chem_id = c.id INNER JOIN term d ON cdr.disease_id = d.id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131953) GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.id, d.nm, d.nm_sort, d.acc_txt, d.acc_db_cd, g.nm, CASE WHEN cdr.via_gene_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cdr.chem_id AND a.disease_id = cdr.disease_id) ELSE NULL END, g.nm, cdr.network_score ORDER BY d.nm_sort, c.nm_sort, "DirectEvidence", g.nm;

Date: 2026-03-04 00:31:15
Duration: 15s525ms
Bind query: yes

SELECT /* BatchChemDiseaseAssnsDAO */ 'heart diseases' "Input", c.nm "ChemicalName", c.acc_txt "ChemicalID", c.secondary_nm "CasRN", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs", CASE WHEN cdr.via_gene_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cdr.chem_id AND a.disease_id = cdr.disease_id) ELSE NULL END "DirectEvidence", g.nm "InferenceGeneSymbol", cdr.network_score "InferenceScore" FROM chem_disease_reference cdr INNER JOIN term c ON cdr.chem_id = c.id INNER JOIN term d ON cdr.disease_id = d.id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2120144) GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.id, d.nm, d.nm_sort, d.acc_txt, d.acc_db_cd, g.nm, CASE WHEN cdr.via_gene_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cdr.chem_id AND a.disease_id = cdr.disease_id) ELSE NULL END, g.nm, cdr.network_score ORDER BY d.nm_sort, c.nm_sort, "DirectEvidence", g.nm;

Date: 2026-03-04 00:30:57
Duration: 9s571ms
Database: ctdprd51
User: pubeu
Bind query: yes

SELECT /* BatchChemDiseaseAssnsDAO */ 'brain diseases' "Input", c.nm "ChemicalName", c.acc_txt "ChemicalID", c.secondary_nm "CasRN", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", ( SELECT STRING_AGG(stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm) FROM slim_term_mapping stm WHERE stm.mapped_term_id = d.id) "DiseaseCategories", STRING_AGG(cdr.source_acc_txt, '|' ORDER BY cdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs", CASE WHEN cdr.via_gene_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cdr.chem_id AND a.disease_id = cdr.disease_id) ELSE NULL END "DirectEvidence", g.nm "InferenceGeneSymbol", cdr.network_score "InferenceScore" FROM chem_disease_reference cdr INNER JOIN term c ON cdr.chem_id = c.id INNER JOIN term d ON cdr.disease_id = d.id LEFT OUTER JOIN reference r ON cdr.reference_id = r.id LEFT OUTER JOIN term g ON cdr.via_gene_id = g.id WHERE d.id in ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = 2131712) GROUP BY c.nm, c.nm_sort, c.acc_txt, c.secondary_nm, d.id, d.nm, d.nm_sort, d.acc_txt, d.acc_db_cd, g.nm, CASE WHEN cdr.via_gene_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cdr.chem_id AND a.disease_id = cdr.disease_id) ELSE NULL END, g.nm, cdr.network_score ORDER BY d.nm_sort, c.nm_sort, "DirectEvidence", g.nm;

Date: 2026-03-04 00:31:30
Duration: 9s390ms
Database: ctdprd51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #10

x Hide

COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 06:00:30
Duration: 7s358ms

COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 18:00:30
Duration: 7s293ms

COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 10:00:30
Duration: 7s284ms

x Hide

Times Reported Time consuming queries #11

x Hide

COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 06:01:14
Duration: 6s440ms

COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 14:01:13
Duration: 6s419ms

COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 10:01:14
Duration: 6s411ms

x Hide

Times Reported Time consuming queries #12

x Hide

COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 06:00:36
Duration: 6s122ms

COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 14:00:35
Duration: 6s74ms

COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;

Date: 2026-03-04 18:00:36
Duration: 6s66ms

x Hide