EnginesGENERALENGINE-SPECIFICSIMULATED

Predicate Pushdown

Push the filter down to the reader so less is read at all — and learn the identical-looking query where it silently does not happen.

What actually happens How to build it Can I trust it?▶ Run the lab

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

My query filters to one day out of a year. Why did it read the whole year?

Who needs this

Anyone whose query is billed or throttled by bytes scanned, and anyone waiting on a dashboard that filters to last week. They are not asking for a faster engine; they are asking for the engine to stop reading data the query already said it did not want.

What one row is

Pushdown operates on skippable units, and there are three sizes of them: a partition (a directory or a manifest entry), a file or row group (a chunk with min/max statistics in its footer), and a page or block inside that chunk. A predicate prunes at whichever of these levels the reader can evaluate it, and at no level below that (Parquet Internals).

The obvious build

Write the filter in the WHERE clause and trust the engine. It is a declarative language: you said which rows you wanted, the optimiser exists to work out how, and every tutorial reinforces that this is somebody else's problem. For a well-laid-out table with a straightforward predicate this is entirely correct and the query prunes perfectly.

Why it breaks

The predicate is wrapped in a function — WHERE date(event_time) = DATE '2024-03-14' — so the engine cannot map it onto the partition values it holds. The filter is still applied, correctly, *after* reading every partition in the table.

How it breaks with real data

The predicate is wrapped in a function — WHERE date(event_time) = DATE '2024-03-14' — so the engine cannot map it onto the partition values it holds. The filter is still applied, correctly, *after* reading every partition in the table.
The predicate's type does not match the column's. Comparing a string partition column to a date literal, or an int column to a numeric literal that needs widening, can force an implicit cast on the *column* side, and a cast on the column side is a function on the column side (Nullability & Defaults).
The filter comes from a join rather than a literal — WHERE order_date IN (SELECT d FROM dim_dates WHERE is_holiday). Nothing is known at planning time, so nothing can be pushed unless the engine implements dynamic filtering, which many do only for some join shapes (Broadcast Joins).
The table is partitioned by the wrong column entirely. Filtering on event_date in a table partitioned by country prunes nothing at all, however clean the predicate is (Partitioning).
The predicate is OR-ed across two columns, or negated, or applied to a column with high cardinality and no sort order, so the min/max statistics in every row group span the value being sought and none can be skipped (Clustering and Sort Order).
A SELECT * upstream in a view means the pushdown that did happen saved nothing, because the reader is fetching every column of the surviving rows anyway (Projection Pushdown).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Filter early means read less. That is the entire idea, and its power comes from where the filter is evaluated rather than from what it computes. A predicate evaluated by the engine after the scan is correct and free of benefit; the same predicate evaluated by the reader before it fetches a chunk is the difference between a scan and a lookup (Sequential Scan, Page by Page).
The first and biggest level is partition pruning. Values encoded in the path or in a table format's manifests let the planner exclude whole directories without opening a single file. This happens before any I/O, and it is the only level that scales with the number of partitions rather than with the data (Partition Pruning).
The second level is statistics-based skipping. A columnar file carries per-row-group min/max, null counts and sometimes bloom filters in its footer, so the reader opens the footer, evaluates the predicate against the range, and skips row groups that cannot contain a match (The Parquet Read Path, Bloom Filters: Skipping Files That Cannot Contain the Key).
That second level only works when the data is clustered by the predicate column. If rows arrive in random order, every row group's min/max spans the whole domain and no row group can be excluded — the statistics are present, correct and useless (Clustering and Sort Order).
The third level is per-page or per-block skipping inside a chunk, and a related trick: applying the filter to the encoded representation before materialising values, so a dictionary-encoded column is filtered as integers rather than as strings (Dictionary, Run-Length, Delta and Bit Packing, Vectorized Execution).
Underneath all of it is a rewrite the planner performs before anything executes: pushing the filter node down through projections, unions and some joins, until it sits directly above the scan and can be handed to the reader. When the rewrite is blocked — by a function, a cast, a non-deterministic expression — the filter node simply stays where it is, and no error is produced (The Planner: Enumerating Ways to Answer).

Four places a filter can be evaluated

A WHERE clause is one thing to the person writing it and four different things to the machine executing it. The clause is always applied — the result is always correct — but where it is applied decides whether it eliminates I/O, eliminates decoding, or merely eliminates rows from a result set that has already cost you everything it was going to cost.

