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

Hi Greg,

I tested your patch with a ranked GIN prototype - essentially GIN with
RUM-like ordering, where the index scan computes the BM25 score and returns
tuples in score order.
This is a very natural use case for amcanreturnorderby: the index already
has the exact score, so there is no reason to calculate the same expression
again above the index scan.
With the patch, the score produced by the index is reused directly. On my
tests this removes the redundant score calculation and gives about 1.7 -
2.2x speedup for full scans, and about 2x on larger toasted documents.
The costing change is useful too. Without it, the planner can still charge
the ordered index scan for evaluating an expression which the index has
already evaluated, and may choose a Seq Scan + Sort instead. With the patch
this distinction is visible to the planner.
One remaining issue on my side is that the cost of BM25 scoring depends
heavily on document size/detoasting, so a single static function COST is
not enough. But that seems like a separate costing problem.
So at least for ranked GIN/RUM-style access methods, this patch provides
exactly the missing interface between the index AM and executor.

Thanks, Oleg

On Wed, Oct 7, 2026, 18:45 Greg Burd <greg(at)burd(dot)me> wrote:

> 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
>
>
>
>