When to resegment/broadcast?

Do we need to correct Resegment/Broadcast even if the plan says to Merge join/Group by pipeline?

Optimizing Query Performance and Resource Pool Tuning
@rbankula @bat

Answers

  • JoeGonzalezVertDBAJoeGonzalezVertDBA Vertica Customer

    If at all possible, I highly recommend it. Keep in mind if you are joining multiple tables in a query, you might not be able to eliminate every single resegment/broadcast. Do your best to do so, but at the very least get rid of the ones with the highest Cost in the explain plan, and unsegment all smaller tables (rule of thumb i follow is under 1 million rows)

  • Joe's recommendation is spot-on. Replicating (unsegmenting) smaller tables (< 1M rows is a good rule of thumb) can have great benefits in eliminating things like resegment and broadcast. If you find that you have hundreds, or even thousands of very small tables that are segmented, I actually have a script that I wrote that will automate that process - it will replicate all small, segmented projections. Keep in mind, that it's irreversible. PM me (is that a thing here?) or just post that you want the script, and I can reach out with the details.

  • cp00634326cp00634326 Vertica Customer

    Thanks Joe and Curtis..i was reading one of the old post and response was to ignore if anyway plan says Merge Join...But i agree we should as long as we can. Also it is difficult to get always Merge join based on user input and the volume of the projection. Please share your automate script if possible.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file