pgBadger-
Global information
- Generated on Sun Sep 13 04:15:04 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260912
- Parsed 15,769 log entries in 2s
- Log start from 2026-09-12 00:00:01 to 2026-09-12 23:59:45
-
Overview
Global Stats
- 71 Number of unique normalized queries
- 78 Number of queries
- 1h51m20s Total query duration
- 2026-09-12 00:09:30 First query
- 2026-09-12 23:46:19 Last query
- 1 queries/s at 2026-09-12 19:39:55 Query peak
- 1h51m20s Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 1h51m20s Execute total duration
- 2 Number of events
- 2 Number of unique normalized events
- 1 Max number of times the same event was reported
- 0 Number of cancellation
- 2 Total number of automatic vacuums
- 14 Total number of automatic analyzes
- 0 Number temporary file
- 0 Max size of temporary file
- 0.00 B Average size of temporary file
- 1,912 Total number of sessions
- 49 sessions at 2026-09-12 00:06:12 Session peak
- 40d1h35m55s Total duration of sessions
- 30m10s Average duration of sessions
- 0 Average queries per session
- 3s493ms Average queries duration per session
- 30m7s Average idle time per session
- 1,912 Total number of connections
- 9 connections/s at 2026-09-12 05:40:08 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 1 queries/s Query Peak
- 2026-09-12 19:39:55 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2026-09-12 04:48:42 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-09-12 19:39:55 Date
Queries duration
Key values
- 1h51m20s Total query duration
Prepared queries ratio
Key values
- 0.00 Ratio of bind vs prepare
- 0.00 % Ratio between prepared and "usual" statements
General Activity
↑ Back to the top of the General Activity tableDay Hour Count Min duration Max duration Avg duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Sep 12 00 2 0ms 9m28s 4m47s 0ms 0ms 9m35s 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 3 0ms 12s726ms 10s115ms 8s348ms 9s272ms 12s726ms 03 0 0ms 0ms 0ms 0ms 0ms 0ms 04 3 0ms 8s124ms 6s505ms 0ms 5s777ms 8s124ms 05 6 0ms 27s516ms 9s561ms 5s628ms 6s30ms 27s516ms 06 0 0ms 0ms 0ms 0ms 0ms 0ms 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 3 0ms 6s651ms 6s578ms 0ms 6s495ms 13s241ms 09 2 0ms 8s270ms 7s397ms 0ms 0ms 8s270ms 10 0 0ms 0ms 0ms 0ms 0ms 0ms 11 0 0ms 0ms 0ms 0ms 0ms 0ms 12 0 0ms 0ms 0ms 0ms 0ms 0ms 13 0 0ms 0ms 0ms 0ms 0ms 0ms 14 0 0ms 0ms 0ms 0ms 0ms 0ms 15 0 0ms 0ms 0ms 0ms 0ms 0ms 16 0 0ms 0ms 0ms 0ms 0ms 0ms 17 0 0ms 0ms 0ms 0ms 0ms 0ms 18 31 0ms 28m5s 1m34s 1m22s 1m44s 28m52s 19 26 0ms 28m46s 1m55s 1m31s 2m30s 28m46s 20 0 0ms 0ms 0ms 0ms 0ms 0ms 21 0 0ms 0ms 0ms 0ms 0ms 0ms 22 0 0ms 0ms 0ms 0ms 0ms 0ms 23 2 0ms 5s624ms 5s538ms 0ms 5s451ms 5s624ms Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Sep 12 00 1 0 9m28s 0ms 0ms 9m28s 01 0 0 0ms 0ms 0ms 0ms 02 3 0 10s115ms 0ms 8s348ms 12s726ms 03 0 0 0ms 0ms 0ms 0ms 04 3 0 6s505ms 0ms 0ms 8s124ms 05 5 0 5s970ms 0ms 5s75ms 7s342ms 06 0 0 0ms 0ms 0ms 0ms 07 0 0 0ms 0ms 0ms 0ms 08 3 0 6s578ms 0ms 0ms 13s241ms 09 2 0 7s397ms 0ms 0ms 8s270ms 10 0 0 0ms 0ms 0ms 0ms 11 0 0 0ms 0ms 0ms 0ms 12 0 0 0ms 0ms 0ms 0ms 13 0 0 0ms 0ms 0ms 0ms 14 0 0 0ms 0ms 0ms 0ms 15 0 0 0ms 0ms 0ms 0ms 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 0 31 1m34s 1m4s 1m22s 28m52s 19 1 25 1m55s 1m4s 1m31s 28m46s 20 0 0 0ms 0ms 0ms 0ms 21 0 0 0ms 0ms 0ms 0ms 22 0 0 0ms 0ms 0ms 0ms 23 2 0 5s538ms 0ms 0ms 5s624ms Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Sep 12 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 0 0 0 0 0ms 0ms 0ms 0ms 12 0 0 0 0 0ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 0 0 0 0 0ms 0ms 0ms 0ms 17 0 0 0 0 0ms 0ms 0ms 0ms 18 0 0 0 0 0ms 0ms 0ms 0ms 19 0 0 0 0 0ms 0ms 0ms 0ms 20 0 0 0 0 0ms 0ms 0ms 0ms 21 0 0 0 0 0ms 0ms 0ms 0ms 22 0 0 0 0 0ms 0ms 0ms 0ms 23 0 0 0 0 0ms 0ms 0ms 0ms Day Hour Prepare Bind Bind/Prepare Percentage of prepare Sep 12 00 0 0 0.00 0.00% 01 0 0 0.00 0.00% 02 0 3 3.00 0.00% 03 0 0 0.00 0.00% 04 0 3 3.00 0.00% 05 0 6 6.00 0.00% 06 0 0 0.00 0.00% 07 0 0 0.00 0.00% 08 0 3 3.00 0.00% 09 0 2 2.00 0.00% 10 0 0 0.00 0.00% 11 0 0 0.00 0.00% 12 0 0 0.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 0 0.00 0.00% 17 0 0 0.00 0.00% 18 0 0 0.00 0.00% 19 0 1 1.00 0.00% 20 0 0 0.00 0.00% 21 0 0 0.00 0.00% 22 0 0 0.00 0.00% 23 0 2 2.00 0.00% Day Hour Count Average / Second Sep 12 00 96 0.03/s 01 74 0.02/s 02 73 0.02/s 03 76 0.02/s 04 77 0.02/s 05 105 0.03/s 06 77 0.02/s 07 74 0.02/s 08 79 0.02/s 09 87 0.02/s 10 89 0.02/s 11 82 0.02/s 12 75 0.02/s 13 78 0.02/s 14 76 0.02/s 15 77 0.02/s 16 75 0.02/s 17 80 0.02/s 18 81 0.02/s 19 75 0.02/s 20 77 0.02/s 21 78 0.02/s 22 75 0.02/s 23 76 0.02/s Day Hour Count Average Duration Average idle time Sep 12 00 96 25m8s 25m2s 01 74 31m26s 31m26s 02 73 31m2s 31m2s 03 76 31m32s 31m32s 04 77 30m22s 30m22s 05 105 23m31s 23m30s 06 77 30m29s 30m29s 07 74 32m8s 32m8s 08 79 30m52s 30m52s 09 87 28m43s 28m43s 10 89 26m51s 26m51s 11 82 29m38s 29m38s 12 75 31m48s 31m48s 13 78 31m42s 31m42s 14 76 32m7s 32m7s 15 77 31m26s 31m26s 16 75 31m34s 31m34s 17 80 30m45s 30m45s 18 80 30m30s 29m54s 19 76 31m52s 31m13s 20 77 30m33s 30m33s 21 78 31m36s 31m36s 22 75 31m54s 31m54s 23 76 31m10s 31m10s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2026-09-12 05:40:08 Date
Connections per database
Key values
- ctdprd51 Main Database
- 1,912 connections Total
Connections per user
Key values
- pubeu Main User
- 1,912 connections Total
-
Sessions
Simultaneous sessions
Key values
- 49 sessions Session Peak
- 2026-09-12 00:06:12 Date
Histogram of session times
Key values
- 1,794 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 1,912 sessions Total
Sessions per user
Key values
- pubeu Main User
- 1,912 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 1,912 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 5,142 buffers Checkpoint Peak
- 2026-09-12 14:38:42 Date
- 514.750 seconds Highest write time
- 0.003 seconds Sync time
Checkpoints Wal files
Key values
- 0 files Wal files usage Peak
- 2026-09-12 05:00:09 Date
Checkpoints distance
Key values
- 95.35 Mo Distance Peak
- 2026-09-12 14:38:42 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Sep 12 00 402 40.463s 0.005s 40.477s 01 144 14.612s 0.002s 14.672s 02 129 13.116s 0.002s 13.125s 03 84 8.614s 0.002s 8.623s 04 88 9.004s 0.002s 9.013s 05 110 11.202s 0.002s 11.21s 06 1,160 116.381s 0.002s 116.39s 07 2,524 252.93s 0.002s 252.987s 08 1,973 197.782s 0.002s 197.838s 09 382 38.461s 0.002s 38.516s 10 72 7.412s 0.002s 7.42s 11 624 62.61s 0.002s 62.619s 12 124 12.596s 0.002s 12.605s 13 69 7.092s 0.002s 7.1s 14 5,196 520.234s 0.002s 520.295s 15 19 2.098s 0.002s 2.107s 16 25 2.699s 0.002s 2.708s 17 32 3.397s 0.002s 3.405s 18 38 3.981s 0.002s 3.992s 19 10 1.094s 0.001s 1.1s 20 4,350 435.603s 0.002s 435.613s 21 117 11.888s 0.002s 11.897s 22 154 15.596s 0.002s 15.606s 23 608 61.061s 0.002s 61.07s Day Hour Added Removed Recycled Synced files Longest sync Average sync Sep 12 00 0 0 0 63 0.002s 0.002s 01 0 1 0 21 0.001s 0.002s 02 0 0 0 22 0.001s 0.002s 03 0 0 0 21 0.001s 0.002s 04 0 0 0 25 0.001s 0.002s 05 0 0 0 28 0.001s 0.002s 06 0 0 0 39 0.001s 0.002s 07 0 1 0 32 0.001s 0.002s 08 0 1 0 39 0.001s 0.002s 09 0 1 0 38 0.001s 0.002s 10 0 0 0 20 0.001s 0.002s 11 0 0 0 34 0.001s 0.002s 12 0 0 0 31 0.001s 0.002s 13 0 0 0 22 0.001s 0.002s 14 0 3 0 33 0.001s 0.002s 15 0 0 0 12 0.001s 0.002s 16 0 0 0 15 0.001s 0.002s 17 0 0 0 15 0.001s 0.002s 18 0 0 0 19 0.001s 0.002s 19 0 0 0 8 0.001s 0.001s 20 0 0 0 25 0.001s 0.002s 21 0 0 0 19 0.001s 0.002s 22 0 0 0 20 0.001s 0.002s 23 0 0 0 31 0.001s 0.002s Day Hour Count Avg time (sec) Sep 12 00 0 0s 01 0 0s 02 0 0s 03 0 0s 04 0 0s 05 0 0s 06 0 0s 07 0 0s 08 0 0s 09 0 0s 10 0 0s 11 0 0s 12 0 0s 13 0 0s 14 0 0s 15 0 0s 16 0 0s 17 0 0s 18 0 0s 19 0 0s 20 0 0s 21 0 0s 22 0 0s 23 0 0s Day Hour Mean distance Mean estimate Sep 12 00 1,356.50 kB 4,618.50 kB 01 146.50 kB 3,869.50 kB 02 138.00 kB 3,150.50 kB 03 35.50 kB 2,568.00 kB 04 150.00 kB 2,108.50 kB 05 179.00 kB 1,729.50 kB 06 4,217.50 kB 4,655.50 kB 07 11,361.00 kB 14,808.50 kB 08 8,259.50 kB 20,917.50 kB 09 1,009.00 kB 17,118.50 kB 10 79.00 kB 13,895.00 kB 11 1,943.00 kB 11,613.00 kB 12 258.50 kB 9,453.00 kB 13 91.50 kB 7,688.00 kB 14 24,464.50 kB 27,694.50 kB 15 49.00 kB 41,747.00 kB 16 46.00 kB 33,823.50 kB 17 34.00 kB 27,404.00 kB 18 44.00 kB 22,205.00 kB 19 34.00 kB 18,938.00 kB 20 226.50 kB 16,218.00 kB 21 45.00 kB 13,159.50 kB 22 69.50 kB 10,672.00 kB 23 1,748.50 kB 8,975.00 kB -
Temporary Files
Size of temporary files
Key values
- 0 Temp Files size Peak
- Date
Size of temporary files (5 minutes period)
NO DATASET
Number of temporary files
Key values
- 0 per second Temp Files Peak
- Date
Number of temporary files (5 minutes period)
NO DATASET
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Sep 12 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 0 0 0 07 0 0 0 08 0 0 0 09 0 0 0 10 0 0 0 11 0 0 0 12 0 0 0 13 0 0 0 14 0 0 0 15 0 0 0 16 0 0 0 17 0 0 0 18 0 0 0 19 0 0 0 20 0 0 0 21 0 0 0 22 0 0 0 23 0 0 0 -
Vacuums
Vacuums / Analyzes Distribution
Key values
- 0.11 sec Highest CPU-cost vacuum
Table pub2.term_set_enrichment
Database ctdprd51 - 2026-09-12 14:20:55 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 0.11 sec Highest CPU-cost vacuum
Table pub2.term_set_enrichment
Database ctdprd51 - 2026-09-12 14:20:55 Date
Analyzes per table
Key values
- pubc.log_query (13) Main table analyzed (database ctdprd51)
- 14 analyzes Total
Vacuums per table
Key values
- pub2.term_set_enrichment (1) Main table vacuumed on database ctdprd51
- 2 vacuums Total
Vacuum throughput per table
Key values
- pub2.term_set_enrichment (0.11) Max CPU elapsed for vacuum on database ctdprd51
- unknown (0 ms) Max I/O read time for vacuum on database ctdprd51
- unknown (0 ms) Max I/O write time for vacuum on database ctdprd51
Tuples removed per table
Key values
- pubc.log_query (16) Main table with removed tuples on database ctdprd51
- 16 tuples Total removed
Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Sep 12 00 0 2 01 0 0 02 0 1 03 0 1 04 0 0 05 0 5 06 0 0 07 0 1 08 0 0 09 0 1 10 0 0 11 1 0 12 0 1 13 0 0 14 1 0 15 0 0 16 0 0 17 0 0 18 0 0 19 0 0 20 0 1 21 0 0 22 0 0 23 0 1 - 0.11 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- unknown Main Lock Type
- 0 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query NO DATASET
Queries that waited the most
Rank Wait time Query NO DATASET
-
Queries
Queries by type
Key values
- 20 Total read queries
- 57 Total write queries
Queries by database
Key values
- unknown Main database
- 58 Requests
- 1h39m7s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 91 Requests
User Request type Count Duration postgres Total 9 9m16s copy to 9 9m16s pubc Total 1 9m28s select 1 9m28s pubeu Total 29 3m55s cte 1 27s516ms select 28 3m28s qaeu Total 2 11s473ms select 2 11s473ms unknown Total 91 1h45m48s copy to 83 1h44m46s others 1 7s175ms select 7 54s962ms Duration by user
Key values
- 1h45m48s (unknown) Main time consuming user
User Request type Count Duration postgres Total 9 9m16s copy to 9 9m16s pubc Total 1 9m28s select 1 9m28s pubeu Total 29 3m55s cte 1 27s516ms select 28 3m28s qaeu Total 2 11s473ms select 2 11s473ms unknown Total 91 1h45m48s copy to 83 1h44m46s others 1 7s175ms select 7 54s962ms Queries by host
Key values
- unknown Main host
- 132 Requests
- 2h8m41s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 76 Requests
- 1h41m31s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-09-12 01:48:43 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 45 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 28m46s COPY pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;[ Date: 2026-09-12 19:34:08 ]
2 28m5s COPY pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;[ Date: 2026-09-12 18:46:06 ]
3 9m28s /* * 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-09-12 00:09:30 - Database: ctdprd51 - User: pubc - Application: psql ]
4 7m1s COPY pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;[ Date: 2026-09-12 19:46:56 ]
5 6m56s COPY pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;[ Date: 2026-09-12 18:58:47 ]
6 1m51s 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-09-12 19:50:04 ]
7 1m45s COPY pub2.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) TO stdout;[ Date: 2026-09-12 19:37:49 ]
8 1m44s COPY pub1.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) TO stdout;[ Date: 2026-09-12 18:49:45 ]
9 1m22s COPY pub1.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) TO stdout;[ Date: 2026-09-12 18:15:34 ]
10 1m22s COPY pub2.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) TO stdout;[ Date: 2026-09-12 19:02:57 ]
11 1m5s COPY pub1.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) TO stdout;[ Date: 2026-09-12 18:16:40 ]
12 1m4s COPY pub2.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) TO stdout;[ Date: 2026-09-12 19:04:02 ]
13 56s269ms COPY pub2.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;[ Date: 2026-09-12 19:01:25 ]
14 56s27ms COPY pub1.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;[ Date: 2026-09-12 18:14:03 ]
15 52s906ms COPY pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) TO stdout;[ Date: 2026-09-12 18:51:11 ]
16 52s577ms COPY pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) TO stdout;[ Date: 2026-09-12 19:39:15 ]
17 47s519ms COPY pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) TO stdout;[ Date: 2026-09-12 19:35:00 ]
18 46s617ms COPY load.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not, create_tm) TO stdout;[ Date: 2026-09-12 18:12:20 ]
19 46s201ms COPY pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) TO stdout;[ Date: 2026-09-12 18:46:56 ]
20 44s28ms COPY pub2.gene_disease (gene_id, disease_id, reference_qty, curated_reference_qty, indirect_chem_qty, exposure_reference_qty, network_score) TO stdout;[ Date: 2026-09-12 19:05:21 ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 28m46s 1 28m46s 28m46s 28m46s copy pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 12 19 1 28m46s 28m46s -
COPY pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 19:34:08 Duration: 28m46s
2 28m5s 1 28m5s 28m5s 28m5s copy pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 12 18 1 28m5s 28m5s -
COPY pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 18:46:06 Duration: 28m5s
3 9m28s 1 9m28s 9m28s 9m28s select maint_query_logs_archive ();Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 12 00 1 9m28s 9m28s [ User: pubc - Total duration: 9m28s - Times executed: 1 ]
[ Application: psql - Total duration: 9m28s - Times executed: 1 ]
-
/* * 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-09-12 00:09:30 Duration: 9m28s Database: ctdprd51 User: pubc Application: psql
4 7m1s 1 7m1s 7m1s 7m1s copy pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 12 19 1 7m1s 7m1s -
COPY pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-09-12 19:46:56 Duration: 7m1s
5 6m56s 1 6m56s 6m56s 6m56s copy pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 12 18 1 6m56s 6m56s -
COPY pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-09-12 18:58:47 Duration: 6m56s
6 1m51s 1 1m51s 1m51s 1m51s 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;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 12 19 1 1m51s 1m51s -
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-09-12 19:50:04 Duration: 1m51s
7 1m45s 1 1m45s 1m45s 1m45s copy pub2.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 12 19 1 1m45s 1m45s -
COPY pub2.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) TO stdout;
Date: 2026-09-12 19:37:49 Duration: 1m45s
8 1m44s 1 1m44s 1m44s 1m44s copy pub1.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Sep 12 18 1 1m44s 1m44s -
COPY pub1.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) TO stdout;
Date: 2026-09-12 18:49:45 Duration: 1m44s
9 1m22s 1 1m22s 1m22s 1m22s copy pub1.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Sep 12 18 1 1m22s 1m22s -
COPY pub1.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) TO stdout;
Date: 2026-09-12 18:15:34 Duration: 1m22s
10 1m22s 1 1m22s 1m22s 1m22s copy pub2.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) to stdout;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Sep 12 19 1 1m22s 1m22s -
COPY pub2.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) TO stdout;
Date: 2026-09-12 19:02:57 Duration: 1m22s
11 1m5s 1 1m5s 1m5s 1m5s copy pub1.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Sep 12 18 1 1m5s 1m5s -
COPY pub1.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) TO stdout;
Date: 2026-09-12 18:16:40 Duration: 1m5s
12 1m4s 1 1m4s 1m4s 1m4s copy pub2.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) to stdout;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Sep 12 19 1 1m4s 1m4s -
COPY pub2.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) TO stdout;
Date: 2026-09-12 19:04:02 Duration: 1m4s
13 56s269ms 1 56s269ms 56s269ms 56s269ms copy pub2.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Sep 12 19 1 56s269ms 56s269ms -
COPY pub2.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 19:01:25 Duration: 56s269ms
14 56s27ms 1 56s27ms 56s27ms 56s27ms copy pub1.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Sep 12 18 1 56s27ms 56s27ms -
COPY pub1.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 18:14:03 Duration: 56s27ms
15 52s906ms 1 52s906ms 52s906ms 52s906ms copy pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) to stdout;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Sep 12 18 1 52s906ms 52s906ms -
COPY pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) TO stdout;
Date: 2026-09-12 18:51:11 Duration: 52s906ms
16 52s577ms 1 52s577ms 52s577ms 52s577ms copy pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) to stdout;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Sep 12 19 1 52s577ms 52s577ms -
COPY pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) TO stdout;
Date: 2026-09-12 19:39:15 Duration: 52s577ms
17 47s519ms 1 47s519ms 47s519ms 47s519ms copy pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) to stdout;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Sep 12 19 1 47s519ms 47s519ms -
COPY pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) TO stdout;
Date: 2026-09-12 19:35:00 Duration: 47s519ms
18 46s617ms 1 46s617ms 46s617ms 46s617ms copy load.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not, create_tm) to stdout;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Sep 12 18 1 46s617ms 46s617ms -
COPY load.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not, create_tm) TO stdout;
Date: 2026-09-12 18:12:20 Duration: 46s617ms
19 46s201ms 1 46s201ms 46s201ms 46s201ms copy pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) to stdout;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Sep 12 18 1 46s201ms 46s201ms -
COPY pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) TO stdout;
Date: 2026-09-12 18:46:56 Duration: 46s201ms
20 44s28ms 1 44s28ms 44s28ms 44s28ms copy pub2.gene_disease (gene_id, disease_id, reference_qty, curated_reference_qty, indirect_chem_qty, exposure_reference_qty, network_score) to stdout;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Sep 12 19 1 44s28ms 44s28ms -
COPY pub2.gene_disease (gene_id, disease_id, reference_qty, curated_reference_qty, indirect_chem_qty, exposure_reference_qty, network_score) TO stdout;
Date: 2026-09-12 19:05:21 Duration: 44s28ms
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 5 29s987ms 5s614ms 7s342ms 5s997ms select ? "Input", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( 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", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(distinct r.acc_txt, ?) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id where (d.id = ?) group by g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by d.nm_sort, g.nm, "DirectEvidence", c.nm;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 12 04 2 11s391ms 5s695ms 05 2 12s971ms 6s485ms 23 1 5s624ms 5s624ms [ User: pubeu - Total duration: 22s644ms - Times executed: 4 ]
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'd009203' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( 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", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE (d.id = 2199183) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-09-12 05:21:52 Duration: 7s342ms Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'mesh:d050197' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( 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", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE (d.id = 2199705) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-09-12 04:48:42 Duration: 5s777ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchDiseaseGeneAssnsDAO */ 'd050197' "Input", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", g.nm "GeneSymbol", g.acc_txt "GeneID", ( 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", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(DISTINCT r.acc_txt, '|') "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id WHERE (d.id = 2199705) GROUP BY g.nm, g.acc_txt, d.nm, d.id, d.acc_txt, d.acc_db_cd, d.nm_sort, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY d.nm_sort, g.nm, "DirectEvidence", c.nm;
Date: 2026-09-12 05:12:34 Duration: 5s628ms Database: ctdprd51 User: pubeu Bind query: yes
2 2 13s241ms 6s589ms 6s651ms 6s620ms select 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, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct r.acc_txt, ?)) as references, 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 inner join reference r on gcr.reference_id = r.id left outer join term taxonterm on gcr.taxon_id = taxonterm.id where exists ( select ? 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 = ?))) and gcr.chem_id = any (array ( select dp.descendant_object_id from dag_path dp inner join term t on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.taxon_id = any (array ( select 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 = ? and n.dag_id = ?)) and exists ( select ? from gene_chem_reference_axn gcra where gcr.id = gcra.gene_chem_reference_id and gcra.action_type_nm in ( select ac.nm from action_type ap, action_type ac where ac.subset_left_no between ap.subset_left_no and ap.subset_right_no and (ap.nm = ?))) 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 ?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 12 08 2 13s241ms 6s620ms [ User: pubeu - Total duration: 13s241ms - Times executed: 2 ]
-
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, ( 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 r.acc_txt, '|')) as references, 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 INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.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.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'BENZO(A)PYRENE' AND t.object_type_id = 2)) 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 = '9606' AND n.dag_id = 7)) AND exists ( SELECT 1 FROM gene_chem_reference_axn gcra WHERE gcr.id = gcra.gene_chem_reference_id AND gcra.action_type_nm IN ( SELECT ac.nm FROM action_type ap, action_type ac WHERE ac.subset_left_no BETWEEN ap.subset_left_no AND ap.subset_right_no AND (ap.nm = 'binding'))) 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: 2026-09-12 08:34:46 Duration: 6s651ms Database: ctdprd51 User: pubeu Bind query: yes
-
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, ( 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 r.acc_txt, '|')) as references, 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 INNER JOIN reference r on gcr.reference_id = r.id LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.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.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'BENZO(A)PYRENE' AND t.object_type_id = 2)) 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 = '9606' AND n.dag_id = 7)) AND exists ( SELECT 1 FROM gene_chem_reference_axn gcra WHERE gcr.id = gcra.gene_chem_reference_id AND gcra.action_type_nm IN ( SELECT ac.nm FROM action_type ap, action_type ac WHERE ac.subset_left_no BETWEEN ap.subset_left_no AND ap.subset_right_no AND (ap.nm = 'binding'))) 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: 2026-09-12 08:34:02 Duration: 6s589ms Database: ctdprd51 User: pubeu Bind query: yes
3 2 11s806ms 5s776ms 6s30ms 5s903ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 12 05 2 11s806ms 5s903ms [ User: pubeu - Total duration: 6s30ms - Times executed: 1 ]
[ User: qaeu - Total duration: 5s776ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-09-12 05:48:47 Duration: 6s30ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1404748)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2026-09-12 05:43:40 Duration: 5s776ms Database: ctdprd51 User: qaeu Bind query: yes
4 2 10s502ms 5s75ms 5s427ms 5s251ms select 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 = ?) and stm.slim_term_id = st.slim_term_id and cd.curated_reference_qty > ?) 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 = ?) and stm.slim_term_id = st.slim_term_id and cd.indirect_gene_qty > ?) inferredcount from slim_term st inner join term t on st.slim_term_id = t.id where st.slim_id = ? order by ?;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 12 05 1 5s75ms 5s75ms 19 1 5s427ms 5s427ms [ User: pubeu - Total duration: 10s502ms - Times executed: 2 ]
-
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 = '1524039') 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 = '1524039') 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: 2026-09-12 19:03:01 Duration: 5s427ms Database: ctdprd51 User: pubeu 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 = '1448193') 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 = '1448193') 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: 2026-09-12 05:16:09 Duration: 5s75ms Database: ctdprd51 User: pubeu Bind query: yes
5 1 28m46s 28m46s 28m46s 28m46s copy pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 12 19 1 28m46s 28m46s -
COPY pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 19:34:08 Duration: 28m46s
6 1 28m5s 28m5s 28m5s 28m5s copy pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 12 18 1 28m5s 28m5s -
COPY pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 18:46:06 Duration: 28m5s
7 1 9m28s 9m28s 9m28s 9m28s select maint_query_logs_archive ();Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 12 00 1 9m28s 9m28s [ User: pubc - Total duration: 9m28s - Times executed: 1 ]
[ Application: psql - Total duration: 9m28s - Times executed: 1 ]
-
/* * 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-09-12 00:09:30 Duration: 9m28s Database: ctdprd51 User: pubc Application: psql
8 1 7m1s 7m1s 7m1s 7m1s copy pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Sep 12 19 1 7m1s 7m1s -
COPY pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-09-12 19:46:56 Duration: 7m1s
9 1 6m56s 6m56s 6m56s 6m56s copy pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Sep 12 18 1 6m56s 6m56s -
COPY pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-09-12 18:58:47 Duration: 6m56s
10 1 1m51s 1m51s 1m51s 1m51s 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;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Sep 12 19 1 1m51s 1m51s -
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-09-12 19:50:04 Duration: 1m51s
11 1 1m45s 1m45s 1m45s 1m45s copy pub2.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Sep 12 19 1 1m45s 1m45s -
COPY pub2.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) TO stdout;
Date: 2026-09-12 19:37:49 Duration: 1m45s
12 1 1m44s 1m44s 1m44s 1m44s copy pub1.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) to stdout;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Sep 12 18 1 1m44s 1m44s -
COPY pub1.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) TO stdout;
Date: 2026-09-12 18:49:45 Duration: 1m44s
13 1 1m22s 1m22s 1m22s 1m22s copy pub1.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Sep 12 18 1 1m22s 1m22s -
COPY pub1.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) TO stdout;
Date: 2026-09-12 18:15:34 Duration: 1m22s
14 1 1m22s 1m22s 1m22s 1m22s copy pub2.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) to stdout;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Sep 12 19 1 1m22s 1m22s -
COPY pub2.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) TO stdout;
Date: 2026-09-12 19:02:57 Duration: 1m22s
15 1 1m5s 1m5s 1m5s 1m5s copy pub1.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) to stdout;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Sep 12 18 1 1m5s 1m5s -
COPY pub1.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) TO stdout;
Date: 2026-09-12 18:16:40 Duration: 1m5s
16 1 1m4s 1m4s 1m4s 1m4s copy pub2.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) to stdout;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Sep 12 19 1 1m4s 1m4s -
COPY pub2.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) TO stdout;
Date: 2026-09-12 19:04:02 Duration: 1m4s
17 1 56s269ms 56s269ms 56s269ms 56s269ms copy pub2.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Sep 12 19 1 56s269ms 56s269ms -
COPY pub2.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 19:01:25 Duration: 56s269ms
18 1 56s27ms 56s27ms 56s27ms 56s27ms copy pub1.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Sep 12 18 1 56s27ms 56s27ms -
COPY pub1.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 18:14:03 Duration: 56s27ms
19 1 52s906ms 52s906ms 52s906ms 52s906ms copy pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) to stdout;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Sep 12 18 1 52s906ms 52s906ms -
COPY pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) TO stdout;
Date: 2026-09-12 18:51:11 Duration: 52s906ms
20 1 52s577ms 52s577ms 52s577ms 52s577ms copy pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) to stdout;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Sep 12 19 1 52s577ms 52s577ms -
COPY pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) TO stdout;
Date: 2026-09-12 19:39:15 Duration: 52s577ms
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 28m46s 28m46s 28m46s 1 28m46s copy pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Sep 12 19 1 28m46s 28m46s -
COPY pub2.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 19:34:08 Duration: 28m46s
2 28m5s 28m5s 28m5s 1 28m5s copy pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Sep 12 18 1 28m5s 28m5s -
COPY pub1.gene_disease_reference (id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 18:46:06 Duration: 28m5s
3 9m28s 9m28s 9m28s 1 9m28s select maint_query_logs_archive ();Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Sep 12 00 1 9m28s 9m28s [ User: pubc - Total duration: 9m28s - Times executed: 1 ]
[ Application: psql - Total duration: 9m28s - Times executed: 1 ]
-
/* * 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-09-12 00:09:30 Duration: 9m28s Database: ctdprd51 User: pubc Application: psql
4 7m1s 7m1s 7m1s 1 7m1s copy pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Sep 12 19 1 7m1s 7m1s -
COPY pub2.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-09-12 19:46:56 Duration: 7m1s
5 6m56s 6m56s 6m56s 1 6m56s copy pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) to stdout;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Sep 12 18 1 6m56s 6m56s -
COPY pub1.term_enrichment_agent (term_id, enriched_term_id, agent_term_id) TO stdout;
Date: 2026-09-12 18:58:47 Duration: 6m56s
6 1m51s 1m51s 1m51s 1 1m51s 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;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Sep 12 19 1 1m51s 1m51s -
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-09-12 19:50:04 Duration: 1m51s
7 1m45s 1m45s 1m45s 1 1m45s copy pub2.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Sep 12 19 1 1m45s 1m45s -
COPY pub2.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) TO stdout;
Date: 2026-09-12 19:37:49 Duration: 1m45s
8 1m44s 1m44s 1m44s 1 1m44s copy pub1.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Sep 12 18 1 1m44s 1m44s -
COPY pub1.phenotype_term_reference (id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm) TO stdout;
Date: 2026-09-12 18:49:45 Duration: 1m44s
9 1m22s 1m22s 1m22s 1 1m22s copy pub1.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Sep 12 18 1 1m22s 1m22s -
COPY pub1.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) TO stdout;
Date: 2026-09-12 18:15:34 Duration: 1m22s
10 1m22s 1m22s 1m22s 1 1m22s copy pub2.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) to stdout;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Sep 12 19 1 1m22s 1m22s -
COPY pub2.dag_path (id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt) TO stdout;
Date: 2026-09-12 19:02:57 Duration: 1m22s
11 1m5s 1m5s 1m5s 1 1m5s copy pub1.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Sep 12 18 1 1m5s 1m5s -
COPY pub1.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) TO stdout;
Date: 2026-09-12 18:16:40 Duration: 1m5s
12 1m4s 1m4s 1m4s 1 1m4s copy pub2.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) to stdout;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Sep 12 19 1 1m4s 1m4s -
COPY pub2.dag_path_step (dag_path_id, step_no, dag_node_id, dag_edge_type_id) TO stdout;
Date: 2026-09-12 19:04:02 Duration: 1m4s
13 56s269ms 56s269ms 56s269ms 1 56s269ms copy pub2.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Sep 12 19 1 56s269ms 56s269ms -
COPY pub2.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 19:01:25 Duration: 56s269ms
14 56s27ms 56s27ms 56s27ms 1 56s27ms copy pub1.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) to stdout;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Sep 12 18 1 56s27ms 56s27ms -
COPY pub1.chem_disease_reference (id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm) TO stdout;
Date: 2026-09-12 18:14:03 Duration: 56s27ms
15 52s906ms 52s906ms 52s906ms 1 52s906ms copy pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) to stdout;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Sep 12 18 1 52s906ms 52s906ms -
COPY pub1.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) TO stdout;
Date: 2026-09-12 18:51:11 Duration: 52s906ms
16 52s577ms 52s577ms 52s577ms 1 52s577ms copy pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) to stdout;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Sep 12 19 1 52s577ms 52s577ms -
COPY pub2.term (id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts) TO stdout;
Date: 2026-09-12 19:39:15 Duration: 52s577ms
17 47s519ms 47s519ms 47s519ms 1 47s519ms copy pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) to stdout;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Sep 12 19 1 47s519ms 47s519ms -
COPY pub2.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) TO stdout;
Date: 2026-09-12 19:35:00 Duration: 47s519ms
18 46s617ms 46s617ms 46s617ms 1 46s617ms copy load.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not, create_tm) to stdout;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Sep 12 18 1 46s617ms 46s617ms -
COPY load.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not, create_tm) TO stdout;
Date: 2026-09-12 18:12:20 Duration: 46s617ms
19 46s201ms 46s201ms 46s201ms 1 46s201ms copy pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) to stdout;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Sep 12 18 1 46s201ms 46s201ms -
COPY pub1.gene_go_annot (gene_id, go_term_id, taxon_id, evidence_cd, is_not) TO stdout;
Date: 2026-09-12 18:46:56 Duration: 46s201ms
20 44s28ms 44s28ms 44s28ms 1 44s28ms copy pub2.gene_disease (gene_id, disease_id, reference_qty, curated_reference_qty, indirect_chem_qty, exposure_reference_qty, network_score) to stdout;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Sep 12 19 1 44s28ms 44s28ms -
COPY pub2.gene_disease (gene_id, disease_id, reference_qty, curated_reference_qty, indirect_chem_qty, exposure_reference_qty, network_score) TO stdout;
Date: 2026-09-12 19:05:21 Duration: 44s28ms
Time consuming prepare
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
Time consuming bind
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
-
Events
Log levels
Key values
- 7,858 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 0 FATAL entries
- 2 ERROR entries
- 0 WARNING entries
- 0 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 1 Max number of times the same event was reported
- 2 Total events found
Rank Times reported Error 1 1 ERROR: value too long for type character varying(...)
Times Reported Most Frequent Error / Event #1
Day Hour Count Sep 12 14 1 - ERROR: value too long for type character varying(256)
Statement: INSERT /* AdvancedQueryDAO.logQuery */ INTO pubc.log_query (type_cd ,query_tm ,submission_qty ,session_id ,server_nm ,node_nm ,remote_addr ,http_user_agent ,results_qty ,execution_ms ,gene_txt ,taxon_txt ,chem_txt ,acc_txt ,party_nm_txt ,gene_query_type ,taxon_query_type ,chem_query_type ,party_query_type ,action_type_txt ,pathway_txt ,pathway_query_type ,gene_form_type_txt ,action_degree_type_txt,go_txt ,go_query_type ,disease_txt ,disease_query_type ,gd_assn_type ,from_yr ,through_yr ,title_abstract_txt ,review_status ) VALUES ($1 ,CURRENT_TIMESTAMP ,1 ,$2 ,$3 ,NULLIF($4,'') ,SUBSTR($5,1,128) ,NULLIF(SUBSTR($6,1,256),'') ,$7 ,NULLIF($8,-1) ,NULLIF($9,'') ,NULLIF($10,'') ,NULLIF($11,'') ,NULLIF(SUBSTR($12,1,4000),'') ,NULLIF($13,'') ,NULLIF($14,'') ,NULLIF($15,'') ,NULLIF($16,'') ,NULLIF($17,'') ,NULLIF(SUBSTR($18,1,4000),'') ,NULLIF(SUBSTR($19,1,4000),'') ,NULLIF(SUBSTR($20,1,4000),'') ,NULLIF(SUBSTR($21,1,4000),'') ,NULLIF(SUBSTR($22,1,4000),'') ,NULLIF($23,'') ,NULLIF($24,'') ,NULLIF(SUBSTR($25,1,4000),'') ,NULLIF($26,'') ,NULLIF($27,'') ,NULLIF($28,0) ,NULLIF($29,0) ,NULLIF($30,'') ,NULLIF($31,'') ) RETURNING id
Date: 2026-09-12 14:18:46 Database: ctdprd51 Application: User: pubeu Remote:
2 1 ERROR: relation "..." does not exist
Times Reported Most Frequent Error / Event #2
Day Hour Count Sep 12 19 1 - ERROR: relation "disease_chem_gene" does not exist at character 369
Statement: SELECT /* DiseaseIxnsDAO */ dcg.chem_id chemid ,c.nm chemnm ,c.nm_html chemnmhtml ,c.acc_txt chemacc ,c.secondary_nm casRN ,dcg.gene_id geneid ,g.nm genesymbol ,g.acc_txt geneacc ,dcg.ixn_id ixnId ,i.ixn_prose_txt ixnProse ,i.ixn_prose_html ixnProseHtml ,i.actions_txt ixnActions ,dcg.reference_qty refCount ,dcg.taxon_qty taxonCount FROM disease_chem_gene dcg INNER JOIN ixn i ON dcg.ixn_id = i.id INNER JOIN term g ON dcg.gene_id = g.id INNER JOIN term c ON dcg.chem_id = c.id WHERE dcg.disease_id = $1 AND g.object_type_id = 4 AND c.object_type_id = 2 ORDER BY g.nm_sort ,c.nm_sort LIMIT 50
Date: 2026-09-12 19:09:05 Database: ctdprd51 Application: User: pubeu Remote: