Slow DuckDB Query? Check Where the Aggregate Runs

A query that returns four rows might fetch thousands to calculate them. When DuckDB queries a remote database, it can sometimes push the aggregate to that server and receive only the totals. In other cases, it fetches the input rows and aggregates locally.

That boundary depends on the connector, its version, and the expressions in your query. If a query feels slow, finding where the work happens gives you a useful place to investigate.

Same answer, different work

Our example has 10,000 orders across four regions. Every fifth order is cancelled, leaving 8,000 paid orders. Here are the first eight rows; the CSV contains the full dataset.

This query asks for four regional totals. Here, pg names an existing PostgreSQL connection through ADBC Scanner:

SELECT region, sum(amount) AS total
FROM pg.public.orders
WHERE status = 'paid'
GROUP BY region
ORDER BY region;

The result alone cannot tell you where the aggregation happened. The execution plan can.

Read the two plans

When the aggregate runs remotely, the captured plan contains GROUP BY inside Remote SQL, and the remote scan returns 4 rows:

Table Scan
  Remote SQL:
  SELECT region, sum(amount) AS total FROM public.orders WHERE (status = 'paid') GROUP BY region ORDER BY region
  Database: pg
  4 rows

PostgreSQL filters, groups, and sums. DuckDB receives the finished totals.

When the aggregate runs locally, the scan returns 8,000 rows, then a DuckDB Hash Group By reduces them to four:

Order By
  Order By: region ASC
  4 rows

Hash Group By
  Groups: #0
  Aggregates: sum(#1)
  4 rows

Projection
  Projections: region,
  amount
  8,000 rows

Table Scan
  Table: orders
  Database: pg_local
  Projections: status,
  region,
  amount
  Filters: status = 'paid'
  8,000 rows

For this comparison, we disabled whole-query pushdown on a second attachment, pg_local, using query_pushdown false. Both attachments read the same data. The filter still runs in PostgreSQL; the aggregate moves to DuckDB. Pushdown is not necessarily all-or-nothing.

Find the boundary when a query is slow

You can cross that boundary by changing an expression, too. In our tested connector build, sum(length(status)) fell back to local aggregation—even though PostgreSQL has a length function. The connector must support translating the complete expression safely. Its dialect checks determine what it can translate; support can change between releases.

For your own query:

1. Inspect the plan. Prefix it with EXPLAIN. Look for the aggregate in generated remote SQL, or a local grouping operator above the remote scan. Operator names and details vary by connector.
2. Measure the work. Use EXPLAIN ANALYZE when you are ready to execute the query. Compare rows entering the local aggregate with rows leaving it, and inspect operator timings. A large reduction tells you many input rows arrived for a small answer.
3. Try one change. Simplify a suspect expression and inspect the plan again. Check that the results stay equivalent before comparing elapsed time. Record the engine, connector, and driver versions.
4. Follow the evidence. If the aggregate already runs remotely, inspect the remote query’s plan and server-side work. If it runs locally, investigate the rows being fetched and the local operators.

Fewer transferred rows can help, but they do not prove a faster query. Remote aggregation also consumes server resources. The useful skill is knowing which side is doing the work, then measuring that work.

Optional: try the calculation

Use the same orders to check an aggregate of your own. This runs locally; the captured plans above demonstrate remote pushdown.

SELECT region, sum(amount) AS total
FROM orders
WHERE status = 'cancelled'
GROUP BY region
HAVING sum(amount) > 25000
ORDER BY region;

Download the sample data SQL and query.

The captured plans use DuckDB 2.0.0, PostgreSQL 14.24, ADBC PostgreSQL 1.13.0, and ADBC Scanner build 02ccfac995. Timings are omitted. The full evidence and reproduction script are available if you want to examine the measurements.