On Thu, Aug 16, 2018 at 5:44 PM, Bhushan Uparkar <bhushan.upar...@gmail.com> wrote: > I was reviewing index-skip patch example and have a comment on it. Example > query “select distinct b from t1” is equivalent to “select b from t1 group by > b”. When I tried the 2nd form of query it came up with different plan, is it > possible that index skip scan can address it as well?
Yeah, there are a few tricks you can do with "index skip scans" (Oracle name, or as IBM calls them, "index jump scans"... I was slightly tempted to suggest we call ours "index hop scans"...). For example: * groups and certain aggregates (MIN() and MAX() of suffix index columns within each group) * index scans where the scan key doesn't include the leading columns (but you expect there to be sufficiently few values) * merge joins (possibly the trickiest and maybe out of range) You're right that a very simple GROUP BY can be equivalent to a DISTINCT query, but I'm not sure if it's worth recognising that directly or trying to implement the more general grouping trick that can handle MIN/MAX, and whether that should be the same executor node... The idea of starting with DISTINCT was just that it's comparatively easy. We should certainly try to look ahead and bear those features in mind when figuring out the interfaces though. Would the indexam skip(scan, direction, prefix_size) operation I proposed be sufficient? Is there a better way? I'm glad to see this topic come back! -- Thomas Munro http://www.enterprisedb.com
