avoid local round robin
phil2
✭
Hello again!
I have a query with a hash join over round robin. Recently I noticed that it consumes 56gb ram to perform this join, whereas hash table takes just 2gb of ram. digraph from explain verbose shows that vertica run this join in parallel using 28 threads. After experimenting with that I believe it does not give any speed and just consumes a lot of memory. I managed to avoid this by using distrib hint and resegmenting fact table. Gladly I need this table to be resegmented on that column alter all. Is there any way to turn off local round robin? (mayby with some session-level parameter or select set_optimizer_directives('SMTH=false') )
0
Answers
EnableLocalJoinRoundRobin is possibly the optimizer directive you're looking for.
However, I think it also will not do this if you turn on early materialized joins / enable join spill globally. Those might be better defaults in your system.