Get table total page quantity and cached page quantity

From: otar shavadze <oshavadze(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Get table total page quantity and cached page quantity
Date: 2021-08-15 11:41:31
Message-ID: CAG-jOyCd1oaf5RdDO86_txuH1oJ62uYV42SOBr1emuoHz2js0A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

created pg_buffercache extension:
CREATE EXTENSION pg_buffercache;

restarted server and updated statistic for table :
VACUUM ANALYZE public.my_table;

then just tried to cache table in buffer:
SELECT * FROM public.my_table;

and then tried to get table total page quantity and how much pages are
cached in buffer for this table:
SELECT
(SELECT relpages FROM pg_class where oid = 'public.my_table'::regclass::oid
) AS table_pages_quantity_total,
(SELECT COUNT(DISTINCT relblocknumber) FROM pg_buffercache WHERE
relfilenode = (
SELECT relfilenode FROM pg_class WHERE oid =
'public.my_table'::regclass::oid -- (SELECT rel_oid FROM my_cte)
) ) AS table_pages_quantity_in_cache;

This shows that table have only one page, while second column shows 3
unique pages in buffer cache. Seems I'm measuring those numbers
incorrectly(?) can you please help, which column is incorrect (or may be
both) ?

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ranier Vilela 2021-08-15 14:25:01 Re: CI/windows docker vs "am a service" autodetection on windows
Previous Message wangsh.fnst@fujitsu.com 2021-08-15 08:04:38 RE: make MaxBackends available in _PG_init