Enabling the Pivotal Query Optimizer
- 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 the Pivotal Query Optimizer. 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, Pivotal recommends refreshing statistics on the root partition when there are significant changes to child 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 the Pivotal Query Optimizer.
- 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 the Pivotal Query Optimizer 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 the Pivotal Query Optimizer for a Database
> ALTER DATABASE test_db SET OPTIMIZER = ON ;
Enabling the Pivotal Query Optimizer 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.