Is the ring buffer not used for index blocks

Поиск
Список
Период
Сортировка
От Daniel Westermann
Тема Is the ring buffer not used for index blocks
Дата
Msg-id AM4PR0901MB1346EF6127174CA84FE2F674D2660@AM4PR0901MB1346.eurprd09.prod.outlook.com
обсуждение исходный текст
Список pgsql-general
Hi,

we just wondered: When a huge table is loaded into buffer cache it goes into the ring buffer to not pollute the cache.
Thesame 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=4166667width=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
thesame way it is currently done for tables? 

Thanks in advance
Daniel

В списке pgsql-general по дате отправления:

Предыдущее
От: Niels Jespersen
Дата:
Сообщение: SV: SV: Implementing pgaudit extension on Microsoft Windows
Следующее
От: Jan Kohnert
Дата:
Сообщение: Re: Subquery to select max(date) value