Pushing Predicates Into Subqueries and CTEs in PostgreSQL #

When you filter the result of a subquery or common table expression, you usually expect the database to apply that filter as early as possible — at the underlying table scan, where an index can eliminate most rows before any work is done on them. That transformation is called predicate pushdown. PostgreSQL performs it aggressively for subqueries, but a single misplaced keyword or an aggregation boundary can turn a cheap Index Cond into an expensive Filter applied after the entire inner result has already been built.

This page shows exactly where pushdown happens in the plan, why it sometimes silently fails, and how to restore it. It sits under Filter Pushdown Mechanics; if you are still separating an Index Cond from a Filter line in general, start with understanding filter vs recheck conditions first.

What Predicate Pushdown Looks Like in a Plan #

Consider a reporting query that filters the output of a subquery aggregating order revenue by customer:

EXPLAIN
SELECT *
FROM (
  SELECT customer_id, SUM(total_amount) AS revenue
  FROM orders
  GROUP BY customer_id
) AS agg
WHERE agg.customer_id = 4210;

Here the outer predicate customer_id = 4210 is on a grouping column, so it is safe to apply before aggregation. The planner recognizes this and pushes it down:

GroupAggregate  (cost=0.43..8.47 rows=1 width=40)
  Group Key: orders.customer_id
  ->  Index Scan using idx_orders_customer on orders   -- ← predicate pushed here
        Index Cond: (customer_id = 4210)               -- ← reaches the scan as an Index Cond

The Index Cond: (customer_id = 4210) line is the whole story. The predicate travelled from the outer query, through the subquery boundary, down to the index scan, so PostgreSQL reads only the handful of rows for customer 4210 instead of scanning every order and aggregating the entire table.

Contrast that with a query where the predicate references an aggregated column instead of a grouping column:

EXPLAIN
SELECT *
FROM (
  SELECT customer_id, SUM(total_amount) AS revenue
  FROM orders
  GROUP BY customer_id
) AS agg
WHERE agg.revenue > 10000;
Subquery Scan on agg  (cost=18500.00..21000.00 rows=8333 width=40)
  Filter: (agg.revenue > 10000)          -- ← applied AFTER the full aggregation
  ->  HashAggregate  (cost=18500.00..19750.00 rows=25000 width=40)
        Group Key: orders.customer_id
        ->  Seq Scan on orders            -- ← whole table read; nothing pushed

The revenue column does not exist until every group has been aggregated, so the filter cannot move below the HashAggregate. This is not a planner failure — it is a correctness boundary. Filtering on an aggregate is what HAVING is for, and it inherently runs after grouping.

The CTE Inlining Boundary: PG11 vs PG12+ #

The most consequential pushdown change in PostgreSQL’s history is the CTE inlining rule introduced in version 12. Before PG12, every WITH clause was an unconditional optimization fence: the CTE was materialized into a temporary result, and no outer predicate could reach the inner scan.

CTE predicate pushdown decision flow A decision flow showing that on PostgreSQL 11 and earlier a WITH clause is always materialized so predicates cannot be pushed, while on PostgreSQL 12 and later a non-recursive single-reference CTE without MATERIALIZED is inlined, allowing the predicate to reach the inner scan as an Index Cond. Outer WHERE on a CTE column PG12+ and not MATERIALIZED? NO YES Fence: materialize CTE Scan + Filter full inner result read Inline the CTE predicate pushed to inner Index Cond Slow: whole CTE built then filtered Fast: index skips non-matching rows Blockers still apply: GROUP BY / LIMIT / window

Take a lookup that filters a CTE on an indexed column. On PostgreSQL 11 the plan fences it:

-- BEFORE (PG11 behaviour, or PG12+ with MATERIALIZED)
CTE Scan on recent  (cost=14200.00..16700.00 rows=500 width=44)
  Filter: (status = 'SHIPPED')        -- ← filter sits above a fully materialized CTE
  CTE recent
    ->  Seq Scan on orders            -- ← every order materialized first

On PostgreSQL 12+ the same non-recursive, single-reference CTE is inlined and the predicate reaches the scan:

-- AFTER (PG12+ inlining, no MATERIALIZED)
Index Scan using idx_orders_status on orders  (cost=0.43..320.15 rows=500 width=44)
  Index Cond: (status = 'SHIPPED')    -- ← predicate pushed into the inner scan

The CTE Scan node disappears entirely, and the Filter becomes an Index Cond. The full-table materialization is gone.

Step-by-Step: Restoring Pushdown #

Step 1 — Locate where the predicate is applied #

