Re: multi column index and order by

Hello,

"order by a asc b desc"

how can I create an index for this?

Mage

On Wed, 5 Jan 2005, Mage wrote:

> "order by a asc b desc"
>
> how can I create an index for this?

You need to create an operator class for reversed comparison of whatever
type b is and then use it on b in the index definition.

Something like (however, you should really use a C function for the
reverse comparison function rather than the sql one shown here):

create function btint4cmprev(integer, integer) returns integer as 'select
btint4cmp($2,$1);' language 'sql';

CREATE OPERATOR CLASS int4_desc_ops FOR TYPE int4 USING BTREE AS
OPERATOR 1 >, OPERATOR 2 >=, OPERATOR 3 =, OPERATOR 4 <=, OPERATOR 5 <,
FUNCTION 1 btint4cmprev(int4,int4);

which you could then use something like:
create index testindex on testtable(a, b int4_desc_ops);

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.