The levels are strictly ordered by leverage. Partition pruning happens before any file is opened and can eliminate almost the entire table for the price of reading metadata. Row-group skipping happens after opening a footer and eliminates chunks. Page-level skipping and encoded-domain filtering happen inside a chunk. Engine-level filtering happens after everything has been read and decoded, and eliminates nothing but rows.

This is also the answer to why analytics people care so much about layout. In a database, a selective predicate has an index to fall back on (Why Is This Query Slow? Indexes). Here, the fallback is the layout — and if the layout does not match the predicate, there is no fallback at all.

Level	What the reader evaluates	What it eliminates	Requires	Blocked by
Partition pruning	The predicate against partition values in paths or manifests, before any I/O.	Whole directories of files. Usually the largest saving available.	The predicate is on the partition column, in its type, unwrapped (Partitioning).	A function or cast on the column, a filter on a non-partition column, a value not known at planning time.
Row-group / chunk skipping	The predicate against min/max, null counts and bloom filters in the file footer.	Chunks inside a file that cannot contain a match.	Data clustered by the predicate column so ranges are narrow (Clustering and Sort Order).	Randomly ordered data — the statistics are correct and every range overlaps.
Page / block skipping	The predicate against finer-grained indexes inside a chunk.	Pages within a column chunk; decoding work rather than fetches.	A format and reader that maintain page-level indexes (Parquet Internals).	Formats without page indexes; predicates over expressions rather than raw values.
Encoded-domain filtering	The predicate against dictionary codes rather than materialised values.	Decoding and comparison cost, not I/O.	A dictionary or run-length encoded column (Dictionary, Run-Length, Delta and Bit Packing).	High-cardinality columns that were not dictionary encoded.
Engine-level filter	The predicate against fully decoded rows, above the scan.	Rows from the result. Nothing else.	Nothing — this is always available and always correct.	Nothing. This is where a blocked predicate lands, silently.

The query that prunes and the query that does not

SIMULATEDThe partition counts and row counts here are outputs of src/de/sim/layout.ts, pinned by scripts/de-sim.test.ts, over a synthetic 200-million-row clickstream. They demonstrate the shape of the effect; the magnitude on your data depends on your partition count, row width and how selective the predicate actually is.

Below are two queries against the same table, returning the same rows, differing by one function call. In the in-repo layout model the table is a clickstream partitioned by day across a year — 365 daily partitions holding 200,000,000 rows in total, one file per partition at the model's target file size.

The first query filters on the partition column directly. The planner matches the literal to the partition values, selects one partition, and the workers read one file. 1 partition read, 547,945 rows scanned. The second wraps the timestamp in a function, and the planner has no way to know which partitions can satisfy date(event_time) = ... without evaluating it — which means reading every row. 365 partitions read, 200,000,000 rows scanned.

Both return the same rows. Neither raises a warning. The only place the difference is visible before the bill is the plan, which is why "read the plan" is the recurring instruction in this lesson rather than a piece of general advice.

The layout below shows the pruning case. Note what the why column is doing: for the read partition it explains the match, and for the skipped ones it names the metadata that allowed the skip. Change the predicate to the opaque form and every read flips to true, with no other change to the table, the query text's meaning, or the result.

A day-partitioned clickstream under a clean date predicate

SELECT country, sum(revenue) FROM events WHERE event_date = DATE '2024-03-14' GROUP BY country

events/event_date=2024-03-12/~548k · 1 file · skipped
events/event_date=2024-03-13/~548k · 1 file · skipped
events/event_date=2024-03-14/~548k · 1 file · read
events/event_date=2024-03-15/~548k · 1 file · skipped
events/event_date=... (361 further daily partitions)~198m total · 361 files · skipped

1 of 5 shown paths are read.

SIMULATED — computed by analyzeLayout in src/de/sim/layout.ts over its declared synthetic dataset. The model reports 1 partition and 547,945 rows for this query and 365 partitions and 200,000,000 rows for the same query with the predicate wrapped in a function. These are properties of a model you can read, not measurements of a machine.

One function call apart

