MIME-Version: 1.0
From: Matthew Tice <mjtice@gmail.com>
Date: Fri, 23 Aug 2024 10:14:34 -0600
Message-ID: <CA+taBv8ce8KtXLcD3sHWRuvPfb4scHU6oVSz8NwcUX=F=ECZBA@mail.gmail.com>
Subject: dead tuple difference between pgstattuple and pg_stat_user_tables
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d4d4f306205c123d"
Archived-At: <https://www.postgresql.org/message-id/CA%2BtaBv8ce8KtXLcD3sHWRuvPfb4scHU6oVSz8NwcUX%3DF%3DECZBA%40mail.gmail.com>
Precedence: bulk

--000000000000d4d4f306205c123d
Content-Type: text/plain; charset="UTF-8"

Hi All,

I'm trying to understand why there's a difference between what pgstattuple
reports and pg_stat_user_tables reports (for the number of dead tuples).

As I understand, pgstattuple and pgstattuple_approx return the exact number
of dead tuples (as noted in the documentation) and based on an older Stack
Overflow answer the value returned from pg_stat_user_tables "uses the most
recent data collected by ANALYZE".

Why would it be that even after analyzing a table the n_dead_tup value is
still vastly different than dead_tuple_count?

> SELECT * FROM (SELECT dead_tuple_count from
pgstattuple_approx('oban.oban_jobs'))a, (SELECT
n_dead_tup,last_autovacuum,last_analyze,now(),autovacuum_c
 ount FROM pg_stat_user_tables WHERE relname = 'oban_jobs' and schemaname =
'oban')b;
-[ RECORD 1 ]-------------------------
dead_tuple_count | 3736
n_dead_tup       | 1127044
last_autovacuum  | 2024-08-23 16:00:30.983141+00
last_analyze     | 2024-08-23 15:33:50.628422+00
now              | 2024-08-23 16:01:19.915893+00
autovacuum_count | 446478
SELECT 1

> vacuum (verbose,analyze) oban.oban_jobs;

vacuuming "oban.oban_jobs"
table "oban_jobs": index scan bypassed: 29341 pages from table (0.79% of
total) have 1111747 dead item identifiers
launched 2 parallel vacuum workers for index cleanup (planned: 2)
index "oban_jobs_args_index" now contains 18281 row versions in 10232 pages
0 index row versions were removed.
0 index pages were newly deleted.
56 index pages are currently deleted, of which 833 are currently reusable.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
index "oban_jobs_meta_index" now contains 18281 row versions in 9698 pages
0 index row versions were removed.
0 index pages were newly deleted.
35 index pages are currently deleted, of which 621 are currently reusable.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
table "oban_jobs": found 855 removable, 9661 nonremovable row versions in
29341 out of 3727204 pages
1330 dead row versions cannot be removed yet, oldest xmin: 1378705314
Skipped 0 pages due to buffer pins, 3696951 frozen pages.
912 skipped pages using mintxid fork.
CPU: user: 0.12 s, system: 0.08 s, elapsed: 0.22 s.
vacuuming "pg_toast.pg_toast_72454950"
table "pg_toast_72454950": found 0 removable, 0 nonremovable row versions
in 0 out of 0 pages
0 dead row versions cannot be removed yet, oldest xmin: 1378705314
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 skipped pages using mintxid fork.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
analyzing "oban.oban_jobs"
"oban_jobs": scanned 30000 of 3727204 pages, containing 75 live rows and
10501 dead rows; 75 rows in sample, 9318 estimated total rows
VACUUM

> SELECT * FROM (SELECT dead_tuple_count from
pgstattuple_approx('oban.oban_jobs'))a, (SELECT
n_dead_tup,last_autovacuum,last_analyze,now(),autovacuum_c
 ount FROM pg_stat_user_tables WHERE relname = 'oban_jobs' and schemaname =
'oban')b;
-[ RECORD 1 ]-------------------------
dead_tuple_count | 1701
n_dead_tup       | 1306009
last_autovacuum  | 2024-08-23 16:01:31.034229+00
last_analyze     | 2024-08-23 16:01:47.85574+00
now              | 2024-08-23 16:01:55.734589+00
autovacuum_count | 446479

This is a Google Alloy DB instance running:
> select version();
-[ RECORD 1 ]-------------------------
version | PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by Debian clang
version 12.0.1, 64-bit
SELECT 1

