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

[Wei Sun]

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 <and...@anarazel.de&gt;
发件时间：2026年9月22日 02:58
收件人：Wei Sun <936739...@qq.com&gt;, Osama Abdul Qader 
<osamaabdulqader...@gmail.com&gt;
抄送：pgsql-hackers <pgsql-hack...@postgresql.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