Unsupported SQL Query Features
- Indexed expressions (an index defined as expression based on one or more columns of the table)
- 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:
- Multiple grouping sets
- These scalar operators:
- Multiple DISTINCT qualified aggregate functions
- Inverse distribution functions
- Short running queries - For GPORCA, short running queries might encounter additional overhead due to GPORCA enhancements for determining an optimal query execution plan.
- ANALYZE - For GPORCA, the ANALYZE command generates root partition statistics for partitioned tables. For the legacy optimizer, these statistics are not generated.
- DML operations - For GPORCA, 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 GPORCA executes SQL statements with the features.
Greenplum Command Center Database Limitation
For Greenplum Command Center monitoring performance, use the default setting for GPORCA (off) for the gpperfmon database that is used by Greenplum Command Center. Enabling GPORCA for the gpperfmon database is not supported. To ensure that GPORCA is disabled for the gpperfmon database, run this command on the system where the database is installed:
ALTER DATABASE gpperfmon SET OPTIMIZER = OFF