Re: Add rows removed by hash join clause to instrumentation

Hi Andrei,

On 17.01.2026 02:51, Andrei Lepikhov wrote:
> Hi,
>
> Playing around [1] to understand how, in practice, an engineer should
> identify potential indexes, I found that nfiltered1 and nfiltered2 are
> sufficient enough to detect issues with leaf table scan operators. But
> the situation is worse when it comes to joins.
>
> The idea behind JOIN optimisation is that sometimes a highly
> selective, parameterised NestLoop is more performant than a HashJoin.
> What we need is to identify that only a tiny part of the hash table or
> a sorted MergeJoin input has been used to produce the JOIN result.
>
> Thanks to [2 - 5], we have metrics showing how many tuples are removed
> by joinqual and otherquals in a JOIN operator. That’s good for
> starters. But some cases aren’t covered yet: how many tuples filtered
> by hashclauses or mergeclauses.
>
> In the attached file, you can see that for the same query, NestLoop
> exposes 100k filtered tuples, but HashJoin shows nothing. Original
> threads argued that ‘Filtered out’ characteristics should expose extra
> work done by the operator. Hashing operation, especially on a set of
> variable-length columns sometimes quite expensive. Further filtering,
> involving hashclauses looks pretty similar to the joinqual filtering.
>
> For me, ‘filtered’ value represents a flag that some massive part of
> the input is not needed at all and using proper parameterisation and
> indexing, we could optimise such a JOIN with NestLoop or MergeJoin.
>
> From this point of view, it seems logical to add a nfiltered3
> instrumentation field and account rows, filtered out by a ‘special’
> join clause like hashclauses or mergeclauses.
Thanks for the detailed write-up and examples - I totally agree that the
proposed metric makes sense, especially for HashJoin where a large
amount of work is currently invisible in EXPLAIN ANALYZE and it can
create lied vision about effectiveness.
>
> In the attachment, I propose a sketch on how to calculate these
> metrics. MergeJoin looks more complicated and I don't propose it for
> now, but HashJoin is quite trivial.
>
> Known issues:
> - Hash clause failures are counted in nfiltered1, which is shared with
>   join filter removals. If both are present, counts are combined.
> - The metric only counts outer tuples with zero hash-value matches,
>   not hash collisions within buckets.
I’ve looked through the patch and didn’t spot any obvious issues.
Instrumentation of this kind is not something we usually test in
regression tests, but I’ve added a small patch with some test coverage
and attached it here. For now, I don't have any more suggestions.

Thanks for working on this.

Best regards,
Alena Rybakina