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 GPORCA enabled, you must collect statistics on the partitioned table root partition
with the ANALYZE ROOTPARTITION command. The command ANALYZE
ROOTPARTITION collects statistics on the root partition of a partitioned table
based on the data in the table. Statistics are not collected on the leaf partitions, leaf
partition data is only sampled. If you specify a list of column names for a partitioned table,
the statistics for the columns and the root partition are collected. For information on the
ANALYZE command, see the Greenplum Database Reference Guide.

You can also use the Greenplum Database utility analyzedb to update table statistics. The
Greenplum Database utility analyzedb can update statistics
for multiple tables in parallel. The utility can also check table statistics and update
statistics only if the statistics are not current or do not exist. For information about the
analyzedb utility, see the Greenplum Database Utility
Guide.

As part of routine database maintenance, Refresh statistics on
the root partition when there are significant changes to leaf partition data.

Setting the optimizer_analyze_root_partition Parameter

When the configuration parameter optimizer_analyze_root_partition is set
to on, root partition statistics will be collected when
ANALYZE is run on a partitioned table. Root partition statistics are
required by GPORCA.

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_analyze_root_partition -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 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 GPORCA:

> set optimizer = on ;

To set the parameter for a specific query, include the SET command prior
to running the query.