Can presto pushdown filters when joining using >=

[bringgdror]

Hi,

I hope someone would be able to help me here.

I have several tables partitioned on date.
One table's rows (let's call it table A) are always created at the same time or before all other tables.
I join the other tables (let's call it table B) using table_B.date >= table_A.date.

When looking at the execution plan of a query like below I see that the date partition is not being used on table_B. It does not filter the partition only on dates after 20210301 despite the fact that the join limits only on dates on or after that date.

Is this the expected behaviour? Is there a way around it?

Thanks.
Dror

select 
col1,col2,col3 
from table_A 
LEFT JOIN table_B ON table_A.id = table_b.A_id 
                   and table_B.date >= table_A.date
WHERE
table_A.date >= 20210301

[javrod87]

Dynamic filtering is what you're looking for #15077. Presto supports local dynamic filtering which means only for broadcast joins.

They talk about this on the podcast of trino, how works. https://trino.io/episodes/11.html.

https://trino.io/docs/current/admin/dynamic-filtering.html
https://trino.io/blog/2020/06/14/dynamic-partition-pruning.html
https://trino.io/blog/2019/06/30/dynamic-filtering.html

I can only find docs for this in Trino but not on Presto so you need to look at the PR.

If you're stuck on presto you maybe can try setting experimental.enable-dynamic-filtering to true. PR contains few other configs to tune. Maybe otherwise try out trino if this is important to you.