Re: Avoid computing ORDER BY junk columns unnecessarily

On 22/12/2023 17:24, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>> It won't
>> help in all cases though, the index might not store the original value
>> in the first place.
>
> I'm a little skeptical that an index could produce an accurate ORDER BY
> result if it doesn't store the values-to-be-sorted exactly. Any loss
> of information would compromise its ability to sort nearly-identical
> values correctly.

In the context of pgvector, its ordering is approximate anyway. Aside
from that, there's one trick that it implements: it compares squares of
distances, avoiding a sqrt() calculation. (I wonder if we could do the
same in GiST opclasses)

> A more credible argument is that the index might
> expose amcanorder ability but not amcanreturn; but what I'm saying is
> that that's probably an AM implementation gap that ought to be fixed.
>
> How much of your patchset still makes sense if we assume that we
> can always extract the ORDER BY column values from the index?

That would make it much less interesting. But I don't think that's a
good assumption. Especially in the kNN case, the ORDER BY value would
not be stored in the index. Most likely the index needs to calculate it
in some form, but it might take shortcuts like avoiding the sqrt().

--
Heikki Linnakangas
Neon (https://neon.tech)