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

Here is the SQL query:

UPDATE "shared_models_session" SET "plan_disabled" = true WHERE
"shared_models_session"."id" IN (SELECT V0."id" FROM "shared_models_session"
V0 INNER JOIN "shared_models_sessionmonitoring" V1 ON (V0."id" =
V1."session_id") WHERE V1."monitoring_id" IN (SELECT U0."id" FROM
"shared_models_monitoring" U0 WHERE U0."owner_id" = 441) FOR UPDATE OF V0)

Le lun. 12 déc. 2022 à 20:40, David Rowley <dgrowleyml(at)gmail(dot)com> a écrit :

> 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
>