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

On Tue, 6 Oct 2026 at 18:29, Greg Burd <greg(at)burd(dot)me> wrote:
>
>
> 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.

Do you have the details on what was tested to get to these results?
And how it came to be that the results are 97% faster?

The index still produces the projected distance output, which
shouldn't be much cheaper for the index than a plan-level operator
evaluation. Assuming 1 projection per row inside the index (which is
reasonable for any ANN index I'm aware of) the theoretical savings are
at most 66% (= 1 - (2/3), because you save one projection for
xs_recheckorderby, and one projection for the plan node ORDER BY
output column; the one inside the index remains). Or did I miss
something -- is detoasting such an expensive factor here?

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)