Pushing down HashJoinExec build side dynamic filters makes tpch queries slower

[adriangb]

Describe the bug

Seen / discussed in:

#19639
#3463

We need to nail down what is slow:

1. Creating the expressions?
2. Evaluating them?

If (2), what's the solution?

• Is our approach to filter pushdown fundamentally flawed because it changes the parallelism or batch sizes at which the filter is applied? Could we do something to make the expression more efficient, like put the min/max evaluation outside of the hashes?
• Is it just because the hashes are being evaluated more times?
• Is it less of a problem on a larger dataset?

To Reproduce

No response

Expected behavior

No response

Additional context

No response

[aviralgarg05]

I would like to solve the issue, please assign me

[adriangb]

@aviralgarg05 this is a very complex issue and it doesn't seem that you've contributed to this codebase before. Do you really think this is something you can work on? Maybe you can propose an outline of how you plan to tackle this first?

[aviralgarg05]

@aviralgarg05 this is a very complex issue and it doesn't seem that you've contributed to this codebase before. Do you really think this is something you can work on? Maybe you can propose an outline of how you plan to tackle this first?

I will give a proposal, moreover just for info, I have contributed to apache projects before, so I think I can reproduce, and solve this issue

[aviralgarg05]

@adriangb

My bet is that we’re seeing the cost of evaluation on the probe side (2), specifically in cases where the dynamic filter has low selectivity. If the filter (like a min/max or Bloom filter) doesn’t strictly prune a significant amount of data, we end up paying a per-row evaluation “tax” without gaining any performance benefit from smaller join inputs.

To nail this down, I’d propose:

1. Isolate the Bottleneck: Can we run a test where we compute the dynamic filters on the build side but do not apply them on the probe side?

   • If it’s still slow, the issue is the creation overhead (or synchronization blocking the probe side).
   • If it speeds up, the issue is definitely the evaluation cost.

2. Verify Selectivity: I suspect for the slow TPC-H queries, the filter is pruning very few rows.

3. Potential Solution: If it is the evaluation cost, we might need an adaptive approach. We could track the filter’s hit rate at runtime; if it’s ineffective (e.g., pruning < 10% of rows) after the first few batches, we should dynamically disable it to stop the bleeding.

[adriangb]

My bet is that we’re seeing the cost of evaluation on the probe side (2), specifically in cases where the dynamic filter has low selectivity.

I think that's a reasonable guess! Since this is all on known open datasets (TPCH) maybe we could verify these sort of assumptions? E.g. measure the selectivity, creation and evaluation time of the filters, trying to tweak parallelism and any other possible knobs / causes and make a report of the impact each seems to have. I.e. let's be a bit scientific here: come up with multiple hypothesis, try to prove/disprove them with data, take the findings and design solution(s) and only then try to tackle them (or have an LLM tackle them).

[aviralgarg05]

I completely agree—let's keep the guesswork out of it and stick to the data. A scientific breakdown is exactly what's needed here.
To prove/disprove the "evaluation tax" hypothesis, we can collect metrics on the specific TPC-H queries that are regressing. I'd suggest we track:

1. Selectivity Impact: Add metrics to track rows_pruned versus total_rows_scanned by the dynamic filter. If we're seeing <1% pruning in the slow queries, we have our answer on why the overhead isn't worth it.
2. Timing Breakdown: Instrument the probe-side filter to measure eval_time. Comparing this against the total join_time will show if the expression engine is the primary bottleneck.
3. Threshold Testing: Run a matrix of SF1 vs SF10. My suspicion is that at SF1, the constant overhead of creating and evaluating the filter dominates, whereas at SF10+, the I/O savings finally make it "profitable."
   I can help put together a quick manual test plan or a PR to instrument these specific metrics if that helps move the needle.