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

Hi

Thanks a lot for your investigation and the diagnostic patch. 
I agree that the evidence points to the EPQ / concurrent-update path, 
rather than treating the whole UPDATE statement as a single atomic operation.

I think this heavy EPQ re-evaluation on every conflicting tuple is the expected execution behavior under Read Committed isolation.

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

Initially, I proposed the above idea.This idea is likely to violate the Read Committed isolation.
This idea is probably not feasible.

However, I'd like to share a few observations and directions that might be worth investigating,
from the perspective of *how* the EPQ recheck is implemented rather than *whether* it should exist:

**1. The subquery is re-executed for every tuple, even though it reads from a different table.**

In the reproducer, the WHERE clause is:

```
WHERE deal_no IN (SELECT deal_no FROM bond_deal_detail ORDER BY deal_no LIMIT 100000)
```

The subquery reads from `bond_deal_detail`, which is a different table that is *not* being updated by the concurrent transaction.
Yet from what you traced, `EvalPlanQualStart()` duplicates all InitPlan / SubPlan node trees, and `EvalPlanQualNext()` re-runs the EPQ plan tree via `ExecProcNode()`. 
This means the same subquery — which returns a fixed set of 100,000 `deal_no` values — gets re-executed **once per conflicting tuple**, i.e. 100,000 times.

I wonder whether there's room to identify when a SubPlan references only *other* relations that are not being modified by the current UPDATE, and therefore its result is invariant across EPQ calls — allowing it to be computed once and cached, rather than re-initialized and re-executed for every tuple.But this also involves whether the data in *other* relations has been modified.I don't have a good idea yet. If I make any new progress, I will continue to share it with you.

Regards,
Wei Sun

原始邮件


发件人：Osama Abdul Qader <osamaabdulqader(dot)cs(at)gmail(dot)com&gt;
发件时间：2026年9月18日 20:58
收件人：Wei Sun <936739278(at)qq(dot)com&gt;
抄送：pgsql-hackers <pgsql-hackers(at)postgresql(dot)org&gt;
主题：Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation

Hi again,

I have been investigating the reported regression involving UPDATE execution and EvalPlanQual (EPQ), and I wanted to share my findings.

I reproduced the issue locally using the bond_deal_detail&nbsp;/ bond_deal_detail_sw&nbsp;reproducer and traced the relevant execution path through the executor.

The affected path in nodeModifyTable.c&nbsp;is the TM_Updated&nbsp;case in ExecUpdate(). When the tuple has been concurrently updated, PostgreSQL:

1. The affected path in nodeModifyTable.c&nbsp;is the TM_Updated&nbsp;case in ExecUpdate(). When the tuple has been concurrently updated, PostgreSQL:

2. runs EvalPlanQual()&nbsp;to recheck the updated tuple against the query's qualifications;

3. continues with the update using the EPQ result.

I also traced the EPQ implementation in execMain.c, including&nbsp;EvalPlanQual(),&nbsp;EvalPlanQualSlot(),&nbsp;EvalPlanQualNext(),&nbsp;EvalPlanQualBegin(),&nbsp;EvalPlanQualStart(),&nbsp;EvalPlanQualEnd().

In particular, EvalPlanQualNext()&nbsp;switches to the EPQ query context and invokes ExecProcNode()&nbsp;on the EPQ plan tree. EvalPlanQualStart()&nbsp;creates a child EState, initializes the required subplans, and initializes the EPQ plan tree with ExecInitNode().

To get more concrete timing information, I temporarily instrumented the TM_Updated&nbsp;path in nodeModifyTable.c&nbsp;to measure table_tuple_lock()&nbsp;and EvalPlanQual()&nbsp;separately. The diagnostic patch is attached as:&nbsp;epq-instrumentation.patch

The instrumentation produces separate log entries of the form:

EPQ DEBUG: table_tuple_lock took ... ms EPQ DEBUG: EvalPlanQual took ... ms

This allowed me to distinguish the time spent acquiring/fetching the latest tuple version from the time spent executing the EPQ recheck itself.

The relevant source path is approximately:

ExecUpdate() &nbsp;-&gt; table_tuple_lock() &nbsp;-&gt; EvalPlanQual() &nbsp; &nbsp; &nbsp; -&gt; EvalPlanQualBegin() &nbsp; &nbsp; &nbsp; -&gt; EvalPlanQualNext() &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; ExecProcNode()

I have also verified the patch with git diff --check.

