Pivotal Knowledge Base


ERROR: statement requires more resources than resource queue allows


Pivotal Greenplum Database (GPDB) all versions


Database queries (DDL OR DML) are erroring out with the message:

ERROR:  statement requires more resources than resource queue allows

From the master logs you can get the queries reporting the same error as shown in below example:

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,


The statement that has encountered the issue is trying to use more resources than the queue actually allows.

In the example, the query is erroring out because the cost upper limit for the query is greater than the max_cost of the resource queue. You can run an EXPLAIN on the failing query to verify the upper limit of the cost to 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 from the resource queue catalog you can find out the max cost of that specific resource queue(47 in this case)

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


  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 statistics is not up to date. Make sure all the underlying tables run ANALYZE.
  3. To eliminate bloat - Perform maintenance task like VACUUM FULL or REDISTRIBUTE to bring down the number of blocks scanned and hence it will reduce the cost
  4. If the above things cannot bring the cost down then the query may need a rewrite by a developer; otherwise, the cost may need to be increased to accommodate expensive queries.


Increase the max_cost of the resource queue through superuser. You can select the value slightly higher than the upper limit from EXPLAIN plan.

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

By default the max_cost for a resource queue is unlimited and you can set that as well

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


Powered by Zendesk