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

David, Tom,

We have been able to refactor the query to reduce the number of nested
loops. With this new query, the issue is less frequent in our tests and it
is taking less time when it occurs.

However, the issue still happens from time to time after an
auto-vacuum/auto-analyze run when running inside a transaction.

Since it was working fine before and something happened in v11, do you
think something needs to be fixed?

Let me know if I can help in any way.

Thanks,

JF

On Mon, Dec 12, 2022 at 9:40 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 13 Dec 2022 at 14:46, Jean-Francois Levesque
> <jf(dot)levesque(at)gmail(dot)com> wrote:
> > 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)
>
> There may be some subtle differences around when the records are
> locked, but I wonder if you couldn't just write:
>
> UPDATE "shared_models_session" SET "plan_disabled" = true
> FROM shared_models_sessionmonitoring V1
> WHERE "shared_models_session"."id" = V1."session_id"
> AND V1."monitoring_id" IN (SELECT U0."id" FROM
> "shared_models_monitoring" U0 WHERE U0."owner_id" = 441);
>
> which I think should get around the issue of the subquery in the
> original version being executed once per matching row. I'm unsure if
> there might have been some good reason to form the query in the way it
> was formed. Getting around some deadlocking issue with another query
> seems unlikely as there is no ORDER BY clause.
>
> I'm not seeing why the UPDATE FROM syntax can't be used. You didn't
> seem to have mentioned anything about the schema design, but even if
> the join has multiple rows per shared_models_session, since you're
> just setting plan_disabled to true, then it does not really matter.
> Any subsequent updates will just not be done because the current
> command has already updated the row. The same wouldn't be true if you
> were setting plan_disabled to some value from one of the other tables.
>
> David
>