Re: Unfortunate Nested Loop + Missing Autovacuum

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

Unfortunately the insertions are not batched—they arrive in a fairly steady
stream. Given that, and the fact that we’re talking about the behavior of
the daemon, I’m 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 regular cadence, though I guess that feels like I’d be stepping
on autovacuum’s 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(at)gmail(dot)com> wrote:

> On 22/2/2025 00:46, Lincoln Swaine-Moore wrote:
> > So, obviously there's a statistics problem, which led me to realize that
> > 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
>