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

Whats causes an "Outer (RESEGMENT)(LOCAL ROUND ROBIN)" during a JOIN?

I have a query where the plan flip/flops between "Inner (RESEGMENT)" and "Outer (RESEGMENT)(LOCAL ROUND ROBIN)" by swapping the INNER / OUTER parts of the JOIN.
The one part has a fixed row count of 70k while the other part varies. The tipping point is not based on row count (alone) as it is somewhere between 5k and 6k rows on the variable part. (Below this point the variable part is the INNER.)
What would be the benefit of the OUTER (RESEGMENT) plan? Is there a way to turn it off?

Answers

  • pnthaopnthao Employee

    Hi rikusg,
    The query optimizer uses a cost-based model to pick what it thinks the best plan. Regarding inner/outer and resegment choices, it basically leans toward using the input with smaller size as the inner (so we can fit the hash table built on the inner in memory), and do network operations (resegment, broadcast) on the smaller-size input too. The decision is not based solely on row count, though in this case we might expect that the 70k part stays on the outer while the other is around 5-6k. But without looking at all the details of the query, schema and data I cannot have further insight.
    If you are comfortable with adding hints to your query, you can use them to force the optimizer to pick the plan you want. Please find more information on that here https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/DirectedQueries/DirectedQueries.htm?

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.