Predicate pushdown and why it speeds queries

WHY IT EXISTS The cheapest data to process is data you never read or move. Predicate pushdown exists so the optimizer applies filters at the earliest, lowest point in the plan, minimizing the rows that flow upward through joins, network hops, and aggregations.

THE MENTAL MODEL Think of a query plan as a tree. A naive engine scans everything, ships it up, and filters at the top. Pushdown rewrites the plan so the WHERE predicate executes during the scan or at the data source itself, discarding non-matching rows before they ever travel.

HOW IT WORKS In a distributed database the coordinator sends predicates to each storage node or partition so filtering happens locally and only matching rows return; with partitioned or file-based storage the engine can skip whole partitions or files whose min/max statistics exclude the predicate, called partition pruning or file skipping. Through a complex view, the optimizer pushes the calling query's filter into the view's underlying scan instead of materializing the full view then filtering. Columnar formats like Parquet expose row-group statistics that make pushdown especially effective.

WHEN IT MATTERS AND LIMITS It is critical for any large or distributed scan because data movement dominates cost. But predicates cannot always be pushed: filters depending on aggregate results, non-deterministic functions, or columns not present at the source may have to stay higher in the plan.

LIKELY FOLLOW-UPS How it differs from projection pushdown, how partition pruning uses statistics, and why pushing past a GROUP BY can be unsafe.

ONE CONCRETE EXAMPLE Querying a year of events filtered to one day over Parquet on object storage: pushdown skips eleven months of files using min/max stats and filters within row groups, so the engine reads and transfers a tiny fraction of the data instead of the whole dataset.