DEV Community

Priyadarshan Singh
Priyadarshan Singh

Posted on Originally published at nossletter.tech

Optimizing DuckDB Scalar Aggregates: Merging Multiple Scans into One Pass

#duckdb #queryoptimization #database #performance

Why This Optimization Matters

If you’ve ever run complex analytical queries on large datasets, you know that scanning the same data multiple times can be painfully expensive. In DuckDB, queries with multiple scalar aggregates over the same file or table used to trigger redundant full data scans — one for each aggregate. This inefficiency bloated IO costs and slowed down query execution significantly, especially for formats like Parquet, CSV, and JSON.

This article dives into a recent DuckDB improvement that tackles this problem head-on by merging sibling scalar aggregates into a single scan. We’ll explore the root cause, the technical fix, and the broader lessons for query optimization.

The Problem: Redundant Full Scans for Each Scalar Aggregate

Scalar aggregates are functions like COUNT(), SUM(), or AVG() that return a single value for an entire dataset or partition. When multiple scalar aggregates operate on the same table or file, DuckDB’s optimizer previously treated each aggregate independently. That meant:

  • Each scalar aggregate triggered its own full scan of the underlying data source.
  • For large files or table functions, this multiplied IO costs and CPU time.
  • The query plan duplicated scanning effort instead of sharing it.

For example, consider a query that calculates both COUNT(*) and SUM(sales) on a large Parquet file. Under the old plan, DuckDB would scan the Parquet file twice — once for each aggregate — even though the data read was identical.

This inefficiency was especially pronounced for external data sources like Parquet, CSV, or JSON files, where IO costs dominate query performance.

The Root Cause: Lack of Aggregate Merging in the Optimizer

The core issue was that DuckDB’s optimizer lacked a rewrite rule to combine sibling scalar aggregates operating on the same table or table function. Without this, each aggregate subquery independently triggered a full scan.

The ideal approach is to merge these aggregates into a single scan that computes all aggregates simultaneously, using conditional aggregation to distinguish which rows contribute to which aggregate.

However, implementing this is non-trivial:

  • The optimizer must detect when multiple aggregates scan the same table or table function.
  • It must merge those aggregates into a single scan node.
  • Each aggregate needs a FILTER clause that acts as a predicate to compute the aggregate only over relevant rows.

Furthermore, this optimization must be cost-aware. For native DuckDB tables, adding FILTER predicates can introduce overhead that outweighs the benefits of merging scans. Thus, the rewrite should be gated by a configurable threshold.

The Fix: Introducing the scalar_aggregate_merge Optimizer Rule

To address this, the DuckDB team introduced a new optimizer rule called scalar_aggregate_merge. This rule:

  • Detects sibling scalar aggregates over the same table function or large catalog tables.
  • Merges these aggregates into a single scan node.
  • Adds per-aggregate FILTER predicates to ensure each aggregate computes over the correct subset of rows.

This rewrite is implemented in scalar_aggregate_merger.cpp. The code tracks identical table function calls and merges their aggregates accordingly.

The optimization is gated by a configurable threshold to avoid regressions on native DuckDB tables, where the overhead of applying FILTER predicates might exceed the savings from scanning once.

How It Works in Practice

Imagine a query with two scalar aggregates:

SELECT
  (SELECT COUNT(*) FROM read_parquet('data.parquet')) AS total_count,
  (SELECT SUM(amount) FROM read_parquet('data.parquet') WHERE amount > 100) AS big_sales_sum;
Enter fullscreen mode Exit fullscreen mode

Before this change, DuckDB would scan data.parquet twice: once for the COUNT and once for the SUM.

With scalar_aggregate_merge, DuckDB merges these scans into one pass over data.parquet, computing both aggregates simultaneously. It applies FILTER clauses internally so that SUM(amount) only sums rows where amount > 100, but the file is read just once.

This reduces IO significantly and speeds up query execution.

Takeaway: Cost-Based Aggregate Merging is Key for Performance

This optimization highlights an important principle in query optimization:

  • When multiple aggregates scan the same data independently, merging them into a single scan with conditional aggregation can drastically reduce IO and improve performance.
  • However, this optimization must be cost-aware. If the overhead of filtering exceeds the saved scans, performance can degrade.

DuckDB’s approach of gating the rewrite by a configurable threshold ensures the optimization applies only when beneficial.

For database engineers and optimizer developers, this serves as a reminder that seemingly straightforward optimizations require careful cost modeling to avoid regressions.

This improvement in DuckDB not only boosts performance for common analytical queries over external data sources but also demonstrates how thoughtful optimizer design can yield significant real-world gains.

Credits: This article is based on the PR "Compute sibling scalar aggregates over the same table in one pass" merged into the DuckDB repository.

Read the full breakdown on nossletter.tech.

Originally published on Nossletter.

Top comments (0)