MIME-Version: 1.0
References: <006D484F-66AC-4734-A7E5-2EFBB6CEC1FA@gmail.com>
 <CAApHDvpcoOPq1JF_-jvNzuYigDQ_mJsXMaOjyux0agaabO3YUA@mail.gmail.com> <48926C17-302B-4C37-849F-E2092CADE9E0@gmail.com>
In-Reply-To: <48926C17-302B-4C37-849F-E2092CADE9E0@gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 4 Jun 2025 19:20:01 -0400
Message-ID: <CANzqJaCy77r1kt6iDH0C868AoVpcXrqkzBLx7OMR-7TGR1Jv0g@mail.gmail.com>
Subject: Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
To: Matthew Tice <mjtice@gmail.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000277c8b0636c73d7b"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCy77r1kt6iDH0C868AoVpcXrqkzBLx7OMR-7TGR1Jv0g%40mail.gmail.com>
Precedence: bulk

--000000000000277c8b0636c73d7b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFPM Matthew Tice <mjtice@gmail.com> wrot=
e:

>
> > On Jun 3, 2025, at 6:23=E2=80=AFPM, 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).  Th=
is
> 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/comma=
nds/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.
>
>

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr">Note also that 15.6 is about 18 months old.=C2=A0 Upgradin=
g really does only take a few minutes, if you download the binaries before =
installation.</div><br><div class=3D"gmail_quote gmail_quote_container"><di=
v dir=3D"ltr" class=3D"gmail_attr">On Wed, Jun 4, 2025 at 2:37=E2=80=AFPM M=
atthew Tice &lt;<a href=3D"mailto:mjtice@gmail.com">mjtice@gmail.com</a>&gt=
; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
&gt; On Jun 3, 2025, at 6:23=E2=80=AFPM, David Rowley &lt;<a href=3D"mailto=
:dgrowleyml@gmail.com" target=3D"_blank">dgrowleyml@gmail.com</a>&gt; wrote=
:<br>
&gt; <br>
&gt; On Wed, 4 Jun 2025 at 07:22, Matthew Tice &lt;<a href=3D"mailto:mjtice=
@gmail.com" target=3D"_blank">mjtice@gmail.com</a>&gt; wrote:<br>
&gt;&gt; Tue 03 Jun 2025 07:13:11 PM UTC (every 1s)<br>
&gt;&gt; n_dead_tup=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 5038<br>
&gt;&gt; autoanalyze_count=C2=A0 =C2=A0| 3078<br>
&gt; <br>
&gt;&gt; Tue 03 Jun 2025 07:13:12 PM UTC (every 1s)<br>
&gt;&gt; n_dead_tup=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 1290579<br>
&gt;&gt; autoanalyze_count=C2=A0 =C2=A0| 3079<br>
&gt; <br>
&gt;&gt; I don&#39;t understand where this large increase is coming from wh=
en there are no corresponding inserts, updates, or deletes (at the magnitud=
e).=C2=A0 This entire process repeats itself and, as mentioned, the same th=
ing is happening on other observed tables.<br>
&gt; <br>
&gt; I imagine it&#39;s from the auto-analyze that ran. Analyze will try to=
<br>
&gt; estimate the live and dead rows, but since analyze only samples some<b=
r>
&gt; blocks, it may come up with something that&#39;s not too accurate if t=
he<br>
&gt; blocks it happened to sample don&#39;t contain similar percentages of =
dead<br>
&gt; rows than the entire table.<br>
&gt; <br>
&gt; See [1].<br>
&gt; <br>
&gt; David<br>
&gt; <br>
&gt; [1] <a href=3D"https://github.com/postgres/postgres/blob/REL_15_STABLE=
/src/backend/commands/analyze.c#L1318" rel=3D"noreferrer" target=3D"_blank"=
>https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/comman=
ds/analyze.c#L1318</a><br>
<br>
Thanks, David.=C2=A0 <br>
<br>
This table is relatively large (1.6B records, 1.5TB, 38 columns).=C2=A0 The=
 `default_statistics_target` is set to 300 - so I think that 90000 may not =
be enough to gather accurate statistics.<br>
<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div>

--000000000000277c8b0636c73d7b--