How does a projection's ORDER BY clause impact performance?

rjs_docsrjs_docs Vertica Employee Employee
edited April 2020 in General Discussion

How does the number of columns in a projection's ORDER BY clause impact performance?
@bat @rbankula

From Optimizing Query Performance and Resource Pool Tuning.

Answers

  • ChuckBChuckB Vertica Employee Employee

    It helps query performance, and doesn't hurt load performance. Sorting the data is a way to keep all the data relevant to a query in the same place, like a clustered index, but even better. If the projection is sorted well, Vertica can quickly grab the data relevant to a query, without reading the rest. All data in Vertica is sorted by something; this is important to the efficiency of housekeeping, just like how you keep your personal possessions in places you can easily find. So, you might as well make your sort order one that is good for predicates first, joins and grouping second, and compression third. Or let the database designer do it for you!

Leave a Comment

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