Broadcast the larger side during join if colA + colB >5 condition is specified

[ubyyj]

we are suing trino version 377, and found that it generates very inefficient plan, broadcasting the much larger table, if a condition like colA + colB >5 is given.

Below is the query:

select account_id,
sum(read_time_web + read_time_smart) as score
from
hive_ad.ml.sn_result_hourly b
join
hive_ad.ml.sn_articles_export_hourly a
using (dt, edition, link_id)
where dt between
format_datetime(date_add('day', -3, now()), 'YYYY-MM-dd')
and
format_datetime(date_add('day', -1, now()), 'YYYY-MM-dd')
and a.hh = '23' and edition = 'en_US'
and b.hh='23'
and (read_time_web + read_time_smart) >= 5 -- this line matters
group by 1

Table sn_result_hourly is much larger, but the query plan shows it broadcast this larger table during join. But if remove the condition and (read_time_web + read_time_smart) >= 5, then the plan looks good.
Please find the incorrect query plan in the attached file.

broadcast_larger_side_plan.txt

for your convenience, here is the key part of the plan:

[findepi]

cc @sopel39 @raunaqmorarka

[sopel39]

What is correct estimate?

[ubyyj]

the desired plan should be table scan of sn_result_hourly on the left of the join, since it has much more rows than table sn_articles_export_hourly.
@sopel39 does this answer your question?

[sopel39]

@ubyyj could you run explain analyze also?

[ubyyj]

run_analyze_bad_query_plan.txt

attached is the result of explain analyze

[raunaqmorarka]

Could you also provide the output of SHOW STATS FOR sn_result_hourly

[ubyyj]

show stats for hive_ad.ml.sn_result_hourly;
column_name | data_size | distinct_values_count | nulls_fraction | row_count | low_value | high_value
-------------------------+-----------------------+-----------------------+----------------+----------------------+-----------+------------
read_time_web | NULL | NULL | NULL | NULL | NULL | NULL
... (omit rows that only has only NULL values)
dt | 1.9505585659707977E13 | 1329.0 | 0.0 | NULL | NULL | NULL
hh | 3.9011171319429473E12 | 24.0 | 0.0 | NULL | NULL | NULL
NULL | NULL | NULL | NULL | 1.950558565970551E12 | NULL | NULL

[raunaqmorarka]

I think this is due to the optimizer not handling unknown stats correctly in the relevant part of the code.
Could you try running ANALYZE hive_ad.ml.sn_result_hourly WITH (columns = ARRAY['read_time_web', 'read_time_smart']) and see if that solves it ?

[ubyyj]

@raunaqmorarka ANALYZE can fix the issue.
Since the optimizer knows the total rows of each side, it should optimize based on the row numbers, still sounds like a bug to me.

[raunaqmorarka]

It is a bug, just wanted to confirm that explicitly generating column stats solves the problem.