DuckDB Performance Tuning: Ten Techniques That Work

Start by measuring

Guessing at optimizations wastes time. DuckDB's profiler tells you exactly where a query spends itself:

EXPLAIN ANALYZE
SELECT country, count(*) FROM events
WHERE timestamp >= current_date - 30 GROUP BY country;

Read the output bottom-up. The scan node reports how many rows it produced and how many it filtered out. Two numbers matter most: rows scanned versus rows returned (a large gap means the filter is not being pushed down), and which node dominates the time.

1. Set memory_limit and threads explicitly

SET memory_limit = '4GB';
SET threads = 4;

DuckDB sizes defaults from the host machine, which is wrong inside a container with a lower cgroup limit — it will happily try to use memory it cannot have and get OOM-killed. Set both to match the container, not the host.

2. Select only the columns you need

SELECT * defeats the main advantage of columnar storage. On a 30-column table, selecting the 4 columns you actually use can be close to an order of magnitude less I/O. This matters far more here than it does on a row store, where the difference is marginal.

3. Filter early, and filter on the partitioning column

Put the most selective predicate on the column your data is ordered or partitioned by — usually the timestamp. Filters on the scan node prune whole row groups before decompression. Filters applied after a join or an aggregation have already paid for the rows they discard.

4. Avoid functions on filtered columns

-- prevents pushdown: the function must run on every row
WHERE date_trunc('day', timestamp) = DATE '2026-07-18'

-- allows pruning on min/max statistics
WHERE timestamp >= DATE '2026-07-18' AND timestamp < DATE '2026-07-19'

Wrapping a column in a function makes its statistics unusable. Rewriting to a plain range predicate is often the single largest win available in a slow query.

5. Let small tables drive joins

DuckDB's optimizer generally picks a sensible join order when it has statistics. It has statistics on tables; it has fewer on subqueries and CTEs. When joining a filtered event set to a small dimension, filter the events first, in a CTE, so the join sees a small input:

WITH recent AS (
  SELECT * FROM events WHERE timestamp >= current_date - 7
)
SELECT d.country, count(*)
FROM recent r JOIN dim_geo d USING (geo_key)
GROUP BY d.country;

6. Reduce cardinality before aggregating

count(DISTINCT visitor_id) over hundreds of millions of rows builds a large hash set. When exactness is not required, approximate counting is dramatically cheaper:

SELECT approx_count_distinct(visitor_id) FROM events WHERE …;

Typical error is around 1–2%. For a "unique visitors" tile on a dashboard that is entirely acceptable; for billing it is not. Know which one you are building.

7. Materialize what you query repeatedly

Views are re-executed on every query. If ten dashboard tiles all read from the same expensive view, you compute it ten times.

CREATE OR REPLACE TABLE daily_rollup AS
SELECT site_id, timestamp::DATE AS day, country, device_type,
       count(*) AS pageviews,
       approx_count_distinct(visitor_id) AS visitors
FROM events
GROUP BY 1,2,3,4;

A daily rollup refreshed hourly turns most dashboard queries into scans of thousands of rows instead of millions. The trade is staleness — make the refresh interval an explicit product decision, not an accident.

8. Prune Parquet with partitioned paths

When reading files, directory structure is part of the query plan:

SELECT count(*) FROM read_parquet('cold/y=2026/m=07/*.parquet');
-- or with hive partitioning inferred from the paths
SELECT count(*) FROM read_parquet('cold/**/*.parquet', hive_partitioning = true)
WHERE y = 2026 AND m = 7;

With hive partitioning, a predicate on y/m skips entire directories without opening a single file. Also keep files reasonably sized — hundreds of tiny Parquet files cost more in per-file overhead than they save.

9. Sort data on the column you filter by

Row-group min/max statistics only prune well when data is clustered. If rows arrive in timestamp order, they already are. If you bulk-load out of order, sorting on insert restores pruning:

CREATE OR REPLACE TABLE events AS SELECT * FROM events ORDER BY site_id, timestamp;

10. Batch writes, and checkpoint

Single-row inserts are the most common cause of "DuckDB is slow" reports, and it is a write-path problem masquerading as a read-path one. Insert in batches of thousands. After a large load, run CHECKPOINT to flush the write-ahead log into the main file so subsequent reads are not replaying it.

Frequently asked questions

What should I measure before optimizing?

EXPLAIN ANALYZE on the real query. It shows where time actually goes, which is regularly not where you assumed — usually a scan that failed to prune rather than the join you were suspicious of.

Does DuckDB need indexes?

Rarely. Columnar scanning with zone maps and filter pushdown covers most analytical access patterns. Indexes help point lookups and enforce constraints, but adding them to speed up aggregations usually does nothing.

Why does wrapping a column in a function slow things down?

It defeats pushdown. WHERE date_trunc('day', ts) = '2026-01-01' forces evaluation of every row, while WHERE ts >= '2026-01-01' AND ts < '2026-01-02' lets the scanner skip whole row groups on statistics.

Should I set memory_limit and threads explicitly?

Yes, in any service context. Defaults are derived from the machine, which in a container often means the host's resources rather than the container's — the usual cause of an out-of-memory kill under load.

When should a view become a materialized table?

When the same view is queried repeatedly between refreshes and its cost is non-trivial. A view recomputes every time; materializing trades storage and staleness for a fixed read cost.

A tuning order of operations

1. EXPLAIN ANALYZE the slow query — find the dominant node.

2. Cut columns (SELECT * → explicit list).

3. Make filters pushable — no functions on filtered columns.

4. Check rows-scanned vs rows-returned; if the gap is large, the filter is not pruning.

5. Only then consider rollups, approximation, or restructuring.

Most "DuckDB is slow" cases resolve at step 2 or 3. Reach for architecture changes after you have confirmed the query is not simply reading far more than it needs. See also Parquet cold storage and why columnar is fast.