Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation

Hi,

On 2026-09-15 16:05:46 +0800, Wei Sun wrote:
> I encountered a serious performance regression when running concurrent
> UPDATE statements targeting the same set of rows on PostgreSQL 18.1.
> The second update session runs extremely slow due to excessive
> EvalPlanQual (EPQ) re‑evaluation logic.
> [...]

> ## Test setup
> Create test table and populate 1000000 rows of mock bond trading data,
> no user‑defined indexes (only identity primary key on `id`).
> Then create a copy table `bond_deal_detail_sw` for concurrent update tests.
> This issue occurs when read committed isolation level.
> Session 1:
> EXPLAIN ANALYZE UPDATE bond_deal_detail_sw
> SET deal_price = 26915
> WHERE deal_no IN (SELECT deal_no FROM bond_deal_detail ORDER BY deal_no LIMIT 100000);

This is obviously an intentionally bad query. Before we add even one more iota
of additional complexity to EPQ, we need a *lot* more convincing use cases
than this. What is the real scenario in which you are updating huge numbers
of rows that also have been updated in another transaction, with a subquery
not implemented as a join?

> since the target update value is a constant and does not reference any column of the updated table,
> logically there is no need to recompute the target new value via EPQ for
> these rows.

I don't think that's correct in this case. The other session could very well
have updated deal_no to not match
deal_no IN (SELECT deal_no FROM bond_deal_detail ORDER BY deal_no LIMIT 100000)
anymore.

Greetings,

Andres Freund