Re: Index Skip Scan

On Thu, 11 Jul 2019 at 19:41, Floris Van Nee <florisvannee@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
implementationof the patch, after bt_first, it skips over (1,1,2) to (2,2,1). It checks quals and moves forward
one-by-oneuntil 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
implementationwould start at the end and do a skip to the beginning of the prefix: (3,1,1). Then it needs to move
forwardone-by-one in order to find the first matching (minimum) item (3,1,2). When it finds it, it needs to skip
backwardsto the beginning of prefix 2 (2,2,1). It needs to move forwards to find the minimum element, but should stop
assoon as it detects that the prefix doesn't match anymore (because there is no match for prefix 2, it will move all
theway 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
forwardto 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