Predicate Pushdown

Partitions and footer statistics save bytes only when the engine compares the query's filter to them before reading. Predicate pushdown is that hand-off: the engine moves a filter from the query down into the scan, which tests it against partition values, row-group min and max, and bloom filters. A filter left above the scan runs after the data is read, so every byte gets fetched first.

One filter, 2 spellings

fct_orders is partitioned by event_date and country. 3 days times 2 countries gives 6 partitions. Each partition holds 1 file of 5 row groups of 128MB, and the footer records min and max order_ts for each row group. The query asks for orders placed on 2026-04-22 at or after 19:00. Switch between the 2 spellings and watch which row groups the engine opens.

SELECT
  order_id,
  order_ts
FROM fct_orders
WHERE event_date = DATE '2026-04-22'
  AND order_ts >= TIMESTAMP '2026-04-22 19:00:00'

SELECT
  order_id,
  order_ts
FROM fct_orders
WHERE date(order_ts) = DATE '2026-04-22'
  AND hour(order_ts) >= 19

The first spelling names event_date, so the engine keeps the 2 partitions for 2026-04-22 and drops the other 4. Inside each file it compares 19:00 to each row group's max order_ts. The first 3 row groups end at 18:00:00 or earlier and are skipped; the last 2 are read. That is 4 row groups and 512MB of file, out of 30 row groups and 3,840MB. The second spelling filters on hour(order_ts), a value no footer stores, and never mentions event_date, so unless the engine rewrites it, the scan reads all 30 row groups and filters the rows afterwards.

• 7.5x

Writing filters the scan can test

4 habits that keep a filter pushable

• Compare the bare column. order_ts >= TIMESTAMP '2026-04-22 19:00:00' can be tested against min and max; hour(order_ts) >= 19 cannot.
• Filter the partition column itself. A condition on order_ts tells the engine nothing about the event_date directories, so state the event_date condition as well.
• Move arithmetic to the constant side. Write event_date >= DATE '2026-04-22' instead of date_add('day', 7, event_date) >= DATE '2026-04-29'.
• Match types. Compare a DATE column to a DATE literal. When types differ, the engine may cast the column on every row, and a cast column can lose its pushdown.

Some engines rewrite a few common wrappers, such as a cast from timestamp to date, back into a range on the bare column. Whether yours did shows up in the plan, covered below.

Which filter lets the engine skip fct_orders row groups using the footer's min and max order_ts?

• hour(order_ts) BETWEEN 19 AND 23
• CAST(order_ts AS varchar) >= '2026-04-22 19:00:00'
• order_ts >= TIMESTAMP '2026-04-22 19:00:00'
• date_add('hour', 5, order_ts) >= TIMESTAMP '2026-04-23 00:00:00'

Bloom filters for needle lookups

Min and max help when values are clustered: a row group spanning 06:00 to 12:00 rules itself out for 19:00. They fail on a column like customer_id, whose values are scattered, so every row group's min and max span almost the whole id range. A bloom filter fills that gap. At write time the writer builds a small bit array per row group from every customer_id in it. At read time the engine asks whether c_48213 could be present. A no is certain, and the row group is skipped. A yes means possibly, with a false-positive rate set at write time, often about 1%.

SELECT
  order_id,
  order_ts
FROM fct_orders
WHERE event_date = DATE '2026-04-22'
  AND customer_id = 'c_48213'

Say the customer ordered once on 2026-04-22, so the order sits in 1 of the 10 row groups across that date's 2 files. The bloom filters rule out the other 9, barring a false positive. They only help with equality and short IN lists. Neither customer_id LIKE 'c_48%' nor a range filter gets anything from them. They also exist only for columns the writer was configured to build them on.

Verify with EXPLAIN

Pushdown is the engine's decision, so check it instead of assuming it. EXPLAIN prints the plan without running the query. EXPLAIN ANALYZE runs it and adds what each step actually read.

To check the first spelling, put EXPLAIN ANALYZE on the line above its SELECT and run it. The plan lists the scan of fct_orders with the constraint the engine pushed into it, and the input size that scan read.

A customer placed 1 order on 2026-04-22. EXPLAIN ANALYZE on a query filtering event_date = DATE '2026-04-22' and customer_id = 'c_48213' shows the scan read all 10 row groups for that date. What is the likely cause?

• Bloom filters only work on partition columns such as event_date
• The files were written without bloom filters on customer_id
• The event_date condition used up the pushdown, so the second was ignored
• Equality conditions use min and max only, and bloom filters need LIKE

About this lesson

This section is part of the Storage Layers: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.