Considerations when Using the Pivotal Query Optimizer

A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 4.x documentation.

Considerations when Using the Pivotal Query Optimizer

To execute queries optimally with the Pivotal Query Optimizer, query criteria to consider.
Ensure the following criteria are met:
  • The table does not contain multi-column partition keys.
  • The multi-level partitioned table is a uniform multi-level partitioned table. See About Uniform Multi-level Partitioned Tables.
  • The server configuration parameter optimizer_enable_master_only_queries is set to on when running against master only tables such as the system table pg_attribute. For information about the parameter, see the Greenplum Database Reference Guide.
    Note: Enabling this parameter decreases performance of short running catalog queries. To avoid this issue, set this parameter only for a session or a query.
  • Statistics have been collected on the root partition of a partitioned table.

If the partitioned table contains more than 20,000 partitions, consider a redesign of the table schema.

The Pivotal Query Optimizer generates minidumps to describe the optimization context for a given query. The minidump files are used by Pivotal support to analyze Greenplum Database issues. The information in the file is not in a format that can be easily used by customers for debugging or troubleshooting. The minidump file is located under the master data directory and uses the following naming format:


For information about the minidump file, see the server configuration parameter optimizer_minidump in the Greenplum Database Reference Guide.

When the EXPLAIN ANALYZE command uses the Pivotal Query Optimizer, the EXPLAIN plan shows only the number of partitions that are being eliminated. The scanned partitions are not shown. To show name of the scanned partitions in the segment logs set the server configuration parameter gp_log_dynamic_partition_pruning to on. This example SET command enables the parameter.

SET gp_log_dynamic_partition_pruning = on;