PostgreSQL Discussion - Create an index with a sort condition

Author

Create an index with a sort condition

sylsau

2006-03-29, 7:29 am

Hello,

I use PostgreSQL 8.1 and I would like create and index on a table's
field with a sort condition on this field.
For example, I have a table named books and I want create an index on
the fields title and id_book with an order by id_book descendant.

I know how to create the index without this condition :

create index book_index on books(id_book, title);

But, I don't know to create this index with the sort condition.
Someone has an idea ?

Thanks to help me.

Sylvain.

Jim Nasby

2006-04-03, 8:26 pm

On Mar 29, 2006, at 2:57 PM, Simon Riggs wrote:

> On Wed, 2006-03-29 at 03:14 -0800, sylsau wrote:
>
>
> The index is already sorted and can be scanned forwards or backwards.

I believe he's talking about something like

CREATE INDEX books__id_title ON books(id_book, title DESC);

which of course we don't support. But you can define a custom set of
operators that work backwards and use those to define the index, and
then use them in the ORDER BY.

BTW, is there a TODO for this? Second request for it I've seen in a
week...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Martijn van Oosterhout

2006-04-04, 3:27 am

On Mon, Apr 03, 2006 at 12:48:22PM -0400, Jim Nasby wrote:
> I believe he's talking about something like
>
> CREATE INDEX books__id_title ON books(id_book, title DESC);
>
> which of course we don't support. But you can define a custom set of
> operators that work backwards and use those to define the index, and
> then use them in the ORDER BY.
>
> BTW, is there a TODO for this? Second request for it I've seen in a
> week...

Well, if COLLATE support ever gets done, it'll fix this too and be SQL
compliant to boot. Unfortunatly I havn't had much time to work on this
recently and there hasn't been much interest externally. Last time I
was bogged down by keeping up with the number of commits to the sort
code which is ofcourse intimately related to this.

One day...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Tom Lane

2006-04-04, 3:27 am

Martijn van Oosterhout <kleptog@svana.org> writes:
> [ Q about reverse-sort opclasses ]

> Well, if COLLATE support ever gets done, it'll fix this too and be SQL
> compliant to boot.

I keep having a nagging feeling that COLLATE is a completely
inappropriate way to deal with reverse-sort semantics for non-textual
datatypes. Still waiting to see this patch ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Martijn van Oosterhout

2006-04-04, 3:27 am

On Tue, Apr 04, 2006 at 01:40:52AM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>
>
> I keep having a nagging feeling that COLLATE is a completely
> inappropriate way to deal with reverse-sort semantics for non-textual
> datatypes. Still waiting to see this patch ...

How so? All it does it invert the result of the comparison. If we do it
for textual types it'll work automatically for all other types too.

All the details of how it would work were described back here:
http://archives.postgresql.org/pgsq...12/msg01121.php

The last patch unfortunatly no longer applies cleanly so you can't
really test it. If someone really wants this feature, now's the time to
pipe up.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Sponsored Links