Enable PQO for a specific user/role

posted Jan 14, 2016, 7:19 PM by Sachchida Ojha   [ updated May 20, 2016, 5:53 PM ]
gpadmin=# select * from pg_roles where rolname='sachi';
-[ RECORD 1 ]-----+---------
rolname           | sachi
rolsuper          | f
rolinherit        | t
rolcreaterole     | f
rolcreatedb       | f
rolcatupdate      | f
rolcanlogin       | f
rolconnlimit      | -1
rolpassword       | ********
rolvaliduntil     | 
rolconfig         | 
rolresqueue       | 6055
oid               | 26097
rolcreaterextgpfd | f
rolcreaterexthttp | f
rolcreatewextgpfd | f
rolcreaterexthdfs | f
rolcreatewexthdfs | f
gpadmin=# alter role sachi set optimizer=on;
ALTER ROLE
gpadmin=#  select * from pg_roles where rolname='sachi';
-[ RECORD 1 ]-----+---------------
rolname           | sachi
rolsuper          | f
rolinherit        | t
rolcreaterole     | f
rolcreatedb       | f
rolcatupdate      | f
rolcanlogin       | f
rolconnlimit      | -1
rolpassword       | ********
rolvaliduntil     | 
rolconfig         | {optimizer=on}
rolresqueue       | 6055
oid               | 26097
rolcreaterextgpfd | f
rolcreaterexthttp | f
rolcreatewextgpfd | f
rolcreaterexthdfs | f
rolcreatewexthdfs | f

gpadmin=# 

optimizer_minidump : 

The PQO generates minidump files to describe the optimization context for a given query. The minidump files are used by Pivotal support to analyze Greenplum Database issues. The information in the file is not in a format that can be easily used by customers for debugging or troubleshooting. The minidump file is located under the master data directory and uses the following naming format:

Minidump_date_time.mdp

The minidump file contains this query related information:
  • Catalog objects including data types, tables, operators, and statistics required by the Pivotal Query Optimizer
  • An internal representation (DXL) of the query
  • An internal representation (DXL) of the plan produced by the Pivotal Query Optimizer
  • System configuration information passed to the Pivotal Query Optimizer such as server configuration parameters, cost and statistics configuration, and number of segments
  • A stack trace of errors generated while optimizing the query
Setting this parameter to ALWAYS generates a minidump for all queries. Pivotal recommends that you set this parameter to ONERROR in production environments to minimize total optimization time.

Value Range Default        Set Classifications
ONERROR ONERROR master
ALWAYS         session
                                                reload

Comments