Re: order by x DESC, y ASC indexing problem

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
likeSELECT ... ORDER BY ... LIMIT some-small-number
then it might be worth the trouble to set up a custom-order index.
        regards, tom lane