Leveraging DuckDB's Automatic Column Pruning to Speed Up Wide Parquet Queries

When you need to run analytical queries against a large Parquet file that contains dozens or hundreds of columns, reading the whole file can waste I/O and memory even if you only need a few fields. DuckDB addresses this with automatic column pruning: it reads only the columns referenced in the query, pushing the filter down to the storage layer. This post shows how to verify that pruning is active, run a concrete example, and understand the trade‑offs.

How Column Pruning Works in DuckDB

DuckDB’s query optimizer treats Parquet (and other columnar formats) as a set of independent column streams. During planning, it builds a Projection operator that lists the required columns and replaces a full ColumnScan with a pruned scan that fetches only those streams. Because execution is vectorized, the data is processed in SIMD‑friendly batches, reducing per‑row overhead.

Pruning happens automatically; you do not need to create indexes or specify storage options. The only prerequisite is that the table is backed by a columnar file format that DuckDB can natively read (Parquet, CSV, JSON, etc.). For CSV the benefit is limited because the format is row‑oriented.

Worked Example: Querying a Wide Parquet Dataset

Assume you have a Parquet file events.parquet with 150 columns, including user identifiers, timestamps, and many metric fields. You want to count distinct users per day, using only user_id and event_ts.

1. Start DuckDB (no server needed).
2. Create a view that points directly at the file:

-- Run in the duckdb CLI or any client that can execute SQL
CREATE VIEW events AS SELECT * FROM read_parquet('/path/to/events.parquet');

1. Run the analytical query:

SELECT date_trunc('day', event_ts) AS day,
       COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY day
ORDER BY day;

Even though the source file has 150 columns, DuckDB should only read user_id and event_ts from disk.

Verifying That Pruning Is Active

Use the EXPLAIN statement to inspect the logical plan. Look for a ColumnScan node that lists the scanned columns and a subsequent Projection that matches the query’s select list.

EXPLAIN SELECT date_trunc('day', event_ts) AS day,
               COUNT(DISTINCT user_id) AS dau
        FROM events
        GROUP BY day;

Expected output (simplified):

Projection
  ├─ ColumnScan (events) columns=[user_id, event_ts]
  └─ Aggregate …

If you see columns=[*] or a long list of all column names, pruning is not happening—check that the table is indeed a Parquet file and that you are not using a CSV fallback.

For a runtime check, compare the query execution time with and without pruning. A simple way is to run the query twice: once referencing only the two columns (as above) and once referencing all columns with SELECT *. The latter will read the entire file and should take noticeably longer, especially on slow storage or network‑mounted object stores.

Trade‑offs and Limitations

• Format dependency: Pruning works only for columnar formats. If your data lives in CSV or JSON, DuckDB will still read the whole file (though it can still apply vectorized execution on the parsed rows).
• Memory pressure: While I/O drops, vectorized batches can still consume memory proportional to the number of rows in the selected columns. Extremely wide rows with high cardinality may cause spilling; you can mitigate this by setting a memory limit via SET memory_limit='2GB';.
• Statistics reliance: The optimizer uses column statistics (gathered by ANALYZE) to estimate selectivity. If statistics are stale, the plan may choose a suboptimal join order, though pruning itself remains active.
• No DML support: DuckDB excels at analytical reads; it does not support triggers, stored procedures, or user‑defined functions beyond SQL, so it cannot replace a transactional OLTP system.

Practical Way to Check the Result

After running your query, you can confirm that only the expected columns were read by examining the I/O counters:

-- Enable profiling (requires duckdb version ≥0.9.0)
PRAGMA enable_profiling;
SELECT date_trunc('day', event_ts) AS day,
       COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY day;
PRAGMA show_profiling;

The profiling output includes a bytes_read metric for the ColumnScan. Compare this value to the file size on disk; it should be roughly proportional to the size of the two columns you selected.

Closing Thoughts

Automatic column pruning is a low‑effort, high‑impact feature that lets you treat large Parquet files as if they were already sliced to the columns you need. By verifying the plan with EXPLAIN and checking I/O via profiling, you can be confident that DuckDB is not wasting bandwidth on unused fields. Keep in mind the format requirement and monitor memory usage on very wide tables, but for most analytical workloads on columnar storage, pruning delivers measurable speed‑ups with virtually no configuration overhead.