vacuum FULL analyze TERM;

Date: 2026-06-30 13:12:50
Duration: 1m23s

vacuum FULL analyze TERM;

Date: 2026-06-30 13:11:38
Duration: 0ms
Database: ctdprd51
User: pub2
Application: pgAdmin 4 - CONN:3472262

x Hide

Times Reported Time consuming queries #1

x Hide

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

x Hide

[ Application: psql - Total duration: 9m18s - 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-06-30 00:09:20
Duration: 9m18s
Database: ctdprd51
User: pubc
Application: psql

x Hide

Times Reported Time consuming queries #2

x Hide

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

x Hide

[ Application: pg_dump - Total duration: 7m30s - 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-06-30 18:06:54
Duration: 1m53s
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-06-30 14:06:54
Duration: 1m52s
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-06-30 06:06:54
Duration: 1m52s
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-06-30 14:07:18
Duration: 24s224ms

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-06-30 18:07:19
Duration: 24s193ms

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-06-30 06:07:18
Duration: 24s87ms

x Hide

Times Reported Time consuming queries #4

x Hide

vacuum FULL analyze TERM;

Date: 2026-06-30 13:12:50
Duration: 1m23s

vacuum FULL analyze TERM;

Date: 2026-06-30 13:11:38
Duration: 0ms
Database: ctdprd51
User: pub2
Application: pgAdmin 4 - CONN:3472262

x Hide

Times Reported Time consuming queries #5

x Hide

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

x Hide

[ Application: pg_dump - Total duration: 1m20s - 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-06-30 14:00:22
Duration: 20s334ms
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-06-30 06:00:22
Duration: 20s60ms
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-06-30 18:00:22
Duration: 20s36ms
Database: ctdprd51
User: postgres
Application: pg_dump

x Hide

Times Reported Time consuming queries #6

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-06-30 14:07:34
Duration: 15s631ms

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-06-30 18:07:34
Duration: 15s590ms

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-06-30 06:07:34
Duration: 15s465ms

x Hide

Times Reported Time consuming queries #7

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-06-30 06:00:53
Duration: 14s932ms

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-06-30 18:00:53
Duration: 14s909ms

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-06-30 14:00:53
Duration: 14s888ms

x Hide

Times Reported Time consuming queries #8

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-06-30 14:01:08
Duration: 14s662ms

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-06-30 18:01:08
Duration: 14s654ms

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-06-30 06:01:08
Duration: 14s592ms

x Hide

Times Reported Time consuming queries #9

x Hide

[ User: pubeu - Total duration: 37s199ms - Times executed: 1 ]

x Hide

SELECT /* createSitemapFiles.pl */ 'https://ctdbase.org' || common.esc_xml (i.loc) "loc", i.changefreq "changefreq", ( SELECT TO_CHAR(cv.value_tm, 'YYYY-MM-DD') FROM cached_value cv WHERE cv.type_nm = 'schema_status' AND cv.key_nm = 'load_end_tm') "lastmod", TRIM(TO_CHAR(i.priority, '0.9')) "priority" FROM ( --Voc start pages SELECT '/voc.go?type=' || ot.cd loc, 'monthly' changefreq, 0.8 priority FROM object_type ot WHERE ot.cd IN ('chem', 'disease', 'gene', 'go', 'taxon', 'pathway', 'anatomy') UNION ALL -- Resource pages SELECT '/resources.jsp?type=' || ot.cd, 'monthly', 0.4 FROM object_type ot WHERE ot.cd IN ('chem', 'disease', 'gene', 'reference') UNION ALL -- Hard-coded pages SELECT url, changefreq, priority FROM sitemap_url UNION ALL --Disease, pathway details (separate b/c need the db cd in acc) SELECT '/detail.go?type=' || ot.cd || '&acc=' || t.acc_db_cd || '%3A' || t.acc_txt "loc", 'monthly', CASE WHEN (t.has_chems OR t.has_diseases OR t.has_genes OR t.has_go OR t.has_ixns OR t.has_phenotypes OR t.has_exposures OR t.has_pathways) THEN 0.8 ELSE 0.6 END FROM term t INNER JOIN object_type ot ON t.object_type_id = ot.id WHERE ot.cd IN ('disease', 'pathway') -- Exclude disease root node. AND t.acc_txt != ( SELECT n.acc_txt FROM dag_node n INNER JOIN dag d ON n.dag_id = d.id WHERE n.subset_left_no = 1 AND d.object_type_id = get_object_type_id ('disease')) -- Gene details UNION ALL SELECT '/detail.go?type=' || ot.cd || '&acc=' || t.acc_txt "loc", 'monthly', CASE WHEN (t.has_diseases OR t.has_chems OR t.has_ixns OR t.has_genes OR t.has_go OR t.has_phenotypes OR t.has_exposures OR t.has_pathways) THEN 0.8 ELSE 0.6 END FROM term t INNER JOIN object_type ot ON t.object_type_id = ot.id WHERE ot.cd = 'gene' -- Chem details UNION ALL SELECT '/detail.go?type=' || ot.cd || '&acc=' || t.acc_txt "loc", 'monthly', CASE WHEN (t.has_diseases OR t.has_genes OR t.has_ixns OR t.has_go OR t.has_phenotypes OR t.has_exposures OR t.has_pathways) THEN 0.8 ELSE 0.6 END FROM term t INNER JOIN object_type ot ON t.object_type_id = ot.id WHERE ot.cd = 'chem' -- Exclude root node. AND t.acc_txt != ( SELECT n.acc_txt FROM dag_node n INNER JOIN dag d ON n.dag_id = d.id WHERE n.subset_left_no = 1 AND d.object_type_id = get_object_type_id ('chem')) -- Taxon details UNION ALL SELECT '/detail.go?type=' || ot.cd || '&acc=' || t.acc_txt "loc", 'yearly', CASE WHEN (t.has_genes) THEN 0.6 ELSE 0.5 END FROM term t INNER JOIN object_type ot ON t.object_type_id = ot.id WHERE ot.cd = 'taxon' -- Exclude root node. AND t.acc_txt != ( SELECT n.acc_txt FROM dag_node n INNER JOIN dag d ON n.dag_id = d.id WHERE n.subset_left_no = 1 AND d.object_type_id = get_object_type_id ('taxon')) UNION ALL --GO details SELECT '/detail.go?type=' || ot.cd || '&acc=' || t.acc_txt "loc", 'monthly', CASE WHEN (t.has_genes OR t.has_phenotypes OR t.has_references OR t.has_exposures OR t.has_diseases) THEN 0.6 ELSE 0.5 END FROM term t INNER JOIN object_type ot ON t.object_type_id = ot.id WHERE ot.cd = 'go' -- Exclude root node. AND t.acc_txt != ( SELECT n.object_acc_txt FROM dag_node n INNER JOIN dag d ON n.dag_id = d.id WHERE d.object_type_id = get_object_type_id ('go') AND d.priority_seq = 1) UNION ALL --Anatomy details SELECT '/detail.go?type=' || ot.cd || '&acc=' || t.acc_txt "loc", 'monthly', CASE WHEN (t.has_genes OR t.has_references OR t.has_phenotypes OR t.has_exposures OR t.has_diseases) THEN 0.6 ELSE 0.5 END FROM term t INNER JOIN object_type ot ON t.object_type_id = ot.id WHERE ot.cd = 'anatomy' -- Exclude root node. AND t.acc_txt != ( SELECT n.object_acc_txt FROM dag_node n INNER JOIN dag d ON n.dag_id = d.id WHERE d.object_type_id = get_object_type_id ('go') AND d.priority_seq = 1) UNION ALL -- Reference details SELECT '/detail.go?type=reference&acc=' || r.acc_txt, 'monthly', CASE WHEN (r.has_diseases OR r.has_ixns OR r.has_phenotypes OR r.has_exposures OR r.has_gene_gene_ixns) THEN 0.7 ELSE 0.5 END FROM reference r) i ORDER BY 1;

Date: 2026-06-30 10:31:57
Duration: 37s199ms
Database: ctdprd51
User: pubeu