Pivotal Knowledge Base

Follow

"EXPLAIN ANALYZE" with a non-superuser role not get controlled by Resource Queue

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x
Others  

Symptom

When "EXPLAIN ANALYZE" on a query with a non-superuser role the task is not taken into account in the running queries of the Resource Queue.

Following is an example to show this issue:

1. Only pg_default resource queue configured

gpadmin=> select * from pg_resqueue;
  rsqname   | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
------------+---------------+--------------+---------------+--------------------
 pg_default |            20 |           -1 | f             |                  0
(1 row)

2. Login as non-super user

gpadmin=> select current_user;
 current_user 
--------------
 test1
(1 row)

Time: 0.309 ms
gpadmin=> \du
                         List of roles
 Role name |            Attributes             |   Member of   
-----------+-----------------------------------+---------------
 gpadmin   | Superuser, Create role, Create DB | 
 test1     |             

3. No query occupying resource queue slot

gpadmin=#  SELECT * FROM gp_toolkit.gp_resqueue_status;
 queueid |  rsqname   | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqhol
ders 
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+-------
    6055 | pg_default |            20 |             0 |           -1 |            0 |             -1 |              0 |          0 |       
   0
(1 row)

4. Run one query and confirm one slot in resource queue is occupied

gpadmin=> select pg_sleep(30);

gpadmin=#  SELECT * FROM gp_toolkit.gp_resqueue_status;
 queueid |  rsqname   | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqhol
ders 
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+-------
    6055 | pg_default |            20 |             1 |           -1 |            1 |             -1 |    1.31072e+08 |          0 |       
   1
(1 row)

5. Run "explain analyze" for the same query and still not any resource queue slot get allocated

gpadmin=> explain analyze select pg_sleep(30);
gpadmin=#  SELECT * FROM gp_toolkit.gp_resqueue_status;
 queueid |  rsqname   | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqhol
ders 
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+-------
-----
    6055 | pg_default |            20 |             0 |           -1 |            0 |             -1 |              0 |          0 |       
   0
(1 row)

Cause

EXPLAIN and EXPLAIN ANALYZE are treated as UTILITY inside GPDB, so they would not occupy resource queue slots. This is a designed behaviour as per JIRA MPP-26445. 

Resolution

No action is needed this is normal behaviour. GPDB documentation will be updated soon with this information.

 

Comments

Powered by Zendesk