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. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
