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

On Monday, October 5th, 2026 at 2:14 PM, Greg Burd <greg(at)burd(dot)me> wrote:
>
> I've not yet measured performance or memory difference due to this change,
> net should be better but I should quantify that at some point.

I've run a test in EC2 to answer this question, details at the end but this
change is significant for some important shapes. For a cheap operator
(point <-> point, 100k rows) the patch is 3-6% faster. The slot fetch is
cheaper than re-evaluating hypot(). For an expensive one it removes the
re-evaluation entirely: a 128-dim float8[] distance over 10k rows goes from
197 ms to 4 ms, a plpgsql expression index from 139 ms to 8 ms. box_ops is a
wash (+0.1%). The circle_ops control, which doesn't qualify and gets no
rewrite, is unchanged (-0.9%, within the 1% stdev), so an opclass that
answers no pays nothing.

What this means is a) this is a good performance win, and b) I now need to see
if it makes sense to adjust the costing of this pattern given that some shapes
are ~97% faster now. For instances, a vec_10k the plan costs ~50× what it
actually does with this fix in place. I'll poke around and see if I come up
with something that avoids tipping the decision in the wrong direction (away
from an index scan) and add a patch in if I do.

best.

-greg

Results
=======

c7i.4xlarge (Xeon 8488C, 1 NUMA node), AL2023, gcc 11.5, -O2, no cassert.
master e8f4f9e3ce2 vs v3. 1M-row tables, fully prewarmed, shared_buffers=4GB,
JIT off, parallelism off, server+client pinned to one core, A/B alternated
per-run, 11 runs each, median. Plans verified: every patched query shows
Order By Values Used: 1 except the circle control as expected.

query ORDER BY key master patched delta
(ms) (ms)
----------------- ----------------------------- ------- -------- -------
gist_point_1k p <-> pt 2.08 2.01 -3.2%
gist_point_100k p <-> pt 70.58 66.18 -6.2%
spg_point_100k p <-> pt (SP-GiST) 75.07 70.64 -5.9%
gist_box_100k b <-> pt 68.49 68.58 +0.1%
gist_circle_100k c <-> pt (lossy, no rewrite) 117.93 116.88 -0.9%
slow_1k plpgsql expr index (~13us) 15.64 2.26 -85.6%
slow_10k plpgsql expr index (~13us) 139.17 8.14 -94.2%
vec_1k 128-dim float8[] L2 distance 20.12 0.59 -97.1%
vec_10k 128-dim float8[] L2 distance 197.38 4.16 -97.9%

Stdev <= 3.3% for all rows.