pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedorder by x DESC, y ASC indexing problem
5+ messages / 4 participants
[nested] [flat]
* order by x DESC, y ASC indexing problem
@ 2002-10-04 02:07 Vincent-Olivier Arsenault <vincent@up4c.com>
2002-10-04 02:30 ` Re: order by x DESC, y ASC indexing problem Josh Berkus <josh@agliodbs.com>
2002-10-04 03:09 ` Re: order by x DESC, y ASC indexing problem Bruno Wolff III <bruno@wolff.to>
2002-10-04 03:14 ` Re: order by x DESC, y ASC indexing problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 5+ messages in thread
From: Vincent-Olivier Arsenault @ 2002-10-04 02:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: order by x DESC, y ASC indexing problem
2002-10-04 02:07 order by x DESC, y ASC indexing problem Vincent-Olivier Arsenault <vincent@up4c.com>
@ 2002-10-04 02:30 ` Josh Berkus <josh@agliodbs.com>
2002-10-04 03:12 ` Re: order by x DESC, y ASC indexing problem Bruno Wolff III <bruno@wolff.to>
2 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2002-10-04 02:30 UTC (permalink / raw)
To: Vincent-Olivier Arsenault <vincent@up4c.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: order by x DESC, y ASC indexing problem
2002-10-04 02:07 order by x DESC, y ASC indexing problem Vincent-Olivier Arsenault <vincent@up4c.com>
2002-10-04 02:30 ` Re: order by x DESC, y ASC indexing problem Josh Berkus <josh@agliodbs.com>
@ 2002-10-04 03:12 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2002-10-04 03:12 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Vincent-Olivier Arsenault <vincent@up4c.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: order by x DESC, y ASC indexing problem
2002-10-04 02:07 order by x DESC, y ASC indexing problem Vincent-Olivier Arsenault <vincent@up4c.com>
@ 2002-10-04 03:09 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2002-10-04 03:09 UTC (permalink / raw)
To: Vincent-Olivier Arsenault <vincent@up4c.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: order by x DESC, y ASC indexing problem
2002-10-04 02:07 order by x DESC, y ASC indexing problem Vincent-Olivier Arsenault <vincent@up4c.com>
@ 2002-10-04 03:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2002-10-04 03:14 UTC (permalink / raw)
To: Vincent-Olivier Arsenault <vincent@up4c.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-10-04 03:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-04 02:07 order by x DESC, y ASC indexing problem Vincent-Olivier Arsenault <vincent@up4c.com>
2002-10-04 02:30 ` Josh Berkus <josh@agliodbs.com>
2002-10-04 03:12 ` Bruno Wolff III <bruno@wolff.to>
2002-10-04 03:09 ` Bruno Wolff III <bruno@wolff.to>
2002-10-04 03:14 ` Tom Lane <tgl@sss.pgh.pa.us>
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