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

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

>> 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.

Tom> I think really the only attraction of that is that it could be
Tom> argued to be standard --- but I rather doubt that it's common for
Tom> DBMSes to recognize such things.

At least MSSQL can recognize that a query with

WHERE (a > @a OR (a = @a AND b > @b)) ORDER BY a,b

can be satisfied with an ordered index scan on an (a,b) index and no
sort, which is good enough for pagination queries. Haven't confirmed
yes/no for any other databases yet.

(As an aside, if you try and do that in PG using UNION ALL in place of
the OR, to try and get a mergeappend of two index scans, it doesn't work
well because of how we discard redundant pathkeys; you end up with Sort
nodes in the plan.)

Tom> It'd certainly be a royal pain in the rear both to implement and
Tom> to use, at least for more than about two sort columns.

For pagination one or two columns seems most likely, but in any event
the query can be generated mechanically if need be.

--
Andrew (irc:RhodiumToad)