Presto: Is dynamic_filtering not available in the view?

[gaojun2048]

This is my test case:
table event and table test3 is hive table.
event_vd is a view with create sql : create view event_vd as select col1 ,col2 from tab1 union all select col1,col2 from tab2.

[raunaqmorarka]

Right now you would see dynamic filtering only if the join in your query is a broadcast join, is that the case here ?
You might also want to take a look at the explain plan for your query to verify that dynamic filters are added by optimizer.

[findepi]

You can force broadcast join with SET SESSION join_distribution_type = 'BROADCAST';

[gaojun2048]

Right now you would see dynamic filtering only if the join in your query is a broadcast join, is that the case here ?
You might also want to take a look at the explain plan for your query to verify that dynamic filters are added by optimizer.

Thank you.
I am sure the join type is broadcast.

the sql to create view event_hive_vd is like this:
CREATE VIEW hive.db_benchmarktest.event_hive_vd AS SELECT "distinct_id" , "xwho" , "ds" FROM hive.db_benchmarktest.event UNION ALL SELECT "distinct_id" , "xwho" , "ds" FROM hive.db_benchmarktest.event_empty

table hive.db_benchmarktest.event_empty is an empty table.

the explain plan for the table query is :
SQL: explain analyze select count(1) from event a inner join test_3 b on a.distinct_id=b.distinct_id where ds='20190501'

explain plan:

the explain plan for the view query is:
SQL:explain analyze select count(1) from event_hive_vd a inner join test_3 b on a.distinct_id=b.distinct_id where ds='20190501'

explain plan:

It seems that if you use view, dynamicFilterAssignments does not work in the source fragment.

[raunaqmorarka]

I believe the optimizer part of adding dynamic filters to the query plan has worked correctly. In the second case, dynamic filter function is present in the ScanFilterProject on table db_benchmarktest.event in Fragment 2.
My understanding is that in the current node local implementation, the table scan on the probe side needs to be part of the same fragment or stage as the inner join for dynamic filtering to kick in. That doesn't seem to be the case here.
@vromanv @sopel39 wdyt ?

[gaojun2048]

@raunaqmorarka

I guess the problem caused by union all. I create a view hive.db_benchmarktest.event_hive_vd_not_unionall , event_hive_vd_not_unionall is not use union all and the create sql is:
CREATE VIEW hive.db_benchmarktest.event_hive_vd_not_unionall AS SELECT "distinct_id" , "xwho" , "ds" FROM hive.db_benchmarktest.event

I run the query sql:
explain analyze select count(1) from event_hive_vd_not_unionall a inner join test_3 b on a.distinct_id=b.distinct_id where ds='20190501';

I found the explain plan is same as two table inner join.
the explain plan is:

[sopel39]

My understanding is that in the current node local implementation, the table scan on the probe side needs to be part of the same fragment or stage as the inner join for dynamic filtering to kick in. That doesn't seem to be the case here.

Yes, join must be in same stage as table scan. Also there are limits to how big the build side can be. Additionally, applying dynamic filter might not always help, because it does filter on stripes (in ORC case). If stripe contains at least on row that matches dynamic filter, then the whole stripe will be read.