Thread: Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
From
David Rowley
Date:
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 > Tue 03 Jun 2025 07:13:12 PM UTC (every 1s) > n_dead_tup | 1290579 > autoanalyze_count | 3079 > 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 observedtables. 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. See [1]. David [1] https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/commands/analyze.c#L1318
Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
From
Matthew Tice
Date:
> On Jun 3, 2025, at 6:23 PM, David Rowley <dgrowleyml@gmail.com> wrote: > > 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 > >> Tue 03 Jun 2025 07:13:12 PM UTC (every 1s) >> n_dead_tup | 1290579 >> autoanalyze_count | 3079 > >> 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 observedtables. > > 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. > > See [1]. > > David > > [1] https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/commands/analyze.c#L1318 Thanks, David. This table is relatively large (1.6B records, 1.5TB, 38 columns). The `default_statistics_target` is set to 300 - so I thinkthat 90000 may not be enough to gather accurate statistics.
Note also that 15.6 is about 18 months old. Upgrading really does only take a few minutes, if you download the binaries before installation.
On Wed, Jun 4, 2025 at 2:37 PM Matthew Tice <mjtice@gmail.com> wrote:
> On Jun 3, 2025, at 6:23 PM, David Rowley <dgrowleyml@gmail.com> wrote:
>
> 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
>
>> Tue 03 Jun 2025 07:13:12 PM UTC (every 1s)
>> n_dead_tup | 1290579
>> autoanalyze_count | 3079
>
>> 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.
>
> 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.
>
> See [1].
>
> David
>
> [1] https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/commands/analyze.c#L1318
Thanks, David.
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.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!