When a query selects from a view, how does the query optimizer handle the view's definition, and does wrapping a query in a view add runtime cost?

## Expansion, not execution When the parser meets a view name it substitutes the stored definition, producing a single query tree that mixes the caller's clauses with the view's. The optimizer then plans that combined tree. There is no separate step where the view is executed and its rows handed to the outer query — unless the optimizer decides materialisation is the best plan for reasons that would apply to an inline subquery too. The practical statement: `SELECT * FROM v WHERE x = 5` is normally planned exactly as if you had pasted the view body in as a subquery. Same access paths, same joins, same cost. That is why "views are slow" is wrong as a blanket claim. ## What the optimizer can do across the boundary **Predicate pushdown.** A filter written outside the view can be applied inside it, at the base table, so an index can serve it. This is the single most important optimization and the reason a view over a huge table can still answer a point query in microseconds. **Join reordering.** Joins from the view body and from the outer query end up in one join set the optimizer can order freely by cost. **Projection pruning.** Columns the caller never selects need not be computed or fetched, which can save expression evaluation and wide-row I/O. **Join elimination.** Some engines can drop a join entirely when the caller selects no column from that table and a foreign key with a unique target guarantees the join neither adds nor removes rows. This is what keeps wide "everything" views tolerable for narrow consumers — but it depends on declared constraints being present, so it silently stops working if they are not. ## Optimization fences A fence is any construct where moving a predicate across it would change the result. The optimizer must respect them, and each one costs you pushdown: - **Aggregation / GROUP BY.** Filtering before grouping changes which rows are aggregated. Some engines can push a predicate on a grouping *key* through, since that only removes whole groups, but a predicate on an aggregate result cannot move. - **DISTINCT.** Similar reasoning — sometimes pushable on projected columns, often not. - **Window functions.** A window value depends on the whole partition, so any filter that would remove rows from the partition alters it. Predicates essentially cannot pass. - **LIMIT / OFFSET.** Which rows survive is positional; filtering earlier changes the set entirely. - **Set operations and recursive CTEs.** Pushdown is limited and engine-dependent. - **Volatile or side-effecting functions.** The engine must not move or re-evaluate them freely. When a fence exists, the inner block is evaluated on its own and the outer predicate applies to its output. Concretely: a view aggregating daily totals across all customers, queried with `WHERE customer_id = 42`, may aggregate every customer before discarding all but one — an enormous amount of work for one row. The fix is usually to parameterise differently: filter inside the view's own logic, use an inline function or a query that groups only what is needed, or accept a materialized result for the aggregate. ## Nested views and plan size Expansion is recursive: a view on a view on a view produces one large tree. Two consequences. First, join count grows, and optimizer search space grows roughly super-linearly with it, so planning time itself becomes measurable and some engines stop exhaustive search past a threshold and fall back to heuristics — meaning a worse plan, not just slower planning. Second, each layer may add joins and columns that only exist to serve some other consumer, and unless projection pruning and join elimination fire, that work is really executed. ## How to verify Read the execution plan for the query as issued against the view. Check that the predicate you expect to be pushed appears as an index condition on the base table rather than as a filter applied late above an aggregate. Compare against the hand-written equivalent when in doubt: if plans differ, a fence or a missing constraint is the reason. Row-count estimates are also worth checking — estimation through an aggregation or a DISTINCT is much weaker, and a bad estimate above a fence propagates into bad join choices higher up. ## The judgement Views are free when they are simple projections, filters and joins. They stop being free the moment the definition contains a fence, and the failure mode is invisible in the calling SQL. That asymmetry is the actual answer to "do views cost anything": not a fixed overhead, but a loss of the optimizer's ability to specialise the query for your predicate, in exactly the cases where the query is expensive enough to matter.