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

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: Jean-Francois Levesque <jf(dot)levesque(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #17710: Slow queries (100% CPU) after auto-vacuum
Date: 2022-12-13 01:40:24
Message-ID: CAApHDvq27_3dTrue5SUnGCBF8CQc=-HvrwO8US1AOdBhnOM=JQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jean-Francois Levesque 2022-12-13 01:46:35 Re: BUG #17710: Slow queries (100% CPU) after auto-vacuum
Previous Message Tom Lane 2022-12-12 21:22:58 Re: BUG #17434: CREATE/DROP DATABASE can be executed in the same transaction with other commands