1-- Prunes. The planner matches DATE '2024-03-14' against the partition
2-- values it holds, selects one partition, and reads one file.
3SELECT country, sum(revenue) AS revenue
4FROM events
5WHERE event_date = DATE '2024-03-14'
6GROUP BY country;
7 
8-- Does not prune. Correct, identical result, entire table read.
9-- The planner cannot invert date(event_time) to a set of partition values,
10-- so it reads every partition and applies the filter afterwards.
11SELECT country, sum(revenue) AS revenue
12FROM events
13WHERE date(event_time) = DATE '2024-03-14'
14GROUP BY country;
15 
16-- Also does not prune, and is harder to spot: event_date is stored as a
17-- string, so the comparison casts the column, not the literal.
18SELECT country, sum(revenue) AS revenue
19FROM events
20WHERE event_date = DATE '2024-03-14'   -- event_date VARCHAR
21GROUP BY country;
22 
23-- Prunes on the partition and refines within it. This is the form to reach
24-- for when the natural predicate is on a timestamp.
25SELECT country, sum(revenue) AS revenue
26FROM events
27WHERE event_date = DATE '2024-03-14'
28  AND event_time >= TIMESTAMP '2024-03-14 09:00:00'
29GROUP BY country;

The third query is the one that reaches production. Nobody wrote a function; the schema did it for them, and the only evidence is a cast on the column side of the comparison in the plan (Reading EXPLAIN ANALYZE).

Every way a predicate stops being pushable

The function-wrapped predicate is the famous case, and it is not the most common one in real platforms. The table below is the full list of ways a filter that looks pushable is not, ordered roughly by how often each one surprises somebody.

Two of them deserve emphasis because they are not the query author's doing. A filter derived from a join is not known at planning time at all — the engine can only push it if it implements dynamic filtering and the join shape qualifies. And a BI tool or a view can wrap a user's filter in an expression the user never wrote and cannot see, so the plan contains a function nobody typed (The Metrics Layer).

The response column is the useful part. Almost every row resolves to one of three moves: change the predicate so the column appears bare, change the layout so the predicate matches the partition or sort key, or materialise the value at write time so the read-time expression becomes unnecessary. Adding capacity appears nowhere on the list.

The predicate looks pushable and is not

Trigger	Symptom	Cause	Response
A function around the column: `date(ts)`, `upper(country)`, `substr(id, 1, 8)`.	Full table scan; results correct; plan shows an engine-level filter above the scan.	The planner cannot invert an arbitrary expression into a set of partition or statistic ranges.	Put the bare column on one side. Where the expression is genuinely needed, materialise it as a stored column at write time (Partitioning).
A type mismatch between the column and the literal.	Identical SQL to the version that prunes, and no pruning. Frequently invisible in review.	Implicit coercion puts a cast on the column side, which is a function on the column side.	Fix the column type, or write the literal in the column's type. Check the plan for a cast wrapping the column (Nullability & Defaults).
The filter values come from a subquery or a join.	The probe-side table is scanned in full even though only a few keys match.	The values are unknown at planning time; only dynamic filtering during execution can help, and not for every join shape.	Confirm dynamic filtering applies, or materialise the driving values into a literal list for the common case (Broadcast Joins).
The filter is on a column the table is not partitioned or sorted by.	Every partition read; row-group statistics skip nothing.	There is nothing in the layout for the predicate to match, and no index to fall back on.	A layout decision, not a query fix: partition or cluster by the column people actually filter on (The Partitioning Decision).
`OR` across two different columns, or a negated predicate.	Partial or no skipping even on a clustered column.	Statistics prune a range; a disjunction over separate columns and a negation both widen the candidate set to nearly everything.	Rewrite as a `UNION ALL` of two prunable branches where the semantics allow, and accept the scan where they do not.
Data arrives unsorted within each partition.	Partition pruning works; row-group skipping does nothing.	Every chunk's min/max spans the domain, so no chunk can be excluded.	Sort within the partition at write time, or during compaction (Clustering and Sort Order, File Compaction).
A view or BI tool wraps the filter in an expression.	The user's SQL is clean; the plan contains a function nobody wrote.	A timezone conversion, a coalesce, or a formatting expression applied in the view definition.	Inspect the expanded plan rather than the authored query, and push the conversion to write time.
A non-deterministic function in the predicate, such as a current-time call.	No pruning, and results that differ between runs.	The planner cannot fold a value it is not allowed to assume is stable.	Bind the value once in the caller and pass it as a literal — which also makes the query reproducible (Idempotent Data Pipelines).

Product detail — verify current documentation

Which expressions an engine can invert — a simple cast, a date truncation, a comparison against a monotonic function — and whether dynamic filtering applies to a given join, both change between engine versions and between connectors. Do not carry a rule of thumb across engines; check the plan your engine prints for your query.

How to build it

Most important first.

