public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lincoln Swaine-Moore <[email protected]>
To: Andrei Lepikhov <[email protected]>
Cc: [email protected]
Subject: Re: Unfortunate Nested Loop + Missing Autovacuum
Date: Sun, 23 Feb 2025 14:49:06 -0800
Message-ID: <CABcidkKZWCE7oxHbxLQyERLQsktLoeWO1BORYe8U4tocNZN9gA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CABcidkJT9FLu4hDT5VRKD+KGu0rLkJqhyqtAA42-P6QoVfh+kA@mail.gmail.com>
	<[email protected]>

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 <[email protected]> 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
>

view thread (6+ 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], [email protected], [email protected]
  Subject: Re: Unfortunate Nested Loop + Missing Autovacuum
  In-Reply-To: <CABcidkKZWCE7oxHbxLQyERLQsktLoeWO1BORYe8U4tocNZN9gA@mail.gmail.com>

* 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