Pivotal Knowledge Base

Follow

ERROR: statement requires more resources than resource queue allows

Environment

Product Version
Pivotal Greenplum (GPDB) All Version

Symptom

Database queries (DDL OR DML) are erroring out with 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,

Cause

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

Resolution

  1. Run EXPLAIN on the query and analyze the slice/operation that is taking most of the cost
  2. Cost will get higher when statistics is not upto 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.

Workaround

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 ); 

Comments

Powered by Zendesk