Postgres Pro
Downloads
Home   >   mailing lists

Thread: order by x DESC, y ASC indexing problem

order by x DESC, y ASC indexing problem

From
Vincent-Olivier Arsenault
Date:
Hello,

I saw a posting regarding this issue in august, with no solution...

How to have the planner use an index in the case of a query like :

SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC;

(X is a date and Y a varchar)

What would that index be?

Is there a function I can use, to invert x (the date), so that I can 
make a query / index set like :



CREATE INDEX INDEX1 ON TABLE 1 (INVERT(X), Y ASC);
SELECT * FROM TABLE1 ORDER BY INVERT(X) ASC, Y ASC;


Wouldn't it be great to have a mySQL, SAPDB-like syntax of the sort :

CREATE INDEX INDEX1 ON TABLE 1 (X DESC, Y ASC);


Thanks,


vincent

Re: order by x DESC, y ASC indexing problem

From
"Josh Berkus"
Date:
Vincent,

> SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC;
> 
> (X is a date and Y a varchar)
> 
> What would that index be?
> 
> Is there a function I can use, to invert x (the date), so that I can
> make a query / index set like :

PostgreSQL's sorting mechanism can use an index either ascending or
descending; it does not make a difference.

However, most of the time ORDER BY does not use an index at all because
a seq scan is faster.   Usually, ORDER BY  uses an index only when
combined with related criteria and/or the LIMIT clause.

-Josh Berkus

Re: order by x DESC, y ASC indexing problem

From
Bruno Wolff III
Date:
On Thu, Oct 03, 2002 at 22:07:40 -0400, Vincent-Olivier Arsenault <vincent@up4c.com> wrote:
> Hello,
> 
> I saw a posting regarding this issue in august, with no solution...

Then you didn't read the entire thread. You can create a new operator
class to get the desired behavior.

Re: order by x DESC, y ASC indexing problem

From
Bruno Wolff III
Date:
On Thu, Oct 03, 2002 at 19:30:59 -0700, Josh Berkus <josh@agliodbs.com> wrote:
> Vincent,
> 
> > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC;
> > 
> > (X is a date and Y a varchar)
> > 
> > What would that index be?
> > 
> > Is there a function I can use, to invert x (the date), so that I can
> > make a query / index set like :
> 
> PostgreSQL's sorting mechanism can use an index either ascending or
> descending; it does not make a difference.

Note this is referring to a multicolumn index. To use all columns of
a multicolumn index the sort directions all need to be the same.

However you are right to point out that this may not be a big deal for
many applications where using the index on the first column gets the
majority of the speed up.

Re: order by x DESC, y ASC indexing problem

From
Tom Lane
Date:
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