On Sun, 15 Aug 2021 at 22:15, Michael Goldberg <mic.goldberg@gmail.com> wrote:
>
>
> On Sun, Aug 15, 2021 at 12:49 PM otar shavadze <oshavadze@gmail.com> wrote:
>>
>> How measure table total pages (block) count? would be this correct way? :
>>
>> SELECT pg_table_size('my_table'::regclass) / current_setting('block_size')::BIGINT;
>
> Did you try:
> SELECT relpages FROM pg_class WHERE relname='my_table';
It might pay to have a quick glance at the documentation here [1].
It's important to know that relpages is *not* kept up-to-date every
time the relation size increases. It's probably most commonly going to
be updated by auto-analyze after the table has grown or changed enough
for an auto-analyze to trigger.
The actual answer to the question depends on what Otar wants to
include when counting the number of blocks. pg_table_size() will count
the TOAST table too. If that's what's required then Otar's original
query looks fine. If not, the table in [2] is likely going to yield
the answer. pg_relation_size() might be what's required.
David
[1] https://www.postgresql.org/docs/current/catalog-pg-class.html
[2] https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE