Laurenz, Merlin, Thanks a lot for your explanations.
>Even if postgres does not cache the table, the o/s will probably > still cache it assuming it has the memory to do so. Could you please clarify, do I understand right that there are no way to determine with 'explain' whether postgres applies to hard drive or OS cache buffer? 2015-09-09 0:47 GMT+03:00 Merlin Moncure <mmonc...@gmail.com>: > On Sat, Sep 5, 2015 at 3:28 PM, Albe Laurenz <laurenz.a...@wien.gv.at> > wrote: > > Pavel Suderevsky wrote: > >> When I have been passing through "Understanding explain" manual ( > http://www.dalibo.org/_media/understanding_explain.pdf) > >> I've faced some strange situation when table with size of 65MB > completely placed in cache with shared_buffers=320MB and it doesn't with > shared_buffers <= 256MB. > >> Actually behaviour of caching in my case is the same with either 256MB > or 32MB. Im my mind shared_buffers > >> with size of 256MB should be enough for caching table with size of > 65MB, but it isn't. Could you please explain such behaviour? > >> > >> Steps: > >> > >> understanding_explain=# select pg_size_pretty(pg_relation_size('foo')); > >> pg_size_pretty > >> ---------------- > >> 65 MB > >> (1 row) > > > >> postgres=# show shared_buffers ; > >> shared_buffers > >> ---------------- > >> 320MB > >> (1 row) > >> > > > >> understanding_explain=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo; > >> QUERY PLAN > >> > ---------------------------------------------------------------------------------------------------------------- > >> Seq Scan on foo (cost=0.00..17500.60 rows=1000000 width=37) (actual > time=0.786..143.686 rows=1000000 loops=1) > >> Buffers: shared read=8334 > > > >> understanding_explain=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo; > >> QUERY PLAN > >> > --------------------------------------------------------------------------------------------------------------- > >> Seq Scan on foo (cost=0.00..17500.60 rows=1000000 width=37) (actual > time=0.009..83.546 rows=1000000 loops=1) > >> Buffers: shared hit=8334 > > > >> understanding_explain=# show shared_buffers; > >> shared_buffers > >> ---------------- > >> 256MB > >> (1 row) > >> > >> understanding_explain=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo; > >> QUERY PLAN > >> > ---------------------------------------------------------------------------------------------------------------- > >> Seq Scan on foo (cost=0.00..17500.60 rows=1000000 width=37) (actual > time=0.772..126.242 rows=1000000 loops=1) > >> Buffers: shared read=8334 > > > >> understanding_explain=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM foo; > >> QUERY PLAN > >> > --------------------------------------------------------------------------------------------------------------- > >> Seq Scan on foo (cost=0.00..17500.60 rows=1000000 width=37) (actual > time=0.029..91.686 rows=1000000 loops=1) > >> Buffers: shared hit=32 read=8302 > > > >> With every new query execution 32 hits adding to shared hit value. > > > > This must be due to this commit: > > > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d526575f893c1a4e05ebd307e80203536b213a6d > > > > See also src/backend/storage/buffer/README, chapter > > "Buffer Ring Replacement Strategy" and the functions initcan() and > GetAccessStrategy() > > in the source. > > > > Basically, if in a sequential table scan shared_buffers is less than > four times the estimated table size, > > PostgreSQL will allocate a "ring buffer" of size 256 KB to cache the > table data, so that a large sequential scan > > does not "blow out" significant parts of the shared cache. > > The rationale is that data from a sequential scan will probably not be > needed again right away, while > > other data in the cache might be hot. > > > > That's what you see in your second example: 32 buffers equals 256 KB, > and the ring buffer is chosen from > > free buffer pages, so the amount of table data cached increases by 32 > buffers every time. > > Yeah. Couple more points: > *) If your table has an index on it, you can try disabling sequential > scans temporarily (via set enable_seqscan) in order to get the > bitmapscan which IIRC does not use ring buffers. > > *) for a more robust approach to that, check out the prewarm utility: > http://www.postgresql.org/docs/9.4/static/pgprewarm.html > > *) Even if postgres does not cache the table, the o/s will probably > still cache it assuming it has the memory to do so. Shared buffers > are faster than reading from memory cached by the kernel, but that's > much faster than reading from storage unless your storage is very, > very fast. > > merlin >