At this point, I believe we have enough evidence to narrow the investigation to the EPQ/concurrent-update path rather than treating the overall UPDATE runtime as a single operation. I would appreciate your thoughts on whether this is the expected execution behavior, and whether there are particular executor or EPQ areas you would recommend investigating next.

I have attached the diagnostic patch for reference.

Best Regards:
Osama Abdul Qader

On Wed, Sep 16, 2026 at 12:57 PM Osama Abdul Qader <osamaabdulqader(dot)cs(at)gmail(dot)com&gt; wrote:
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&nbsp;UPDATE path around table_tuple_lock()&nbsp;and EvalPlanQual()&nbsp;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 <936739278(at)qq(dot)com&gt; wrote:
Hi

Thanks for your reply.

At first, I suspected that for every row with an update conflict,&nbsp;
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&nbsp;will have&nbsp;
an impact on the degree of slowing down. especially when the subquery&nbsp;
needs to write temporary files, the slowdown will be even more severe.

I am currently trying to create different scenarios locally,&nbsp;
and if I make any new discoveries, I will also synchronize with you.

Regards,
Wei Sun

原始邮件

发件人：Osama Abdul Qader <osamaabdulqader(dot)cs(at)gmail(dot)com&gt;
发件时间：2026年9月15日 18:49
收件人：Wei Sun <936739278(at)qq(dot)com&gt;
抄送：pgsql-hackers <pgsql-hackers(at)postgresql(dot)org&gt;
主题：Re: Severe performance degradation with concurrent updates due to excessive EvalPlanQual (EPQ) re‑evaluation

Hi again,&nbsp;

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&nbsp;UPDATE path around table_tuple_lock()&nbsp;and EvalPlanQual()&nbsp;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&gt; wrote:
Hi,&nbsp;

I'm interested in working on the bug, I'll let you know once I finish reproducing it.

With Regards,&nbsp;
Osama Abdul Qader

On Tue, Sep 15, 2026 at 1:36 PM Wei Sun <936739278(at)qq(dot)com&gt; 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 (
&nbsp; &nbsp; id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
&nbsp; &nbsp; deal_no TEXT,
&nbsp; &nbsp; bond_code TEXT,
&nbsp; &nbsp; bond_name TEXT,
&nbsp; &nbsp; trade_date DATE,
&nbsp; &nbsp; trade_time TIME,
&nbsp; &nbsp; buy_inst TEXT,
&nbsp; &nbsp; sell_inst TEXT,
&nbsp; &nbsp; deal_amt NUMERIC(20,4),
&nbsp; &nbsp; deal_price NUMERIC(12,6),
&nbsp; &nbsp; yield_rate NUMERIC(10,6),
&nbsp; &nbsp; trade_type TEXT,
&nbsp; &nbsp; settle_date DATE,
&nbsp; &nbsp; create_at TIMESTAMP
);

INSERT INTO bond_deal_detail(
&nbsp; &nbsp; deal_no, bond_code, bond_name, trade_date, trade_time,
&nbsp; &nbsp; buy_inst, sell_inst, deal_amt, deal_price, yield_rate,
&nbsp; &nbsp; trade_type, settle_date, create_at
)
SELECT
&nbsp; &nbsp; 'DEAL' || LPAD(i::TEXT,10,'0'),
&nbsp; &nbsp; '10' || LPAD((i % 99999)::TEXT,8,'0'),
&nbsp; &nbsp; 'SimBond_' || (i % 2000),
&nbsp; &nbsp; '2025-01-01'::DATE + (i % 365),
&nbsp; &nbsp; ('09:00:00'::TIME + (i % 32400) * INTERVAL '1 second'),
&nbsp; &nbsp; 'Inst_' || (i % 1500),
&nbsp; &nbsp; 'Inst_' || ((i + 777) % 1500),
&nbsp; &nbsp; (random() * 500000000)::NUMERIC(20,4),
&nbsp; &nbsp; (90 + random() * 20)::NUMERIC(12,6),
&nbsp; &nbsp; (1.5 + random() * 3.5)::NUMERIC(10,6),
&nbsp; &nbsp; CASE WHEN i % 5 = 0 THEN 'Repo' ELSE 'SpotBond' END,
&nbsp; &nbsp; '2025-01-01'::DATE + (i % 365) + (CASE WHEN i%5=0 THEN 1 ELSE 0 END),
&nbsp; &nbsp; 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,&nbsp;
Wei Sun