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

avoid local round robin

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') )

Answers

  • ChuckBChuckB Employee

    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.

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.