Hi,
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)
postgres=# \c understanding_explain
You are now connected to database "understanding_explain" as user
"postgres".
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
Planning time: 3.796 ms
Execution time: 195.557 ms
(4 rows)
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
Planning time: 0.029 ms
Execution time: 129.499 ms
(4 rows)
=============================================================================================================================
[root@dbtest3 ~]# systemctl stop postgres
[root@dbtest3 ~]# sync
[root@dbtest3 ~]# echo 3 > /proc/sys/vm/drop_caches
[root@dbtest3 ~]# systemctl start postgres
=============================================================================================================================
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
Planning time: 5.164 ms
Execution time: 181.306 ms
(4 rows)
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
Planning time: 0.025 ms
Execution time: 136.584 ms
(4 rows)
With every new query execution 32 hits adding to shared hit value.
