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:

Enabling GPORCA for a System
Enabling GPORCA for a Database
Enabling GPORCA for a Session or a Query

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.