Re: Let an ordering index scan hand its ORDER BY value to the target list

On Wednesday, October 7th, 2026 at 8:59 AM, Greg Burd <greg(at)burd(dot)me> wrote:
> I'll rerun with the harness fixed and post the corrected numbers.

Here they are. The gist has a new revision with the harness, every
sample and every plan [1]; the old revision is still in its history.

What changed in the harness: one vector per row, AB/BA order randomized
per block, both builds run on copies of one data directory, a working
pg_prewarm, and each patch measured against its own parent commit. It
also fails on any error, and each patched build's values are checked
against the operator before anything is timed. Two sessions, 21
blocks each for the executor and 11 for the planner, c7i.4xlarge. The
95% intervals below are over the pooled blocks.

0001, executor, same plan for both:

expression index, SQL 128-d dist., LIMIT 10k -93.6% [-93.6, -93.5]
" LIMIT 1k -88.1% [-88.2, -88.0]
expression index, plpgsql, LIMIT 10k -92.2% [-92.3, -92.2]
" LIMIT 10 -53.4% [-53.9, -52.8]
box <-> point, boxes rarely contain the point -27.1% [-27.6, -26.5]
point <-> point, GiST, LIMIT 100k -2.2% [ -3.1, -1.3]
point <-> point, SP-GiST, LIMIT 100k -1.0% [ -1.7, -0.4]
box <-> point, 1/4 contain the point +0.3% [ -0.7, +1.2]
circle_ops (doesn't qualify), control +0.1% [ -0.4, +0.6]

The vector rows were -97/-98% on the bad data; with real vectors they
are -88/-94%. I'm withdrawing the -3 to -6% I gave for points. The
0003 vs 0004 builds run identical code for queries whose plan 0004
doesn't change, and still differ by 2.8-3.7%, so a couple of percent
between two builds isn't evidence of anything. Points and the control
are within that. The box result is new. dist_bp() returns 0 at once
for a box that contains the point, so the earlier tie-heavy data could
not show any saving.

0002, default settings, plan flips from Sort to the kNN scan:

grp < 50 -90.3% grp < 10 -51.8%
grp < 20 -76.2% grp < 5 +1.5% [+0.6, +2.5]

grp < 5 sits at the crossover. The kNN plan touches about 200k buffers
to Sort's 1.7k, the estimate is optimistic there, and the flip gains
nothing. That's within the build noise, but it's the worst case I
found.

0003, below a join: -44% to -90% where the plan flips from Sort to
the kNN nested loop, and -93.5% at region < 9, where the plan was
already kNN and the join now reads the value instead of evaluating it.
Selective filters keep the Sort plan, as before. Timing both shapes
under one binary confirms the planner's choice at both ends: region < 4
kNN 159 ms vs Sort 1047 ms, cat < 3 Sort 17 ms vs kNN 152 ms.

0004: the estimate is fixed, but Heikki's exact query still picks Seq
Scan + Sort, as I said in the v5 mail.

Planning time differs by at most 0.02 ms in any pair.

[1] https://gist.github.com/gburd/3f6acd95583845f4924a548167a58e07

best.

-greg