I am new in postgresql and I try to understand explain (buffers, analyse) instruction. I have a query and I execute it using explain (buffers, analyse).
The first time i execute it the performance is worse than the second time. Also, the first time i get a 'read' parameter next to 'hit' while the second time the 'read' does not exist.
Can somebody help me understand?
first time you select, pages get warm - they are loaded to cache, once they are in RAM - all next selects will be faster (RAM speed is higher).
Accordingly buffers show read, when pages are not in cache, cos postgres reads them, and no read when they are warm, so cache is hit...
Update with docs:
BUFFERS
Include information on buffer usage. Specifically, include the number of shared blocks hit, read, dirtied, and written, the number of local blocks hit, read, dirtied, and written, and the number of temp blocks read and written. A hit means that a read was avoided because the block was found already in cache when needed. Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temp blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases. The number of blocks dirtied indicates the number of previously unmodified blocks that were changed by this query; while the number of blocks written indicates the number of previously-dirtied blocks evicted from cache by this backend during query processing. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text format, only non-zero values are printed. This parameter may only be used when ANALYZE is also enabled. It defaults to FALSE.
And surprisingly not much about buffers here.
