Predicate Pushdown Through UNION ALL Views #

A reporting view unions three archive tables. Queries against it filter by customer, and each table has an index on customer_id. The plan reads all three tables in full, appends 900 million rows, and filters afterwards. Removing one DISTINCT from the view definition changes the plan to three index scans returning 4,000 rows.

Where predicates are evaluated is covered in filter pushdown mechanics. This page covers the subquery and set-operation boundary, which is where pushdown most often stops.

The Planner Condition #

A filter in an outer query can be pushed into a subquery — including each branch of a UNION ALL — only when doing so cannot change the result. The planner refuses when the subquery contains:

UNION ALL itself is transparent: the planner flattens it into an Append and pushes qualifying filters into each branch, exactly as it does for partitions. That is why a union-all view over archive tables behaves like a partitioned table for pruning purposes — and why one blocking construct anywhere in the view removes the benefit for every branch.

The same rules govern ordinary subqueries in FROM and inlined CTEs; a MATERIALIZED CTE is an explicit fence, as covered in materialized vs not materialized CTEs.

Can this filter move down? Three cases. With a plain UNION ALL of simple selects, the filter is pushed into every branch and each can use its index. With DISTINCT, a window function or a LIMIT inside, the filter stays above and every branch is read in full. With a filter on a grouping or partition column, pushdown is still possible. filter above a UNION ALL or subquery plain UNION ALL branches pushed into each branch indexes usable DISTINCT, window, LIMIT inside filter stays above full reads filter on grouping key still pushed safe by definition one blocking construct affects every branch of the union

Annotated EXPLAIN Evidence #

The view with a blocking construct:

CREATE VIEW all_orders AS
SELECT DISTINCT id, customer_id, placed_at, total FROM orders_2024
UNION ALL
SELECT DISTINCT id, customer_id, placed_at, total FROM orders_2025
UNION ALL
SELECT DISTINCT id, customer_id, placed_at, total FROM orders_2026;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM all_orders WHERE customer_id = 8812;
Append  (actual time=18402.1..92104.6 rows=4021 loops=1)
  ->  Subquery Scan on "*SELECT* 1"  (actual rows=1204 loops=1)
        Filter: ("*SELECT* 1".customer_id = 8812)
        Rows Removed by Filter: 298412004
        ->  HashAggregate  (actual rows=298413208 loops=1)
              Group Key: orders_2024.id, orders_2024.customer_id, …
              ->  Seq Scan on orders_2024  (actual rows=298413208 loops=1)
  ->  … two more branches, each identical …
Execution Time: 92410.2 ms
-- DISTINCT blocks pushdown: each branch is deduplicated in full, then filtered

Without DISTINCT (the ids are already unique):

Append  (actual time=0.05..1.2 rows=4021 loops=1)
  ->  Index Scan using orders_2024_customer_idx on orders_2024  (actual rows=1204 loops=1)
        Index Cond: (customer_id = 8812)
  ->  Index Scan using orders_2025_customer_idx on orders_2025  (actual rows=1402 loops=1)
        Index Cond: (customer_id = 8812)
  ->  Index Scan using orders_2026_customer_idx on orders_2026  (actual rows=1415 loops=1)
        Index Cond: (customer_id = 8812)
Execution Time: 1.4 ms
-- the filter was pushed into every branch
Where the filter ended up Three items are annotated. A Subquery Scan with a Filter above the branch shows the predicate did not move down. A HashAggregate reading the whole table shows the blocking construct being evaluated over everything. Index Cond lines inside each branch show successful pushdown. Subquery Scan … Filter: (customer_id = 8812) filter stayed above HashAggregate (rows=298413208) DISTINCT over the whole table Index Cond: (customer_id = 8812) per branch pushdown succeeded look for the filter inside each branch, not above the Append

