Compute sibling scalar aggregates over the same table in one pass
The Issue & Context
Queries with multiple scalar aggregates over the same file or table function scanned the data multiple times—once per aggregate—causing redundant expensive IO for Parquet, CSV, and JSON inputs.
Root Cause Analysis
Each scalar aggregate subquery independently triggered a full scan of the underlying table function or file, because the optimizer lacked a rewrite to combine these sibling aggregates into a single scan with FILTER clauses. The old plan duplicated scan effort instead of sharing it.
Solution Strategy & Architectural Pattern
Introduced a new optimizer rule `scalar_aggregate_merge` that detects sibling scalar aggregates over the same table function or large catalog tables and merges them into one scan with per-aggregate FILTER predicates. This rewrite is gated by a configurable threshold to avoid regressions on native DuckDB tables where FILTER overhead outweighs scan savings. The implementation tracks identical table function calls and merges their aggregates in `scalar_aggregate_merger.cpp`.
Key Takeaway for Contributors
When multiple aggregates scan the same data independently, merging them into a single scan with conditional aggregation can drastically reduce IO and improve performance, but only if the overhead of filtering does not outweigh the saved scans—making cost-based gating essential.