Run EXPLAIN and read from the bottom up. If the condition appears as Index Cond (or a Filter directly on the base table scan), it was pushed. If it appears as a Filter on a CTE Scan or Subquery Scan node sitting above a fully built inner result, it was not.

Step 2 — Check version and the MATERIALIZED keyword #

SHOW server_version;

On PG12 and later, inlining is automatic for a non-recursive CTE referenced exactly once and not marked MATERIALIZED. If the CTE says WITH recent AS MATERIALIZED (...), that keyword is an explicit fence and pushdown will not happen.

Step 3 — Identify structural blockers #

Even with inlining available, the planner will not push a predicate past an operation that would change the result. Inside the subquery or CTE, look for:

Step 4 — Remove the fence or rewrite #

Drop MATERIALIZED, or rewrite a fenced CTE as an inline subquery in the FROM clause. Both let the planner inline and push.

-- Rewrite: fenced CTE → inline subquery so the predicate can descend
SELECT o.order_id, o.status, o.total_amount
FROM (SELECT * FROM orders) AS o
WHERE o.status = 'SHIPPED';

Step 5 — If a fence is unavoidable, push the predicate by hand #

Sometimes you deliberately want materialization — for example, to compute an expensive CTE once and join it several times. In that case, add the predicate inside the CTE body so the inner scan filters early:

WITH recent AS MATERIALIZED (
  SELECT order_id, status, total_amount
  FROM orders
  WHERE status = 'SHIPPED'      -- ← predicate placed inside the fence manually
)
SELECT * FROM recent;

Step 6 — Validate with EXPLAIN ANALYZE #

Re-run the query with EXPLAIN (ANALYZE, BUFFERS) and confirm the predicate now shows as an Index Cond, that actual rows on the inner scan dropped from the full table to the matching subset, and that buffer reads fell accordingly. Understanding how the plan’s cost lines relate to real timing is covered in identifying plan bottlenecks.

Before/After Plan Comparison #

-- BEFORE: MATERIALIZED CTE, predicate stuck above the fence
CTE Scan on recent  (actual time=88.4..92.1 rows=500 loops=1)
  Filter: (status = 'SHIPPED')  Rows Removed by Filter: 499500
  CTE recent
    ->  Seq Scan on orders  (actual rows=500000 loops=1)

-- AFTER: CTE inlined, predicate pushed to the index
Index Scan using idx_orders_status on orders
  (actual time=0.03..0.9 rows=500 loops=1)  Index Cond: (status = 'SHIPPED')

The Rows Removed by Filter: 499500 line in the before-plan is the tell: half a million rows were built and then discarded. After pushdown, the index visits only the 500 matching rows.

Common Pitfalls #

Assuming MATERIALIZED is free. Developers add MATERIALIZED to “cache” a CTE without realizing it disables pushdown. If the CTE is referenced once, the keyword almost always costs more than it saves.

Filtering on an aggregate and expecting pushdown. A WHERE on SUM(...) or COUNT(...) output can never descend below the aggregation — that is a HAVING boundary, not a planner limitation. Move the filter to a grouping column if you want it pushed.

LEFT JOIN nullable-side filters. A predicate on the outer (nullable) side of a LEFT JOIN inside a subquery cannot be pushed without changing the join semantics into an inner join. If that is actually what you want, write an inner join explicitly.

A LIMIT inside the subquery. Pushing a filter below a LIMIT would change which rows the LIMIT returns, so the planner correctly refuses. Remove the inner LIMIT or accept the fence.

Frequently Asked Questions #

Does PostgreSQL push a WHERE clause into a CTE? Since PostgreSQL 12, a non-recursive CTE referenced once and not marked MATERIALIZED is inlined into the outer query, letting the planner push the outer WHERE predicate down to the underlying scan as an Index Cond. Before PG12, every WITH clause was an optimization fence that was always materialized, so no predicate could reach the inner scan.

What stops a predicate from being pushed into a subquery? GROUP BY, DISTINCT, and window functions block pushdown because a filter applied before aggregation would change the grouped result. A LIMIT inside the subquery blocks it because filtering before LIMIT changes which rows survive. Volatile functions, the explicit MATERIALIZED keyword, and predicates on the nullable side of a LEFT JOIN also prevent the planner from moving the condition down.

How do I force predicate pushdown into a materialized CTE? Drop the MATERIALIZED keyword so the planner may inline the CTE, or rewrite it as an inline subquery. If a fence is required for another reason, add the predicate explicitly inside the CTE body so the inner scan filters early. Confirm with EXPLAIN: the condition should appear as an Index Cond on the inner scan rather than a Filter above a full CTE Scan.

Related