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

Hi again,

>One useful distinction I confirmed is that convert_ANY_sublink_to_join() itself creates the logical JOIN_SEMI; it does not directly select the physical Hash Join shown by EXPLAIN. The >physical join implementation is selected later in planning.

>My next step would be to trace the resulting JOIN_SEMI further into the join-planning path and identify where the physical Hash Join is selected.

>Sharing this as an investigation update in case there are particular planner functions you would recommend tracing next.

I think continuing to analyze this issue from the planner may deviate from our original intention,
even if parameters enable_hashjoin and enable_mergejoin are turned off, the same problem will still occur when using Nestloop.
It is necessary to determine whether a modified tuple still meets the filtering condition, 
and under this premise, reducing the cost of evaluation may be a feasible solution.

But I did not come up with an effective solution to reduce costs. Instead, 
the cost of modifying SQL was much lower, such as adding a judgment in the filtering criteria that 
the current value is not equal to the value to be updated.

At first, I thought this was a scenario of performance degradation, 
but later I realized that it was a necessary condition to ensure the isolation level of read committed.
Therefore, only for the scenario I proposed, I think it may not be a scenario worth optimizing from the kernel.
I'm sorry for providing you with impractical ideas in the early stages.

Regarding the phenomenon you discovered in the planner, 
I think it may be discussed as a separate scenario.

Best regards, 
Wei Sun

原始邮件


发件人：Osama Abdul Qader <osamaabdulqader(dot)cs(at)gmail(dot)com&gt;
发件时间：2026年9月25日 11:05
收件人：Wei Sun <936739278(at)qq(dot)com&gt;
抄送：Andres Freund <andres(at)anarazel(dot)de&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 again,

I continued investigating the planner path for an ANY&nbsp;SubLink and traced it with GDB.

For the following query:
EXPLAIN (VERBOSE, COSTS) SELECT * FROM pg_class c WHERE c.oid = ANY ( &nbsp; &nbsp;SELECT oid &nbsp; &nbsp;FROM pg_namespace );

the resulting plan is a Hash Join with:
Hash Cond: (c.oid = pg_namespace.oid)

I placed a breakpoint in convert_ANY_sublink_to_join()&nbsp;and confirmed that the query reaches this function through:
standard_planner &nbsp;-&gt; subquery_planner &nbsp;-&gt; pull_up_sublinks &nbsp;-&gt; pull_up_sublinks_jointree_recurse &nbsp;-&gt; pull_up_sublinks_qual_recurse &nbsp;-&gt; convert_ANY_sublink_to_join

At the breakpoint:
sublink-&gt;subLinkType = ANY_SUBLINK under_not = false

The function constructs a JoinExpr&nbsp;with:
jointype = JOIN_SEMI

The generated join has the pulled-up subquery as its rarg, while the comparison is represented by an OpExpr.

I also checked the operator OID from the generated OpExpr:
opno = 607

and confirmed through pg_operator&nbsp;that it is:
607 | = | oid | oid

So the transformation at this stage is effectively:
c.oid = ANY (SELECT oid FROM pg_namespace) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;↓ JOIN_SEMI &nbsp;condition: c.oid = pg_namespace.oid

One useful distinction I confirmed is that convert_ANY_sublink_to_join()&nbsp;itself creates the logical JOIN_SEMI; it does not directly select the physical Hash Join shown by EXPLAIN. The physical join implementation is selected later in planning.

My next step would be to trace the resulting JOIN_SEMI&nbsp;further into the join-planning path and identify where the physical Hash Join is selected.

Sharing this as an investigation update in case there are particular planner functions you would recommend tracing next.

Regards,
Osama

On Fri, Sep 25, 2026 at 6:53 AM Osama Abdul Qader <osamaabdulqader(dot)cs(at)gmail(dot)com&gt; wrote:
Hi Andres and Wei

Thanks for the additional&nbsp;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 <936739278(at)qq(dot)com&gt; wrote:
Hi

&gt;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
&gt;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
&gt;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
&gt;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
&gt;not&nbsp;implemented&nbsp;as&nbsp;a&nbsp;join?

The background of the issue is a business function in which developers use an&nbsp;
UPDATE statement containing complex subqueries to perform batch data updates,
&nbsp;and the performance of these subqueries is poor.&nbsp; This feature may be called concurrently,&nbsp;
causing two updates to update the same part of the data, ultimately resulting in the scenario&nbsp;
I described.This problem can indeed be solved by rewriting SQL, I'm just not sure if there's&nbsp;
a better way to implement EPQ in this scenario.

&gt;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
&gt;have&nbsp;updated&nbsp;deal_no&nbsp;to&nbsp;not&nbsp;match
&gt; 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)
&gt;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; 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