MIME-Version: 1.0
References: 
 <CABcidkJT9FLu4hDT5VRKD+KGu0rLkJqhyqtAA42-P6QoVfh+kA@mail.gmail.com>
 <e35ade27-17e0-4a51-a769-490291571d2f@gmail.com>
 <CABcidkKZWCE7oxHbxLQyERLQsktLoeWO1BORYe8U4tocNZN9gA@mail.gmail.com>
 <CAMkU=1womOrr1Z5obLdot4wQBhzWu_ZvfEiqen0jY93O1F6jPA@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1womOrr1Z5obLdot4wQBhzWu_ZvfEiqen0jY93O1F6jPA@mail.gmail.com>
From: Lincoln Swaine-Moore <lswainemoore@gmail.com>
Date: Mon, 24 Feb 2025 18:17:08 -0800
Message-ID: 
 <CABcidk+WEnWyAQW0V_NOzH790KwdKom6JoEmVD=Sg0Z_MVfJ7g@mail.gmail.com>
Subject: Re: Unfortunate Nested Loop + Missing Autovacuum
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>,
 pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006feab1062eee0e58"
Archived-At: 
 <https://www.postgresql.org/message-id/CABcidk%2BWEnWyAQW0V_NOzH790KwdKom6JoEmVD%3DSg0Z_MVfJ7g%40mail.gmail.com>
Precedence: bulk

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

Thanks Jeff for the response--I did end up just analyzing the tables
manually, as a stopgap. Resource consumption was a non-issue as you
predicted (and plan was corrected, though estimates were still slightly
awkward).

With respect to the blocking of the autovacuum/analyze: no it shouldn't be
the case that those are running frequently in our case. Ditto re: database
restarts--this is out of my control because it would be DigitalOcean's
doing, but I don't see any evidence of it. Nor anything amiss in \dt+,
unfortunately.

I'll try to figure out if I can get access to the logs to search for
cancellations. Do you happen to know what that would look like if I'm
grep-ing for it? And do you have any other guesses about possible
explanations?

Thanks again for your help.

Lincoln


On Sun, Feb 23, 2025 at 6:09=E2=80=AFPM Jeff Janes <jeff.janes@gmail.com> w=
rote:

> On Sun, Feb 23, 2025 at 5:49=E2=80=AFPM Lincoln Swaine-Moore <
> lswainemoore@gmail.com> wrote:
>
>> 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 prod=
uction 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 m=
eantime.
>> But that may be false or avoidable.
>>
>
> The default setting of vacuum_cost_delay is zero, while the default
> setting of autovacuum_vacuum_cost_delay is 2ms (or 20ms, depending on the
> software version).  So you can give a manual run the same resource
> consumption as an autorun just by changing vacuum_cost_delay in that
> session so that it has the same value as autovacuum_vacuum_cost_delay.
> Also, if you just do ANALYZE (rather than VACUUM ANALYZE) the resource
> usage should be rather modest anyway.
>
> Another difference is that autovac will cancel itself if it detects it is
> blocking something else, while a manual vac/analyze operation will not
> do that.  Normal operations (DML) don't block against vacuum anyway, only
> things like index creation or partition maintenance do that.  But if thos=
e
> types of operation are frequent, then doing a manual VACUUM or ANALYZE
> could indeed gum things up.  Also, if those operations are frequent, it
> could explain the missing autovac.  If every auto attempt gets cancelled
> before it completes, then it will never complete.  pg_stat_user_tables
> doesn't reflect cancelled vacuum or analyze so those will go missing.  (Y=
ou
> should see mentions of cancelled autovac operations in the log file thoug=
h.)
>
> Database restarts will also interrupt vacuums.  So if your database is
> shutdown and restarted regularly (for cold back-ups, or just out of some
> misguided belief that restarting occasionally is a good practise) and the
> period between restarts is shorter than how long it would take autovac to
> run, this could also explain the lack of completed autovacs.  Also, if a
> table qualifies for both auto vacuum and auto analyze, the vacuum is done
> first.  So even if auto analyze would be fast by itself, it still won't
> complete if auto vacuum is slow and never gets to finish.
>
> It is possible to override your vacuum settings on a per-table basis.  So
> another possible explanation for the missing autovacs is that those two
> tables have been specifically configured to disable autovacuum on them an=
d
> only them.  If you use psql, \dt+ (but not regular \dt) will show such
> customizations.  I'm sure other tools also have ways to detect this, but =
I
> don't know what those ways are off the top of my head.
>
> Cheers,
>
> Jeff
>


--=20
Lincoln Swaine-Moore

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

