Understanding Vertica Query Budgets

This blog post was authored by Shrirang Kamat.

The purpose of this document is to explain how the query budget of a resource pool used by the query can influence the initial memory acquisition for a query and how it impacts query performance. For more details about how we compute the query budget, see the Vertica documentation.

The Vertica cost-based optimizer generates an optimal query plan for every query that is submitted to the database. A query plan has a set of operators and an execution path that the nodes executing a query follow. An operator in a query plan makes a reservation that can be either bounded or unbounded, depending on how an operator reserves and uses memory.

• Bounded reservations reserve and use a fixed amount of memory per thread. The GROUP BY pipeline is an example of a bounded operator.
• Unbounded reservations reserve initial memory, but can request additional memory during query execution. The GROUP BY hash is an example of an unbounded operator.

It is possible that query plans have all bounded operators or a mix of bounded and unbounded operators.

The Vertica cost-based optimizer will tune the thread count for each operator in a query plan based on the query budget and execution parallelism of the resource pool used by the query and the statistics of participating tables. By using a resource pool with a higher query budget, we may get a query plan with a higher thread count for operators, if the optimizer thinks it will make the query run faster.

Once the query plan is generated, Vertica can compute the memory required to execute a plan because each operator in a query plan requires a fixed amount of initial memory per thread.

• Vertica will reserve the minimum amount of memory required to execute a query if it is higher than the query budget.
• Queries that have only bounded operators will request the memory required to execute the query. This memory may be less than the query budget.
• Queries that have unbounded operators will reserve the entire query budget even if the memory required to execute the query is less than the query budget. The extra memory that is reserved will be used by the unbounded operator before it makes requests for additional memory.

Queries with unbounded operators may need more memory than the initial reserved memory. In this case, the query can request chunks of additional memory until it has exhausted the available memory in the resource pool used by the query, at which point the query will error.