Thread: Reverse order sort in multi-column indexes

In searching the archives, I have found postings by people migrating
from other databases, who have expressed a need to support the semantic
of descending sort direction in a multi-column index, as in:

   create index test_idx on test_table (column_a asc, column_b desc)

I understand this is not the convention in PostgreSQL and that this
syntax is not likely to be supported, as direction is only meaningful
for B-tree index types.  So, I gather that the way to do this for a
B-tree index in PostgreSQL is by defining an opclass which knows how to
sort a data type in reverse order.  A previous post suggests:

[...]
 > A useful descending-order opclass
 > simply rearranges the logical relationships of the standard comparison
 > operators.  You do need a new comparison function, but nothing else:
 >
 >   CREATE OPERATOR CLASS int4_reverse_order_ops
 >      FOR TYPE int4 USING btree AS
 >          OPERATOR        1       > ,
 >          OPERATOR        2       >= ,
 >          OPERATOR        3       = ,
 >          OPERATOR        4       <= ,
 >          OPERATOR        5       < ,
 >          FUNCTION        1       int4_reverse_order_cmp(int4, int4);
 > Now you can just use ASC/DESC in your ORDER BY ...
 >
 >             regards, tom lane

What I'm having trouble understanding is the bit about the new
comparison function:

1a)  If we've already "reversed" the operators' meanings as described
above by reassigning them to different strategy numbers, why is it also
necessary to define a new comparison function?  Wouldn't a reference to
the standard comparison function for the target data type cause it to
pick up the new, "reverse" operations automatically for this opclass?

1b)  I suppose the converse question is:  if we provide a new comparison
function which implements the reverse comparison strategy, why is it
necessary to re-assign all the operators to different strategy numbers?

2)  I couldn't find any posting describing how such a comparison
function would actually be defined.  From Chapter 33 of the 7.4.2 user
manual, it is possible to define SQL, procedural, internal, and
C-language functions.  Which is most appropriate here?  Can anyone point
to an example of the simplest way to do this?

TIA for any help.

Regards,
Eric Faulhaber

Eric Faulhaber <ecf@goldencode.com> writes:
> What I'm having trouble understanding is the bit about the new
> comparison function:

> 1a)  If we've already "reversed" the operators' meanings as described
> above by reassigning them to different strategy numbers, why is it also
> necessary to define a new comparison function?

Because the comparison function has to agree with the semantics of the
operators: for instance it has to return a negative value exactly when
operator 1 would return "true".

> 1b)  I suppose the converse question is:  if we provide a new comparison
> function which implements the reverse comparison strategy, why is it
> necessary to re-assign all the operators to different strategy numbers?

Because the same operators serve different roles.  In a normal opclass
"A < B" indicates whether A comes before B in the index ordering, but for
a reverse opclass you'd need "A > B" to mean that.  So you need to
assign ">" not "<" to be operator 1.

BTW, there is a gotcha that probably prevents this idea from working
really nicely in existing PG releases, namely that NULLs will still sort
to the end in both kinds of opclass.  Aside from creating inconsistencies
between index-scan and explicit-sort results, that's quite likely to
break merge joins.  I've been thinking about how to fix this, and may be
able to do something about it in 8.2.  In the meantime, I'd recommend
against using a reverse-sort opclass unless you can mark the column NOT
NULL.

            regards, tom lane