Step-by-Step Resolution #

  1. Locate the filter in the plan. Inside each branch means pushdown worked; above the Append or on a Subquery Scan means it did not.

  2. Find the blocking construct in the view or subquery: DISTINCT, GROUP BY, a window function, LIMIT, UNION without ALL, or a volatile function.

  3. Remove it if it is unnecessary. DISTINCT added defensively over already-unique rows is the most common case; verify uniqueness before removing it.

  4. If deduplication is genuinely needed, move it up: union the branches with UNION ALL, filter, and deduplicate once at the top. The planner then pushes the filter into the branches and deduplicates a much smaller set.

    CREATE VIEW all_orders AS
SELECT id, customer_id, placed_at, total FROM orders_2024
UNION ALL SELECT … FROM orders_2025
UNION ALL SELECT … FROM orders_2026;
-- consumer: SELECT DISTINCT * FROM all_orders WHERE customer_id = 8812;

  5. For window functions, check the partition key. If the filter column is in every PARTITION BY, the planner can push it; otherwise compute the window above the filter.

  6. Replace volatile functions in the view’s target list with values computed in the outer query, or mark genuinely stable functions STABLE.

  7. Consider real partitioning if the union view exists to simulate it; pruning is more capable and better supported, as compared in partial indexes vs partitioning.

Rows read for one customer With DISTINCT inside each branch, the query reads 900 million rows. With deduplication moved above the union, it reads 4,021 rows and deduplicates those. With no deduplication at all, it reads 4,021 rows. DISTINCT per branch 900M rows read DISTINCT above the union 4,021 rows read no DISTINCT 4,021 rows read the same deduplication, applied at a different level

Before and After #

-- BEFORE: DISTINCT inside each UNION ALL branch
Append → Subquery Scan (Filter) → HashAggregate → Seq Scan  ×3        Execution Time: 92410.2 ms

-- AFTER: plain branches, DISTINCT applied by the consumer
Append → Index Scan per branch  Index Cond: (customer_id = 8812)       Execution Time: 1.4 ms

Views that hide the problem #

The trouble with view-level blockers is distance: the person writing SELECT * FROM all_orders WHERE customer_id = $1 sees a simple query, and the DISTINCT that makes it read 900 million rows lives in a definition written years earlier. That is an argument for keeping views thin — projections and unions, no deduplication, no ordering, no limits — and for putting anything expensive in the consuming query where it is visible.

It is also an argument for reviewing view definitions when a query against them is slow, before looking at indexes. Every index in the example already existed and was perfectly usable; the plan simply never got the chance to use them.

Ordering is a related trap. ORDER BY inside a view does not block pushdown, but it does force a sort of the full branch output on every query, including ones that then apply their own ordering or a LIMIT. Views rarely need to order; consumers almost always do.

Common Pitfalls #

Defensive DISTINCT in views. Blocks pushdown for every consumer. Diagnostic signal: HashAggregate over a whole table under a filtered query. Fix: verify uniqueness and remove it, or move it up.

UNION instead of UNION ALL. Deduplicates across branches and blocks pushdown. Diagnostic signal: HashAggregate above the Append. Fix: UNION ALL when branches are disjoint.

Window functions in a reporting view. The filter stays above. Diagnostic signal: WindowAgg over full branch output. Fix: compute windows in the consuming query.

LIMIT inside a view. Semantically meaningful and blocks pushdown. Diagnostic signal: a Limit node inside a subquery scan. Fix: remove it, or accept the cost deliberately.

Frequently Asked Questions #

Why is my filter not pushed into a UNION ALL view? #

Some construct in a branch prevents it: DISTINCT, GROUP BY on other columns, a window function, LIMIT, or a deduplicating set operation. The planner then evaluates the branch fully and filters the result.

Does UNION ALL itself block predicate pushdown? #

No. UNION ALL is flattened into an Append and filters are pushed into each branch, much like partition pruning. UNION without ALL deduplicates and does block pushdown.

Can filters be pushed into a subquery with GROUP BY? #

Only when the filter is on a grouping column, where filtering before grouping cannot change the groups. Filters on aggregated values must be applied afterwards.

Up: Filter Pushdown Mechanics