MIME-Version: 1.0
References: 
 <CABcidkJT9FLu4hDT5VRKD+KGu0rLkJqhyqtAA42-P6QoVfh+kA@mail.gmail.com>
 <e35ade27-17e0-4a51-a769-490291571d2f@gmail.com>
In-Reply-To: <e35ade27-17e0-4a51-a769-490291571d2f@gmail.com>
From: Lincoln Swaine-Moore <lswainemoore@gmail.com>
Date: Sun, 23 Feb 2025 14:49:06 -0800
Message-ID: 
 <CABcidkKZWCE7oxHbxLQyERLQsktLoeWO1BORYe8U4tocNZN9gA@mail.gmail.com>
Subject: Re: Unfortunate Nested Loop + Missing Autovacuum
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000097a1e9062ed708c8"
Archived-At: 
 <https://www.postgresql.org/message-id/CABcidkKZWCE7oxHbxLQyERLQsktLoeWO1BORYe8U4tocNZN9gA%40mail.gmail.com>
Precedence: bulk

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

Thanks for the reply! I tried the analysis on our much shorter staging
table and it did change the plan. I haven=E2=80=99t tried it on the product=
ion ones
because my understanding is that the autovacuum process is gentler with
resource consumption and I didn=E2=80=99t want to gum things up in the mean=
time.
But that may be false or avoidable.

Unfortunately the insertions are not batched=E2=80=94they arrive in a fairl=
y steady
stream. Given that, and the fact that we=E2=80=99re talking about the behav=
ior of
the daemon, I=E2=80=99m not sure how to provide a reproduction (though woul=
d be
game if you had pointers). I could in theory make a job analyze these
tables on a regular cadence, though I guess that feels like I=E2=80=99d be =
stepping
on autovacuum=E2=80=99s toes.

Would be happy to read over similar reports if you could steer me toward
them.

Thanks again for your help.

Lincoln Swaine-Moore


On Sat, Feb 22, 2025 at 00:37 Andrei Lepikhov <lepihov@gmail.com> wrote:

> On 22/2/2025 00:46, Lincoln Swaine-Moore wrote:
> > So, obviously there's a statistics problem, which led me to realize tha=
t
> > actually these tables have *never* been autovacuumed/analyzed according
> > to pg_stat_user_tables.
> > I'm using a managed database which makes it a little tricky to debug,
> > but all my settings
> > (autovacuum/autovacuum_vacuum_threshold/autovacuum_analyze_threshold/
> > autovacuum_vacuum_insert_threshold) are default,
> > and I can see that other tables have been vacuumed recently.
> I know a couple of reports related to this kind of behaviour and
> different solutions to resolve it. But first, if you execute the ANALYZE
> command on these problematic tables, does it fix your issue? May you
> live with manual vacuum analysis each time after batch insertion?
> If not, may you provide a synthetic reproduction of the case?
>
> --
> regards, Andrei Lepikhov
>

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

<div dir=3D"auto">Thanks for the reply! I tried the analysis on our much sh=
orter staging table and it did change the plan. I haven=E2=80=99t tried it =
on the production ones because my understanding is that the autovacuum proc=
ess is gentler with resource consumption and I didn=E2=80=99t want to gum t=
hings up in the meantime. But that may be false or avoidable.=C2=A0</div><d=
iv dir=3D"auto"><br></div><div dir=3D"auto">Unfortunately the insertions ar=
e not batched=E2=80=94they arrive in a fairly steady stream. Given that, an=
d the fact that we=E2=80=99re talking about the behavior of the daemon, I=
=E2=80=99m not sure how to provide a reproduction (though would be game if =
you had pointers). I could in theory make a job analyze these tables on a r=
egular cadence, though I guess that feels like I=E2=80=99d be stepping on a=
utovacuum=E2=80=99s toes.</div><div dir=3D"auto"><br></div><div dir=3D"auto=
">Would be happy to read over similar reports if you could steer me toward =
them.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Thanks again for y=
our help.=C2=A0</div><div dir=3D"auto"><br clear=3D"all"><div dir=3D"auto">=
<div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_signatur=
e">Lincoln Swaine-Moore</div></div></div><div><br></div><div><br><div class=
=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr=
">On Sat, Feb 22, 2025 at 00:37 Andrei Lepikhov &lt;<a href=3D"mailto:lepih=
ov@gmail.com">lepihov@gmail.com</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;bo=
rder-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)">=
On 22/2/2025 00:46, Lincoln Swaine-Moore wrote:<br>
&gt; So, obviously there&#39;s a statistics problem, which led me to realiz=
e that <br>
&gt; actually these tables have *never* been autovacuumed/analyzed accordin=
g <br>
&gt; to pg_stat_user_tables.<br>
&gt; I&#39;m using a managed database which makes it a little tricky to deb=
ug, <br>
&gt; but all my settings<br>
&gt; (autovacuum/autovacuum_vacuum_threshold/autovacuum_analyze_threshold/ =
<br>
&gt; autovacuum_vacuum_insert_threshold) are default,<br>
&gt; and I can see that other tables have been vacuumed recently.<br>
I know a couple of reports related to this kind of behaviour and <br>
different solutions to resolve it. But first, if you execute the ANALYZE <b=
r>
command on these problematic tables, does it fix your issue? May you <br>
live with manual vacuum analysis each time after batch insertion?<br>
If not, may you provide a synthetic reproduction of the case?<br>
<br>
-- <br>
regards, Andrei Lepikhov<br>
</blockquote></div></div>

--00000000000097a1e9062ed708c8--