pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedN_live_tup value is less than actual row count in a table
2+ messages / 2 participants
[nested] [flat]
* N_live_tup value is less than actual row count in a table
@ 2022-06-10 20:23 Teja Jakkidi <teja.jakkidi05@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Teja Jakkidi @ 2022-06-10 20:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello PGSQL Admin group,
I am noticing this wierd behavior and not sure if this is how Postgres actually works.
I have a test table with 100000 rows and I deleted 20000 rows. So the current rows are 80000 and dead tuples should be 20000. After autovacuum completed, the dead tuples were gone.
When querying the pg_stat_all_tables, it returned n_live_tup value as 63945. Why is it less than actual row count 80000?
I read some articles and it is suggested to analyze manually to bring that n_live_tup number to actual row count. And it did work. After manual analyze, the n_live_tup is showing as 80000.
I am having hard time understanding why is this behavior and if there is something internally that is happening which I am missing. Please help me understand.
Thanks,
Teja.=
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: N_live_tup value is less than actual row count in a table
@ 2022-06-10 20:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Teja Jakkidi <teja.jakkidi05@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-06-10 20:46 UTC (permalink / raw)
To: Teja Jakkidi <teja.jakkidi05@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Teja Jakkidi <teja.jakkidi05@gmail.com> writes:
> I have a test table with 100000 rows and I deleted 20000 rows. So the current rows are 80000 and dead tuples should be 20000. After autovacuum completed, the dead tuples were gone.
> When querying the pg_stat_all_tables, it returned n_live_tup value as 63945. Why is it less than actual row count 80000?
The pg_stats counters are only approximate. In the first place, they're
updated using a simplistic model that can't 100% account for the effects
of concurrent transactions, and in the second place, it's possible for
the stats mechanism to drop update messages altogether under load.
With so few details it's impossible to speculate about just why your
counter was so far off ... but no, you should not expect exactness.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-06-10 20:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-10 20:23 N_live_tup value is less than actual row count in a table Teja Jakkidi <teja.jakkidi05@gmail.com>
2022-06-10 20:46 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox