public inbox for [email protected]  

help / color / mirror / Atom feed

From: Matthew Tice <[email protected]>
Cc: [email protected]
Subject: Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
Date: Wed, 4 Jun 2025 12:37:34 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvpcoOPq1JF_-jvNzuYigDQ_mJsXMaOjyux0agaabO3YUA@mail.gmail.com>
References: <[email protected]>
	<CAApHDvpcoOPq1JF_-jvNzuYigDQ_mJsXMaOjyux0agaabO3YUA@mail.gmail.com>


> 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.

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox