Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Reverse-sort indexes and NULLS FIRST/LAST sorting
Date: 2007-01-02 14:37:34
Message-ID: 3105.1167748654@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> Issues which you havn't addressed are:

> - Pathkeys: How is the forward/reverse/nulls first/last going to be
> encoded in the pathkey?

I'm envisioning a struct with operator OID and null-ordering flag.
If we implement the explicit REVERSE variant then we'd have to be
prepared to match the OID against either the LessThan or GreaterThan
member of an index opclass depending --- it wouldn't add a third
field to pathkeys.

> But that leaves you with
> deciding whether to keep support for SORT_LT/GTFUNC?

I'm kind of inclined to drop the LTFUNC/GTFUNC business and insist that
every operator used as a sort operator must be a btree opclass member.
But that decision seems entirely orthogonal to the rest of it.

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

That's right, you can't.  Just like any other ordering incompatibility.

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

This doesn't work at all unless you make all the operators non-strict,
which I hardly think we want.  Also, that path leads to needing FOUR
opclasses per datatype to support all the orderings :-(

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-01-02 14:47:06
Subject: Re: Reverse-sort indexes and NULLS FIRST/LAST sorting
Previous:From: Neil ConwayDate: 2007-01-02 14:19:50
Subject: Re: float8 width_bucket function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group