The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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.