<div dir=3D"ltr">Thanks Jeff for the response--I did end up just analyzing =
the tables manually, as a stopgap. Resource consumption was a non-issue as =
you predicted (and plan was corrected, though estimates were still slightly=
 awkward).<div><br></div><div>With respect to the blocking of the autovacuu=
m/analyze: no it shouldn&#39;t be the case that those=C2=A0are running freq=
uently in our case. Ditto re: database restarts--this is out of my control =
because it would be DigitalOcean&#39;s doing, but I don&#39;t see any evide=
nce of it. Nor anything amiss in \dt+, unfortunately.</div><div><br></div><=
div>I&#39;ll try to figure out if I can get access to the logs to search fo=
r cancellations. Do you happen=C2=A0to know what that would look like if I&=
#39;m grep-ing for it? And do you have any other guesses about possible exp=
lanations?</div><div><br></div><div>Thanks again for your help.</div><div><=
br></div><div>Lincoln</div><div><br></div><div><br></div></div><br><div cla=
ss=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_at=
tr">On Sun, Feb 23, 2025 at 6:09=E2=80=AFPM Jeff Janes &lt;<a href=3D"mailt=
o:jeff.janes@gmail.com">jeff.janes@gmail.com</a>&gt; wrote:<br></div><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"lt=
r">On Sun, Feb 23, 2025 at 5:49=E2=80=AFPM Lincoln Swaine-Moore &lt;<a href=
=3D"mailto:lswainemoore@gmail.com" target=3D"_blank">lswainemoore@gmail.com=
</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex"><div dir=3D"auto">Thanks for the reply! I tried the =
analysis on our much shorter staging table and it did change the plan. I ha=
ven=E2=80=99t tried it on the production ones because my understanding is t=
hat the autovacuum process is gentler with resource consumption and I didn=
=E2=80=99t want to gum things up in the meantime. But that may be false or =
avoidable.=C2=A0</div></blockquote><div><br></div><div>The default setting =
of=C2=A0vacuum_cost_delay is zero, while the default setting of=C2=A0autova=
cuum_vacuum_cost_delay is 2ms (or 20ms, depending on the software version).=
=C2=A0 So you can give a manual run the=C2=A0same resource consumption as a=
n autorun just by changing vacuum_cost_delay in that session so that it has=
 the same value as autovacuum_vacuum_cost_delay.=C2=A0 Also, if you just do=
 ANALYZE (rather than VACUUM ANALYZE) the resource usage should be rather=
=C2=A0modest anyway.</div><div><br></div><div>Another difference is that au=
tovac will cancel itself if it detects it is blocking something=C2=A0else, =
while a manual vac/analyze operation will not do=C2=A0that.=C2=A0 Normal op=
erations (DML) don&#39;t block against vacuum anyway, only things like inde=
x creation or partition maintenance=C2=A0do that.=C2=A0 But if those types =
of operation are frequent, then doing a manual VACUUM or ANALYZE could inde=
ed gum things up.=C2=A0 Also, if those operations are frequent, it could ex=
plain the missing autovac.=C2=A0 If every auto attempt gets cancelled befor=
e it completes, then it will never complete.=C2=A0=C2=A0<span style=3D"colo=
r:rgb(0,0,0)">pg_stat_user_tables doesn&#39;t reflect cancelled vacuum or a=
nalyze so those=C2=A0will go missing.=C2=A0 (You should see mentions of can=
celled autovac operations in the log file though.)</span></div><div><span s=
tyle=3D"color:rgb(0,0,0)"><br></span></div><div><span style=3D"color:rgb(0,=
0,0)">Database restarts will also interrupt vacuums.=C2=A0 So if your datab=
ase is shutdown and restarted regularly (for cold back-ups, or just out of =
some misguided belief that restarting occasionally is a good practise) and =
the period between restarts is shorter than how long it would take autovac =
to run, this could also explain the lack of completed autovacs.=C2=A0 Also,=
 if a table qualifies for both auto vacuum and auto analyze, the vacuum is =
done first.=C2=A0 So even if auto analyze would be fast=C2=A0by itself, it =
still won&#39;t complete if auto vacuum is slow and never gets to finish.</=
span></div><div><br></div><div>It is possible to override your vacuum setti=
ngs on a per-table basis.=C2=A0 So another possible=C2=A0explanation for th=
e missing autovacs is that those two tables have been specifically configur=
ed to disable autovacuum on them and only them.=C2=A0 If you use psql, \dt+=
 (but not regular \dt) will show such customizations.=C2=A0 I&#39;m sure ot=
her tools also have ways to detect this, but I don&#39;t know what those wa=
ys are off the top of my head.</div><div><br></div><div>Cheers,</div><div><=
br></div><div>Jeff</div></div></div>
</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">Lincoln Swaine-Moore</div>

--0000000000006feab1062eee0e58--