Hi Andres, Haibo,
thanks for the explanations and for testing this.
On Wed, 14 Oct 2026 19:01:43 -0700, Haibo Yan wrote:
> With default alignment, the qual-only case improved latency by
> roughly 5-6%. With 64-byte alignment, however, the improvement
> disappeared.
I see the same here, only the direction differs per query. Same queries
as before, master vs patch, default alignment and -falign-functions=64
(i7-13700H, gcc 12):
instructions wall-clock wall-clock
default align 64
no qual, no projection -5.2% -10.9% -5.1%
with qual -4.8% -5.2% -2.8%
with projection -6.3% -4.4% -10.4%
So with 64-byte alignment the qual case shrinks like in your run, and
the projection case gets bigger. Your SeqScan control probably explains
it, the patch moves everything behind it in the binary. I'd rather go
by the instruction counts, those are stable and match between our two
machines.
> In particular, it would be interesting to see how much of the
> improvement survives with LTO/PGO enabled.
I tried -Db_lto=true (gcc 12, -flto=auto):
master+LTO patch+LTO diff
no qual, no projection 637,092,111 603,092,078 -5.3%
with qual 659,425,866 626,759,171 -5.0%
with projection 645,088,506 603,088,469 -6.5%
LTO alone gives about 2.7% on master for me, and the patch still saves
the same ~5% on top. nm on the LTO master binary still shows ExecScan()
and IndexOnlyNext() as seperate functions, so I guess LTO didn't inline
anything there, which would match what Andres said.
JIT: LLVM 15, jit=on, jit_above_cost=0, jit_optimize_above_cost=0. To
get the compile cost out of the callgrind numbers I ran the same
queries with 1 row and subtracted that. The patch then saves 34.0M /
32.7M / 42.0M instructions, the same as without JIT to within a hundred
instructions, which makes sense since it doesn't touch the JIT compiled
parts at all. Net of the compile that's -5.6% / -5.3% / -6.7%.
Wall-clock with JIT: -9.2%, -8.1%, -5.8%.
Andres mentioned nested loops, so I also tried that out: 1M outer rows,
inner index only scan on the pkey of the 5M row table, one match each.
Instructions -0.4% without and -0.6% with a filter on the inner side,
wall-clock within noise (~1.3 s per query, rounds vary by about 5%).
Makes sense I think, the lookup is ~9400 instructions and almost all of
it is rescan and btree, ExecScan() is only 30-50 of them.
> I'd appreciate your thoughts on whether the attached EPQ isolation
> test would be useful to include.
Yes, I think so. It passes here on master and with the patch, and CI on
my github fork is green with it. With a temporary elog() in
ExecIndexOnlyScanEPQ() I could see that it really goes through that
function. I attached it as 0001 of v3 with you as author, tell me if
this is the usual flow here.
Is there anything else you'd want to see tested or benchmarked before
this could move on? Other workloads, other compilers, more EPQ cases?
Happy to run more, I'm just not sure which cases matter most.
Regards,
Matthias
From 8857dbf0e0d16a3fca56e65bd9eeea726682f417 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Thu, 15 Oct 2026 05:37:56 +0000
Subject: [PATCH v3 1/2] Add isolation test for an index-only scan under
EvalPlanQual
No existing test ran an index-only scan inside an EPQ recheck. Add one
where the correlated subquery of an UPDATE has to be re-evaluated after
a concurrent update changed the outer keys, so that the index-only scan
runs in the EPQ estate with refreshed runtime keys, a qual and a
projection, including a NULL runtime key.
Author: Haibo Yan <[email protected]>
---
.../eval-plan-qual-index-only-scan.out | 23 ++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../specs/eval-plan-qual-index-only-scan.spec | 35 +++++++++++++++++++
3 files changed, 59 insertions(+)
create mode 100644 src/test/isolation/expected/eval-plan-qual-index-only-scan.out
create mode 100644 src/test/isolation/specs/eval-plan-qual-index-only-scan.spec
diff --git a/src/test/isolation/expected/eval-plan-qual-index-only-scan.out b/src/test/isolation/expected/eval-plan-qual-index-only-scan.out
new file mode 100644
index 00000000000..511c51cac05
--- /dev/null
+++ b/src/test/isolation/expected/eval-plan-qual-index-only-scan.out
@@ -0,0 +1,23 @@
+Parsed test spec with 2 sessions
+
+starting permutation: change_keys update_values commit_keys check_values
+step change_keys:
+ UPDATE epq_ios_target
+ SET k = CASE id WHEN 1 THEN 3 WHEN 2 THEN 2 ELSE NULL END;
+
+step update_values:
+ UPDATE epq_ios_target t
+ SET v = (SELECT k + 10 FROM epq_ios_lookup
+ WHERE k = t.k AND k % 3 <> 2)
+ WHERE id >= 1;
+ <waiting ...>
+step commit_keys: COMMIT;
+step update_values: <... completed>
+step check_values: SELECT * FROM epq_ios_target ORDER BY id;
+id|k| v
+--+-+--
+ 1|3|13
+ 2|2|
+ 3| |
+(3 rows)
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index bf9a2037c70..608643b0b62 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -44,6 +44,7 @@ test: subxid-overflow
test: eval-plan-qual
test: eval-plan-qual-trigger
test: eval-plan-qual-partition-prune
+test: eval-plan-qual-index-only-scan
test: inplace-inval
test: intra-grant-inplace
test: intra-grant-inplace-db
diff --git a/src/test/isolation/specs/eval-plan-qual-index-only-scan.spec b/src/test/isolation/specs/eval-plan-qual-index-only-scan.spec
new file mode 100644
index 00000000000..3b6f19863c4
--- /dev/null
+++ b/src/test/isolation/specs/eval-plan-qual-index-only-scan.spec
@@ -0,0 +1,35 @@
+# Run a correlated index-only scan in EPQ using the updated outer keys.
+# The three rows respectively need projection, fail the scan qual, and use
+# a NULL runtime key. All are rechecked in the same EPQ state tree.
+setup
+{
+ CREATE TABLE epq_ios_target (id int PRIMARY KEY, k int, v int);
+ CREATE TABLE epq_ios_lookup (k int PRIMARY KEY);
+ INSERT INTO epq_ios_target VALUES (1, 1, 0), (2, 1, 0), (3, 1, 0);
+ -- Enough rows to choose an index-only scan without planner overrides.
+ INSERT INTO epq_ios_lookup SELECT generate_series(0, 999);
+}
+setup { VACUUM ANALYZE epq_ios_lookup; }
+
+teardown { DROP TABLE epq_ios_target, epq_ios_lookup; }
+
+session s1
+setup { BEGIN; }
+step change_keys
+{
+ UPDATE epq_ios_target
+ SET k = CASE id WHEN 1 THEN 3 WHEN 2 THEN 2 ELSE NULL END;
+}
+step commit_keys { COMMIT; }
+
+session s2
+step update_values
+{
+ UPDATE epq_ios_target t
+ SET v = (SELECT k + 10 FROM epq_ios_lookup
+ WHERE k = t.k AND k % 3 <> 2)
+ WHERE id >= 1;
+}
+step check_values { SELECT * FROM epq_ios_target ORDER BY id; }
+
+permutation change_keys update_values(commit_keys) commit_keys check_values
--
2.39.5
From f00d911115efbbd5dff484f85df75abf4314c819 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Matthias=20D=C3=B6tsch?= <[email protected]>
Date: Tue, 13 Oct 2026 13:12:12 +0200
Subject: [PATCH v3 2/2] Specialize ExecIndexOnlyScan() like ExecSeqScan()
ExecIndexOnlyScan() went through the out-of-line ExecScan(), which
calls IndexOnlyNext() through a function pointer for every tuple and
checks for EPQ, qual and projection at runtime. As fb9f95502 and
83a53572a6f did for SeqScan, add ExecIndexOnlyScan*() variants that
pass const-NULLs to the inline ExecScanExtended() for whatever the node
doesn't need, and choose one in ExecInitIndexOnlyScan(). The EPQ case
keeps using ExecScan(). Mark IndexOnlyNext() as pg_always_inline so
that it gets inlined into each variant.
The runtime-key setup has to come before the pg_assume() calls,
because the compiler can't rely on them across the ExecReScan() call.
It would then keep the code for the no-qual, no-projection case too,
with a second copy of IndexOnlyNext().
In a 1M-row index-only range scan (gcc 12, -O2), executed instructions
drop by 5.2% without qual or projection, by 4.8% with a qual and by
6.3% with a projection (callgrind). Wall-clock time for the same
queries drops by 3-11%, but that varies by several percent with code
layout alone.
---
src/backend/executor/nodeIndexonlyscan.c | 145 ++++++++++++++++++++++-
1 file changed, 140 insertions(+), 5 deletions(-)
diff --git a/src/backend/executor/nodeIndexonlyscan.c b/src/backend/executor/nodeIndexonlyscan.c
index b1009027ac8..20d798e3e02 100644
--- a/src/backend/executor/nodeIndexonlyscan.c
+++ b/src/backend/executor/nodeIndexonlyscan.c
@@ -33,6 +33,7 @@
#include "access/genam.h"
#include "access/relscan.h"
#include "access/tableam.h"
+#include "executor/execScan.h"
#include "executor/executor.h"
#include "executor/instrument.h"
#include "executor/nodeIndexonlyscan.h"
@@ -50,7 +51,7 @@ static TupleTableSlot *IndexOnlyNext(IndexOnlyScanState *node);
* Retrieve a tuple from the IndexOnlyScan node's index.
* ----------------------------------------------------------------
*/
-static TupleTableSlot *
+static pg_always_inline TupleTableSlot *
IndexOnlyNext(IndexOnlyScanState *node)
{
EState *estate;
@@ -164,8 +165,28 @@ IndexOnlyRecheck(IndexOnlyScanState *node, TupleTableSlot *slot)
return false; /* keep compiler quiet */
}
+/*
+ * IndexOnlyScanSetupRuntimeKeys -- compute runtime keys if not done yet
+ */
+static pg_always_inline void
+IndexOnlyScanSetupRuntimeKeys(IndexOnlyScanState *node)
+{
+ /*
+ * If we have runtime keys and they've not already been set up, do it now.
+ */
+ if (node->ioss_NumRuntimeKeys != 0 && !node->ioss_RuntimeKeysReady)
+ ExecReScan((PlanState *) node);
+}
+
/* ----------------------------------------------------------------
* ExecIndexOnlyScan(node)
+ *
+ * Returns the next qualifying tuple from the index-only scan. This
+ * variant is used when there is no es_epq_active, no qual and no
+ * projection. As with the ExecSeqScan() variants, passing const-NULLs
+ * for these to ExecScanExtended() allows the compiler to eliminate the
+ * additional code that would ordinarily be required for the evaluation
+ * of these, and to inline IndexOnlyNext().
* ----------------------------------------------------------------
*/
static TupleTableSlot *
@@ -173,11 +194,104 @@ ExecIndexOnlyScan(PlanState *pstate)
{
IndexOnlyScanState *node = castNode(IndexOnlyScanState, pstate);
+ IndexOnlyScanSetupRuntimeKeys(node);
+
+ Assert(pstate->state->es_epq_active == NULL);
+ Assert(pstate->qual == NULL);
+ Assert(pstate->ps_ProjInfo == NULL);
+
+ return ExecScanExtended(&node->ss,
+ (ExecScanAccessMtd) IndexOnlyNext,
+ (ExecScanRecheckMtd) IndexOnlyRecheck,
+ NULL,
+ NULL,
+ NULL);
+}
+
+/*
+ * Variant of ExecIndexOnlyScan() but when qual evaluation is required.
+ */
+static TupleTableSlot *
+ExecIndexOnlyScanWithQual(PlanState *pstate)
+{
+ IndexOnlyScanState *node = castNode(IndexOnlyScanState, pstate);
+
+ IndexOnlyScanSetupRuntimeKeys(node);
+
/*
- * If we have runtime keys and they've not already been set up, do it now.
+ * Use pg_assume() for != NULL tests to make the compiler realize no
+ * runtime check for the field is needed in ExecScanExtended(). This has
+ * to come after IndexOnlyScanSetupRuntimeKeys(), as the compiler can't
+ * know that ExecReScan() leaves the field alone.
*/
- if (node->ioss_NumRuntimeKeys != 0 && !node->ioss_RuntimeKeysReady)
- ExecReScan((PlanState *) node);
+ Assert(pstate->state->es_epq_active == NULL);
+ pg_assume(pstate->qual != NULL);
+ Assert(pstate->ps_ProjInfo == NULL);
+
+ return ExecScanExtended(&node->ss,
+ (ExecScanAccessMtd) IndexOnlyNext,
+ (ExecScanRecheckMtd) IndexOnlyRecheck,
+ NULL,
+ pstate->qual,
+ NULL);
+}
+
+/*
+ * Variant of ExecIndexOnlyScan() but when projection is required.
+ */
+static TupleTableSlot *
+ExecIndexOnlyScanWithProject(PlanState *pstate)
+{
+ IndexOnlyScanState *node = castNode(IndexOnlyScanState, pstate);
+
+ IndexOnlyScanSetupRuntimeKeys(node);
+
+ Assert(pstate->state->es_epq_active == NULL);
+ Assert(pstate->qual == NULL);
+ pg_assume(pstate->ps_ProjInfo != NULL);
+
+ return ExecScanExtended(&node->ss,
+ (ExecScanAccessMtd) IndexOnlyNext,
+ (ExecScanRecheckMtd) IndexOnlyRecheck,
+ NULL,
+ NULL,
+ pstate->ps_ProjInfo);
+}
+
+/*
+ * Variant of ExecIndexOnlyScan() but when qual evaluation and projection are
+ * required.
+ */
+static TupleTableSlot *
+ExecIndexOnlyScanWithQualProject(PlanState *pstate)
+{
+ IndexOnlyScanState *node = castNode(IndexOnlyScanState, pstate);
+
+ IndexOnlyScanSetupRuntimeKeys(node);
+
+ Assert(pstate->state->es_epq_active == NULL);
+ pg_assume(pstate->qual != NULL);
+ pg_assume(pstate->ps_ProjInfo != NULL);
+
+ return ExecScanExtended(&node->ss,
+ (ExecScanAccessMtd) IndexOnlyNext,
+ (ExecScanRecheckMtd) IndexOnlyRecheck,
+ NULL,
+ pstate->qual,
+ pstate->ps_ProjInfo);
+}
+
+/*
+ * Variant of ExecIndexOnlyScan() for when EPQ evaluation is required. As
+ * with ExecSeqScanEPQ(), we don't bother adding variants of this for
+ * with/without qual and projection, and just use the out-of-line ExecScan().
+ */
+static TupleTableSlot *
+ExecIndexOnlyScanEPQ(PlanState *pstate)
+{
+ IndexOnlyScanState *node = castNode(IndexOnlyScanState, pstate);
+
+ IndexOnlyScanSetupRuntimeKeys(node);
return ExecScan(&node->ss,
(ExecScanAccessMtd) IndexOnlyNext,
@@ -368,7 +482,6 @@ ExecInitIndexOnlyScan(IndexOnlyScan *node, EState *estate, int eflags)
indexstate = makeNode(IndexOnlyScanState);
indexstate->ss.ps.plan = (Plan *) node;
indexstate->ss.ps.state = estate;
- indexstate->ss.ps.ExecProcNode = ExecIndexOnlyScan;
/*
* Miscellaneous initialization
@@ -416,6 +529,28 @@ ExecInitIndexOnlyScan(IndexOnlyScan *node, EState *estate, int eflags)
indexstate->recheckqual =
ExecInitQual(node->recheckqual, (PlanState *) indexstate);
+ /*
+ * Assign ExecProcNode based on whether EvalPlanQual is in use, and on the
+ * presence of qual and projection. Each ExecIndexOnlyScan*() variant is
+ * optimized for the specific combination of these conditions.
+ */
+ if (estate->es_epq_active != NULL)
+ indexstate->ss.ps.ExecProcNode = ExecIndexOnlyScanEPQ;
+ else if (indexstate->ss.ps.qual == NULL)
+ {
+ if (indexstate->ss.ps.ps_ProjInfo == NULL)
+ indexstate->ss.ps.ExecProcNode = ExecIndexOnlyScan;
+ else
+ indexstate->ss.ps.ExecProcNode = ExecIndexOnlyScanWithProject;
+ }
+ else
+ {
+ if (indexstate->ss.ps.ps_ProjInfo == NULL)
+ indexstate->ss.ps.ExecProcNode = ExecIndexOnlyScanWithQual;
+ else
+ indexstate->ss.ps.ExecProcNode = ExecIndexOnlyScanWithQualProject;
+ }
+
/*
* If we are just doing EXPLAIN (ie, aren't going to run the plan), stop
* here. This allows an index-advisor plugin to EXPLAIN a plan containing
--
2.39.5
