From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vincent-Olivier Arsenault <vincent@up4c.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: order by x DESC, y ASC indexing problem
Date: Thu, 03 Oct 2002 23:14:43 -0400
Message-ID: <16871.1033701283@sss.pgh.pa.us> (raw)
In-Reply-To: <3D9CF7EC.8080102@up4c.com>
References: <3D9CF7EC.8080102@up4c.com>
Vincent-Olivier Arsenault <vincent@up4c.com> writes:
> How to have the planner use an index in the case of a query like :
> SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC;
A normal index on (X,Y) is useless for this query, because neither
scan direction in the index corresponds to the sort ordering you
are asking for.
In theory you could build a custom "reverse sort order" operator
class for X's datatype, and then make an index using the reverse
opclass for X and the normal opclass for Y. Or the other way
round (normal sort order for X and reverse for Y).
In practice, as Josh notes nearby, this is a waste of time for
the query as given: whole-table sorts usually are better done
by sorting not by indexscanning. If you are doing a partial scan
like
SELECT ... ORDER BY ... LIMIT some-small-number
then it might be worth the trouble to set up a custom-order index.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, vincent@up4c.com
Subject: Re: order by x DESC, y ASC indexing problem
In-Reply-To: <16871.1033701283@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox