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