God I love the sheer brilliance of that minus trick :-))
Tnx a lot

BTW: Are there any plans to change this kind of indexing behaviour ?
It makes no sense at all, and, it makes databases slow when you don't know about this.

On 23 May 2005, at 23:15, Andrew Lazarus wrote:

What you are trying to do makes perfect sense, but for some strange reason, Postgres doesn't like to do it. In a PG index, all of the columns are always stored in ascending order. So if you have an ORDER BY that is all ASC, it can start from the start of the index. And if you have an ORDER BY that is all DESC, it can start from the end. But if you want one column (like pages) DESC and the other (description) ASC, then PG will use a sequential scan or something else slow and stupid.

Other RDBMS know how to do this, by supporting the

CREATE INDEX foo ON bar(baz DESC, baz2 ASC)

syntax. For PG, you need to fool it with an index on an expression, or a custom operator, or something. I once just made an extra column and used a trigger to be sure that -myvariable was in it at all times (-pages for you) and then made my index on the extra column. Since the extra column in ASC order is the same as the original in DESC, it works.
<andrew.vcf>
Met vriendelijke groeten,
Bien vous,
Kind regards,

Yves Vindevogel
Implements

<<inline: Pasted Graphic 2.tiff>>



Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91

Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76

Web: http://www.implements.be
<x-tad-smaller>
First they ignore you. Then they laugh at you. Then they fight you. Then you win.
Mahatma Ghandi.</x-tad-smaller>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to