Re: Backwards index scan

On Tue, 8 Jul 2003, Dmitry Tkach wrote:

> Stephan Szabo wrote:
>
> >If you make an opclass that orders in the reverse order you can use that
> >opclass in creating the index (which effectively can give you an index
> >like x, y desc by using the new opclass on y). There was some talk
> >recently about whether we should provide such opclasses as builtins or
> >contrib items.
> >
> >
> Actually, I just thought, it is not exactly equivalent, unless I am
> missing something.
> If I create this opclass and the index, and then make a query like
> select * from huge_table where x=10 order by x,y desc
>
> ... it won't know to use the index for sorting, will it?

I don't know the mechanics (haven't looked) but it seems to know
based on the way the operators are assigned to the opclass. I've
done some minimal tests and for queries like
select * from tab order by a, b desc
and then gotten effectively
Index scan using <index> on <table>
as the plan with no sort steps.

> In this situation, it will work... But it may be a problem when the
> query is (a lot) more complicated, with several joins and a bunch of
> different paths available to the planner - how can I guarantee then that
> it will always choose this index and return the results in the right order?

You can't guarantee that it'll always choose this index because it's
possible that the index is more expensive for a particular query, but
it should consider the index.