Re: Index Skip Scan

On Wed, Jul 10, 2019 at 1:32 AM Jesper Pedersen
<jesper(dot)pedersen(at)redhat(dot)com> wrote:
> > While updating the Loose Index Scan wiki page with links to other
> > products' terminology on this subject, I noticed that MySQL can
> > skip-scan MIN() and MAX() in the same query. Hmm. That seems quite
> > desirable. I think it requires a new kind of skipping: I think you
> > have to be able to skip to the first AND last key that has each
> > distinct prefix, and then stick a regular agg on top to collapse them
> > into one row. Such a path would not be so neatly describable by
> > UniqueKeys, or indeed by the amskip() interface in the current patch.
> > I mention all this stuff not because I want us to run before we can
> > walk, but because to be ready to commit the basic distinct skip scan
> > feature, I think we should know approximately how it'll handle the
> > future stuff we'll need.
>
> Thomas, do you have any ideas for this ? I can see that MySQL did the
> functionality in two change sets (base and function support), but like
> you said we shouldn't paint ourselves into a corner.

I think amskip() could be augmented by later patches to take a
parameter 'skip mode' that can take values SKIP_FIRST, SKIP_LAST and
SKIP_FIRST | SKIP_LAST (meaning please give me both). What we have in
the current patch is SKIP_FIRST behaviour. Being able to choose
SKIP_FIRST or SKIP_LAST allows you do handle i, MAX(j) GROUP BY (i)
ORDER BY i (ie forward scan for the order, but wanting the highest key
for each distinct prefix), and being able to choose both allows for i,
MIN(j), MAX(j) GROUP BY i. Earlier I thought that this scheme that
allows you to ask for both might be incompatible with David's
suggestion of tracking UniqueKeys in paths, but now I see that it
isn't: it's just that SKIP_FIRST | SKIP_LAST would have no UniqueKeys
and therefore require a regular agg on top. I suspect that's OK: this
min/max transformation stuff is highly specialised and can have
whatever magic path-building logic is required in
preprocess_minmax_aggregates().

--
Thomas Munro
https://enterprisedb.com