Re: BUG #17710: Slow queries (100% CPU) after auto-vacuum

On Tue, 13 Dec 2022 at 04:04, Jean-Francois Levesque
<jf(dot)levesque(at)gmail(dot)com> wrote:
> ------- v11 - normal speed -------
>
> -> Bitmap Index Scan on shared_models_sessionmonitoring_monitoring_id_e9953e3e (cost=0.00..4.36 rows=11 width=0) (never executed)

> ------- v11 - slow query -------
>
> -> Index Scan using shared_models_sessionmonitoring_monitoring_id_e9953e3e on shared_models_sessionmonitoring v1 (cost=0.25..8.26 rows=1 width=14) (actual time=0.004..0.116 rows=488
> -> Index Scan using shared_models_session_pkey on shared_models_session v0 (cost=0.25..8.26 rows=1 width=10) (actual time=0.002..0.354 rows=244 loops=238144)

It looks like the problem might be due to auto-vacuum only finding 1
live tuple in the table and setting pg_class.retuples to 1.0. Looks
like by the time the query runs that there's 488 rows in that table,
not 1. That might be tricky to work around. The planner seems to
prefer to use a non-parameterized nested loop thinking that only 1 row
will exist. That choice turns out not to be a good one as 488 rows
are found and the subquery is executed 488 times.

It would be good to see the SQL that produces this. What's of most
interest is you seem to have a FOR UPDATE in the subquery. It's
possible you could just rewrite the query using the UPDATE FROM syntax
and avoid this entire problem. If you're doing something like FOR
UPDATE (SKIP LOCKED | NOWAIT), then that's not going to be possible.
It's hard to speculate without seeing the SQL.

David