Re: Reverse-sort indexes and NULLS FIRST/LAST sorting

On Mon, Jan 01, 2007 at 05:53:35PM -0500, Tom Lane wrote:
> The SQL2003 spec adds optional "NULLS FIRST" and "NULLS LAST" modifiers
> for ORDER BY clauses. Teodor proposed an implementation here:
> http://archives.postgresql.org/pgsql-patches/2006-12/msg00019.php
> which I didn't care for at all:
> http://archives.postgresql.org/pgsql-hackers/2006-12/msg00133.php

<snip>

> One way we could handle this is to say that reverse-sort indexes are
> implemented by adding explicit catalog entries for reverse-sort opclasses,
> with no additions to the underlying btree index mechanisms. So you
> might make an index using a command like
>
> CREATE INDEX fooi ON foo (x, y reverse_int4_ops);

Personally I favour this approach. It's also the approach similar to
what I did with the COLLATE stuff. It's IMHO the cleanest because it
encapsulates the order at the level where it's important.

In particular, NULLS FIRST/LAST makes sense for btree, but no other
index type, so storing the order seperatly is wasted space for any
other index type.

But in a sense this doesn't go far enough. In general a column can be
ordered four ways, and like you say later, it doesn't allow mixed NULLS
FIRST/LAST orderins.

> The other way that seems like it could win acceptance is to make REVERSE
> an explicit optional property of an index column; and if we do that we
> might as well allow NULLS FIRST/LAST to be an optional property as well.
> Then you could say something like
>
> CREATE INDEX fooi ON foo (x, y REVERSE NULLS FIRST);

While the syntax is nice, I think this method of implementation is a
bad idea. Like I said it's wasted processing for non-btree index types.

Issues which you havn't addressed are:

- Pathkeys: How is the forward/reverse/nulls first/last going to be
encoded in the pathkey? I don't think the current method (using the
operator OID) is going to stretch far enough. But that leaves you with
deciding whether to keep support for SORT_LT/GTFUNC?

- How do you deal with people asking for NULLS FIRST/LAST which is the
opposite of how the index is defined. Say you can't use the index?

> Comments? I've got mixed feelings about which way to jump myself.

Somehow neither is quite satisfying. My COLLATE patch solved it by
adding an extra layer on top of the operator classes to encode the
ordering nulls first/last, but I don't think we really want that.

One totally whacked out idea is to allowed the btree code to call the
operator to decide nulls first/last, that would allow you to factor
that part out at least.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.