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 <[email protected]>
发件时间：2026年9月25日 11:05
收件人：Wei Sun <[email protected]>
抄送：Andres Freund <[email protected]>, pgsql-hackers
<[email protected]>
主题：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
<[email protected]> 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 <[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
