Re: knngist - 0.8

On Fri, Oct 15, 2010 at 08:45:26PM -0400, Robert Haas wrote:
> But I'm also not sure how far this gets us with KNNGIST, where the
> issue is not the typmods but the auxilliary information about the
> context of the sort and/or whether this is a sort or qual.

ISTM there are two issues here. With Btree you have the issue where the
index is of a particular collation and at planning time you're given a
collation and either it's compatable or it's not.

With Gist you have the situation where index isn't of a specific
collation but it can be used in different ways to get different
collection as output. (I'm here thinking of that Gist can in some
circumstance be asked to return tuples in a certain order.)

Actually, NULLS FIRST/LAST, ASC/DESC are just variations on collations
and my original patch many years back for each locale made *four*
collation IDs, one for each of the combinations of the above. Not
terribly scalable, but I never did get the planning code to work. :)

What you're going for here I think is a sort of cross-collation
operators, or something, that tells the planner how to get the
particular collation out of the given index. So you have something
like:

Input: en_US, NULLS FIRST, ASC
Output: en_US, NULLS_LAST, DESC
-> Reverse index scan

Input: en_US, NULLS FIRST, ASC
Output: en_US, NULLS LAST, ASC
-> Index scan, add x IS NOT NULL test
-> Append output of x IS NULL.

Input: Gist
Output: KnnGist
-> Index scan with scan type 10000

Input: Gist
Output: Gist
-> Index scan with scan type 0

Obviously this doesn't solve the problem of being able to represent the
required collation in the first place, and if this is at all compatable
with what the SQL standard calls a collation. I just don't think you
can make hard and fast rules about how to make this work and that
perhaps we should be looking for a way to push that to the index
implementation code, with the default rule being: same collection yes,
different no.

Just some thoughts,

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
> - Charles de Gaulle