Re: Eager aggregation, take 3

On Fri, Sep 27, 2024 at 11:53 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> Here is an updated version of this patch that fixes the rowcount
> estimate issue along this routine. (see set_joinpath_size.)

I have worked on inventing some heuristics to limit the planning
effort of eager aggregation. One simple yet effective approach I'm
thinking of is to consider a grouped path as NOT useful if its row
reduction ratio falls below a predefined minimum threshold. Currently
I'm using 0.5 as the threshold, but I'm open to other values.

+/* Minimum row reduction ratio at which a grouped path is considered useful */
+#define EAGER_AGGREGATE_RATIO 0.5

When deciding to generate a grouped relation for a base or join
relation, we calculate the row reduction ratio of its grouped paths.
If the ratio is less than EAGER_AGGREGATE_RATIO, we will skip
generating the grouped relation for a base relation, and will only
generate the grouped relation for a join relation if we can produce
any grouped paths by joining its input relations. In either case, we
will NOT generate any grouped paths by adding partial aggregation on
top of the non-grouped paths. This would reduce the number of grouped
paths as well as the grouped relations in many cases where eager
aggregation would not help a lot.

Thanks
Richard