I have a situation where my query plan is about 1000x slower because Vertica chooses a different plan with a subquery vs a constant.
E.g,
lastid AS
(
SELECT last_id as max(id) from processed_rows;
)
SELECT * From my_table m
inner join other_table x om m.a = x.a
where m.id > (select last_id from last_id )
That query does a bad plan and doesn't actually apply the filter until after the join (scanning a huge terabyte table)
lastid AS ( SELECT last_id as max(id) from processed_rows; )
SELECT * From my_table m
inner join other_table x om m.a = x.a
where m.id > 10000
This plan applies the id filter at join time (resulting in usually just a few rows to have to join)
I can't find anyway to trick the planner into applying the filter. I've tried lots of different hints and even using inner joins instead of WHERE clause filters.
If I can't find a solution I'll have to change a huge part of our ETL process to allow templating of these SQL scripts.
