|From:||Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>|
|To:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|Cc:||David Fetter <david(at)fetter(dot)org>, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: Reverse collations (initially for making keyset pagination cover more cases)|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
>>>>> "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.
|Next Message||Andres Freund||2019-11-18 22:12:54||Re: physical slot xmin dependency on logical slot?|
|Previous Message||Jeremy Finzel||2019-11-18 21:36:47||physical slot xmin dependency on logical slot?|