Re: Multicolumn indexes and ORDER BY

Jernej Kos <kostko@jweb-network.net> writes:
> Well, writing a C function is not a problem ;) So where could i find any
> documentation regarding this matter ?

Read the "Interfacing Extensions To Indexes" docs chapter.  A crude
example for integers would go like

regression=# create function revcmp(int,int) returns int as
regression-# 'select $2 - $1' language sql;
CREATE FUNCTION
regression=# create operator class rev_int_ops for type int using btree as
regression-# operator 1 > ,
regression-# operator 2 >= ,
regression-# operator 3 = ,
regression-# operator 4 <= ,
regression-# operator 5 < ,
regression-# function 1 revcmp(int,int);
CREATE OPERATOR CLASS

(compare the operator order here to the "standard" btree order shown in
the docs --- we're swapping < for > and <= for >=)

This actually works:

regression=# create table foo (f1 int, f2 int);
CREATE TABLE
regression=# create index fooi on foo (f1, f2 rev_int_ops);
CREATE INDEX
regression=# explain select * from foo order by f1, f2 desc;
                             QUERY PLAN
--------------------------------------------------------------------
 Index Scan using fooi on foo  (cost=0.00..52.00 rows=1000 width=8)
(1 row)

regression=# explain select * from foo order by f1 desc, f2 asc;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan Backward using fooi on foo  (cost=0.00..52.00 rows=1000 width=8)
(1 row)

but index performance would be pretty sucky without reducing the
comparator function to C.  Also I didn't consider overflow when writing
this comparator function, so the above would probably fall over if faced
with index entries outside +/- 1 billion or so.

At the C level it'd probably be best to call the standard comparator
function for the type and then negate its result, viz
    PG_RETURN_INT32(- DatumGetInt32(btint4cmp(fcinfo)));
which reduces what might otherwise be a bit complicated to trivial
boilerplate.

We have previously discussed putting together a contrib package that
implements reverse-sort opclasses of this kind for all the standard
datatypes.  If you feel like doing the legwork, the submission would
be gratefully accepted ...

            regards, tom lane