Predicate Pushdown

Old style: read all rows, filter in memory.

Predicate pushdown: push WHERE clauses down to the storage layer.

SELECT name FROM users WHERE age > 50 AND country = 'USA'

1. Stripe pruning: skip stripes where max(age) <= 50 OR country dict doesn't contain 'USA'.
2. Page pruning: within stripe, skip pages with max(age) <= 50.
3. Run-level skip: in RLE-encoded country column, skip runs of value != 'USA'.
4. Bit-mask: produce a survivor mask per page.

Each level cuts I/O exponentially.

Late materialization: only fetch projection columns for survivors.

Order of predicates matters: cheapest + most-selective first.

• Indexed equality (cheap, very selective): first.
• Range scan (cheap-ish): middle.
• Substring match (expensive): last.

Pushdown across systems:

• SQL → query plan → pushed to Parquet reader → pushed to S3 (range requests).
• Iceberg, Delta Lake table formats track per-file stats for cross-file pushdown.

Modern push-down can also include:

• Aggregate pushdown: storage computes COUNT, MIN, MAX.
• Projection pushdown: read only required columns.
• Partition pruning: skip partitions by directory naming (year=2024/month=01/...).

Effect: a 100GB table query might read 100MB.