Hi Andres and Wei Thanks for the additional context. That makes the use case much cleaner.
I understand that the original optimization of skipping the EPQ recheck for constant assignments is not valid, and I’ll consider that direction closed. I’ll continue investigating the implementation side instead: specifically, how expensive SubPlans are re-executed during repeated EPQ rechecks, whether they depend on the EPQ tuple, and whether any independent work can be safely reused while preserving the required Read Committed semantics. I’ll also keep the broader batch-update use case in mind rather than optimizing only for the current reproducer. Regards Osama Abdul Qader. On Tue, Sep 22, 2026 at 7:45 AM Wei Sun <[email protected]> wrote: > Hi > > > >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? > > The background of the issue is a business function in which developers use > an > UPDATE statement containing complex subqueries to perform batch data > updates, > and the performance of these subqueries is poor. This feature may be > called concurrently, > causing two updates to update the same part of the data, ultimately > resulting in the scenario > I described.This problem can indeed be solved by rewriting SQL, I'm just > not sure if there's > a better way to implement EPQ in this scenario. > > > >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. > > Your judgment was correct, and I later became aware of this issue and > rejected this idea in subsequent emails. > > > Regards, > Wei Sun > > 原始邮件 > ------------------------------ > 发件人：Andres Freund <[email protected]> > 发件时间：2026年9月22日 02:58 > 收件人：Wei Sun <[email protected]>, Osama Abdul Qader < > [email protected]> > 抄送：pgsql-hackers <[email protected]> > 主题：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 > > >
