From: Matthew Tice <mjtice@gmail.com>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.600.51.1.1\))
Subject: Re: Sudden increase in n_dead_tup with no corresponding
 insert/update/delete
Date: Wed, 4 Jun 2025 12:37:34 -0600
In-Reply-To: <CAApHDvpcoOPq1JF_-jvNzuYigDQ_mJsXMaOjyux0agaabO3YUA@mail.gmail.com>
Cc: pgsql-general@postgresql.org
References: <006D484F-66AC-4734-A7E5-2EFBB6CEC1FA@gmail.com>
 <CAApHDvpcoOPq1JF_-jvNzuYigDQ_mJsXMaOjyux0agaabO3YUA@mail.gmail.com>
Message-Id: <48926C17-302B-4C37-849F-E2092CADE9E0@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/48926C17-302B-4C37-849F-E2092CADE9E0%40gmail.com>
Precedence: bulk


> On Jun 3, 2025, at 6:23=E2=80=AFPM, David Rowley =
<dgrowleyml@gmail.com> wrote:
>=20
> On Wed, 4 Jun 2025 at 07:22, Matthew Tice <mjtice@gmail.com> wrote:
>> Tue 03 Jun 2025 07:13:11 PM UTC (every 1s)
>> n_dead_tup          | 5038
>> autoanalyze_count   | 3078
>=20
>> Tue 03 Jun 2025 07:13:12 PM UTC (every 1s)
>> n_dead_tup          | 1290579
>> autoanalyze_count   | 3079
>=20
>> I don't understand where this large increase is coming from when =
there are no corresponding inserts, updates, or deletes (at the =
magnitude).  This entire process repeats itself and, as mentioned, the =
same thing is happening on other observed tables.
>=20
> I imagine it's from the auto-analyze that ran. Analyze will try to
> estimate the live and dead rows, but since analyze only samples some
> blocks, it may come up with something that's not too accurate if the
> blocks it happened to sample don't contain similar percentages of dead
> rows than the entire table.
>=20
> See [1].
>=20
> David
>=20
> [1] =
https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/comman=
ds/analyze.c#L1318

Thanks, David. =20

This table is relatively large (1.6B records, 1.5TB, 38 columns).  The =
`default_statistics_target` is set to 300 - so I think that 90000 may =
not be enough to gather accurate statistics.=