Hi,
we just wondered: When a huge table is loaded into buffer cache it goes into
the ring buffer to not pollute the cache. The same is apparently not true for
indexes as much more blocks are cached.
-- Restarted the instance
pgbench=# explain (analyze,buffers) select count(*) from pgbench_accounts;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=212771.98..212771.99 rows=1 width=8) (actual
time=848.179..848.179 rows=1 loops=1)
Buffers: shared hit=6 read=9045
-> Gather (cost=212771.77..212771.98 rows=2 width=8) (actual
time=847.981..848.172 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=6 read=9045
-> Partial Aggregate (cost=211771.77..211771.78 rows=1 width=8)
(actual time=835.475..835.475 rows=1 loops=3)
Buffers: shared hit=18 read=27325
-> Parallel Index Only Scan using pgbench_accounts_pkey on
pgbench_accounts (cost=0.43..201355.10 rows=4166667 width=0
Heap Fetches: 0
Buffers: shared hit=18 read=27325
Planning time: 0.166 ms
Execution time: 853.673 ms
(13 rows)
pgbench=# SELECT c.relname, count(*) AS buffers
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode=c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase=d.oid AND d.datname=current_database())
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 50;
relname | buffers
----------------------------------+---------
pgbench_accounts_pkey | 16160
pgbench_accounts | 6
pg_index | 4
pgbench=# show shared_buffers ;
shared_buffers
----------------
128MB
(1 row)
pgbench=# select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
(Red Hat 4.8.5-16), 64-bit
(1 row)
Can someone please explain this? Why do we see so many blocks of the primary
key? Shouldn't this be limited somehow in the same way it is currently done for
tables?
Thanks in advance
Daniel
