From: Yves Vindevogel <yves.vindevogel@implements.be>
To: pgsql-performance@postgresql.org
Cc: Hendrik De Hertogh <hendrik.dehertogh@ecseed.com>
Subject: Re: Fwd: Index on table when using DESC clause
Date: Mon, 23 May 2005 23:18:49 +0200
Message-ID: <adcc4892d7715a10069cfd0c62028a9b@implements.be> (raw)
In-Reply-To: <42924803.3020101@pillette.com>
References: <6d31bed04ab35cbbe2df37af979d9882@implements.be>
<42923CA3.1040306@pillette.com>
<3513c801b1c6c11559b50ddf24dbb2be@implements.be>
<42924803.3020101@pillette.com>
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
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:Pasted Graphic 2.tiff (image/tiff, 5.6K) downloadview image
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-performance@postgresql.org
Cc: yves.vindevogel@implements.be, hendrik.dehertogh@ecseed.com
Subject: Re: Fwd: Index on table when using DESC clause
In-Reply-To: <adcc4892d7715a10069cfd0c62028a9b@implements.be>
* 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