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 <[email protected]> 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 >