Filter on the partition column itself, in its own type, with no function around it. If the partition column is event_date of type date, compare it to a date literal. Everything else in this list is a variation on that one sentence.
Where the natural predicate is on a timestamp, materialise the derived partition column at write time rather than deriving it at read time. WHERE event_date = DATE '2024-03-14' AND event_time >= TIMESTAMP '2024-03-14 09:00' prunes on the first clause and refines with the second (Partitioning).
Sort or cluster each partition by the column you filter on next. Partition pruning handles the first dimension; row-group statistics handle the second, and they only help if the data is ordered (Clustering and Sort Order).
Read the plan and confirm the filter appears as a partition predicate or a reader-level filter, not as an engine-level filter above the scan. This takes seconds and is the only way to know (Reading EXPLAIN ANALYZE).
Assert it in CI where it matters. A scheduled query whose bytes scanned jumps by two orders of magnitude after a harmless-looking edit is exactly the regression a test on plan shape or scanned bytes catches (Scan Cost).
Where the filter genuinely comes from a join, either materialise the driving values into a literal list, or confirm the engine's dynamic filtering applies to that join shape. Do not assume it does.

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

Pushdown never changes the result. Whether a predicate is evaluated by the reader or by the engine, the rows returned are the same — which is precisely why the failure is invisible.
Skipping is guaranteed sound, not guaranteed complete: statistics let the reader skip chunks that *cannot* match, and reading a chunk that turns out to contain no match is normal, not a fault.
Nothing guarantees a predicate will be pushed. It is an optimisation, and its absence is silent by design.
Partition pruning guarantees only that partitions excluded by the predicate are not read. It says nothing about whether the partitions that *were* read are complete (Missing Rows).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The check is a scanned-bytes assertion on scheduled queries: record bytes scanned per run and alert when it changes by an order of magnitude against its own history. It catches a lost pushdown, a partition scheme change, and a view that started selecting every column.
It misses the case where a query reads far too much and always has, because it is compared against its own baseline. It also fires falsely on legitimate backfills and on the first run after a genuine data growth event (Backfills).
The complementary check is on the plan itself: assert that the filter on the partition column appears as a partition predicate. It catches the regression on the day it is introduced rather than on the day someone reads the bill.

Freshness

Pushdown is a read-path property and adds no latency of its own; the freshness of the answer is entirely the writer's.
Indirectly it is the main reason freshness targets are affordable at all: a query that reads one partition can run every few minutes, while the same query reading a year cannot run hourly at any budget (Cost vs Freshness).
A layout that makes pushdown work — partitioned by time, clustered within the partition — is normally also the layout that lets a writer append the newest data without rewriting history.

When the schema or meaning changes

Retyping a partition column is the schema change most likely to silently disable pruning. The query still works, the results are still right, and the predicate now needs a cast on the column side (Breaking Schema Changes).
Changing the partition scheme — from date to date+country, or from daily to hourly — changes which predicates prune, and every existing query keeps working while some of them quietly begin scanning much more (Partition Cardinality).
Adding a column is neutral for predicate pushdown and not for projection pushdown, which is a good reason to think of the two as separate concerns despite the shared name (Projection Pushdown).

How to re-run this safely

There is nothing to repair: a query without pushdown produced correct results expensively. The recovery is the fix — rewrite the predicate, change the layout, or both.
When the fix is a layout change, it is a rewrite of existing files, and rewrites are exactly the operation to run against a staging location and publish atomically (Atomic Publish).
Re-clustering a large table is a real backfill with real cost, so it is worth deciding the sort order at write time rather than discovering it as a remediation (File Compaction).

What can go wrong

Failure modes

The function-wrapped predicate: identical-looking SQL, correct results, full-table scan. The archetype of this lesson.
Silent type coercion putting a cast on the column side of the comparison.
A join-derived filter that cannot be known until execution, with dynamic filtering either absent or not applicable to that join shape.
Statistics that exist and cannot help, because unsorted data gives every row group an overlapping min/max range.
A view or a BI tool wrapping the user's filter in an expression the user never wrote and cannot see (The Metrics Layer).
The mitigation failing in its own way: over-partitioning to make more predicates prune, which produces a small-files problem that costs more than the scans it saved (File Size and the Small-Files Problem).

Misreads

