Thread: descending Indexes

I can't find a way to create descending Indexes in PG. Is this possible?

If so, can you point me in the right direction?
If not, can I request this to be an enhancement?

Thanks

On Thu, 4 Sep 2003, Bupp Phillips wrote:

> I can't find a way to create descending Indexes in PG. Is this possible?
>
> If so, can you point me in the right direction?
> If not, can I request this to be an enhancement?

You can do it, but it's a bit difficult.  You need to make an operator
class for the type in question that orders in the reverse order and use
that operator class in making the index.  I don't remember if I sent an
example the last time this came up, but you might want to check the
archives.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Thu, 4 Sep 2003, Bupp Phillips wrote:
>> I can't find a way to create descending Indexes in PG. Is this possible?

> You can do it, but it's a bit difficult.

Do you really *need* a descending index?  In most cases a backwards scan
on a regular index gets the job done.

            regards, tom lane

Yes, I really *need* a descending index.

How do you do a backward scan on an index?

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:17675.1062723498@sss.pgh.pa.us...
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > On Thu, 4 Sep 2003, Bupp Phillips wrote:
> >> I can't find a way to create descending Indexes in PG. Is this
possible?
>
> > You can do it, but it's a bit difficult.
>
> Do you really *need* a descending index?  In most cases a backwards scan
> on a regular index gets the job done.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

A long time ago, in a galaxy far, far away, "Relaxin" <noname@spam.com> wrote:
> Yes, I really *need* a descending index.
>
> How do you do a backward scan on an index?

Normally, your query would look like:

  "... order by field14 descending;"

That will scan backwards based on the index, assuming that the
optimizer decided to use an index on field14.
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/oses.html
"What this list needs is a good five-dollar plasma weapon."
--paraphrased from `/usr/bin/fortune`

On Thu, Sep 04, 2003 at 18:24:01 -0700,
  Relaxin <noname@spam.com> wrote:
> Yes, I really *need* a descending index.

Can you go into more detail about this?

Generally you only need a descending index if it has more than one part
and one part needs to be ascending and the other needs to be descending.
If this case then general solution is to create a new operator class.
In 7.4, you will be able to get around this is some cases by using a
functional index on - whatever and using - whatever in the query. (This
will only work on datatypes that have a unary - defined, so is pretty much
limited to numeric types.)