On Wed, Jul 3, 2019 at 12:27 AM David Rowley <david.row...@2ndquadrant.com> wrote: > On Tue, 2 Jul 2019 at 21:00, Thomas Munro <thomas.mu...@gmail.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