Predicate pushdown moves a query condition into an underlying data operation, so filtering happens before later result processing. With Parquet, DuckDB can evaluate predicates at the storage layer and avoid loading irrelevant data; with views, additional WHERE conditions can be applied to the underlying query rather than after the view’s data is loaded. section 1.7.1 section 3.3.3
Use predicate pushdown to keep analytical workflows selective and efficient:
- Querying Parquet files can read only relevant data segments instead of loading the complete dataset. section 5.4
- Combine predicate pushdown with projection pushdown to fetch only matching rows and selected columns. section 5.4
- For files in S3 or other cloud buckets, execute computation near the data to reduce network transfer, latency, and egress costs. section 10.4
- Treat views as reusable query interfaces, but materialize a temporary table with CTAS when repeated view execution causes performance problems. section 3.3.3
Filtering a power-production view
Define an aggregated relation once, then apply a condition when querying it: CREATE OR REPLACE VIEW v_power_per_day AS SELECT system_id, date_trunc('day', read_on) AS day, round(sum(power) / 4 / 1000, 2) AS kWh FROM readings GROUP BY system_id, day; SELECT * FROM v_power_per_day WHERE system_id = 1; DuckDB can push the additional predicate into the view’s underlying query, avoiding a post-load filter. section 3.3.3
Listing 3.4 Creating a view for power production by system and day
CREATE OR REPLACE VIEW v_power_per_day AS
SELECT system_id,
date_trunc('day', read_on) AS day,
round(sum(power) / 4 / 1000, 2) AS kWh,
FROM readings
GROUP BY system_id, day;
Inspect the view definition that supplies the underlying relation for pushed-down predicates. section 3.3.3
