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

rikusg

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?

pnthao

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?