Predicate pushdown
Predicate pushdown is a query optimization that moves filter conditions as close as possible to the data source, so fewer rows (or bytes) are read before filtering happens.
Overview
Without pushdown, a naive query engine would read an entire table or file into memory and only then apply a WHERE clause. Predicate pushdown instead moves the filter down into the scan itself — or even into the storage format or remote source — so that data which can't possibly match is never read in the first place. This is one of the highest-leverage optimizations for large datasets, since I/O is usually the dominant cost of an analytical query.
Why It Matters
Consider a query filtering a billion-row table down to a few thousand matching rows. Without pushdown, the engine reads and processes all billion rows before discarding most of them. With pushdown into a columnar file format, the engine can use per-block statistics to skip whole blocks that can't match, and skip fetching columns the query doesn't need at all.
DuckDB's Pushdown into Parquet and Remote Files
DuckDB's optimizer pushes filter and projection predicates down into its scan operators. When scanning Parquet files, this means DuckDB checks each row group's footer statistics (min/max ranges) and Bloom filters before reading data, skipping row groups that can't match:
Copy code
EXPLAIN SELECT customer_id, total
FROM read_parquet('s3://bucket/orders/*.parquet')
WHERE order_date = '2024-06-01';
The plan shows the filter applied at the PARQUET_SCAN step rather than as a separate operator afterward. When reading from remote storage via httpfs, pushdown becomes even more valuable: DuckDB only fetches the byte ranges it actually needs, reducing network transfer as well as local processing.
Related terms
Query optimization is the process by which a database transforms a SQL query into an efficient execution plan, choosing among logically equivalent strategies for the one expected to run fastest.
row group →A row group is a fundamental storage concept in columnar databases like DuckDB that represents a horizontal partition of data containing a fixed number of…
Parquet →Apache Parquet is a columnar storage file format designed for efficient data processing and analytics.
ORC →ORC (Optimized Row Columnar) is a columnar file format originally built for Hadoop and Hive, designed for fast reads, high compression, and efficient predicate pushdown on large analytical datasets.
Bloom filter →A Bloom filter is a space-efficient probabilistic data structure used to test whether a value might be in a set, with no false negatives but a small, tunable rate of false positives.
Reading files in DuckDB →DuckDB can read CSV, Parquet, JSON, and other file formats directly with SQL table functions, whether the files are local, remote over HTTP/S3, or matched in bulk with a glob pattern.
FAQS
Run EXPLAIN on the query. If the filter is shown as part of the scan operator (e.g., PARQUET_SCAN) rather than as a separate FILTER step applied afterward, it was pushed down.
Yes. When DuckDB reads remote Parquet files via httpfs, pushed-down filters let it skip fetching row groups and byte ranges that can't match, reducing network transfer as well as local scan time.
