Does ordering by lots of columns hurt performance?

Does the number of columns in Order By clause cause any performance issues?
Like if we have high number of columns in order by of segmentation.

Question from Optimizing Query Performance and Resource Pool Tuning @rbankula @bat

Answers

  • ChuckBChuckB Employee

    No, it doesn't.

    At some point, when we have to compare records for the sort order, if there's a difference in one field, we stop processing the comparison, so the following fields aren't checked. But if there are differences in the later fields, we do want a strong sort order, so it actually helps performance of other system components, like record lookup.

  • Chuck,
    I expect the loading is slower, if the underlying projection is sorted by too many columns.
    Also we were facing to very poor performance of export to vertica, if there were too many columns in order by.
    Things are getting even worse when columns are large VARCHARs...

  • ChuckBChuckB Employee

    Jan,
    Sounds like there could be some other things going on in your scenario, rather than just something happening with the sort itself. We could look into the profiles.
    ~CB

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.