Perf: Specialize ExecIndexOnlyScan() like ExecSeqScan()

On Wed, Oct 14, 2026 at 3:37 AM Matthias Dötsch <matze@mdoetsch.de> wrote:

Hey,

this is my first patch for Postgres, so please let me know if I got
something about the contribution policies wrong.

Our main workload does a lot of index-only scans, and in perf profiles of it
a good part of the time showed up under ExecScan(). Digging into the
code I noticed that IndexScan, IndexOnlyScan and BitmapHeapScan all
still go through the generic out-of-line ExecScan(). So each tuple pays
for an indirect call to the access method and for the runtime EPQ /
qual / projection checks. SeqScan got rid of that with fb9f95502 (and
83a53572a6f made sure SeqNext actually gets inlined).

The attached patch does the same for index-only scans. There are now
ExecIndexOnlyScan, ...WithQuals, ...WithProject, ...WithQualProject and
...EPQ, the variant is picked in ExecInitIndexOnlyScan(), and
IndexOnlyNext() is pg_always_inline. The EPQ variant just calls
ExecScan() like ExecSeqScanEPQ does.

One thing that cost me bit more time: the runtime key setup (which may call
ExecReScan()) has to happen before the pg_assume() calls. If it comes
after, gcc can't know that pstate->qual is still non-NULL after the
call, keeps the !qual && !projInfo branch of ExecScanExtended() and you
end up with a second inlined copy of IndexOnlyNext() in each variant.
I added a comment for that.

Test setup: table with id int primary key and two more int columns, 5M
rows, vacuumed so it's all-visible. enable_seqscan and enable_bitmapscan
off, jit off, no parallel. gcc 12, -O2, i7-13700H.

instructions latency
SELECT count(*) FROM t
WHERE id BETWEEN 1 AND 1000000 -5.2% -5 to -11%
same, plus AND id % 3 = 0 -4.8% -3 to -5%
SELECT id + 1 FROM t
WHERE id BETWEEN 1 AND 1000000 OFFSET 2000000 -6.3% -4 to -10%

Instructions are from callgrind (single user mode), so those are kinda
deterministic. Latency is median pgbench latency over 5 interleaved
rounds, server pinned to one P-core. It's a range because I ran
everything a second time with -falign-functions=64 and the wall clock
results moved by several percent just from the the layout change. So I
wouldn't put too much weight on the exact latency numbers, but it was
faster in all cases.

tbh the code gets bigger: the per-tuple function is 576-858 bytes depending
on the variant, and the IOS functions together go from ~0.6kB to
~3.5kB.

Tests: regress and isolation pass with cassert, and CI on my github
fork is green on all platforms. There is no existing test that runs an
index-only scan under EPQ though. I checked that path manually with a
correlated subquery in an UPDATE that has to wait for a concurrent
update. I can turn that into an isolation test if people think it's
worth it.

any thoughts?

Best,
Matthias

Hi Matthias,

This looks like a natural extension of the ExecSeqScan specialization
work, and the reduction in instruction count is encouraging.

However, I wonder whether we should evaluate the broader tradeoffs
before introducing more hand-specialized executor variants. While
the optimization makes sense in isolation, I'm less certain about
its long-term benefits relative to the additional code complexity
and maintenance cost.

In particular, it would be interesting to see how much of the
improvement survives with LTO/PGO enabled. Both can help with
inlining and code layout, potentially reducing the benefit of
manual specialization. It would also be useful to measure the
incremental gain with JIT expression compilation enabled, even
though PostgreSQL's current JIT does not cover the scan loop itself.

I'm also concerned about the cumulative code-size cost. The
increase from roughly 0.6 KB to 3.5 KB may be acceptable for
IndexOnlyScan alone, but applying the same approach to IndexScan,
BitmapHeapScan, and other nodes could have broader implications
for instruction-cache locality and maintainability.

Finally, with ongoing work on batched execution and index access,
I think it would be worth understanding whether these optimizations
remain complementary or primarily target the same per-tuple
overhead. In particular, if batching amortizes executor dispatch
and function-call costs, how much additional benefit would
specialization provide?

I'm not suggesting that we should avoid this optimization. The
existing ExecSeqScan work provides a reasonable precedent, and
the results so far are promising. I'd just like to see a clearer
picture of the incremental performance benefit and the longer-term
maintenance implications before extending this pattern further.

Thanks,
Haibo

Hi,

On 2026-10-14 11:12:57 -0700, Haibo Yan wrote:

However, I wonder whether we should evaluate the broader tradeoffs
before introducing more hand-specialized executor variants. While
the optimization makes sense in isolation, I'm less certain about
its long-term benefits relative to the additional code complexity
and maintenance cost.

In particular, it would be interesting to see how much of the
improvement survives with LTO/PGO enabled. Both can help with
inlining and code layout, potentially reducing the benefit of
manual specialization.

I really don't see how it could obsolete the improvement in the patch (it
could help at the same time obviously, but the patch should improve LTO and
non-LTO). The compiler has no way of knowing that estate->es_epq_active,
indexstate->ss.ps.qual, indexstate->ss.ps.ps_ProjInfo don't change during
query execution, that's something only we know.

It's possible that that some LTO implementations would end up inlining
ExecSeqScan() and then convert the indirect function call to IndexOnlyNext()
into a [potentially guarded] direct function call. But that's only a
relatively small portion of the benefit of the specializations!

There's is *some* support for speculative assumptions with PGO informed LTO,
but to my knowledge that's for stuff like speculative devirtualization. I
don't think it'd apply to anything like what we're dealing with here. But even
if it did, it'd not help with specializations, as the problem is having to
check all those branches in every IndexOnlyNext(), which in turn require a
bunch of loads.

It would also be useful to measure the
incremental gain with JIT expression compilation enabled, even
though PostgreSQL's current JIT does not cover the scan loop itself.

I'm also concerned about the cumulative code-size cost. The
increase from roughly 0.6 KB to 3.5 KB may be acceptable for
IndexOnlyScan alone, but applying the same approach to IndexScan,
BitmapHeapScan, and other nodes could have broader implications
for instruction-cache locality and maintainability.

Of course we shouldn't go overboard, but I'd expect this to *increase* icache
locality in a broad range of use cases. Postgres' binary is way too big and
our execution paths way too "wide" for relatively small increases in code size
to harm meaningfully, when the same changes make the amount of code executed
smaller in almost all cases. E.g. the EPQ handling code is never required in
hot execution paths, and keeping that on separate cachelines will *increase*
the actual icache effectiveness.

Finally, with ongoing work on batched execution and index access,
I think it would be worth understanding whether these optimizations
remain complementary or primarily target the same per-tuple
overhead. In particular, if batching amortizes executor dispatch
and function-call costs, how much additional benefit would
specialization provide?

I think batching in ExecIndexOnlyScan() is unlikely to be very generally
applicable anytime soon, because it's so often used in contexts like nested
loops, where - unless you have a much more complicated join implementation -
you will only want to fetch a single tuple on the inner side.

I also think we're not that close to actually getting the batched execution
stuff.

And more importantly, even with the batched execution, and a lot of additional
improvements, you'd *still* want specialization for having a qual, EPQ,
projection, because checking them inside the loop over the tuples in the batch
makes no sense. The only thing that'd actually be amortized is the indirect
function call to *Next().

Greetings,

Andres Freund