>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.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)
