Pivotal Query Optimizer Limitations
There are limitations in Greenplum Database
4.3.5.0 and later when the Pivotal Query Optimizer is enabled. The Pivotal Query
Optimizer and the legacy query optimizer currently coexist in Greenplum Database 4.3.5.0 and later because the Pivotal Query Optimizer does not
support all Greenplum Database features.
This section describes the limitations.
Unsupported SQL Query Features
These are unsupported features when the Pivotal Query Optimizer is enabled:
- Indexed expressions
- PERCENTILE window function
- External parameters
- These types of partitioned tables:
- Non-uniform partitioned tables.
- Partitioned tables that have been altered to use an external table as a leaf child partition.
- SortMergeJoin (SMJ)
- Ordered aggregations
- These analytics extensions:
- CUBE
- Multiple grouping sets
- These scalar operators:
- ROW
- ROWCOMPARE
- FIELDSELECT
- Multiple DISTINCT qualified aggregate functions
- Inverse distribution functions
Performance Regressions
When the Pivotal Query Optimizer is enabled in Greenplum Database, the
following features are known performance regressions:
- Short running queries - For the Pivotal Query Optimizer, short running queries might encounter additional overhead due to the Pivotal Query Optimizer enhancements for determining an optimal query execution plan.
- ANALYZE - For Pivotal Query Optimizer, the ANALYZE command generates root partition statistics for partitioned tables. For the legacy optimizer, these statistics are not generated.
- DML operations - For Pivotal Query Optimizer, DML enhancements including the support of updates on partition and distribution keys might require additional overhead.
Also, enhanced functionality of the features from previous versions could result in additional time required when Pivotal Query Optimizer executes SQL statements with the features.
Greenplum Command Center Database Limitation
For Greenplum Command Center monitoring performance, Pivotal recommends the default setting for Pivotal Query Optimizer (off) for the gpperfmon database that is used by Greenplum Command Center. Enabling Pivotal Query Optimizer for the gpperfmon database is not supported. To ensure that the Pivotal Query Optimizer is disabled for the gpperfmon database, run this command on the system where the database is installed:
ALTER DATABASE gpperfmon SET OPTIMIZER = OFF