public inbox for [email protected]
help / color / mirror / Atom feedRe: Sudden increase in n_dead_tup with no corresponding insert/update/delete
3+ messages / 3 participants
[nested] [flat]
* Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
@ 2025-06-04 00:23 David Rowley <[email protected]>
2025-06-04 18:37 ` Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete Matthew Tice <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2025-06-04 00:23 UTC (permalink / raw)
To: Matthew Tice <[email protected]>; +Cc: pgsql-general
On Wed, 4 Jun 2025 at 07:22, Matthew Tice <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
2025-06-04 00:23 Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete David Rowley <[email protected]>
@ 2025-06-04 18:37 ` Matthew Tice <[email protected]>
2025-06-04 23:20 ` Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete Ron Johnson <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Matthew Tice @ 2025-06-04 18:37 UTC (permalink / raw)
To: ; +Cc: pgsql-general
> On Jun 3, 2025, at 6:23 PM, David Rowley <[email protected]> wrote:
>
> On Wed, 4 Jun 2025 at 07:22, Matthew Tice <[email protected]> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
2025-06-04 00:23 Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete David Rowley <[email protected]>
2025-06-04 18:37 ` Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete Matthew Tice <[email protected]>
@ 2025-06-04 23:20 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2025-06-04 23:20 UTC (permalink / raw)
To: Matthew Tice <[email protected]>; +Cc: pgsql-general
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 <[email protected]> wrote:
>
> > On Jun 3, 2025, at 6:23 PM, David Rowley <[email protected]> wrote:
> >
> > On Wed, 4 Jun 2025 at 07:22, Matthew Tice <[email protected]> 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!
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-06-04 23:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-04 00:23 Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete David Rowley <[email protected]>
2025-06-04 18:37 ` Matthew Tice <[email protected]>
2025-06-04 23:20 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox