Join Filter vs Hash Cond Predicates #

A join has three conditions in its ON clause and the plan shows one of them under Hash Cond and two under Join Filter, with Rows Removed by Join Filter: 41208004. The join returns 90,000 rows. Forty-one million pairs were formed and discarded, because only one of the three conditions was able to limit which pairs were formed at all.

Predicate placement in general is covered in filter pushdown mechanics. This page is about reading the join’s own conditions.

The Planner Condition #

A join node can evaluate its conditions in three ways, and EXPLAIN labels each differently:

A Join Filter is not automatically bad. When a driving condition already limits the pairs, the filter is applied to a small number of them. The number to read is Rows Removed by Join Filter relative to the join’s output: a similar magnitude is fine; orders of magnitude larger means the join is forming pairs that never had a chance.

A fourth placement matters as much: a condition mentioning only one relation belongs in that relation’s scan, where it removes rows before the join. The planner does this automatically for inner joins, but conditions written in the ON clause of an outer join cannot be pushed to the preserved side, because they decide null-extension rather than row removal — the mechanics in outer join reordering rules.

Four placements for a join predicate A table. A hash or merge condition drives the join and forms only matching pairs. An inner index condition probes only matching rows. A join filter is evaluated on every pair the join produces. A single-relation condition is pushed into that relation's scan and removes rows before the join. evaluated cost Hash / Merge Cond while matching only matching pairs inner Index Cond per outer row only matching rows Join Filter per formed pair every pair scan-level Filter per scanned row before the join read Rows Removed by Join Filter against the join's output rows

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, s.carrier
FROM orders o
JOIN shipments s
  ON s.order_id = o.id
 AND s.shipped_at >= o.placed_at
 AND (s.carrier = o.preferred_carrier OR o.preferred_carrier IS NULL)
WHERE o.placed_at >= '2026-09-01';
Hash Join  (actual time=1204.6..8402.1 rows=90412 loops=1)
  Hash Cond: (s.order_id = o.id)
  Join Filter: ((s.shipped_at >= o.placed_at) AND ((s.carrier = o.preferred_carrier) OR (o.preferred_carrier IS NULL)))
  Rows Removed by Join Filter: 41208004
  ->  Seq Scan on shipments s  (actual rows=41298416 loops=1)
  ->  Hash  (actual rows=184204 loops=1)
        ->  Index Scan using orders_placed_at_idx on orders o  (actual rows=184204 loops=1)
Execution Time: 8412.4 ms
-- one driving condition; two filters applied to 41.3M formed pairs

The same query with the shipments side narrowed first:

-- shipments has an index on (order_id, shipped_at)
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, s.carrier
FROM orders o
JOIN shipments s ON s.order_id = o.id AND s.shipped_at >= o.placed_at
WHERE o.placed_at >= '2026-09-01'
  AND (s.carrier = o.preferred_carrier OR o.preferred_carrier IS NULL);
Nested Loop  (actual time=0.06..412.8 rows=90412 loops=1)
  Join Filter: ((s.carrier = o.preferred_carrier) OR (o.preferred_carrier IS NULL))
  Rows Removed by Join Filter: 12044
  ->  Index Scan using orders_placed_at_idx on orders o  (actual rows=184204 loops=1)
  ->  Index Scan using shipments_order_shipped_idx on shipments s  (actual rows=1 loops=184204)
        Index Cond: ((order_id = o.id) AND (shipped_at >= o.placed_at))
Execution Time: 418.2 ms
-- both equality and the inequality became index conditions; only the OR remains a filter
Reading a join's conditions Three items are annotated. The Hash Cond names the single driving condition. The Join Filter lists everything evaluated per pair. Rows Removed by Join Filter compared with the join's output rows shows whether the pairs were formed pointlessly. Hash Cond: (s.order_id = o.id) the only driving condition Join Filter: (shipped_at >= … AND (…OR…)) evaluated on every pair Rows Removed: 41,208,004 vs 90,412 output 456× wasted pairs a small removed count next to the output is healthy

Step-by-Step Resolution #

  1. Compute the ratio of Rows Removed by Join Filter to the join’s output rows. Below about ten is usually fine; hundreds means the join shape is wrong.

  2. Give the join a second driving condition where possible. A composite index on the inner relation covering the equality and the inequality lets a nested loop use both as index conditions.

  3. Move single-relation conditions out of the ON clause for inner joins — they belong in WHERE, where they read naturally and are pushed to the scan either way.

  4. Split OR conditions that mix relations into UNION branches when they dominate, as described in BitmapOr and BitmapAnd plans.

  5. Check the outer-join case separately. A condition in the ON clause of a LEFT JOIN belongs there if it decides matching; moving it to WHERE changes the result by removing null-extended rows.

  6. Re-read the plan: the goal is fewer pairs formed, not a different join algorithm for its own sake.

Pairs formed by the join With only the equality as a driving condition, the join forms 41.3 million pairs and discards 41.2 million. With a composite index on order_id and shipped_at, it forms 102 thousand pairs and discards 12 thousand. equality only 41.3M pairs formed + shipped_at in the index 102k pairs formed same rows returned; 400× fewer comparisons

Before and After #

-- BEFORE: one driving condition, two join filters
Hash Join  Rows Removed by Join Filter: 41208004                        Execution Time: 8412.4 ms

-- AFTER: composite index makes the inequality an index condition too
Nested Loop  Index Cond: (order_id = o.id AND shipped_at >= o.placed_at)  Execution Time: 418.2 ms

When a Join Filter is the right answer #

Some conditions cannot be anything else. An OR across two relations, a comparison of computed expressions, a similarity threshold — none of these can drive a hash or merge join, and none can be an index condition unless the inner relation has a matching expression index. For those, the goal is not to eliminate the filter but to ensure it runs on as few pairs as possible, which means making sure the other conditions are doing their job.

That is why the ratio matters more than the presence of a filter. A join that forms 100,000 pairs and discards 10,000 through a filter is doing reasonable work; the same filter over 40 million pairs is a different problem with the same plan text. Reading the two numbers together is the habit that distinguishes a healthy join from a quadratic one, and it applies equally to nested loops, hash joins and merge joins.

Common Pitfalls #

Reading a Join Filter as an error. It is normal when the driving conditions already narrow the pairs. Diagnostic signal: a small removed count. Fix: nothing.

Single-relation conditions inside ON. Harmless for inner joins, meaningful for outer ones. Diagnostic signal: outer-join results changing when a condition moves. Fix: decide the semantics explicitly.

Missing composite index for the inequality. The range condition stays a filter. Diagnostic signal: a large removed count with an equality-only index condition. Fix: index both columns in the right order.

Assuming a different join algorithm will help. The pairs are the problem, not the method. Diagnostic signal: forcing a merge join and seeing the same removed count. Fix: reduce the pairs formed.

Frequently Asked Questions #

What is the difference between Hash Cond and Join Filter? #

Hash Cond is the equality that drives the join, so only matching pairs are formed. Join Filter lists the conditions evaluated on each pair the join produces, after matching.

Is Rows Removed by Join Filter always a problem? #

No. Compare it with the join’s output row count. A similar magnitude is normal; orders of magnitude larger means the join is forming pairs that could have been excluded earlier.

How do I turn a join filter into an index condition? #

Give the inner relation an index whose columns match both the equality and the additional condition, in an order where the equality comes first. A nested loop can then apply both as index conditions per outer row.

Up: Filter Pushdown Mechanics