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 <[email protected]> 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 <[email protected]> > 发件时间：2026年9月15日 18:49 > 收件人：Wei Sun <[email protected]> > 抄送：pgsql-hackers <[email protected]> > 主题：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 < > [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 > > >
