In a query plan, what does it mean to "push a predicate down", and what does the optimizer gain by doing it? Include projection pushdown in your answer.

## The plan as a pipeline An execution plan is a tree of operators - scans at the leaves, then joins, aggregations, sorts, and a final projection. Rows flow from leaves to root. The cost of every operator is roughly proportional to the number and width of the rows it receives. That single observation drives the whole family of pushdown rewrites: **discard rows and columns as early as possible.** ## Predicate pushdown SQL lets you write filters where they read best - usually a flat `WHERE` at the top. The rewriter is free to move each conjunct downward, as long as results are unchanged. Concretely: - A predicate on a single table moves below the join onto that table's scan. A join of a million-row and a ten-million-row table where a filter reduces one side to a thousand rows becomes a completely different problem. - A predicate that references only grouping keys moves below a `GROUP BY` (below `HAVING`, in other words), reducing the number of groups built. - A predicate can be pushed into a scan as a *storage-level* filter: an index range condition, a partition-elimination condition, a column-store min/max skip, or a filter shipped to a remote source in a federated query. Each step is a strict improvement in expected cost as long as the predicate itself is cheap to evaluate. Cost models do account for expensive predicates - a filter calling a costly function may be deliberately evaluated late, after cheaper filters have reduced cardinality. ## Projection pushdown / column pruning The rewriter computes, for each operator, the set of columns actually required by its parents, and prunes everything else. Effects: - Narrower tuples mean more rows per memory page, smaller hash tables and sort runs, and fewer spills to disk. - On column stores or Parquet-like formats, unread columns are simply never fetched. - Over a network boundary (federated query, remote table, storage-disaggregated engine) it directly reduces bytes on the wire. - It is why `SELECT *` costs more than naming columns even when the row count is identical: it defeats pruning, and it can also prevent a covering-index-only access path because a needed column is not in the index. ## Legality: when a predicate may not move Pushdown is a rewrite, so it must be semantics-preserving: 1. **Outer joins.** A filter on the null-supplying side cannot be pushed below the join. Applied before the join it removes inner rows and the outer row survives NULL-extended; applied after it removes the whole outer row. Those are different results. (Pushing a filter into the *preserved* side is fine.) 2. **Aggregation boundary.** A predicate over an aggregate result (`HAVING SUM(x) > 100`) cannot move below the aggregation; one over grouping columns can. 3. **Row-limiting and ordering boundaries.** Pushing a filter below a `LIMIT` changes which rows the limit picks. 4. **Set operations and DISTINCT.** Filters generally push through `UNION ALL` per branch; care is needed with `UNION` de-duplication and window functions, whose frames change when input rows disappear. 5. **Volatile or side-effecting predicates.** Their evaluation count is observable, so the engine restricts movement. These boundaries are often called *optimization fences*: operators past which the rewriter cannot freely move things. ## Reading it in a plan You want to see the predicate attached to the scan or index access, not sitting as a filter above a join. Tell-tale signs of failed pushdown: a join whose input row count is huge while the final result is tiny; a filter operator directly under the root; a remote scan returning far more rows than the query keeps. ## Practical levers - Write predicates in a form the rewriter can analyze: compare a bare column to a constant or parameter rather than wrapping the column in a function. - Prefer explicit column lists over `SELECT *` so pruning and index-only access stay available. - Understand that pushing a filter *through* a view or derived table depends on that view being mergeable - a view containing `DISTINCT`, a window function, or a `LIMIT` becomes a fence and the filter stays above it. - On partitioned tables, a pushed-down predicate on the partition key is what enables partition elimination, often the single largest win available.