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

Hi Wei,

I traced the concurrent UPDATE case into PostgreSQL's EvalPlanQual (EPQ)
execution using GDB.

For the reproduced case:

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
);

After the concurrent update was committed, execution entered:

ExecUpdate() → EvalPlanQual() → EvalPlanQualNext() →
ExecProcNode(epqstate->recheckplanstate).

The important finding is that the EPQ executor tree is the full planned
tree:

EPQ
└── MergeJoinState
├── SortState
│ └── SeqScanState
│ └── bond_deal_detail_sw
│
└── SubqueryScanState
└── LimitState
└── SortState
└── SeqScanState
└── bond_deal_detail

This matches the original EXPLAIN plan, whose top-level node is a Merge
Semi Join.

At the actual EvalPlanQualNext() runtime state I observed:

recheckplanstate = T_MergeJoinState
lefttree = T_SortState
righttree = T_SubqueryScanState
root chgParam = NULL

For the right side:

SubqueryScanState
-> T_LimitState
-> T_SortState
-> T_SeqScanState

The LimitState and its child SortState also had chgParam = NULL at this
point.

So we now have concrete evidence that EPQ constructs and executes a recheck
plan corresponding to the original UPDATE's plan, rather than reducing the
recheck to just the target-table scan.

I have stopped the GDB investigation here rather than stepping through the
entire executor. My next step is to inspect the relevant EPQ/executor code
and see whether there is a safe optimization opportunity, while preserving
the required READ COMMITTED recheck semantics.

I wanted to share these findings before making any code changes.

Best,
Osama

On Fri, Sep 25, 2026 at 7:35 PM Osama Abdul Qader <
osamaabdulqader(dot)cs(at)gmail(dot)com> wrote:

> Hi Wei,
>
> Thanks for the clarification.
>
> I understand the distinction now. The behavior we were investigating is
> fundamentally tied to the EPQ recheck required by Read Committed, rather
> than the particular join strategy chosen by the planner. Since the same
> behavior occurs with Nested Loop after disabling Hash Join and Merge Join,
> continuing to trace the physical join selection would indeed be separate
> from the original issue.
>
> I'll stop pursuing the planner path as part of the EPQ investigation.
>
> The `ANY_SUBLINK` → `JOIN_SEMI` behavior I observed is interesting as a
> separate planner investigation, so I'll keep the findings documented
> separately rather than treating them as a solution to the original issue.
>
> Thanks again for the guidance, and no worries about the earlier
> suggestions. They helped me understand the problem and led me to
> investigate the planner behavior more closely.
>
> Best regards,
> Osama
>
>
> On Fri, 25 Sept, 2026, 2:07 pm Wei Sun, <936739278(at)qq(dot)com> wrote:
>
>> 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>
>> 发件时间：2026年9月25日 11:05
>> 收件人：Wei Sun <936739278(at)qq(dot)com>
>> 抄送：Andres Freund <andres(at)anarazel(dot)de>, pgsql-hackers <
>> pgsql-hackers(at)postgresql(dot)org>
>> 主题：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 SubLink and traced
>> it with GDB.
>>
>> For the following query:
>>
>> EXPLAIN (VERBOSE, COSTS)
>> SELECT *
>> FROM pg_class c
>> WHERE c.oid = ANY (
>> SELECT oid
>> 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() and confirmed
>> that the query reaches this function through:
>>
>> standard_planner
>> -> subquery_planner
>> -> pull_up_sublinks
>> -> pull_up_sublinks_jointree_recurse
>> -> pull_up_sublinks_qual_recurse
>> -> convert_ANY_sublink_to_join
>>
>> At the breakpoint:
>>
>> sublink->subLinkType = ANY_SUBLINK
>> under_not = false
>>
>> The function constructs a JoinExpr 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 that it is:
>>
>> 607 | = | oid | oid
>>
>> So the transformation at this stage is effectively:
>>
>> c.oid = ANY (SELECT oid FROM pg_namespace)
>> ↓
>> JOIN_SEMI
>> condition: c.oid = pg_namespace.oid
>>
>> 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.
>>
>> Regards,
>> Osama
>>
>> On Fri, Sep 25, 2026 at 6:53 AM Osama Abdul Qader <
>> osamaabdulqader(dot)cs(at)gmail(dot)com> wrote:
>>
>> 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 <936739278(at)qq(dot)com> 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 <andres(at)anarazel(dot)de>
>> 发件时间：2026年9月22日 02:58
>> 收件人：Wei Sun <936739278(at)qq(dot)com>, Osama Abdul Qader <
>> osamaabdulqader(dot)cs(at)gmail(dot)com>
>> 抄送：pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
>> 主题：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
>>
>>
>>
>>