ERROR: statement requires more resources than resource queue allows

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Database queries (DDL OR DML) produce the following error:

ERROR:  statement requires more resources than resource queue allows

In the master logs, the queries report the same error:

2016-01-04 14:44:30.649191 PST,"test11","template1",p22695,th1490502224,"[local]",,2016-01-04 14:44:28 PST,1648067,con78,cmd2,seg-1,,dx586,x1648067,sx1,"ERROR","53000","statement requires more resources than resource queue allows",,,,,,"INSERT INTO target_table SELECT * FROM source_table",0,,"resqueue.c",377,

Environment

Cause

The query is attempting to use more resources than the queue actually allows. In this example, the query produces an error because the upper limit cost for the query is greater than the max_cost of the resource queue. Run an EXPLAIN on the failing query to verify the upper limit cost, which is 47.75 in this case.

template1=> EXPLAIN INSERT INTO target_table SELECT * FROM source_table;
                           QUERY PLAN                           
----------------------------------------------------------------
 Insert (slice0; segments: 8)  (rows=385 width=103)
   ->  Seq Scan on source_table  (cost=0.00..47.75 rows=385 width=103)
 Optimizer status: legacy query optimizer
(3 rows)

Then find out the MaxCost of that specific resource queue from the resource queue catalog. In this case, the max cost is 47.

 RQname   | ActiveStatment | MaxCost | MinCost | CostOvercommit | MemoryLimit | Priority | RQAssignedUsers
----------+----------------+---------+---------+----------------+-------------+----------+---------------
etl        | 1              | 47   | 0    | 0              | -1       | medium   |               1

Resolution

1. Run EXPLAIN on the query and analyze the slice/operation that is taking most of the cost.
2. The cost will get higher when the statistics are not up to date. Make sure all the underlying tables run ANALYZE.
3. To eliminate bloat, perform a maintenance task, such as VACUUM FULL or REDISTRIBUTE, to bring down the number of blocks scanned and reduce the cost.
4. If the above steps cannot bring the cost down then the query may need to be rewritten by a developer. Otherwise, the cost may need to be increased to accommodate expensive queries.

Workaround

Increase the max_cost of the resource queue through superuser. Select the value slightly higher than the upper limit from the EXPLAIN PLAN:

ALTER RESOURCE QUEUE res_name WITH ( max_cost=<value> ); 

Set the max_cost as well. By default, the max_cost for a resource queue is unlimited.

ALTER RESOURCE QUEUE res_name WITH ( max_cost=-1 );