Reverse order sort in multi-column indexes

From: Eric Faulhaber <ecf(at)goldencode(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Reverse order sort in multi-column indexes
Date: 2005-10-31 21:19:16
Message-ID: 43668A54.4070602@goldencode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2005-10-31 21:39:40 Re: Reverse order sort in multi-column indexes
Previous Message Kevin Crenshaw 2005-10-31 18:11:27 Re: Problem inserting a row containing GUIDs