Re: index in desc order

Is it possible to create an index in descending order?

On 2 November 2010 12:36, AI Rumman <rummandba(at)gmail(dot)com> wrote:
> Is it possible to create an index in descending order?
>

Yes it is - http://www.postgresql.org/docs/current/interactive/indexes-ordering.html

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp

On 2 November 2010 10:36, AI Rumman <rummandba(at)gmail(dot)com> wrote:

> Is it possible to create an index in descending order?
>

yes...

create index i on t(i desc);

regards
Szymon

But I am using Postgresql 8.1. Is it possible here?

On Tue, Nov 2, 2010 at 3:42 PM, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:

>
>
> On 2 November 2010 10:36, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>
>> Is it possible to create an index in descending order?
>>
>
> yes...
>
> create index i on t(i desc);
>
>
> regards
> Szymon
>

On 2 November 2010 12:57, AI Rumman <rummandba(at)gmail(dot)com> wrote:
> But I am using Postgresql 8.1. Is it possible here?

I am afraid not. You could try to do the index using kind of 1/field
trick but I am not sure if it performs better than backward index scan
in general.

>
> On Tue, Nov 2, 2010 at 3:42 PM, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:
>>
>>
>> On 2 November 2010 10:36, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>>>
>>> Is it possible to create an index in descending order?
>>
>> yes...
>> create index i on t(i desc);
>>
>> regards
>> Szymon
>

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp

Szymon Guz <mabewlun(at)gmail(dot)com> writes:
> On 2 November 2010 10:36, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>> Is it possible to create an index in descending order?

> create index i on t(i desc);

Note that there is actually no point at all in such a declaration.
The planner is perfectly capable of using backwards indexscans at
need, so the above index doesn't do anything you couldn't do with
a regular ascending-order index.

The cases where this feature is actually worth something is where
you have a multi-column index and you need different sort orders
for the components, for example

create index xy on t (x asc, y desc);

which could be used to satisfy SELECT ... ORDER BY x ASC, y DESC.

The OP didn't say what he wanted to use the feature for, but
unless it's something like that, there's probably a better way.

regards, tom lane

On Tue, 2 Nov 2010 10:10:19 -0400, Tom Lane wrote:
> Szymon Guz <mabewlun(at)gmail(dot)com> writes:
> > On 2 November 2010 10:36, AI Rumman <rummandba(at)gmail(dot)com> wrote:
> >> Is it possible to create an index in descending order?
>
> > create index i on t(i desc);
>
> Note that there is actually no point at all in such a declaration.
> The planner is perfectly capable of using backwards indexscans at
> need, so the above index doesn't do anything you couldn't do with
> a regular ascending-order index.

Cannot there be a (system/hardware) setup where there is a perceptible
performance difference between forward and backward index scans?

--
Michał Politowski
Talking has been known to lead to communication if practiced carelessly.

Michal Politowski <mpol+pg(at)meep(dot)pl> writes:
> Cannot there be a (system/hardware) setup where there is a perceptible
> performance difference between forward and backward index scans?

I think it's been reported already that backward index scans indeed can
be much slower than forward index scan, but that how to model that is
still unclear and undone in the cost estimations.

You will have to crawl the pgsql-performance list yourself, though…

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support