Horton

Re: Eager aggregation, take 3

Richard Guo <guofenglinux@gmail.com>

View thread View on postgr.es
From: Richard Guo <guofenglinux@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tender Wang <tndrwang@gmail.com>, Paul George <p.a.george19@gmail.com>, Andy Fan <zhihuifan1213@163.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, pgsql-hackers@lists.postgresql.org
Date: 2024-10-05T10:23:02Z
Lists: pgsql-hackers

Attachments

On Fri, Sep 27, 2024 at 11:53 AM Richard Guo <guofenglinux@gmail.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

Commits

  1. Fix eager aggregation for semi/antijoin inner rels

  2. Cover additional errors and corner conditions in repack.c

  3. Fix volatile function evaluation in eager aggregation

  4. Fix collation handling for grouping keys in eager aggregation

  5. Rename apply_at to apply_agg_at for clarity

  6. Fix comment in eager_aggregate.sql

  7. Remove unnecessary include of "utils/fmgroids.h"

  8. Implement Eager Aggregation

  9. Allow negative aggtransspace to indicate unbounded state size

  10. Add macros for looping through a List without a ListCell.

  11. Account for the effect of lossy pages when costing bitmap scans.

  12. Fix a thinko in join_is_legal: when we decide we can implement a semijoin