starocean999 opened a new issue #8744: URL: https://github.com/apache/incubator-doris/issues/8744
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues. ### Description Currently, in hash join node, the doris sql planner always create a runtime filter from build side table and push it to probe-side. However, in some primay-foregin key join scenario, if the build side table has no predicate to filter data, the runtime filter would have no effect on probe side and the creation and using of runtime filter is a waste. take TPCH q18 as an example: `select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 300 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate limit 100;` there will be 3 runtime filters RF000, RF001, RF002 as follow, but only RF000 is useful, **RF001** and **RF002** should be removed: 7:VHASH JOIN | | | join op: LEFT SEMI JOIN (BUCKET_SHUFFLE) | | | hash predicates: | | | colocate: false, reason: Tables are not in the same group | | | equal join conjunct: `o_orderkey` = <slot 2> `l_orderkey` | | | runtime filters: RF000[in_or_bloom] <- <slot 2> `l_orderkey` | | | cardinality=0 | 4:VHASH JOIN | | | join op: INNER JOIN (BUCKET_SHUFFLE) | | | hash predicates: | | | colocate: false, reason: Tables are not in the same group | | | equal join conjunct: `o_orderkey` = `l_orderkey` | | | **runtime filters: RF001[in_or_bloom] <- `l_orderkey`** | | | cardinality=0 | 2:VHASH JOIN | | | join op: INNER JOIN (BROADCAST) | | | hash predicates: | | | colocate: false, reason: Tables are not in the same group | | | equal join conjunct: `o_custkey` = `c_custkey` | | | **runtime filters: RF002[in_or_bloom] <- `c_custkey`** | | | cardinality=0 ### Solution 1. Remove useless runtime filters if the build side table has no predicate. 2. Add a session variable to control if enable this runtime filter removement behavior. ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
