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

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
>