Enabling GPORCA
Enabling GPORCA
By default, Greenplum Database uses the legacy query planner. Server configuration
parameters enable or disable GPORCA.
You can configure GPORCA usage at the system, database, session, or query level using the
optimizer parameter. Refer to one of the following sections:
Note: You can disable the ability to enable or disable GPORCA with the server configuration
parameter optimizer_control. For information about the server configuration
parameters, see the Greenplum Database Reference Guide.
Important: If you intend to execute queries on partitioned tables with the Pivotal
Query Optimizer enabled, you must collect statistics on the partitioned table root partition
with the ANALYZE ROOTPARTITION command. See Collecting Root Partition Statistics.
Enabling GPORCA for a System
Set the server configuration parameter optimizer for the Greenplum Database system.
- Log into the Greenplum Database master host as gpadmin, the Greenplum Database administrator.
- Set the values of the server configuration parameters. These Greenplum Database
gpconfig utility commands sets the value of the parameters to
on:
$ gpconfig -c optimizer -v on --masteronly
- Restart Greenplum Database. This Greenplum Database gpstop utility
command reloads the postgresql.conf files of the master and segments
without shutting down Greenplum Database.
gpstop -u
Enabling GPORCA for a Database
Set the server configuration parameter optimizer for individual
Greenplum databases with the ALTER DATABASE command. For example, this
command enables GPORCA for the database
test_db.
> ALTER DATABASE test_db SET OPTIMIZER = ON ;
Enabling GPORCA for a Session or a Query
You can use the SET command to set optimizer server
configuration parameter for a session. For example, after you use the
psql utility to connect to Greenplum Database, this
SET command enables the Pivotal Query
Optimizer:
> set optimizer = on ;
To set the parameter for a specific query, include the SET command prior to running the query.