Re: Redesign the EXPLAIN metric ‘Removed by Join Filter’?

On 25/09/2026 16:53, Andrei Lepikhov wrote:
> Having this metric, Postgres would enable DBAs and extensions to search for
> beneficial indexes - not only for scan purposes as now, but for the JOIN too.

Hi,

After some analysis, I found that counting unmatched outer rows is almost free:
join nodes already track whether the current outer tuple has found a partner, so
it is enough to count matched outer tuples and subtract that from the outer
child's row count. See 0001 in the attachment: the executor changes are a few
lines per join node. The 0002 (mostly auto-generated) contains docs and tests.

This new number does not depend on the join method. With the same outer side,
NestLoop, HashJoin and MergeJoin count the same matched rows.

The idea is close to 'Rows Removed by Filter' on a scan. I see several uses:
1. Find cases where a parameterised NestLoop could beat other join types - in
other words, a missing index on the join key.
2. Spot an inefficient join order that carries many tuples further up the plan
than necessary.
3. Expose the hidden work of hash and merge clauses, which may go through tons
of tuples to find a few matching ones.
4. Possibly, find cases where a Bloom filter could speed up the query.

One corner case:
Hash Right Semi Join reports nothing: it skips already matched inner tuples
before checking the join qual, so it cannot tell which outer rows have a
partner. The inner side is not covered yet.

Thoughts?

--
regards, Andrei Lepikhov,
pgEdge