Reverse order sort in multi-column indexes
От | Eric Faulhaber |
---|---|
Тема | Reverse order sort in multi-column indexes |
Дата | |
Msg-id | 43668A54.4070602@goldencode.com обсуждение исходный текст |
Ответы |
Re: Reverse order sort in multi-column indexes
|
Список | 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
В списке pgsql-novice по дате отправления: