Can I run an EXPLAIN query plan that rejects the query plan based on the returned Cost?

rjs_docsrjs_docs Vertica Employee Employee
edited April 2020 in General Discussion

Is there an option for Vertica to run an internal EXPLAIN plan that rejects the query plan if the cost is over a specified amount? At either a user or resource pool level?
@bat @rbankula

From Optimizing Query Performance and Resource Pool Tuning.

Answers

  • ChuckBChuckB Vertica Employee Employee

    The query optimizer uses "cost" to choose between query plans for the same query in the same context. Costs across queries and across contexts are not great indicators of run time, or other resources, even with state-of-the-art academic research. (See http://pages.cs.wisc.edu/~wentaowu/slides/ICDE-2013.pdf)

    Fortunately, Vertica has a solution that works much better in practice: run the query, and if it actually exceeds resource caps, kill it. That's at the resource pool level, or you can assign the pool to a user.

Leave a Comment

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