Re: issues with eager aggregation

On Fri, Sep 18, 2026 at 9:20 AM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> How likely is it that a working query starts failing due to flipping to
> a plan with eager aggregate?

It needs three things at once: an aggregate argument that can raise an
error (such as division by zero), values that fail only in rows the
join removes, and the planner actually choosing eager aggregation,
which requires partial groups of at least min_eager_agg_group_size
rows and a cheaper plan. So I agree it should be rare, but your
products example shows it's not unrealistic.

> And if it happens, how can the user remedy it? I can think of disabling
> the eager aggregation, or adding an "OFFSET 0" ...

Those two work. The expression can also be guarded with CASE, which
is what the docs already recommend for forcing evaluation order, e.g.

sum(CASE WHEN c <> 0 THEN 100 / c END)

For comparison, the existing cases have no GUC at all. The HAVING
case upthread is probably the closest one: HAVING is supposed to apply
to groups formed after the join, yet a HAVING clause can be pushed
below the join, and see rows that never form a group. CTEs are
another: since v12 they are inlined by default, so a WHERE clause on a
CTE can be pushed below a join inside it, and a query that worked in
v11 can fail in v12:

WITH s AS (SELECT t2.c FROM t1 JOIN t2 ON t1.b = t2.b)
SELECT count(*) FROM s WHERE 100 / s.c > 0;

ERROR: division by zero

The remedy there is AS MATERIALIZED, and elsewhere OFFSET 0 or
guarding the expression with CASE. So I think eager aggregation is no
worse off, and has one more way out, the enable_eager_aggregate GUC.

> FWIW I suspect 99.999% of conditions won't have this problem, because
> most clauses won't have this type of failures. And as demonstrated,
> there are preexisting cases with similar behavior.

Agreed.

- Richard