Re: Backwards index scan

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?
My understanding is that I'd have to get rid of the sort clause
completely, and just rely on the query plan, right?

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?

Currently I just always use the sort clause, and that forces it to pick
the right index even if another path looks a little less expensive, but
with this custom opclass, I believe, having the sort clause will always
cause it to actually sort even if it does use the right index...

Or am I missing something here?

Thanks!

Dima