Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Filters with subqueries

mritsemamritsema
edited June 2017 in General Discussion

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.

Comments

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.