Re: Index Skip Scan

On Thu, 11 Jul 2019 at 14:50, Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
>
> On Thu, Jul 11, 2019 at 2:40 AM Floris Van Nee <florisvannee(at)optiver(dot)com> wrote:
> > I verified that the backwards index scan is indeed functioning now. However, I'm afraid it's not that simple, as I think the cursor case is broken now. I think having just the 'scan direction' in the btree code is not enough to get this working properly, because we need to know whether we want the minimum or maximum element of a certain prefix. There are basically four cases:
> >
> > - Forward Index Scan + Forward cursor: we want the minimum element within a prefix and we want to skip 'forward' to the next prefix
> >
> > - Forward Index Scan + Backward cursor: we want the minimum element within a prefix and we want to skip 'backward' to the previous prefix
> >
> > - Backward Index Scan + Forward cursor: we want the maximum element within a prefix and we want to skip 'backward' to the previous prefix
> >
> > - Backward Index Scan + Backward cursor: we want the maximum element within a prefix and we want to skip 'forward' to the next prefix
> >
> > These cases make it rather complicated unfortunately. They do somewhat tie in with the previous discussion on this thread about being able to skip to the min or max value. If we ever want to support a sort of minmax scan, we'll encounter the same issues.
>
> Oh, right! So actually we already need the extra SKIP_FIRST/SKIP_LAST
> argument to amskip() that I theorised about, to support DISTINCT ON.
> Or I guess it could be modelled as SKIP_LOW/SKIP_HIGH or
> SKIP_MIN/SKIP_MAX. If we don't add support for that, we'll have to
> drop DISTINCT ON support, or use Materialize for some cases. My vote
> is: let's move forwards and add that parameter and make DISTINCT ON
> work.

Does it not just need to know the current direction of the cursor's
scroll, then also the intended scan direction?

For the general forward direction but for a backwards cursor scroll,
we'd return the lowest value for each distinct prefix, but for the
general backwards direction (DESC case) we'd return the highest value
for each distinct prefix. Looking at IndexNext() the cursor direction
seems to be estate->es_direction and the general scan direction is
indicated by the plan's indexorderdir. Can't we just pass both of
those to index_skip() to have it decide what to do? If we also pass in
indexorderdir then index_skip() should know if it's to return the
highest or lowest value, right?

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services