Comment by ants_a | Hacker News Reader

Comment by ants_a | Hacker News Reader

Parent Full thread

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

You 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

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)

Thanks 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.

Reply on news.ycombinator.com