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