On Thu, 11 Jul 2019 at 19:41, Floris Van Nee <florisvan...@optiver.com> wrote: > SELECT DISTINCT ON (a) a,b,c FROM a WHERE c = 2 (with an index on a,b,c) > Data (imagine every tuple here actually occurs 10.000 times in the index to > see the benefit of skipping): > 1,1,1 > 1,1,2 > 1,2,2 > 1,2,3 > 2,2,1 > 2,2,3 > 3,1,1 > 3,1,2 > 3,2,2 > 3,2,3 > > Creating a cursor on this query and then moving forward, you should get > (1,1,2), (3,1,2). In the current implementation of the patch, after bt_first, > it skips over (1,1,2) to (2,2,1). It checks quals and moves forward > one-by-one until it finds a match. This match only comes at (3,1,2) however. > Then it skips to the end. > > If you move the cursor backwards from the end of the cursor, you should still > get (3,1,2) (1,1,2). A possible implementation would start at the end and do > a skip to the beginning of the prefix: (3,1,1). Then it needs to move forward > one-by-one in order to find the first matching (minimum) item (3,1,2). When > it finds it, it needs to skip backwards to the beginning of prefix 2 (2,2,1). > It needs to move forwards to find the minimum element, but should stop as > soon as it detects that the prefix doesn't match anymore (because there is no > match for prefix 2, it will move all the way from (2,2,1) to (3,1,1)). It > then needs to skip backwards again to the start of prefix 1: (1,1,1) and scan > forward to find (1,1,2). > Perhaps anyone can think of an easier way to implement it?
One option is just don't implement it and just change ExecSupportsBackwardScan() so that it returns false for skip index scans, or perhaps at least implement an index am method to allow the planner to be able to determine if the index implementation supports it... amcanskipbackward -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
