Join Filter in Nested Loops Without an Index #

A query matches events to the pricing window they fall in: JOIN price_windows w ON e.occurred_at >= w.starts_at AND e.occurred_at < w.ends_at. The plan is a Nested Loop with Join Filter and Rows Removed by Join Filter: 4180200000. Four billion comparisons, for a result of two million rows. There is no index condition on either side, so every event is compared with every window.

A nested loop is only as good as its inner side, as described in merge join vs nested loop. This page covers the worst version of it: a loop that cannot probe, only compare.

The Planner Condition #

The planner has three ways to evaluate a join condition, and it reports which one it used:

A condition that fits none of these — or a part of the condition left over after the others are used — becomes a Join Filter, evaluated for every pair the join produces. When a join has no equality condition at all, hash and merge joins are impossible, and if no index on the inner relation matches the inequality either, the only remaining algorithm is a nested loop that pairs every outer row with every inner row and then filters: O(outer × inner).

The usual causes are:

A Materialize over the inner side often accompanies it, so the inner input is read once and replayed for every outer row rather than re-scanned.

Hash, merge, index — or pairwise filter A join condition is classified. A hashable equality becomes a Hash Cond. A condition matching an index on the inner relation becomes an inner Index Cond evaluated per outer row. Anything else becomes a Join Filter evaluated for every pair of rows, which is quadratic when no other condition narrows the pairs. how can this join condition be evaluated? equality on hashable types Hash Cond or Merge Cond one pass per side inner column vs outer value inner Index Cond one probe per outer row neither Join Filter every pair compared a Join Filter with no other join condition means outer × inner comparisons

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT e.id, w.price
FROM events e
JOIN price_windows w
  ON e.occurred_at >= w.starts_at AND e.occurred_at < w.ends_at
WHERE e.occurred_at >= '2026-09-01';
Nested Loop  (actual time=0.9..612804.2 rows=2090100 loops=1)
  Join Filter: ((e.occurred_at >= w.starts_at) AND (e.occurred_at < w.ends_at))
  Rows Removed by Join Filter: 4180200000                  -- 4.18 billion pairs rejected
  ->  Seq Scan on events e  (actual rows=2090100 loops=1)
        Filter: (occurred_at >= '2026-09-01'::timestamptz)
  ->  Materialize  (actual time=0.00..0.11 rows=2001 loops=2090100)
        ->  Seq Scan on price_windows w  (actual rows=2001 loops=1)
Execution Time: 613102.8 ms
-- no Hash Cond, no Merge Cond, no inner Index Cond: pure pairwise comparison
-- Materialize replays 2001 windows for each of 2.09M events

After adding a range-capable index on the inner relation:

CREATE INDEX CONCURRENTLY price_windows_starts_at_idx ON price_windows (starts_at);
Nested Loop  (actual time=0.05..9120.4 rows=2090100 loops=1)
  ->  Seq Scan on events e  (actual rows=2090100 loops=1)
  ->  Index Scan Backward using price_windows_starts_at_idx on price_windows w
        (actual time=0.003..0.004 rows=1 loops=2090100)
        Index Cond: (starts_at <= e.occurred_at)
        Filter: (e.occurred_at < ends_at)
Execution Time: 9208.1 ms
-- the lower bound is now a probe; only the upper bound is filtered per window visited
The signature of a quadratic join Three plan lines are annotated. A Join Filter with no Hash Cond, Merge Cond or inner Index Cond. Rows Removed by Join Filter in the billions, far above the result. A Materialize node with loops equal to the outer row count, replaying the inner side. Join Filter: ((e.occurred_at >= w.starts_at) …) no probe-able condition Rows Removed by Join Filter: 4180200000 outer × inner pairs compared Materialize (rows=2001 loops=2090100) inner side replayed per outer row removed-by-join-filter far above output rows means pairwise work

