Best practices and tips for tuning analytical functions?

rjs_docsrjs_docs Vertica Employee Employee
edited April 2020 in General Discussion

Can you please provide any tips for tuning analytical functions? For example, how does ordering projections by the PARTITION BY or ORDER BY columns help?
@bat @rbankula

From Optimizing Query Performance and Resource Pool Tuning.

Answers

  • baron42bbabaron42bba Vertica Customer

    First starting point is always EXPLAIN query.
    If joins and nested queries are used CREATE LOCAL TEMPORARY TABLE can be a way out of the slow experience. Using those keep an eye on using the same segmentation for the temporary table as is used for the source table. If the output get's later inserted into another table compare the segmentation clause as well.

    If possible develop the query with a smaller dataset on a slower system.
    It might not work due to memory limits and gives ideas where to improve further.

Leave a Comment

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