--000000000000d4d4f306205c123d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi All,<div><br></div><div>I&#39;m trying to understand wh=
y there&#39;s a difference between what pgstattuple reports and pg_stat_use=
r_tables reports (for the number of dead tuples).<br><br>As I understand, p=
gstattuple and pgstattuple_approx return the exact number of dead tuples (a=
s noted in the documentation) and based on an older Stack Overflow answer t=
he value returned from pg_stat_user_tables &quot;uses the most recent data =
collected by ANALYZE&quot;.<br><br>Why would it be that even after analyzin=
g a table the n_dead_tup value is still vastly different than dead_tuple_co=
unt?<br><br>&gt; SELECT * FROM (SELECT dead_tuple_count from pgstattuple_ap=
prox(&#39;oban.oban_jobs&#39;))a, (SELECT n_dead_tup,last_autovacuum,last_a=
nalyze,now(),autovacuum_c<br>=C2=A0ount FROM pg_stat_user_tables WHERE reln=
ame =3D &#39;oban_jobs&#39; and schemaname =3D &#39;oban&#39;)b;<br>-[ RECO=
RD 1 ]-------------------------<br>dead_tuple_count | 3736<br>n_dead_tup =
=C2=A0 =C2=A0 =C2=A0 | 1127044<br>last_autovacuum =C2=A0| 2024-08-23 16:00:=
30.983141+00<br>last_analyze =C2=A0 =C2=A0 | 2024-08-23 15:33:50.628422+00<=
br>now =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2024-08-23 16:01:1=
9.915893+00<br>autovacuum_count | 446478<br>SELECT 1<br><br>&gt; vacuum (ve=
rbose,analyze) oban.oban_jobs;<br><br>vacuuming &quot;oban.oban_jobs&quot;<=
br>table &quot;oban_jobs&quot;: index scan bypassed: 29341 pages from table=
 (0.79% of total) have 1111747 dead item identifiers<br>launched 2 parallel=
 vacuum workers for index cleanup (planned: 2)<br>index &quot;oban_jobs_arg=
s_index&quot; now contains 18281 row versions in 10232 pages<br>0 index row=
 versions were removed.<br>0 index pages were newly deleted.<br>56 index pa=
ges are currently deleted, of which 833 are currently reusable.<br>CPU: use=
r: 0.01 s, system: 0.00 s, elapsed: 0.01 s.<br>index &quot;oban_jobs_meta_i=
ndex&quot; now contains 18281 row versions in 9698 pages<br>0 index row ver=
sions were removed.<br>0 index pages were newly deleted.<br>35 index pages =
are currently deleted, of which 621 are currently reusable.<br>CPU: user: 0=
.01 s, system: 0.00 s, elapsed: 0.01 s.<br>table &quot;oban_jobs&quot;: fou=
nd 855 removable, 9661 nonremovable row versions in 29341 out of 3727204 pa=
ges<br>1330 dead row versions cannot be removed yet, oldest xmin: 137870531=
4<br>Skipped 0 pages due to buffer pins, 3696951 frozen pages.<br>912 skipp=
ed pages using mintxid fork.<br>CPU: user: 0.12 s, system: 0.08 s, elapsed:=
 0.22 s.<br>vacuuming &quot;pg_toast.pg_toast_72454950&quot;<br>table &quot=
;pg_toast_72454950&quot;: found 0 removable, 0 nonremovable row versions in=
 0 out of 0 pages<br>0 dead row versions cannot be removed yet, oldest xmin=
: 1378705314<br>Skipped 0 pages due to buffer pins, 0 frozen pages.<br>0 sk=
ipped pages using mintxid fork.<br>CPU: user: 0.00 s, system: 0.00 s, elaps=
ed: 0.00 s.<br>analyzing &quot;oban.oban_jobs&quot;<br>&quot;oban_jobs&quot=
;: scanned 30000 of 3727204 pages, containing 75 live rows and 10501 dead r=
ows; 75 rows in sample, 9318 estimated total rows<br>VACUUM<br><br>&gt; SEL=
ECT * FROM (SELECT dead_tuple_count from pgstattuple_approx(&#39;oban.oban_=
jobs&#39;))a, (SELECT n_dead_tup,last_autovacuum,last_analyze,now(),autovac=
uum_c<br>=C2=A0ount FROM pg_stat_user_tables WHERE relname =3D &#39;oban_jo=
bs&#39; and schemaname =3D &#39;oban&#39;)b;<br>-[ RECORD 1 ]--------------=
-----------<br>dead_tuple_count | 1701<br>n_dead_tup =C2=A0 =C2=A0 =C2=A0 |=
 1306009<br>last_autovacuum =C2=A0| 2024-08-23 16:01:31.034229+00<br>last_a=
nalyze =C2=A0 =C2=A0 | 2024-08-23 16:01:47.85574+00<br>now =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2024-08-23 16:01:55.734589+00<br>autova=
cuum_count | 446479<br><br>This is a Google Alloy DB instance running:<br>&=
gt; select version();<br>-[ RECORD 1 ]-------------------------<br>version =
| PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by Debian clang version=
 12.0.1, 64-bit<br>SELECT 1<br></div></div>

--000000000000d4d4f306205c123d--