Enabling GPORCA
A newer version of this documentation is available. Use the version menu above to view the most up-to-date release of the Greenplum 4.x documentation.
Enabling GPORCA
- Set the optimizer_analyze_root_partition parameter to on to enable statistics collection for the root partition of a partitioned table.
- Set the optimizer parameter to on to enable GPORCA. You can set the parameter at these levels:
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
> ALTER DATABASE test_db SET OPTIMIZER = ON ;
Enabling GPORCA for a Session or a Query
> set optimizer = on ;
To set the parameter for a specific query, include the SET command prior to running the query.