Re: Multi ordered select and indexing

On Fri, 23 Apr 2004, Antal Attila wrote:

> Hi!
>
> We have a complex problematic area. What is the simplest solution for
> the next query type:
>
> SELECT * FROM tablename ORDER BY col1 ASC, col2 DESC;
>
> In our experience, postgres cannot use a multi-colum index on (col1,
> col2) in this situation, becouse there are different directions after
> ORDER BY. Is custom operator class the easiest solution, which can solve
> the reverse indexing on col2? Our problem with this solution, is that we
> have to replace "DESC" with "USING myoperator". Is it possible, that
> postgres can recognize "myoperator" without replacing "DESC"?

Yes. You should be able to make an opclass where the operators are the
standard operators but in a different order which should get used when
doing DESC. I think there should be examples in the archives.