Thread: index in desc order
Is it possible to create an index in descending order?
On 2 November 2010 12:36, AI Rumman <rummandba@gmail.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.ru@gmail.com / Skype: gray-hemp
On 2 November 2010 10:36, AI Rumman <rummandba@gmail.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@gmail.com> wrote:
On 2 November 2010 10:36, AI Rumman <rummandba@gmail.com> wrote:Is it possible to create an index in descending order?yes...create index i on t(i desc);regardsSzymon
On 2 November 2010 12:57, AI Rumman <rummandba@gmail.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@gmail.com> wrote: >> >> >> On 2 November 2010 10:36, AI Rumman <rummandba@gmail.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.ru@gmail.com / Skype: gray-hemp
Szymon Guz <mabewlun@gmail.com> writes: > On 2 November 2010 10:36, AI Rumman <rummandba@gmail.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@gmail.com> writes: > > On 2 November 2010 10:36, AI Rumman <rummandba@gmail.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@meep.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