Content-Type: multipart/alternative;
 boundary=Apple-Mail-626EB01A-09D1-4558-93F5-D9C779026737
Content-Transfer-Encoding: 7bit
From: Teja Jakkidi <teja.jakkidi05@gmail.com>
Mime-Version: 1.0 (1.0)
Date: Fri, 10 Jun 2022 13:23:26 -0700
Subject: N_live_tup value is less than actual row count in a table
Message-Id: <D93AF3EA-7668-4FFD-87F4-211982B76C17@gmail.com>
To: pgsql-admin@lists.postgresql.org
Archived-At: 
 <https://www.postgresql.org/message-id/D93AF3EA-7668-4FFD-87F4-211982B76C17%40gmail.com>
Precedence: bulk


--Apple-Mail-626EB01A-09D1-4558-93F5-D9C779026737
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable

Hello PGSQL Admin group,

I am noticing this wierd  behavior and not sure if this is how Postgres actu=
ally works.

I have a test table with 100000 rows and I deleted 20000 rows. So the curren=
t 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.=20
I am having hard time understanding why is this behavior and if there is som=
ething internally that is happening which I am missing. Please help me under=
stand.

Thanks,
Teja.=

--Apple-Mail-626EB01A-09D1-4558-93F5-D9C779026737
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Hello PGSQL Admin group,<div><br></div><div=
>I am noticing this wierd &nbsp;behavior and not sure if this is how Postgre=
s actually works.</div><div><br></div><div>I have a test table with 100000 r=
ows and I deleted 20000 rows. So the current rows are 80000 and dead tuples s=
hould be 20000. After autovacuum completed, the dead tuples were gone.</div>=
<div>When querying the pg_stat_all_tables, it returned&nbsp;<span style=3D"c=
aret-color: rgb(0, 0, 0); color: rgb(0, 0, 0);">n_live_tup</span>&nbsp;value=
 as 63945. Why is it less than actual row count 80000?</div><div><br></div><=
div>I read some articles and it is suggested to analyze manually to bring th=
at&nbsp;<span style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0);">n_li=
ve_tup&nbsp;</span>number to actual row count. And it did work. After manual=
 analyze, the&nbsp;<span style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0=
, 0);">n_live_tup is showing as 80000.&nbsp;</span></div><div><font color=3D=
"#000000"><span style=3D"caret-color: rgb(0, 0, 0);">I am having hard time u=
nderstanding why is this behavior and if there is something internally that i=
s happening which I am missing. Please help me understand.<br></span></font>=
<br><div dir=3D"ltr">Thanks,<div>Teja.</div></div></div></body></html>=

--Apple-Mail-626EB01A-09D1-4558-93F5-D9C779026737--