ANALYZE, pg_class.xmin && pg_class.reltuples

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: ANALYZE, pg_class.xmin && pg_class.reltuples
Date: 2021-11-25 12:58:02
Message-ID: CAKoxK+7Uru98iwW=AZ=+qDJa5yzP3_mA5JS5CQX35LwAoaxw1g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,
I've a doubt about running ANALYZE and inspecting pg_class.xmin and
pg_class.reltuples.
Let's create a simple table (note, I'm the only user on this instance):

testdb=> CREATE TABLE fizz AS SELECT v FROM generate_series( 1, 100 ) v;
SELECT 100
testdb=> SELECT relpages, reltuples, xmin, age( xmin ) FROM pg_class
WHERE relname = 'fizz';
-[ RECORD 1 ]---------
relpages | 0
reltuples | 0
xmin | 2359180435
age | 1

Therefore transaction 435 created the table.
Now analyze the table:

testdb=> analyze fizz;
ANALYZE

testdb=> SELECT relpages, reltuples, xmin, age( xmin ) FROM pg_class
WHERE relname = 'fizz';
-[ RECORD 1 ]---------
relpages | 1
reltuples | 100
xmin | 2359180435
age | 2

So the pg_class record has changed its content, and in fact we are now
2 transaction away the table creation, but the pg_class.xmin is the
same.
I would have expected that pg_class.xmin was updated as per
user-tables.Now, clearly ANALYZE hit the pg_statistic table and the
xmin "update" is there, but this would make me thing
pg_class.reltuples is a generated column based on some aggregation of
the latter pg_statistic, that apparently is not.
Am I missing something?

Thanks,
Luca

Responses

Browse pgsql-general by date

  From Date Subject
Next Message sivapostgres@yahoo.com 2021-11-25 13:05:49 Re: Error message while trying to connect from PGAdmin 4
Previous Message Dilip Kumar 2021-11-25 05:55:15 Re: Max connections reached without max connections reached