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

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 <andres(at)anarazel(dot)de&gt;
发件时间：2026年9月22日 02:58
收件人：Wei Sun <936739278(at)qq(dot)com&gt;, Osama Abdul Qader <osamaabdulqader(dot)cs(at)gmail(dot)com&gt;
抄送：pgsql-hackers <pgsql-hackers(at)postgresql(dot)org&gt;
主题：Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation

Hi,

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

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

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

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

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

Greetings,

Andres&nbsp;Freund