On Thu, Jun 18, 2015 at 3:05 PM, Oleg Serov <serovov@gmail.com> wrote:
Hello!
I'm wondering, if there any way to measure how much disk-io were generated by a query?
For an individual query execution, you can explain it with
explain (analyze, buffers) select .....
It will report on the pages hit in the buffer cache versus the pages read. However, for pages which were hit in the OS filesystem cache, those will be reported as if they were read from disk. There is no way (that I know of) to distinguish at the statement level true disk io from OS caching. The best way may be to turn track_io_timing on, then you can see how much time it spent waiting on pages. If not much time was spent, then it must be coming from the OS cache.
If you enable pg_stat_statements extension, you can get the same data summed over all natural calls of the same query string. 'Natural' meaning executions from applications, not just queries manually decorated with 'explain (analyze,buffers)'. This too is best used in conjunction with track_io_timing.
I've been thinking about making individual statements which exceed log_min_duration_statement log their track_io_timing numbers and their rusage numbers into the server log, rather than just their wall-time durations as it does now. I'm not sure how that idea is going to work out yet, though. Anyway, it wouldn't be until version 9.6 at minimum.
Also, for temp file, see log_temp_files config parameter.