"The results are correct, so the query is fine." Correctness and pushdown are unrelated. The failure mode of this lesson is a query that is completely correct and reads a thousand times more than it needed to.
"The optimiser will rewrite my function away." Some engines can invert simple expressions on some types; none can invert an arbitrary function, and relying on which is which is relying on an engine version (Query Optimizers).
"Partitioning by more columns means more pushdown." It means more partitions, more files and more metadata. Past a point the small-files cost exceeds the scan saving (Partition Cardinality).
"Statistics mean the reader can skip." Only if the data is ordered such that the ranges are narrow. Statistics on randomly ordered data are perfectly accurate and completely useless (Clustering and Sort Order).
"Adding an index would fix this." There are no indexes here. Skipping comes from layout and file statistics, and that is the whole of it (An Index Scan Is Not Automatically Faster).

Operating it

How you see it in production

Bytes scanned and partitions read per query, compared with the number of partitions the predicate should have selected. Those two numbers next to each other are the whole diagnosis (Scan Cost).
The plan's filter placement. Reader-level, partition-level or engine-level is printed and is the ground truth (Reading EXPLAIN ANALYZE).
Row-group skip counts where the engine exposes them — the ratio of row groups read to row groups available tells you whether clustering is doing anything.
Scanned bytes per scheduled query over time, as a chart. Pushdown regressions look like a step function, which is the easiest possible signal to alert on (What Actually Drives Data Platform Cost).

What changes at 10x and 100x

At 10x history, a query with working partition pruning barely changes: it still reads one day. The same query without pruning gets 10x more expensive, because its cost was always a function of the whole table.
At 100x, the two queries are not in the same category of thing. One is interactive and one is not runnable, and the SQL text differs by a single function call.
At high partition counts the pruning itself becomes work — the planner must evaluate the predicate against every partition's metadata — which is where manifest-based table formats pull away from directory listing (Open Table Formats).

What drives cost here

Bytes scanned is the driver, and pushdown is the largest single lever on it available to a query author without changing any data.
Partition pruning and column pruning multiply. Selecting one day out of a year and two columns out of eight is not two small savings, it is one large one (Projection Pushdown).
Metadata cost moves in the opposite direction: more partitions means more manifest and listing work, which is why pruning has an optimum rather than an unbounded benefit (Partition Cardinality).
The cost of a lost pushdown is not distributed evenly — it lands on the largest tables, which are exactly the ones where somebody wrote the filter carefully and never checked the plan.

What this approach costs

Designing for pushdown means committing to a partition column and a sort order, which makes one class of query cheap and leaves every other class scanning. There is no layout that prunes for all predicates (The Partitioning Decision).
Materialising a derived partition column costs storage and a write-time transformation, and buys pruning for the predicate people actually write. It is nearly always the right trade and it is not free.
Adding plan assertions to CI costs test maintenance and couples your tests to an engine's plan output, which changes across versions. The alternative is finding out from a bill.

Predicate and projection pushdown

Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.

Predicate and projection pushdown

Both queries return the same rows. One of them reads the whole table.

prunesthe predicate the planner can see through

SELECT country, sum(revenue)
FROM events
WHERE event_date = '2026-08-25'
GROUP BY country

1.9 MBsim scanned · 1sim of 365sim partitions read

reads far morethe query as configured

SELECT country, sum(revenue)
FROM events
WHERE date(event_time) = '2026-08-25'
GROUP BY country

709.5 MBsim scanned · 365sim of 365sim partitions read

Extra bytes read

365xsim

Rows scanned

200.0Msim

Columns read

2 of 8

Files opened

365sim

The date predicate is wrapped in a function, so the planner cannot match it to the partition values. Every partition is read. The query looks correct and scans the whole table.

Neither query is slower because the engine is slow. They differ in how much data the engine was allowed to skip — which is decided by the layout and by whether the predicate is written in terms the planner can match against it.

SIMULATEDBytes come from the layout model's declared column widths and encoding factors. The ratio between the two queries is the finding; the absolute figures are the model's.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe three levels of skipping — partition, chunk statistics, page — and the fact that an expression around the column blocks the first two, hold across columnar engines and formats. What differs is how much of the third level exists.
ENGINE-SPECIFICWhether an engine can invert a simple expression such as a cast or a date truncation, and whether dynamic filtering pushes a join-derived predicate into the probe-side scan, varies by engine and by version. Two engines over identical files can prune very differently.
SIMULATEDThe scan figures in this lesson come from src/de/sim/layout.ts, an in-repo model with a declared synthetic dataset, not from a benchmark. They exist to make the multiplication of pruning and projection concrete; the ratios in your data will differ with your row widths and cardinalities.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasequery-planner-internals cost-based-optimization reading-explain sequential-scan-internals query-tuning

Observabilityscan-vs-index-performance measure-before-optimizing

Cloudobject-storage