Re: Index usage in order by with multiple columns in order-by-clause

On Friday 10 August 2007 23:30:14 Tom Lane wrote:
> Andreas Joseph Krogh <andreak@officenet.no> writes:
> > Is there a way I can have multiple columns in the ORDER BY clause, each
> > with different ASC/DESC-order and still use an index to speed up sorting?
>
> A btree index isn't magic, it's just an ordered list of entries.  So you
> can't just randomly flip the ordering of individual columns.  For
> instance, the natural sort order of a 2-column index on (x,y) is like
>
>     x    y
>
>     1    1
>     1    2
>     1    3
>     2    1
>     2    2
>     2    3
>     3    1
>     3    2
>     3    3
>
> If you scan this index forwards, you get the equivalent of
>     ORDER BY x ASC, y ASC
> If you scan it backwards, you get the equivalent of
>     ORDER BY x DESC, y DESC
> But there is no way to get the equivalent of x ASC, y DESC from
> a scan of this index, nor x DESC, y ASC.
>
> If you have a specific requirement for one of those combinations,
> what you can do is build an index in which one of the columns is
> "reverse sorted".  For instance, if we reverse-sort y, the index
> ordering looks like
>
>     x    y
>
>     1    3
>     1    2
>     1    1
>     2    3
>     2    2
>     2    1
>     3    3
>     3    2
>     3    1
>
> Now we can get ORDER BY x ASC, y DESC from a forwards indexscan,
> or ORDER BY x DESC, y ASC from a backwards scan.  But there's no
> way to get ASC/ASC or DESC/DESC from this index.  If you really need
> all four orderings to be available, you're stuck with maintaining
> two indexes.
>
> Reverse-sorted index columns are possible but not well supported in
> existing PG releases (you need a custom operator class, and the planner
> is not all that bright about using them).  8.3 will have full support.

Thank you for your in-depth reply (a always)!

How exactly do I build an index in which one of the columns is "reverse
sorted" in 8.2 (and 8.3)? This may be *the* reason to upgrade for me if 8.3
is better at this.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst Allé 11      | know how to do a thing and to watch         |
PO. Box 529 Skøyen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
Fax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+