Joe: show buffers number in bytes + show all the numbers that are present in the plan
Goal
3 goals for the end-user:
- always be able to see all the numbers related to the operations with shared buffers (blocks), in the order: hit, ready, dirtied, written, even if some of them are 0;
- be able to see "local buffers hit/read/dirtied/written" and "tmp buffers read/written" (see the full list at https://gitlab.com/postgres/postgres/blob/master/src/backend/commands/explain.c#L2937) -- if those numbers are 0, no need to show them
- additionally, understand how much equivalent in bytes it is (for example, 1024 buffer hits is equivalent to 1024 * 8 KiB = 8 MiB, however, some buffers might be "hit" multiple times, it would be good to indicate that).
In the future we will extend it with:
- IO timing (
I/O Read TimeandI/O Write Time, Postgres can provide it in EXPLAIN ANALYZE); - real disk IO buffers numbers and timing -- this we will need to discuss separately.
TODO / How to implement
New form to represent the data in the footer of Joe's response:
----
Planning time: 0.719 ms
Execution time: 3.969 ms
Total Cost: 8.45
Buffers
- shared hit: 100 -- ~800 kiB; from the buffer pool;
-- some buffers might be hit multiple times
- shared read: 23000 -- ~175.48 MiB; from the OS cache, includes disk IO
- shared dirtied: 2 -- ~16 kiB
- shared written: 3 -- ~24 kiB
- temp read: 20 -- ~160 kiB
Acceptance criteria
As a user, I can quickly understand, how much data was read from the buffer pool, how much from the OS cache / disk, how much was dirtied and written, and what were "local" and "temp" buffers amounts, if any.
Edited by Nikolay Samokhvalov