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

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(at)qq(dot)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(dot)cs(at)gmail(dot)com>
> 发件时间：2026年9月15日 18:49
> 收件人：Wei Sun <936739278(at)qq(dot)com>
> 抄送：pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
> 主题：Re: Severe performance degradation with concurrent updates due to
> excessive EvalPlanQual (EPQ) re‑evaluation
>
> Hi 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(dot)cs(at)gmail(dot)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(at)qq(dot)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
>
>
>