From: Tom Lane <tgl@sss.pgh.pa.us>
To: jf.levesque@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17710: Slow queries (100% CPU) after auto-vacuum
In-reply-to: <17710-22ff6a35aac256ff@postgresql.org>
References: <17710-22ff6a35aac256ff@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 09 Dec 2022 15:46:04 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1185212.1670638389.1@sss.pgh.pa.us>
Date: Fri, 09 Dec 2022 21:13:09 -0500
Message-ID: <1185213.1670638389@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1185213.1670638389%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> [ query gets slower after auto-vacuum ]

This sounds less like an auto-vacuum problem than an auto-analyze
problem.  That is, probably a background update of table statistics
is happening and that is triggering the planner to pick a different
plan.  In an ideal world, more up-to-date stats would always result
in a better plan, but reality is not always so kind.

There is not enough info here to diagnose the problem though.
At minimum we need to see EXPLAIN ANALYZE output for the problem
query in both the fast and slow states.  We have a wiki page about
reporting slow-query problems [1] which asks for quite a bit of
info, but for starters maybe the EXPLAIN ANALYZE results would
be enough.

> I am having an issue that started with 11.0 and is still present with 11.18.
> The issue is not present with 10.23.

If you could get EXPLAIN ANALYZE results from v10 as well as v11,
that would be useful too.

			regards, tom lane

[1] https://wiki.postgresql.org/wiki/Slow_Query_Questions