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(at)officenet(dot)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(at)officenet(dot)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 | |
------------------------+---------------------------------------------+