Overview of the Pivotal Query Optimizer
The Pivotal Query Optimizer extends the planning and optimization
capabilities of the Greenplum Database legacy optimizer.
The Pivotal
Query Optimizer is extensible and achieves better optimization in multi-core architecture
environments. When the Pivotal Query Optimizer is enabled, Greenplum Database uses
the Pivotal Query Optimizer to generate an execution plan for a query when possible.
The Pivotal Query Optimizer also enhances Greenplum Database query performance
tuning in the following areas:
- Queries against partitioned tables
- Queries that contain a common table expression (CTE)
- Queries that contain subqueries
In Greenplum Database 4.3.5.0 and later, the Pivotal Query Optimizer co-exists with the legacy query optimizer. By default, Greenplum Database uses the legacy query optimizer. When the Pivotal Query Optimizer is enabled, Greenplum Database uses the Pivotal Query Optimizer to generate an execution plan for a query when possible. If the Pivotal Query Optimizer cannot be used, the legacy query optimizer is used.
The following figure shows how the Pivotal Query Optimizer fits into the query planning architecture.
![](../../../graphics/piv-opt.png)
Note: All legacy query optimizer (planner) server configuration parameters are ignored by the
Pivotal Query Optimizer. However, if Greenplum Database falls back to the legacy
optimizer, the planner server configuration parameters will impact the query plan generation.
For a list of legacy query optimizer (planner) server configuration parameters, see Query Tuning Parameters.