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: Tom Lane <tgl@sss.pgh.pa.us>, 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: 2025-06-26T02:01:35Z
Lists: pgsql-hackers

Attachments

On Fri, Jun 13, 2025 at 4:41 PM Richard Guo <guofenglinux@gmail.com> wrote:
> I've switched back to this thread and will begin by working through
> the key concerns that were previously raised.
>
> The first concern is the lack of a proof demonstrating the correctness
> of this transformation.  To address this, I plan to include a detailed
> proof in the README, along the lines of the following.

> The second concern is that a RelOptInfo representing a grouped
> relation may include paths that produce different row sets due to
> partial aggregation being applied at different join levels.  This
> potentially violates a fundamental assumption in the planner.
>
> Additionally, the patch currently performs an exhaustive search by
> exploring partial aggregation at every possible join level, leading to
> excessive planning effort, which may not be justified by the
> cost-benefit ratio.
>
> To address these concerns, I'm thinking that maybe we can adopt a
> strategy where partial aggregation is only pushed to the lowest
> possible level in the join tree that is deemed useful.  In other
> words, if we can build a grouped path like "AGG(B) JOIN A" -- and
> AGG(B) yields a significant reduction in row count -- we skip
> exploring alternatives like "AGG(A JOIN B)".

Here is the patch based on the proposed ideas.  It includes the proof
of correctness in the README and implements the strategy of pushing
partial aggregation only to the lowest applicable join level where it
is deemed useful.  This is done by introducing a "Relids apply_at"
field to track that level and ensuring that partial aggregation is
applied only at the recorded "apply_at" level.

Additionally, this patch changes how grouped relations are stored.
Since each grouped relation represents a partially aggregated version
of a non-grouped relation, we now associate each grouped relation with
the RelOptInfo of the corresponding non-grouped relation.  This
eliminates the need for a dedicated list of all grouped relations and
avoids list searches when retrieving a grouped relation.

It also addresses other previously raised concerns, such as the
potential memory blowout risks with large partial-aggregation values,
and includes improvements to comments and the commit message.

Another change is that this feature is now enabled by default.

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