Enable PQO for a specific user/role
Post date: Jan 15, 2016 3:19:22 AM
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