On Thu, Jul 11, 2019 at 2:40 AM Floris Van Nee <florisvannee@optiver.com> wrote:
> I verified that the backwards index scan is indeed functioning now. However, I'm afraid it's not that simple, as I
thinkthe cursor case is broken now. I think having just the 'scan direction' in the btree code is not enough to get
thisworking properly, because we need to know whether we want the minimum or maximum element of a certain prefix. There
arebasically four cases:
>
> - Forward Index Scan + Forward cursor: we want the minimum element within a prefix and we want to skip 'forward' to
thenext prefix
>
> - Forward Index Scan + Backward cursor: we want the minimum element within a prefix and we want to skip 'backward' to
theprevious prefix
>
> - Backward Index Scan + Forward cursor: we want the maximum element within a prefix and we want to skip 'backward' to
theprevious prefix
>
> - Backward Index Scan + Backward cursor: we want the maximum element within a prefix and we want to skip 'forward' to
thenext prefix
>
> These cases make it rather complicated unfortunately. They do somewhat tie in with the previous discussion on this
threadabout being able to skip to the min or max value. If we ever want to support a sort of minmax scan, we'll
encounterthe 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.
--
Thomas Munro
https://enterprisedb.com