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) ?
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 |