Query budgeting

Before it can execute a query, Vertica devises a query plan, which it sends to each node that will participate in executing the query.

Before it can execute a query, Vertica devises a query plan, which it sends to each node that will participate in executing the query. The Resource Manager evaluates the plan on each node and estimates how much memory and concurrency the node needs to execute its part of the query. This is the query budget, which Vertica stores in the query_budget_kb column of system table V_MONITOR.RESOURCE_POOL_STATUS.

A query budget is based on several parameter settings of the resource pool where the query will execute:

  • MEMORYSIZE

  • MAXMEMORYSIZE

  • PLANNEDCONCURRENCY

You can modify MAXMEMORYSIZE and PLANNEDCONCURRENCY for the GENERAL resource pool with ALTER RESOURCE POOL. This resource pool typically executes queries that are not assigned to a user-defined resource pool. You can set all three parameters for any user-defined resource pool when you create it with CREATE RESOURCE POOL, or later with ALTER RESOURCE POOL.

Computing the GENERAL pool query budget

Vertica calculates query budgets in the GENERAL pool with the following formula:

queryBudget = queuingThresholdPool / PLANNEDCONCURRENCY

Computing query budgets for user-defined resource pools

For user-defined resource pools, Vertica uses the following algorithm:

  1. If MEMORYSIZE is set to 0 and MAXMEMORYSIZE is not set:

    queryBudget = queuingThresholdGeneralPool / PLANNEDCONCURRENCY
    
  2. If MEMORYSIZE is set to 0 and MAXMEMORYSIZE is set to a non-default value:

    query-budget = queuingThreshold / PLANNEDCONCURRENCY
    
  3. If MEMORYSIZE is set to a non-default value:

    queryBudget = MEMORYSIZE / PLANNEDCONCURRENCY
    

By carefully tuning a resource pool's MEMORYSIZE and PLANNEDCONCURRENCY parameters, you can control how much memory can be budgeted for queries.

See also

Do You Need to Put Your Query on a Budget? in the Vertica User Community.