Horton

Re: Eager aggregation, take 3

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Richard Guo <guofenglinux@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, Matheus Alcantara <matheusssilv97@gmail.com>
Date: 2025-09-09T14:20:14Z
Lists: pgsql-hackers 
On Tue, Sep 9, 2025 at 5:20 AM Richard Guo <guofenglinux@gmail.com> wrote:
> Yeah, ideally we should tell whether an aggregate's transition state
> may grow unbounded just by looking at system catalogs.  Unfortunately,
> after trying for a while, it seems to me that the current catalog
> doesn't provide enough information.
>
> I once considered adding a flag (e.g., aggtransbounded) to catalog
> pg_aggregate to indicate whether the transition state size is bounded.
> This flag could be specified by users when creating aggregate
> functions, and then leveraged by features such as eager aggregation.
>
> However, adding new information to system catalogs involves a lot of
> discussions and changes, including updates to DDL commands, dump and
> restore processes, and upgrade procedures.  Therefore, to keep the
> focus of this patch on the eager aggregation feature itself, I prefer
> to treat this enhancement as future work.

I don't really like that. I think there's a lot of danger of that
future work never getting done, and thus leaving us stuck more-or-less
permanently with a system that's not really extensible. Data type and
function extensibility is one of the strongest areas of PostgreSQL,
and we should try hard to avoid situations where we regress it. I'm
not sure whether the aggtransbounded flag is exactly the right thing
here, but I don't think adding a new catalog column is an unreasonable
amount of work for a feature of this type.

Having said that, I wonder whether there's some way that we could use
the aggtransspace property for this. For instance, for stanullfrac, we
use values >0 to mean absolute quantities and values <0 to mean
proportions. The current definition of aggtranspace assigns no meaning
to values <0, and the current coding seems to assume that sizes are
fixed regardless of how many inputs are supplied. Maybe we could
define aggtransspace<0 to mean that the number of bytes used per input
value is the additive inverse of the value, or something like that.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

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