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