Step-by-Step Resolution #

  1. Confirm the pattern. A Nested Loop with a Join Filter, no inner Index Cond, and Rows Removed by Join Filter near outer rows × inner rows.

  2. Give the planner an inner index condition. For range joins, index one bound on the inner relation so each outer row probes rather than compares:

    CREATE INDEX CONCURRENTLY price_windows_starts_at_idx ON price_windows (starts_at);

  3. Use a range type with a GiST index for overlap and containment joins; the @> operator is index-supported and expresses the whole condition at once:

    ALTER TABLE price_windows ADD COLUMN during tstzrange
  GENERATED ALWAYS AS (tstzrange(starts_at, ends_at, '[)')) STORED;
CREATE INDEX CONCURRENTLY price_windows_during_gist ON price_windows USING gist (during);
-- join: ON w.during @> e.occurred_at

  4. Rewrite OR joins as UNION of two equality joins, each of which can hash or probe:

    SELECT … FROM a JOIN b ON a.x = b.x
UNION
SELECT … FROM a JOIN b ON a.y = b.y;

  5. Rewrite a “latest window” lookup as LATERAL … LIMIT 1 when windows do not overlap, turning the range join into one ordered index probe per outer row — the pattern in LATERAL join plan shapes.

  6. Re-check Rows Removed by Join Filter. It should now be small relative to output rows.

Pairs compared per plan The pairwise nested loop compares 4.18 billion pairs. The nested loop with a B-tree index on starts_at examines about 4 million index entries. The GiST range join examines about 2.1 million entries, roughly one per event. Join Filter, no index 4.18 billion pairs B-tree on starts_at ≈ 4.2 million entries GiST on tstzrange ≈ 2.1 million entries values in millions; the pairwise plan does not fit on a sensible scale

Before and After #

-- BEFORE: pairwise Join Filter
Nested Loop  Join Filter: (…)  Rows Removed by Join Filter: 4180200000        Execution Time: 613102.8 ms

-- AFTER: GiST index on tstzrange(starts_at, ends_at)
Nested Loop → Index Scan using price_windows_during_gist  Index Cond: (during @> e.occurred_at)   Execution Time: 6104.2 ms

Join filters that are harmless #

Not every Join Filter is a problem. When a join has an equality condition used as Hash Cond or inner Index Cond, a leftover inequality may still appear as Join Filter, but it is evaluated only for the pairs the equality already matched. Rows Removed by Join Filter will then be of the same order as the output, or smaller. The quadratic case is specifically a join filter doing all the work. The quick test is the ratio: if rows removed by the join filter exceed output rows by orders of magnitude, the join is comparing pairs that never had a chance of matching.

The same ratio test applies to predicates on outer joins, which must be evaluated as join filters rather than pushed below the join because they decide whether a row is null-extended rather than removed; their placement rules are covered in filter pushdown mechanics.

Common Pitfalls #

Indexing the outer relation. The probe needs an index on the inner side of the loop. Diagnostic signal: a new index that the plan ignores. Fix: index the relation whose columns appear with the outer value in the condition, or let the planner swap sides by indexing both.

Using functions on both sides. lower(a.email) = lower(b.email) is hashable but not indexable without an expression index. Diagnostic signal: hash join with a large build over all rows. Fix: an expression index or a stored normalized column.

Overlapping windows with LIMIT 1. The LATERAL … LIMIT 1 rewrite returns one window per event and silently drops overlaps. Diagnostic signal: fewer rows after the rewrite. Fix: use it only when windows are guaranteed disjoint, ideally by an exclusion constraint.

Cross joins in ORM-generated SQL. A missing join condition produces a Cartesian product filtered later. Diagnostic signal: Nested Loop with Join Filter built from WHERE terms. Fix: express the relationship as an explicit JOIN … ON.

Frequently Asked Questions #

What is a Join Filter in EXPLAIN? #

It is a join condition that the planner could not use as a hash, merge or index condition, so it is evaluated on each pair of rows the join produces. On its own it means every outer row is compared with every inner row.

How do I speed up a range join in PostgreSQL? #

Give the inner relation an index that matches part of the range condition, so each outer row probes instead of comparing against every inner row. For overlap and containment, a range column with a GiST index expresses the whole condition as one indexable operator.

Is Rows Removed by Join Filter always bad? #

No. It is harmless when an equality condition already limited the pairs and the removed count is similar to the output. It signals a problem when it is orders of magnitude larger than the rows returned.

Up: Merge Join vs Nested Loop