Re: Reverse order sort in multi-column indexes

Eric Faulhaber <ecf(at)goldencode(dot)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