Re: Index Skip Scan

On Wed, Jul 3, 2019 at 12:27 AM David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On Tue, 2 Jul 2019 at 21:00, Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> > 2. SELECT i, MIN(j) FROM t GROUP BY i could benefit from this if
> > you're allowed to go forwards. Same for SELECT i, MAX(j) FROM t GROUP
> > BY i if you're allowed to go backwards. Those queries are equivalent
> > to SELECT DISTINCT ON (i) i, j FROM t ORDER BY i [DESC], j [DESC]
> > (though as Floris noted, the backwards version gives the wrong answers
> > with v20). That does seem like a much more specific thing applicable
> > only to MIN and MAX, and I think preprocess_minmax_aggregates() could
> > be taught to handle that sort of query, building an index only scan
> > path with skip scan in build_minmax_path().
>
> For the MIN query you just need a path with Pathkeys: { i ASC, j ASC
> }, UniqueKeys: { i, j }, doing the MAX query you just need j DESC.

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 Munro
https://enterprisedb.com