Redesign the EXPLAIN metric ‘Removed by Join Filter’?

Hi,

We've had this EXPLAIN parameter for some time. As I see in the threads where
its design was discussed, the purpose was a little vague, as were the semantics.

It is reported for all three join node types, but joinqual holds only the
residual condition: the hash clauses are subtracted from it in a HashJoin, the
merge clauses in a MergeJoin. Take a join of 100 rows against 10, where only 10
outer rows have a partner:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF)
SELECT * FROM
(SELECT x FROM generate_series(1,100) AS x) AS f(x),
(SELECT y FROM generate_series(1,10) AS y) AS g(y)
WHERE f.x = g.y;

And you will see different 'join filter' numbers for hash and nestloop variants
of this query. Two plans of the same query are not comparable. The info on the
join filter itself is quite interesting, but I believe we can do better.

There is a key metric Postgres can't calculate from statistics during planning:
the number of 'unmatched' rows. I mean, how many outer rows haven't found a
match on the inner side, and vice versa. A high value of this metric is an
important signal that we might be missing an index, and with it the opportunity
to use a targeted, parameterised NestLoop instead of a massive HashJoin. The
bloom filter thread could use such a metric too.

For example, imagine JOIN(A,B) where A and B both have 1E6 rows. Actual output
of this JOIN is 1E6 rows. Does it mean we need to touch 1E6 tuples on each side,
or does a single tuple on the outer side match all the tuples on the inner side?
If our query ran with a hash or merge join, we don't know the answer. But if the
second statement is true, an index might speed up the join drastically. Right
now it is a blind spot for us.

So, my proposal is to add a metric like 'Unmatched Rows' for the outer side, and
possible for the inner side too.

In addition, with the concept of showing 'unmatched' rows we have a clean and
platform-independent (nice for testing purposes) metric for each JOIN type. Such
a metric would also be nicely coupled with another existing EXPLAIN parameter,
'Rows Removed by Filter', that allows us to detect the case for a potential index.

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.

What do you think?

P.S. This is a continuation of the topic [1].

[1]
https://www.postgresql.org/message-id/flat/9f6c6846-036f-4298-a315-5ef674d25eb7%40gmail.com

--
regards, Andrei Lepikhov,
pgEdge