pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedSevere performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
15+ messages / 3 participants
[nested] [flat]
* Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-15 08:05 Wei Sun <936739278@qq.com>
0 siblings, 2 replies; 15+ messages in thread
From: Wei Sun @ 2026-09-15 08:05 UTC (permalink / raw)
To: pgsql-hackers
Hi hackers,
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.
```sql
DROP TABLE IF EXISTS bond_deal_detail;
CREATE TABLE bond_deal_detail (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
deal_no TEXT,
bond_code TEXT,
bond_name TEXT,
trade_date DATE,
trade_time TIME,
buy_inst TEXT,
sell_inst TEXT,
deal_amt NUMERIC(20,4),
deal_price NUMERIC(12,6),
yield_rate NUMERIC(10,6),
trade_type TEXT,
settle_date DATE,
create_at TIMESTAMP
);
INSERT INTO bond_deal_detail(
deal_no, bond_code, bond_name, trade_date, trade_time,
buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
trade_type, settle_date, create_at
)
SELECT
'DEAL' || LPAD(i::TEXT,10,'0'),
'10' || LPAD((i % 99999)::TEXT,8,'0'),
'SimBond_' || (i % 2000),
'2025-01-01'::DATE + (i % 365),
('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
'Inst_' || (i % 1500),
'Inst_' || ((i + 777) % 1500),
(random() * 500000000)::NUMERIC(20,4),
(90 + random() * 20)::NUMERIC(12,6),
(1.5 + random() * 3.5)::NUMERIC(10,6),
CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
'2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
NOW()
FROM generate_series(1,1000000) AS t(i);
-- create working table for concurrent update
CREATE TABLE bond_deal_detail_sw(LIKE bond_deal_detail);
INSERT INTO bond_deal_detail_sw SELECT * FROM bond_deal_detail;
## Concurrent reproduction steps
Open two independent sessions and run below UPDATE SQL
simultaneously against table `bond_deal_detail_sw`.
Both queries try to update the same top‑100 000 rows derived
from the source table `bond_deal_detail`.
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);
Session 2 (run concurrently with 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);
1. Session 1 executes quickly, it locks and updates those 100 000 target rows.
2. Session 2 blocks waiting for row‑level locks. After session 1 commits,
session 2 resumes execution but becomes extremely slow.
3. From execution plan and trace, the slowdown comes from massive EvalPlanQual (EPQ) re‑evaluation:
for each row already modified and committed by transaction 1,
PostgreSQL fetches the new tuple version and re‑evaluates the whole sub‑query / qual tree for EPQ.
Even though the new value of `deal_price` is a constant literal (`SET deal_price = 26915`)
and does not depend on original row values, heavy EPQ overhead still occurs for every conflicting row.
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.
But PostgreSQL still triggers full EPQ re‑evaluation for every updated tuple,
leading to huge overhead and long elapsed time for the second concurrent transaction.
Expectation / question
When the updated assignment is pure constant and does not reference
any columns from the target relation, could PostgreSQL skip the expensive EPQ re‑computation
for the new target value, even though it still needs to check row visibility and tuple versions?
Best regards,
Wei Sun
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-15 09:15 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Wei Sun <936739278@qq.com>
1 sibling, 1 reply; 15+ messages in thread
From: Osama Abdul Qader @ 2026-09-15 09:15 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: pgsql-hackers
Hi,
I'm interested in working on the bug, I'll let you know once I finish
reproducing it.
With Regards,
Osama Abdul Qader
On Tue, Sep 15, 2026 at 1:36 PM Wei Sun <936739278@qq.com> wrote:
> Hi hackers,
> 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.
>
> ```sql
> DROP TABLE IF EXISTS bond_deal_detail;
> CREATE TABLE bond_deal_detail (
> id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
> deal_no TEXT,
> bond_code TEXT,
> bond_name TEXT,
> trade_date DATE,
> trade_time TIME,
> buy_inst TEXT,
> sell_inst TEXT,
> deal_amt NUMERIC(20,4),
> deal_price NUMERIC(12,6),
> yield_rate NUMERIC(10,6),
> trade_type TEXT,
> settle_date DATE,
> create_at TIMESTAMP
> );
>
> INSERT INTO bond_deal_detail(
> deal_no, bond_code, bond_name, trade_date, trade_time,
> buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
> trade_type, settle_date, create_at
> )
> SELECT
> 'DEAL' || LPAD(i::TEXT,10,'0'),
> '10' || LPAD((i % 99999)::TEXT,8,'0'),
> 'SimBond_' || (i % 2000),
> '2025-01-01'::DATE + (i % 365),
> ('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
> 'Inst_' || (i % 1500),
> 'Inst_' || ((i + 777) % 1500),
> (random() * 500000000)::NUMERIC(20,4),
> (90 + random() * 20)::NUMERIC(12,6),
> (1.5 + random() * 3.5)::NUMERIC(10,6),
> CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
> '2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
> NOW()
> FROM generate_series(1,1000000) AS t(i);
>
> -- create working table for concurrent update
> CREATE TABLE bond_deal_detail_sw(LIKE bond_deal_detail);
> INSERT INTO bond_deal_detail_sw SELECT * FROM bond_deal_detail;
>
> ## Concurrent reproduction steps
>
> Open two independent sessions and run below UPDATE SQL
> simultaneously against table `bond_deal_detail_sw`.
> Both queries try to update the same top‑100 000 rows derived
> from the source table `bond_deal_detail`.
>
> 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);
>
> Session 2 (run concurrently with 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);
>
> 1. Session 1 executes quickly, it locks and updates those 100 000 target
> rows.
> 2. Session 2 blocks waiting for row‑level locks. After session 1 commits,
> session 2 resumes execution but becomes extremely slow.
> 3. From execution plan and trace, the slowdown comes from massive
> EvalPlanQual (EPQ) re‑evaluation:
> for each row already modified and committed by transaction 1,
> PostgreSQL fetches the new tuple version and re‑evaluates the whole
> sub‑query / qual tree for EPQ.
> Even though the new value of `deal_price` is a constant literal (`SET
> deal_price = 26915`)
> and does not depend on original row values, heavy EPQ overhead still
> occurs for every conflicting row.
>
> 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.
> But PostgreSQL still triggers full EPQ re‑evaluation for every updated
> tuple,
> leading to huge overhead and long elapsed time for the second concurrent
> transaction.
>
> Expectation / question
> When the updated assignment is pure constant and does not reference
> any columns from the target relation, could PostgreSQL skip the expensive
> EPQ re‑computation
> for the new target value, even though it still needs to check row
> visibility and tuple versions?
>
> Best regards,
> Wei Sun
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-15 10:49 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Osama Abdul Qader @ 2026-09-15 10:49 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: pgsql-hackers
Hi again,
I was able to reproduce the reported slowdown locally on PostgreSQL 20devel.
In my reproduction, the second concurrent UPDATE initially waits on a
transactionid lock. After the first transaction commits, the second UPDATE
completes but can take tens of seconds. For example, with 10,000
conflicting rows I measured 69.57 seconds. The original query shape with
the subquery took approximately 102 seconds in another run.
I also tested a simplified form using WHERE id <= N, which still exhibits a
significant slowdown. This suggests that the subquery may amplify the issue
but is not necessarily the sole cause.
I am currently instrumenting the ModifyTable UPDATE path around
table_tuple_lock() and EvalPlanQual() to determine where the
post-lock-release time is actually being spent.
I have not yet determined whether this is an EPQ issue or another
executor/locking-related performance problem. I wanted to share the
reproduction and preliminary observations before proceeding further.
On Tue, Sep 15, 2026 at 2:45 PM Osama Abdul Qader <
osamaabdulqader.cs@gmail.com> wrote:
> Hi,
>
> I'm interested in working on the bug, I'll let you know once I finish
> reproducing it.
>
> With Regards,
> Osama Abdul Qader
>
> On Tue, Sep 15, 2026 at 1:36 PM Wei Sun <936739278@qq.com> wrote:
>
>> Hi hackers,
>> 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.
>>
>> ```sql
>> DROP TABLE IF EXISTS bond_deal_detail;
>> CREATE TABLE bond_deal_detail (
>> id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
>> deal_no TEXT,
>> bond_code TEXT,
>> bond_name TEXT,
>> trade_date DATE,
>> trade_time TIME,
>> buy_inst TEXT,
>> sell_inst TEXT,
>> deal_amt NUMERIC(20,4),
>> deal_price NUMERIC(12,6),
>> yield_rate NUMERIC(10,6),
>> trade_type TEXT,
>> settle_date DATE,
>> create_at TIMESTAMP
>> );
>>
>> INSERT INTO bond_deal_detail(
>> deal_no, bond_code, bond_name, trade_date, trade_time,
>> buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
>> trade_type, settle_date, create_at
>> )
>> SELECT
>> 'DEAL' || LPAD(i::TEXT,10,'0'),
>> '10' || LPAD((i % 99999)::TEXT,8,'0'),
>> 'SimBond_' || (i % 2000),
>> '2025-01-01'::DATE + (i % 365),
>> ('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
>> 'Inst_' || (i % 1500),
>> 'Inst_' || ((i + 777) % 1500),
>> (random() * 500000000)::NUMERIC(20,4),
>> (90 + random() * 20)::NUMERIC(12,6),
>> (1.5 + random() * 3.5)::NUMERIC(10,6),
>> CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
>> '2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
>> NOW()
>> FROM generate_series(1,1000000) AS t(i);
>>
>> -- create working table for concurrent update
>> CREATE TABLE bond_deal_detail_sw(LIKE bond_deal_detail);
>> INSERT INTO bond_deal_detail_sw SELECT * FROM bond_deal_detail;
>>
>> ## Concurrent reproduction steps
>>
>> Open two independent sessions and run below UPDATE SQL
>> simultaneously against table `bond_deal_detail_sw`.
>> Both queries try to update the same top‑100 000 rows derived
>> from the source table `bond_deal_detail`.
>>
>> 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);
>>
>> Session 2 (run concurrently with 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);
>>
>> 1. Session 1 executes quickly, it locks and updates those 100 000 target
>> rows.
>> 2. Session 2 blocks waiting for row‑level locks. After session 1 commits,
>> session 2 resumes execution but becomes extremely slow.
>> 3. From execution plan and trace, the slowdown comes from massive
>> EvalPlanQual (EPQ) re‑evaluation:
>> for each row already modified and committed by transaction 1,
>> PostgreSQL fetches the new tuple version and re‑evaluates the whole
>> sub‑query / qual tree for EPQ.
>> Even though the new value of `deal_price` is a constant literal (`SET
>> deal_price = 26915`)
>> and does not depend on original row values, heavy EPQ overhead still
>> occurs for every conflicting row.
>>
>> 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.
>> But PostgreSQL still triggers full EPQ re‑evaluation for every updated
>> tuple,
>> leading to huge overhead and long elapsed time for the second concurrent
>> transaction.
>>
>> Expectation / question
>> When the updated assignment is pure constant and does not reference
>> any columns from the target relation, could PostgreSQL skip the expensive
>> EPQ re‑computation
>> for the new target value, even though it still needs to check row
>> visibility and tuple versions?
>>
>> Best regards,
>> Wei Sun
>>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-15 12:59 Wei Sun <936739278@qq.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wei Sun @ 2026-09-15 12:59 UTC (permalink / raw)
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: pgsql-hackers
Hi
Thanks for your reply.
At first, I suspected that for every row with an update conflict,
the subquery would be executed tofetches the new tuple version.
Because from the stack, some nodes obviously should not be called recursively.
But from the degree of slowing down, it doesn't seem to be like that.
The number of conflicting rows and different join operator will have
an impact on the degree of slowing down. especially when the subquery
needs to write temporary files, the slowdown will be even more severe.
I am currently trying to create different scenarios locally,
and if I make any new discoveries, I will also synchronize with you.
Regards,
Wei Sun
原始邮件
发件人：Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
发件时间：2026年9月15日 18:49
收件人：Wei Sun <936739278@qq.com>
抄送：pgsql-hackers <pgsql-hackers@postgresql.org>
主题：Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
Hi again,
I was able to reproduce the reported slowdown locally on PostgreSQL 20devel.
In my reproduction, the second concurrent UPDATE initially waits on a transactionid lock. After the first transaction commits, the second UPDATE completes but can take tens of seconds. For example, with 10,000 conflicting rows I measured 69.57 seconds. The original query shape with the subquery took approximately 102 seconds in another run.
I also tested a simplified form using WHERE id <= N, which still exhibits a significant slowdown. This suggests that the subquery may amplify the issue but is not necessarily the sole cause.
I am currently instrumenting the ModifyTable UPDATE path around table_tuple_lock() and EvalPlanQual() to determine where the post-lock-release time is actually being spent.
I have not yet determined whether this is an EPQ issue or another executor/locking-related performance problem. I wanted to share the reproduction and preliminary observations before proceeding further.
On Tue, Sep 15, 2026 at 2:45 PM Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
Hi,
I'm interested in working on the bug, I'll let you know once I finish reproducing it.
With Regards,
Osama Abdul Qader
On Tue, Sep 15, 2026 at 1:36 PM Wei Sun <936739278@qq.com> wrote:
Hi hackers,
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.
```sql
DROP TABLE IF EXISTS bond_deal_detail;
CREATE TABLE bond_deal_detail (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
deal_no TEXT,
bond_code TEXT,
bond_name TEXT,
trade_date DATE,
trade_time TIME,
buy_inst TEXT,
sell_inst TEXT,
deal_amt NUMERIC(20,4),
deal_price NUMERIC(12,6),
yield_rate NUMERIC(10,6),
trade_type TEXT,
settle_date DATE,
create_at TIMESTAMP
);
INSERT INTO bond_deal_detail(
deal_no, bond_code, bond_name, trade_date, trade_time,
buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
trade_type, settle_date, create_at
)
SELECT
'DEAL' || LPAD(i::TEXT,10,'0'),
'10' || LPAD((i % 99999)::TEXT,8,'0'),
'SimBond_' || (i % 2000),
'2025-01-01'::DATE + (i % 365),
('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
'Inst_' || (i % 1500),
'Inst_' || ((i + 777) % 1500),
(random() * 500000000)::NUMERIC(20,4),
(90 + random() * 20)::NUMERIC(12,6),
(1.5 + random() * 3.5)::NUMERIC(10,6),
CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
'2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
NOW()
FROM generate_series(1,1000000) AS t(i);
-- create working table for concurrent update
CREATE TABLE bond_deal_detail_sw(LIKE bond_deal_detail);
INSERT INTO bond_deal_detail_sw SELECT * FROM bond_deal_detail;
## Concurrent reproduction steps
Open two independent sessions and run below UPDATE SQL
simultaneously against table `bond_deal_detail_sw`.
Both queries try to update the same top‑100 000 rows derived
from the source table `bond_deal_detail`.
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);
Session 2 (run concurrently with 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);
1. Session 1 executes quickly, it locks and updates those 100 000 target rows.
2. Session 2 blocks waiting for row‑level locks. After session 1 commits,
session 2 resumes execution but becomes extremely slow.
3. From execution plan and trace, the slowdown comes from massive EvalPlanQual (EPQ) re‑evaluation:
for each row already modified and committed by transaction 1,
PostgreSQL fetches the new tuple version and re‑evaluates the whole sub‑query / qual tree for EPQ.
Even though the new value of `deal_price` is a constant literal (`SET deal_price = 26915`)
and does not depend on original row values, heavy EPQ overhead still occurs for every conflicting row.
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.
But PostgreSQL still triggers full EPQ re‑evaluation for every updated tuple,
leading to huge overhead and long elapsed time for the second concurrent transaction.
Expectation / question
When the updated assignment is pure constant and does not reference
any columns from the target relation, could PostgreSQL skip the expensive EPQ re‑computation
for the new target value, even though it still needs to check row visibility and tuple versions?
Best regards,
Wei Sun
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-16 07:27 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Wei Sun <936739278@qq.com>
0 siblings, 1 reply; 15+ messages in thread
From: Osama Abdul Qader @ 2026-09-16 07:27 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: pgsql-hackers
Hi,
Thanks for the update.
Your observations are consistent with some of what I have seen locally.
In my reproduction, I was also able to reproduce a substantial slowdown
after the second session was released from the row-level conflict. In the
original query shape, the execution plan uses an external merge sort and
writes temporary files:
Sort Method: external merge
Disk: 18632kB
In one run, the second UPDATE took approximately 102 seconds, while the
initial execution was under one second.
I also tried a simplified UPDATE using WHERE id <= 10000, which still
showed a significant slowdown under concurrent updates, although the
timings were quite variable. This makes me think we should distinguish the
lock-waiting time from the work performed after the conflicting tuple is
fetched.
I am currently instrumenting the ModifyTable UPDATE path around
table_tuple_lock() and EvalPlanQual() to measure where the post-conflict
time is actually being spent.
I will share the measurements once I have them.
With Regards,
Osama Abdul Qader
On Tue, Sep 15, 2026 at 6:29 PM Wei Sun <936739278@qq.com> wrote:
> Hi
>
> Thanks for your reply.
>
> At first, I suspected that for every row with an update conflict,
> the subquery would be executed tofetches the new tuple version.
> Because from the stack, some nodes obviously should not be called
> recursively.
>
> But from the degree of slowing down, it doesn't seem to be like that.
> The number of conflicting rows and different join operator will have
> an impact on the degree of slowing down. especially when the subquery
> needs to write temporary files, the slowdown will be even more severe.
>
> I am currently trying to create different scenarios locally,
> and if I make any new discoveries, I will also synchronize with you.
>
> Regards,
> Wei Sun
>
> 原始邮件
> ------------------------------
> 发件人：Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
> 发件时间：2026年9月15日 18:49
> 收件人：Wei Sun <936739278@qq.com>
> 抄送：pgsql-hackers <pgsql-hackers@postgresql.org>
> 主题：Re: Severe performance degradation with concurrent updates due to
> excessive EvalPlanQual (EPQ) re‑evaluation
>
> Hi again,
>
> I was able to reproduce the reported slowdown locally on PostgreSQL
> 20devel.
>
> In my reproduction, the second concurrent UPDATE initially waits on a
> transactionid lock. After the first transaction commits, the second UPDATE
> completes but can take tens of seconds. For example, with 10,000
> conflicting rows I measured 69.57 seconds. The original query shape with
> the subquery took approximately 102 seconds in another run.
>
> I also tested a simplified form using WHERE id <= N, which still exhibits
> a significant slowdown. This suggests that the subquery may amplify the
> issue but is not necessarily the sole cause.
>
> I am currently instrumenting the ModifyTable UPDATE path around
> table_tuple_lock() and EvalPlanQual() to determine where the
> post-lock-release time is actually being spent.
>
> I have not yet determined whether this is an EPQ issue or another
> executor/locking-related performance problem. I wanted to share the
> reproduction and preliminary observations before proceeding further.
>
> On Tue, Sep 15, 2026 at 2:45 PM Osama Abdul Qader <
> osamaabdulqader.cs@gmail.com> wrote:
>
> Hi,
>
> I'm interested in working on the bug, I'll let you know once I finish
> reproducing it.
>
> With Regards,
> Osama Abdul Qader
>
> On Tue, Sep 15, 2026 at 1:36 PM Wei Sun <936739278@qq.com> wrote:
>
> Hi hackers,
> 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.
>
> ```sql
> DROP TABLE IF EXISTS bond_deal_detail;
> CREATE TABLE bond_deal_detail (
> id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
> deal_no TEXT,
> bond_code TEXT,
> bond_name TEXT,
> trade_date DATE,
> trade_time TIME,
> buy_inst TEXT,
> sell_inst TEXT,
> deal_amt NUMERIC(20,4),
> deal_price NUMERIC(12,6),
> yield_rate NUMERIC(10,6),
> trade_type TEXT,
> settle_date DATE,
> create_at TIMESTAMP
> );
>
> INSERT INTO bond_deal_detail(
> deal_no, bond_code, bond_name, trade_date, trade_time,
> buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
> trade_type, settle_date, create_at
> )
> SELECT
> 'DEAL' || LPAD(i::TEXT,10,'0'),
> '10' || LPAD((i % 99999)::TEXT,8,'0'),
> 'SimBond_' || (i % 2000),
> '2025-01-01'::DATE + (i % 365),
> ('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
> 'Inst_' || (i % 1500),
> 'Inst_' || ((i + 777) % 1500),
> (random() * 500000000)::NUMERIC(20,4),
> (90 + random() * 20)::NUMERIC(12,6),
> (1.5 + random() * 3.5)::NUMERIC(10,6),
> CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
> '2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
> NOW()
> FROM generate_series(1,1000000) AS t(i);
>
> -- create working table for concurrent update
> CREATE TABLE bond_deal_detail_sw(LIKE bond_deal_detail);
> INSERT INTO bond_deal_detail_sw SELECT * FROM bond_deal_detail;
>
> ## Concurrent reproduction steps
>
> Open two independent sessions and run below UPDATE SQL
> simultaneously against table `bond_deal_detail_sw`.
> Both queries try to update the same top‑100 000 rows derived
> from the source table `bond_deal_detail`.
>
> 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);
>
> Session 2 (run concurrently with 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);
>
> 1. Session 1 executes quickly, it locks and updates those 100 000 target
> rows.
> 2. Session 2 blocks waiting for row‑level locks. After session 1 commits,
> session 2 resumes execution but becomes extremely slow.
> 3. From execution plan and trace, the slowdown comes from massive
> EvalPlanQual (EPQ) re‑evaluation:
> for each row already modified and committed by transaction 1,
> PostgreSQL fetches the new tuple version and re‑evaluates the whole
> sub‑query / qual tree for EPQ.
> Even though the new value of `deal_price` is a constant literal (`SET
> deal_price = 26915`)
> and does not depend on original row values, heavy EPQ overhead still
> occurs for every conflicting row.
>
> 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.
> But PostgreSQL still triggers full EPQ re‑evaluation for every updated
> tuple,
> leading to huge overhead and long elapsed time for the second concurrent
> transaction.
>
> Expectation / question
> When the updated assignment is pure constant and does not reference
> any columns from the target relation, could PostgreSQL skip the expensive
> EPQ re‑computation
> for the new target value, even though it still needs to check row
> visibility and tuple versions?
>
> Best regards,
> Wei Sun
>
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-18 12:58 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Osama Abdul Qader @ 2026-09-18 12:58 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: pgsql-hackers
Hi again,
I have been investigating the reported regression involving UPDATE
execution and EvalPlanQual (EPQ), and I wanted to share my findings.
I reproduced the issue locally using the bond_deal_detail /
bond_deal_detail_sw reproducer and traced the relevant execution path
through the executor.
The affected path in nodeModifyTable.c is the TM_Updated case in
ExecUpdate(). When the tuple has been concurrently updated, PostgreSQL:
1. The affected path in nodeModifyTable.c is the TM_Updated case in
ExecUpdate(). When the tuple has been concurrently updated, PostgreSQL:
2. runs EvalPlanQual() to recheck the updated tuple against the query's
qualifications;
3. continues with the update using the EPQ result.
I also traced the EPQ implementation in execMain.c,
including EvalPlanQual(), EvalPlanQualSlot(), EvalPlanQualNext(),
EvalPlanQualBegin(), EvalPlanQualStart(), EvalPlanQualEnd().
In particular, EvalPlanQualNext() switches to the EPQ query context and
invokes ExecProcNode() on the EPQ plan tree. EvalPlanQualStart() creates a
child EState, initializes the required subplans, and initializes the EPQ
plan tree with ExecInitNode().
To get more concrete timing information, I temporarily instrumented the
TM_Updated path in nodeModifyTable.c to measure table_tuple_lock() and
EvalPlanQual() separately. The diagnostic patch is attached
as: epq-instrumentation.patch
The instrumentation produces separate log entries of the form:
EPQ DEBUG: table_tuple_lock took ... ms
EPQ DEBUG: EvalPlanQual took ... ms
This allowed me to distinguish the time spent acquiring/fetching the
latest tuple version from the time spent executing the EPQ recheck
itself.
The relevant source path is approximately:
ExecUpdate()
-> table_tuple_lock()
-> EvalPlanQual()
-> EvalPlanQualBegin()
-> EvalPlanQualNext()
-> ExecProcNode()
I have also verified the patch with git diff --check.
At this point, I believe we have enough evidence to narrow the
investigation to the EPQ/concurrent-update path rather than treating
the overall UPDATE runtime as a single operation. I would appreciate
your thoughts on whether this is the expected execution behavior, and
whether there are particular executor or EPQ areas you would recommend
investigating next.
I have attached the diagnostic patch for reference.
Best Regards:
Osama Abdul Qader
On Wed, Sep 16, 2026 at 12:57 PM Osama Abdul Qader <
osamaabdulqader.cs@gmail.com> wrote:
> Hi,
>
> Thanks for the update.
>
> Your observations are consistent with some of what I have seen locally.
>
> In my reproduction, I was also able to reproduce a substantial slowdown
> after the second session was released from the row-level conflict. In the
> original query shape, the execution plan uses an external merge sort and
> writes temporary files:
>
> Sort Method: external merge
> Disk: 18632kB
>
> In one run, the second UPDATE took approximately 102 seconds, while the
> initial execution was under one second.
>
> I also tried a simplified UPDATE using WHERE id <= 10000, which still
> showed a significant slowdown under concurrent updates, although the
> timings were quite variable. This makes me think we should distinguish the
> lock-waiting time from the work performed after the conflicting tuple is
> fetched.
>
> I am currently instrumenting the ModifyTable UPDATE path around
> table_tuple_lock() and EvalPlanQual() to measure where the post-conflict
> time is actually being spent.
>
> I will share the measurements once I have them.
>
> With Regards,
> Osama Abdul Qader
>
> On Tue, Sep 15, 2026 at 6:29 PM Wei Sun <936739278@qq.com> wrote:
>
>> Hi
>>
>> Thanks for your reply.
>>
>> At first, I suspected that for every row with an update conflict,
>> the subquery would be executed tofetches the new tuple version.
>> Because from the stack, some nodes obviously should not be called
>> recursively.
>>
>> But from the degree of slowing down, it doesn't seem to be like that.
>> The number of conflicting rows and different join operator will have
>> an impact on the degree of slowing down. especially when the subquery
>> needs to write temporary files, the slowdown will be even more severe.
>>
>> I am currently trying to create different scenarios locally,
>> and if I make any new discoveries, I will also synchronize with you.
>>
>> Regards,
>> Wei Sun
>>
>> 原始邮件
>> ------------------------------
>> 发件人：Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
>> 发件时间：2026年9月15日 18:49
>> 收件人：Wei Sun <936739278@qq.com>
>> 抄送：pgsql-hackers <pgsql-hackers@postgresql.org>
>> 主题：Re: Severe performance degradation with concurrent updates due to
>> excessive EvalPlanQual (EPQ) re‑evaluation
>>
>> Hi again,
>>
>> I was able to reproduce the reported slowdown locally on PostgreSQL
>> 20devel.
>>
>> In my reproduction, the second concurrent UPDATE initially waits on a
>> transactionid lock. After the first transaction commits, the second UPDATE
>> completes but can take tens of seconds. For example, with 10,000
>> conflicting rows I measured 69.57 seconds. The original query shape with
>> the subquery took approximately 102 seconds in another run.
>>
>> I also tested a simplified form using WHERE id <= N, which still
>> exhibits a significant slowdown. This suggests that the subquery may
>> amplify the issue but is not necessarily the sole cause.
>>
>> I am currently instrumenting the ModifyTable UPDATE path around
>> table_tuple_lock() and EvalPlanQual() to determine where the
>> post-lock-release time is actually being spent.
>>
>> I have not yet determined whether this is an EPQ issue or another
>> executor/locking-related performance problem. I wanted to share the
>> reproduction and preliminary observations before proceeding further.
>>
>> On Tue, Sep 15, 2026 at 2:45 PM Osama Abdul Qader <
>> osamaabdulqader.cs@gmail.com> wrote:
>>
>> Hi,
>>
>> I'm interested in working on the bug, I'll let you know once I finish
>> reproducing it.
>>
>> With Regards,
>> Osama Abdul Qader
>>
>> On Tue, Sep 15, 2026 at 1:36 PM Wei Sun <936739278@qq.com> wrote:
>>
>> Hi hackers,
>> 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.
>>
>> ```sql
>> DROP TABLE IF EXISTS bond_deal_detail;
>> CREATE TABLE bond_deal_detail (
>> id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
>> deal_no TEXT,
>> bond_code TEXT,
>> bond_name TEXT,
>> trade_date DATE,
>> trade_time TIME,
>> buy_inst TEXT,
>> sell_inst TEXT,
>> deal_amt NUMERIC(20,4),
>> deal_price NUMERIC(12,6),
>> yield_rate NUMERIC(10,6),
>> trade_type TEXT,
>> settle_date DATE,
>> create_at TIMESTAMP
>> );
>>
>> INSERT INTO bond_deal_detail(
>> deal_no, bond_code, bond_name, trade_date, trade_time,
>> buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
>> trade_type, settle_date, create_at
>> )
>> SELECT
>> 'DEAL' || LPAD(i::TEXT,10,'0'),
>> '10' || LPAD((i % 99999)::TEXT,8,'0'),
>> 'SimBond_' || (i % 2000),
>> '2025-01-01'::DATE + (i % 365),
>> ('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
>> 'Inst_' || (i % 1500),
>> 'Inst_' || ((i + 777) % 1500),
>> (random() * 500000000)::NUMERIC(20,4),
>> (90 + random() * 20)::NUMERIC(12,6),
>> (1.5 + random() * 3.5)::NUMERIC(10,6),
>> CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
>> '2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
>> NOW()
>> FROM generate_series(1,1000000) AS t(i);
>>
>> -- create working table for concurrent update
>> CREATE TABLE bond_deal_detail_sw(LIKE bond_deal_detail);
>> INSERT INTO bond_deal_detail_sw SELECT * FROM bond_deal_detail;
>>
>> ## Concurrent reproduction steps
>>
>> Open two independent sessions and run below UPDATE SQL
>> simultaneously against table `bond_deal_detail_sw`.
>> Both queries try to update the same top‑100 000 rows derived
>> from the source table `bond_deal_detail`.
>>
>> 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);
>>
>> Session 2 (run concurrently with 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);
>>
>> 1. Session 1 executes quickly, it locks and updates those 100 000 target
>> rows.
>> 2. Session 2 blocks waiting for row‑level locks. After session 1 commits,
>> session 2 resumes execution but becomes extremely slow.
>> 3. From execution plan and trace, the slowdown comes from massive
>> EvalPlanQual (EPQ) re‑evaluation:
>> for each row already modified and committed by transaction 1,
>> PostgreSQL fetches the new tuple version and re‑evaluates the whole
>> sub‑query / qual tree for EPQ.
>> Even though the new value of `deal_price` is a constant literal (`SET
>> deal_price = 26915`)
>> and does not depend on original row values, heavy EPQ overhead still
>> occurs for every conflicting row.
>>
>> 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.
>> But PostgreSQL still triggers full EPQ re‑evaluation for every updated
>> tuple,
>> leading to huge overhead and long elapsed time for the second concurrent
>> transaction.
>>
>> Expectation / question
>> When the updated assignment is pure constant and does not reference
>> any columns from the target relation, could PostgreSQL skip the expensive
>> EPQ re‑computation
>> for the new target value, even though it still needs to check row
>> visibility and tuple versions?
>>
>> Best regards,
>> Wei Sun
>>
>>
>>
Attachments:
epq-instrumentation.patch (text/x-patch, 1.8K) download
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 9a1c0992bfe..fcd076e092b 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -2895,7 +2895,10 @@ redo_act:
{
TupleTableSlot *inputslot;
TupleTableSlot *epqslot;
-
+ instr_time lock_start;
+ instr_time lock_end;
+ instr_time epq_start;
+ instr_time epq_end;
if (IsolationUsesXactSnapshot())
ereport(ERROR,
(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
@@ -2907,23 +2910,30 @@ redo_act:
*/
inputslot = EvalPlanQualSlot(context->epqstate, resultRelationDesc,
resultRelInfo->ri_RangeTableIndex);
-
+ INSTR_TIME_SET_CURRENT(lock_start);
result = table_tuple_lock(resultRelationDesc, tupleid,
estate->es_snapshot,
inputslot, estate->es_output_cid,
updateCxt.lockmode, LockWaitBlock,
TUPLE_LOCK_FLAG_FIND_LAST_VERSION,
&context->tmfd);
-
+ INSTR_TIME_SET_CURRENT(lock_end);
+ INSTR_TIME_SUBTRACT(lock_end, lock_start);
+ elog(LOG, "EPQ DEBUG: table_tuple_lock took %.3f ms",
+ INSTR_TIME_GET_MILLISEC(lock_end));
switch (result)
{
case TM_Ok:
Assert(context->tmfd.traversed);
-
+ INSTR_TIME_SET_CURRENT(epq_start);
epqslot = EvalPlanQual(context->epqstate,
resultRelationDesc,
resultRelInfo->ri_RangeTableIndex,
inputslot);
+ INSTR_TIME_SET_CURRENT(epq_end);
+ INSTR_TIME_SUBTRACT(epq_end, epq_start);
+ elog(LOG, "EPQ DEBUG: EvalPlanQual took %.3f ms",
+ INSTR_TIME_GET_MILLISEC(epq_end));
if (TupIsNull(epqslot))
/* Tuple not passing quals anymore, exiting... */
return NULL;
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-20 02:50 Wei Sun <936739278@qq.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wei Sun @ 2026-09-20 02:50 UTC (permalink / raw)
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: pgsql-hackers
Hi
Thanks a lot for your investigation and the diagnostic patch.
I agree that the evidence points to the EPQ / concurrent-update path,
rather than treating the whole UPDATE statement as a single atomic operation.
I think this heavy EPQ re-evaluation on every conflicting tuple is the expected execution behavior under Read Committed isolation.
>When the updated assignment is pure constant and does not reference
>any columns from the target relation, could PostgreSQL skip the expensive EPQ re‑computation
>for the new target value, even though it still needs to check row visibility and tuple versions?
Initially, I proposed the above idea.This idea is likely to violate the Read Committed isolation.
This idea is probably not feasible.
However, I'd like to share a few observations and directions that might be worth investigating,
from the perspective of *how* the EPQ recheck is implemented rather than *whether* it should exist:
**1. The subquery is re-executed for every tuple, even though it reads from a different table.**
In the reproducer, the WHERE clause is:
```
WHERE deal_no IN (SELECT deal_no FROM bond_deal_detail ORDER BY deal_no LIMIT 100000)
```
The subquery reads from `bond_deal_detail`, which is a different table that is *not* being updated by the concurrent transaction.
Yet from what you traced, `EvalPlanQualStart()` duplicates all InitPlan / SubPlan node trees, and `EvalPlanQualNext()` re-runs the EPQ plan tree via `ExecProcNode()`.
This means the same subquery — which returns a fixed set of 100,000 `deal_no` values — gets re-executed **once per conflicting tuple**, i.e. 100,000 times.
I wonder whether there's room to identify when a SubPlan references only *other* relations that are not being modified by the current UPDATE, and therefore its result is invariant across EPQ calls — allowing it to be computed once and cached, rather than re-initialized and re-executed for every tuple.But this also involves whether the data in *other* relations has been modified.I don't have a good idea yet. If I make any new progress, I will continue to share it with you.
Regards,
Wei Sun
原始邮件
发件人：Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
发件时间：2026年9月18日 20:58
收件人：Wei Sun <936739278@qq.com>
抄送：pgsql-hackers <pgsql-hackers@postgresql.org>
主题：Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
Hi again,
I have been investigating the reported regression involving UPDATE execution and EvalPlanQual (EPQ), and I wanted to share my findings.
I reproduced the issue locally using the bond_deal_detail / bond_deal_detail_sw reproducer and traced the relevant execution path through the executor.
The affected path in nodeModifyTable.c is the TM_Updated case in ExecUpdate(). When the tuple has been concurrently updated, PostgreSQL:
1. The affected path in nodeModifyTable.c is the TM_Updated case in ExecUpdate(). When the tuple has been concurrently updated, PostgreSQL:
2. runs EvalPlanQual() to recheck the updated tuple against the query's qualifications;
3. continues with the update using the EPQ result.
I also traced the EPQ implementation in execMain.c, including EvalPlanQual(), EvalPlanQualSlot(), EvalPlanQualNext(), EvalPlanQualBegin(), EvalPlanQualStart(), EvalPlanQualEnd().
In particular, EvalPlanQualNext() switches to the EPQ query context and invokes ExecProcNode() on the EPQ plan tree. EvalPlanQualStart() creates a child EState, initializes the required subplans, and initializes the EPQ plan tree with ExecInitNode().
To get more concrete timing information, I temporarily instrumented the TM_Updated path in nodeModifyTable.c to measure table_tuple_lock() and EvalPlanQual() separately. The diagnostic patch is attached as: epq-instrumentation.patch
The instrumentation produces separate log entries of the form:
EPQ DEBUG: table_tuple_lock took ... ms EPQ DEBUG: EvalPlanQual took ... ms
This allowed me to distinguish the time spent acquiring/fetching the latest tuple version from the time spent executing the EPQ recheck itself.
The relevant source path is approximately:
ExecUpdate() -> table_tuple_lock() -> EvalPlanQual() -> EvalPlanQualBegin() -> EvalPlanQualNext() -> ExecProcNode()
I have also verified the patch with git diff --check.
At this point, I believe we have enough evidence to narrow the investigation to the EPQ/concurrent-update path rather than treating the overall UPDATE runtime as a single operation. I would appreciate your thoughts on whether this is the expected execution behavior, and whether there are particular executor or EPQ areas you would recommend investigating next.
I have attached the diagnostic patch for reference.
Best Regards:
Osama Abdul Qader
On Wed, Sep 16, 2026 at 12:57 PM Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
Hi,
Thanks for the update.
Your observations are consistent with some of what I have seen locally.
In my reproduction, I was also able to reproduce a substantial slowdown after the second session was released from the row-level conflict. In the original query shape, the execution plan uses an external merge sort and writes temporary files:
Sort Method: external merge
Disk: 18632kB
In one run, the second UPDATE took approximately 102 seconds, while the initial execution was under one second.
I also tried a simplified UPDATE using WHERE id <= 10000, which still showed a significant slowdown under concurrent updates, although the timings were quite variable. This makes me think we should distinguish the lock-waiting time from the work performed after the conflicting tuple is fetched.
I am currently instrumenting the ModifyTable UPDATE path around table_tuple_lock() and EvalPlanQual() to measure where the post-conflict time is actually being spent.
I will share the measurements once I have them.
With Regards,
Osama Abdul Qader
On Tue, Sep 15, 2026 at 6:29 PM Wei Sun <936739278@qq.com> wrote:
Hi
Thanks for your reply.
At first, I suspected that for every row with an update conflict,
the subquery would be executed tofetches the new tuple version.
Because from the stack, some nodes obviously should not be called recursively.
But from the degree of slowing down, it doesn't seem to be like that.
The number of conflicting rows and different join operator will have
an impact on the degree of slowing down. especially when the subquery
needs to write temporary files, the slowdown will be even more severe.
I am currently trying to create different scenarios locally,
and if I make any new discoveries, I will also synchronize with you.
Regards,
Wei Sun
原始邮件
发件人：Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
发件时间：2026年9月15日 18:49
收件人：Wei Sun <936739278@qq.com>
抄送：pgsql-hackers <pgsql-hackers@postgresql.org>
主题：Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
Hi again,
I was able to reproduce the reported slowdown locally on PostgreSQL 20devel.
In my reproduction, the second concurrent UPDATE initially waits on a transactionid lock. After the first transaction commits, the second UPDATE completes but can take tens of seconds. For example, with 10,000 conflicting rows I measured 69.57 seconds. The original query shape with the subquery took approximately 102 seconds in another run.
I also tested a simplified form using WHERE id <= N, which still exhibits a significant slowdown. This suggests that the subquery may amplify the issue but is not necessarily the sole cause.
I am currently instrumenting the ModifyTable UPDATE path around table_tuple_lock() and EvalPlanQual() to determine where the post-lock-release time is actually being spent.
I have not yet determined whether this is an EPQ issue or another executor/locking-related performance problem. I wanted to share the reproduction and preliminary observations before proceeding further.
On Tue, Sep 15, 2026 at 2:45 PM Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
Hi,
I'm interested in working on the bug, I'll let you know once I finish reproducing it.
With Regards,
Osama Abdul Qader
On Tue, Sep 15, 2026 at 1:36 PM Wei Sun <936739278@qq.com> wrote:
Hi hackers,
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.
```sql
DROP TABLE IF EXISTS bond_deal_detail;
CREATE TABLE bond_deal_detail (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
deal_no TEXT,
bond_code TEXT,
bond_name TEXT,
trade_date DATE,
trade_time TIME,
buy_inst TEXT,
sell_inst TEXT,
deal_amt NUMERIC(20,4),
deal_price NUMERIC(12,6),
yield_rate NUMERIC(10,6),
trade_type TEXT,
settle_date DATE,
create_at TIMESTAMP
);
INSERT INTO bond_deal_detail(
deal_no, bond_code, bond_name, trade_date, trade_time,
buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
trade_type, settle_date, create_at
)
SELECT
'DEAL' || LPAD(i::TEXT,10,'0'),
'10' || LPAD((i % 99999)::TEXT,8,'0'),
'SimBond_' || (i % 2000),
'2025-01-01'::DATE + (i % 365),
('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
'Inst_' || (i % 1500),
'Inst_' || ((i + 777) % 1500),
(random() * 500000000)::NUMERIC(20,4),
(90 + random() * 20)::NUMERIC(12,6),
(1.5 + random() * 3.5)::NUMERIC(10,6),
CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
'2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
NOW()
FROM generate_series(1,1000000) AS t(i);
-- create working table for concurrent update
CREATE TABLE bond_deal_detail_sw(LIKE bond_deal_detail);
INSERT INTO bond_deal_detail_sw SELECT * FROM bond_deal_detail;
## Concurrent reproduction steps
Open two independent sessions and run below UPDATE SQL
simultaneously against table `bond_deal_detail_sw`.
Both queries try to update the same top‑100 000 rows derived
from the source table `bond_deal_detail`.
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);
Session 2 (run concurrently with 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);
1. Session 1 executes quickly, it locks and updates those 100 000 target rows.
2. Session 2 blocks waiting for row‑level locks. After session 1 commits,
session 2 resumes execution but becomes extremely slow.
3. From execution plan and trace, the slowdown comes from massive EvalPlanQual (EPQ) re‑evaluation:
for each row already modified and committed by transaction 1,
PostgreSQL fetches the new tuple version and re‑evaluates the whole sub‑query / qual tree for EPQ.
Even though the new value of `deal_price` is a constant literal (`SET deal_price = 26915`)
and does not depend on original row values, heavy EPQ overhead still occurs for every conflicting row.
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.
But PostgreSQL still triggers full EPQ re‑evaluation for every updated tuple,
leading to huge overhead and long elapsed time for the second concurrent transaction.
Expectation / question
When the updated assignment is pure constant and does not reference
any columns from the target relation, could PostgreSQL skip the expensive EPQ re‑computation
for the new target value, even though it still needs to check row visibility and tuple versions?
Best regards,
Wei Sun
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-21 15:25 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Wei Sun <936739278@qq.com>
0 siblings, 0 replies; 15+ messages in thread
From: Osama Abdul Qader @ 2026-09-21 15:25 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: pgsql-hackers
Hi again Wei,
Thanks for the detailed review and explanation, I understand your point.
I'll focus on the next part of investigation on how SubPlan is executed
during repeated EPQ rechecks. In particular, I'll trace whether the same
uncorrelated is being re-executed for conflicting tuples, and look into its
parameter/dependency and snapshot semantics to determine whether its result
could be safely reused across EPQ calls.
I'll continue from here and share my findings once I get heal from common
fever and have something concrete.
Once again, I'd like to express the gratitude to you for eviewing my work
and guiding me.
With Regards,
Osama Abdul Qader.
On Sun, 20 Sept, 2026, 8:20 am Wei Sun, <936739278@qq.com> wrote:
> Hi
>
> Thanks a lot for your investigation and the diagnostic patch.
> I agree that the evidence points to the EPQ / concurrent-update path,
> rather than treating the whole UPDATE statement as a single atomic
> operation.
>
> I think this heavy EPQ re-evaluation on every conflicting tuple is the
> expected execution behavior under Read Committed isolation.
>
> >When the updated assignment is pure constant and does not reference
> >any columns from the target relation, could PostgreSQL skip the expensive
> EPQ re‑computation
> >for the new target value, even though it still needs to check row
> visibility and tuple versions?
>
> Initially, I proposed the above idea.This idea is likely to violate the
> Read Committed isolation.
> This idea is probably not feasible.
>
> However, I'd like to share a few observations and directions that might be
> worth investigating,
> from the perspective of *how* the EPQ recheck is implemented rather than
> *whether* it should exist:
>
> **1. The subquery is re-executed for every tuple, even though it reads
> from a different table.**
>
> In the reproducer, the WHERE clause is:
>
> ```
> WHERE deal_no IN (SELECT deal_no FROM bond_deal_detail ORDER BY deal_no
> LIMIT 100000)
> ```
>
> The subquery reads from `bond_deal_detail`, which is a different table
> that is *not* being updated by the concurrent transaction.
> Yet from what you traced, `EvalPlanQualStart()` duplicates all InitPlan /
> SubPlan node trees, and `EvalPlanQualNext()` re-runs the EPQ plan tree via
> `ExecProcNode()`.
> This means the same subquery — which returns a fixed set of 100,000
> `deal_no` values — gets re-executed **once per conflicting tuple**, i.e.
> 100,000 times.
>
> I wonder whether there's room to identify when a SubPlan references only
> *other* relations that are not being modified by the current UPDATE, and
> therefore its result is invariant across EPQ calls — allowing it to be
> computed once and cached, rather than re-initialized and re-executed for
> every tuple.But this also involves whether the data in *other* relations
> has been modified.I don't have a good idea yet. If I make any new progress,
> I will continue to share it with you.
>
> Regards,
> Wei Sun
>
>
> 原始邮件
> ------------------------------
> 发件人：Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
> 发件时间：2026年9月18日 20:58
> 收件人：Wei Sun <936739278@qq.com>
> 抄送：pgsql-hackers <pgsql-hackers@postgresql.org>
> 主题：Re: Severe performance degradation with concurrent updates due to
> excessive EvalPlanQual (EPQ) re‑evaluation
>
> Hi again,
>
> I have been investigating the reported regression involving UPDATE
> execution and EvalPlanQual (EPQ), and I wanted to share my findings.
>
> I reproduced the issue locally using the bond_deal_detail /
> bond_deal_detail_sw reproducer and traced the relevant execution path
> through the executor.
>
> The affected path in nodeModifyTable.c is the TM_Updated case in
> ExecUpdate(). When the tuple has been concurrently updated, PostgreSQL:
>
> 1. The affected path in nodeModifyTable.c is the TM_Updated case in
> ExecUpdate(). When the tuple has been concurrently updated, PostgreSQL:
>
> 2. runs EvalPlanQual() to recheck the updated tuple against the query's
> qualifications;
>
> 3. continues with the update using the EPQ result.
>
> I also traced the EPQ implementation in execMain.c,
> including EvalPlanQual(), EvalPlanQualSlot(), EvalPlanQualNext(), EvalPlanQualBegin(), EvalPlanQualStart(), EvalPlanQualEnd().
>
> In particular, EvalPlanQualNext() switches to the EPQ query context and
> invokes ExecProcNode() on the EPQ plan tree. EvalPlanQualStart() creates
> a child EState, initializes the required subplans, and initializes the
> EPQ plan tree with ExecInitNode().
>
> To get more concrete timing information, I temporarily instrumented the
> TM_Updated path in nodeModifyTable.c to measure table_tuple_lock() and
> EvalPlanQual() separately. The diagnostic patch is attached
> as: epq-instrumentation.patch
>
> The instrumentation produces separate log entries of the form:
>
> EPQ DEBUG: table_tuple_lock took ... ms
> EPQ DEBUG: EvalPlanQual took ... ms
>
> This allowed me to distinguish the time spent acquiring/fetching the latest tuple version from the time spent executing the EPQ recheck itself.
>
> The relevant source path is approximately:
>
> ExecUpdate()
> -> table_tuple_lock()
> -> EvalPlanQual()
> -> EvalPlanQualBegin()
> -> EvalPlanQualNext()
> -> ExecProcNode()
>
> I have also verified the patch with git diff --check.
>
> At this point, I believe we have enough evidence to narrow the investigation to the EPQ/concurrent-update path rather than treating the overall UPDATE runtime as a single operation. I would appreciate your thoughts on whether this is the expected execution behavior, and whether there are particular executor or EPQ areas you would recommend investigating next.
>
> I have attached the diagnostic patch for reference.
>
> Best Regards:
> Osama Abdul Qader
>
>
> On Wed, Sep 16, 2026 at 12:57 PM Osama Abdul Qader <*osamaabdulqader.cs@gmail.com
> <osamaabdulqader.cs@gmail.com>*> wrote:
>
> Hi,
>
> Thanks for the update.
>
> Your observations are consistent with some of what I have seen locally.
>
> In my reproduction, I was also able to reproduce a substantial slowdown
> after the second session was released from the row-level conflict. In the
> original query shape, the execution plan uses an external merge sort and
> writes temporary files:
>
> Sort Method: external merge
> Disk: 18632kB
>
> In one run, the second UPDATE took approximately 102 seconds, while the
> initial execution was under one second.
>
> I also tried a simplified UPDATE using WHERE id <= 10000, which still
> showed a significant slowdown under concurrent updates, although the
> timings were quite variable. This makes me think we should distinguish the
> lock-waiting time from the work performed after the conflicting tuple is
> fetched.
>
> I am currently instrumenting the ModifyTable UPDATE path around
> table_tuple_lock() and EvalPlanQual() to measure where the post-conflict
> time is actually being spent.
>
> I will share the measurements once I have them.
>
> With Regards,
> Osama Abdul Qader
>
> On Tue, Sep 15, 2026 at 6:29 PM Wei Sun <*936739278@qq.com
> <936739278@qq.com>*> wrote:
>
> Hi
>
> Thanks for your reply.
>
> At first, I suspected that for every row with an update conflict,
> the subquery would be executed tofetches the new tuple version.
> Because from the stack, some nodes obviously should not be called
> recursively.
>
> But from the degree of slowing down, it doesn't seem to be like that.
> The number of conflicting rows and different join operator will have
> an impact on the degree of slowing down. especially when the subquery
> needs to write temporary files, the slowdown will be even more severe.
>
> I am currently trying to create different scenarios locally,
> and if I make any new discoveries, I will also synchronize with you.
>
> Regards,
> Wei Sun
>
> 原始邮件
> ------------------------------
> 发件人：Osama Abdul Qader <*osamaabdulqader.cs@gmail.com
> <osamaabdulqader.cs@gmail.com>*>
> 发件时间：2026年9月15日 18:49
> 收件人：Wei Sun <*936739278@qq.com <936739278@qq.com>*>
> 抄送：pgsql-hackers <*pgsql-hackers@postgresql.org
> <pgsql-hackers@postgresql.org>*>
> 主题：Re: Severe performance degradation with concurrent updates due to
> excessive EvalPlanQual (EPQ) re‑evaluation
>
> Hi again,
>
> I was able to reproduce the reported slowdown locally on PostgreSQL
> 20devel.
>
> In my reproduction, the second concurrent UPDATE initially waits on a
> transactionid lock. After the first transaction commits, the second UPDATE
> completes but can take tens of seconds. For example, with 10,000
> conflicting rows I measured 69.57 seconds. The original query shape with
> the subquery took approximately 102 seconds in another run.
>
> I also tested a simplified form using WHERE id <= N, which still exhibits
> a significant slowdown. This suggests that the subquery may amplify the
> issue but is not necessarily the sole cause.
>
> I am currently instrumenting the ModifyTable UPDATE path around
> table_tuple_lock() and EvalPlanQual() to determine where the
> post-lock-release time is actually being spent.
>
> I have not yet determined whether this is an EPQ issue or another
> executor/locking-related performance problem. I wanted to share the
> reproduction and preliminary observations before proceeding further.
>
> On Tue, Sep 15, 2026 at 2:45 PM Osama Abdul Qader <*osamaabdulqader.cs@gmail.com
> <osamaabdulqader.cs@gmail.com>*> wrote:
>
> Hi,
>
> I'm interested in working on the bug, I'll let you know once I finish
> reproducing it.
>
> With Regards,
> Osama Abdul Qader
>
> On Tue, Sep 15, 2026 at 1:36 PM Wei Sun <*936739278@qq.com
> <936739278@qq.com>*> wrote:
>
> Hi hackers,
> 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.
>
> ```sql
> DROP TABLE IF EXISTS bond_deal_detail;
> CREATE TABLE bond_deal_detail (
> id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
> deal_no TEXT,
> bond_code TEXT,
> bond_name TEXT,
> trade_date DATE,
> trade_time TIME,
> buy_inst TEXT,
> sell_inst TEXT,
> deal_amt NUMERIC(20,4),
> deal_price NUMERIC(12,6),
> yield_rate NUMERIC(10,6),
> trade_type TEXT,
> settle_date DATE,
> create_at TIMESTAMP
> );
>
> INSERT INTO bond_deal_detail(
> deal_no, bond_code, bond_name, trade_date, trade_time,
> buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
> trade_type, settle_date, create_at
> )
> SELECT
> 'DEAL' || LPAD(i::TEXT,10,'0'),
> '10' || LPAD((i % 99999)::TEXT,8,'0'),
> 'SimBond_' || (i % 2000),
> '2025-01-01'::DATE + (i % 365),
> ('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
> 'Inst_' || (i % 1500),
> 'Inst_' || ((i + 777) % 1500),
> (random() * 500000000)::NUMERIC(20,4),
> (90 + random() * 20)::NUMERIC(12,6),
> (1.5 + random() * 3.5)::NUMERIC(10,6),
> CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
> '2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
> NOW()
> FROM generate_series(1,1000000) AS t(i);
>
> -- create working table for concurrent update
> CREATE TABLE bond_deal_detail_sw(LIKE bond_deal_detail);
> INSERT INTO bond_deal_detail_sw SELECT * FROM bond_deal_detail;
>
> ## Concurrent reproduction steps
>
> Open two independent sessions and run below UPDATE SQL
> simultaneously against table `bond_deal_detail_sw`.
> Both queries try to update the same top‑100 000 rows derived
> from the source table `bond_deal_detail`.
>
> 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);
>
> Session 2 (run concurrently with 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);
>
> 1. Session 1 executes quickly, it locks and updates those 100 000 target
> rows.
> 2. Session 2 blocks waiting for row‑level locks. After session 1 commits,
> session 2 resumes execution but becomes extremely slow.
> 3. From execution plan and trace, the slowdown comes from massive
> EvalPlanQual (EPQ) re‑evaluation:
> for each row already modified and committed by transaction 1,
> PostgreSQL fetches the new tuple version and re‑evaluates the whole
> sub‑query / qual tree for EPQ.
> Even though the new value of `deal_price` is a constant literal (`SET
> deal_price = 26915`)
> and does not depend on original row values, heavy EPQ overhead still
> occurs for every conflicting row.
>
> 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.
> But PostgreSQL still triggers full EPQ re‑evaluation for every updated
> tuple,
> leading to huge overhead and long elapsed time for the second concurrent
> transaction.
>
> Expectation / question
> When the updated assignment is pure constant and does not reference
> any columns from the target relation, could PostgreSQL skip the expensive
> EPQ re‑computation
> for the new target value, even though it still needs to check row
> visibility and tuple versions?
>
> Best regards,
> Wei Sun
>
>
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-21 18:58 Andres Freund <andres@anarazel.de>
parent: Wei Sun <936739278@qq.com>
1 sibling, 1 reply; 15+ messages in thread
From: Andres Freund @ 2026-09-21 18:58 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: pgsql-hackers
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-22 02:15 Wei Sun <936739278@qq.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 15+ messages in thread
From: Wei Sun @ 2026-09-22 02:15 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: pgsql-hackers
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@anarazel.de>
发件时间：2026年9月22日 02:58
收件人：Wei Sun <936739278@qq.com>, Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
抄送：pgsql-hackers <pgsql-hackers@postgresql.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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-25 01:23 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Wei Sun <936739278@qq.com>
0 siblings, 1 reply; 15+ messages in thread
From: Osama Abdul Qader @ 2026-09-25 01:23 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-hackers
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@qq.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@anarazel.de>
> 发件时间：2026年9月22日 02:58
> 收件人：Wei Sun <936739278@qq.com>, Osama Abdul Qader <
> osamaabdulqader.cs@gmail.com>
> 抄送：pgsql-hackers <pgsql-hackers@postgresql.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
>
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-25 03:05 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Osama Abdul Qader @ 2026-09-25 03:05 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-hackers
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.cs@gmail.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@qq.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@anarazel.de>
>> 发件时间：2026年9月22日 02:58
>> 收件人：Wei Sun <936739278@qq.com>, Osama Abdul Qader <
>> osamaabdulqader.cs@gmail.com>
>> 抄送：pgsql-hackers <pgsql-hackers@postgresql.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
>>
>>
>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-25 08:37 Wei Sun <936739278@qq.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wei Sun @ 2026-09-25 08:37 UTC (permalink / raw)
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-hackers
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.cs@gmail.com>
发件时间：2026年9月25日 11:05
收件人：Wei Sun <936739278@qq.com>
抄送：Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.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.cs@gmail.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@qq.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@anarazel.de>
发件时间：2026年9月22日 02:58
收件人：Wei Sun <936739278@qq.com>, Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
抄送：pgsql-hackers <pgsql-hackers@postgresql.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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-09-25 14:05 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Wei Sun <936739278@qq.com>
0 siblings, 1 reply; 15+ messages in thread
From: Osama Abdul Qader @ 2026-09-25 14:05 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-hackers
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@qq.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.cs@gmail.com>
> 发件时间：2026年9月25日 11:05
> 收件人：Wei Sun <936739278@qq.com>
> 抄送：Andres Freund <andres@anarazel.de>, pgsql-hackers <
> pgsql-hackers@postgresql.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.cs@gmail.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@qq.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@anarazel.de>
> 发件时间：2026年9月22日 02:58
> 收件人：Wei Sun <936739278@qq.com>, Osama Abdul Qader <
> osamaabdulqader.cs@gmail.com>
> 抄送：pgsql-hackers <pgsql-hackers@postgresql.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
>
>
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation
@ 2026-10-02 20:34 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Osama Abdul Qader @ 2026-10-02 20:34 UTC (permalink / raw)
To: Wei Sun <936739278@qq.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-hackers
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.cs@gmail.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@qq.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.cs@gmail.com>
>> 发件时间：2026年9月25日 11:05
>> 收件人：Wei Sun <936739278@qq.com>
>> 抄送：Andres Freund <andres@anarazel.de>, pgsql-hackers <
>> pgsql-hackers@postgresql.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.cs@gmail.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@qq.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@anarazel.de>
>> 发件时间：2026年9月22日 02:58
>> 收件人：Wei Sun <936739278@qq.com>, Osama Abdul Qader <
>> osamaabdulqader.cs@gmail.com>
>> 抄送：pgsql-hackers <pgsql-hackers@postgresql.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
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-10-02 20:34 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-15 08:05 Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation Wei Sun <936739278@qq.com>
2026-09-15 09:15 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-15 10:49 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-15 12:59 ` Wei Sun <936739278@qq.com>
2026-09-16 07:27 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-18 12:58 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-20 02:50 ` Wei Sun <936739278@qq.com>
2026-09-21 15:25 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-21 18:58 ` Andres Freund <andres@anarazel.de>
2026-09-22 02:15 ` Wei Sun <936739278@qq.com>
2026-09-25 01:23 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-25 03:05 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-25 08:37 ` Wei Sun <936739278@qq.com>
2026-09-25 14:05 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-10-02 20:34 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox