3 ms·
Configuration parameter `track_io_timing = 'on'` will measure I/O time. And running `EXPLAIN (ANALYZE, BUFFERS)` will output per plan node buffer statistics and
by ants_a 7y ago
Configuration parameter `track_io_timing = 'on'` will measure I/O time. And running `EXPLAIN (ANALYZE, BUFFERS)` will output per plan node buffer statistics and I/O time spent. On most modern system IO timing has no measurable overhead and should be permanently enabled. Collecting buffer statistics is also relatively cheap and could be enabled for all queries. For example the following configuration will get a log entry with an explain plan and per node I/O stats for every query above a threshold:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '5s'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_timing = false
- mulander 7y agoYou don't need to log every auto explain. Just enable track_io_timing and pg_stat_statements and you get per query IO performance metrics much cheaper. Table F.21. pg_stat_statements Columns https://www.postgresql.org/docs/11/pgstatstatements.html https://www.postgresql.org/docs/11/pgstatstatements.html blk_read_time double precision Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) blk_write_time double precision Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
- jka 7y agoThanks for this - 'pg_stat_statements' definitely looks like a similar feature, in particular because it reflects the count of disk-related operations (not just I/O time). It took some brief configuration but I've been able to try it out locally and will refer to it when doing PostgreSQL query performance tuning in future.