Re: multi column index and order by

On Wed, Jan 05, 2005 at 15:35:22 +0100,
Mage <mage(at)mage(dot)hu> wrote:
> Hello,
>
> "order by a asc b desc"
>
> how can I create an index for this?

Currently you can't directly.

In recent versions of Postgres you have some options:

If one of the types has a normal minus operator, then you can use a functional
index using the minus operator that will allow you to get the ordering you
want. You will have to change the queries to use that operator explicitly
as the optimizer won't be able to figure this out on its own.

You can make a new operator class that defines ordering in the opposite
direction and then use that opclass when defining the index. I believe that
you also need to specify the opclass in the ORDER BY clause when doing this.

As a varient on the first case, you could make a function that returns
values that can be used for sorting. The output values need not be the
same type as the input values. For example you might convert dates
to the negative of the julian day number.