Queries performing aggregation after a JOIN, such as counting grouped keys from one side of a join, were inefficient because aggregation was done post-join, processing large join inputs unnecessarily.

The optimizer lacked a transformation to push aggregation below the JOIN, missing opportunities to reduce the join input size by aggregating early on one join input, especially when that input has few distinct group keys.

Implemented an AggregationPushdown rule in the Cascades optimizer with partial and full pushdown variants that move aggregation below the join input when safe. Partial pushdown performs partial aggregation with a merge step after the join, while full pushdown applies final aggregation below the join when join conditions and group keys allow. Safety checks ensure correctness by restricting cases where aggregation pushdown could alter results.

This cost-based aggregation pushdown optimization significantly improves query efficiency by reducing join input size when beneficial, while preserving correctness through strict legality and safety rules, demonstrating the value of advanced optimizer transformations in complex query planning.