Re: Reverse collations (initially for making keyset pagination cover more cases)

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> Lastly, your proposed use-case has some attraction, but this
Tom> proposal only supports it if the column you need to be differently
Tom> sorted is textual. What if the sort columns are all numerics and
Tom> timestamps?

There are already trivial ways to reverse the orders of those, viz.
(-number) and (-extract(epoch from timestampcol)). The lack of any
equivalent method for text is what prompted this idea.

Tom> Thinking about that, it seems like what we'd want is some sort of
Tom> more-general notion of row comparison, to express "bounded below
Tom> in an arbitrary ORDER BY ordering". Not quite sure what it ought
Tom> to look like.

Well, one obvious completely general method is to teach the planner
(somehow) to spot conditions of the form

(a > $1 OR (a = $1 AND b > $2) OR (a = $1 AND b = $2 AND c > $3) ...)

etc. and make them indexable if the sense of the > or < operator at
each step matched an ASC or DESC column in the index.

This would be a substantial win, because this kind of condition is one
often (incorrectly, for current PG) shown as an example of how to do
keyset pagination on multiple columns. But it would require some amount
of new logic in both the planner and, afaik, in the btree AM; I haven't
looked at how much.

--
Andrew (irc:RhodiumToad)