From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Re: Index usage in order by with multiple columns in order-by-clause
Date: Sat, 11 Aug 2007 11:22:08 +0200
User-Agent: KMail/1.9.6
Cc: Tom Lane <tgl@sss.pgh.pa.us>
References: <200708101653.13011.andreak@officenet.no>
	<14180.1186781414@sss.pgh.pa.us>
In-Reply-To: <14180.1186781414@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200708111122.08235.andreak@officenet.no>

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 sortin=
g?
>
> 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=20
sorted" in 8.2 (and 8.3)? This may be *the* reason to upgrade for me if 8.3=
=20
is better at this.

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