Re: Create an index with a sort condition

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.

On Wed, 2006-03-29 at 03:14 -0800, sylsau wrote:

> 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);
>

The index is already sorted and can be scanned forwards or backwards.

Perhaps you want CLUSTER?

Best Regards, Simon Riggs

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

> On Wed, 2006-03-29 at 03:14 -0800, sylsau wrote:
>
>> 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);
>>
>
> 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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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(at)svana(dot)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.

Martijn van Oosterhout <kleptog(at)svana(dot)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

On Tue, Apr 04, 2006 at 01:40:52AM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)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 ...

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/pgsql-hackers/2005-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(at)svana(dot)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.