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.

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.
Parent topic: About the Pivotal Query Optimizer