Re: multi column index and order by

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);