goldmedal opened a new issue, #13156: URL: https://github.com/apache/datafusion/issues/13156
### Is your feature request related to a problem or challenge? To produce a better SQL for the performance, we can change the unparsing result for TableScan with pushdown. See the discussion https://github.com/apache/datafusion/pull/13132#discussion_r1819442675 ### Describe the solution you'd like Instead of putting the predicates in the join condition, we can wrap a subquery for the table scan and put the predicate in the WHERE clause. This way can trigger the filter pushdown in any join type. I did some tests for this pattern: ``` -----------------inner join------------------- ###### predicate in filter ###### SQL: select o_orderkey from orders inner join (select c_custkey from customer where c_name = 'Customer#000000001') on o_custkey = c_custkey Projection: orders.o_orderkey Inner Join: orders.o_custkey = customer.c_custkey TableScan: orders projection=[o_orderkey, o_custkey] Projection: customer.c_custkey Filter: customer.c_name = Utf8("Customer#000000001") TableScan: customer projection=[c_custkey, c_name], partial_filters=[customer.c_name = Utf8("Customer#000000001")] -----------------left join------------------- ###### predicate in filter ###### SQL: select o_orderkey from orders left join (select c_custkey from customer where c_name = 'Customer#000000001') on o_custkey = c_custkey Projection: orders.o_orderkey Left Join: orders.o_custkey = customer.c_custkey TableScan: orders projection=[o_orderkey, o_custkey] Projection: customer.c_custkey Filter: customer.c_name = Utf8("Customer#000000001") TableScan: customer projection=[c_custkey, c_name], partial_filters=[customer.c_name = Utf8("Customer#000000001")] -----------------right join------------------- ###### predicate in filter ###### SQL: select o_orderkey from orders right join (select c_custkey from customer where c_name = 'Customer#000000001') on o_custkey = c_custkey Projection: orders.o_orderkey Right Join: orders.o_custkey = customer.c_custkey TableScan: orders projection=[o_orderkey, o_custkey] Projection: customer.c_custkey Filter: customer.c_name = Utf8("Customer#000000001") TableScan: customer projection=[c_custkey, c_name], partial_filters=[customer.c_name = Utf8("Customer#000000001")] -----------------full join------------------- ###### predicate in filter ###### SQL: select o_orderkey from orders full join (select c_custkey from customer where c_name = 'Customer#000000001') on o_custkey = c_custkey Projection: orders.o_orderkey Full Join: orders.o_custkey = customer.c_custkey TableScan: orders projection=[o_orderkey, o_custkey] Projection: customer.c_custkey Filter: customer.c_name = Utf8("Customer#000000001") TableScan: customer projection=[c_custkey, c_name], partial_filters=[customer.c_name = Utf8("Customer#000000001")] ``` We can see the filter pushdown works well. ### Describe alternatives you've considered _No response_ ### Additional context _No response_ -- 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: github-unsubscr...@datafusion.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
