Determining the Query Optimizer that is Used

When the Pivotal Query Optimizer is enabled, you can determine if Greenplum Database is using the Pivotal Query Optimizer or is falling back to the legacy query optimizer.

You can examine the EXPLAIN query plan for the query determine which query optimizer was used by Greenplum Database to execute the query:

When the Pivotal Query Optimizer generates the query plan, the setting optimizer=on and the Pivotal Query Optimizer version are displayed at the end of the query plan. For example.
```
 Settings:  optimizer=on
 Optimizer status: PQO version 1.584
```
When Greenplum Database falls back to the legacy optimizer to generate the plan, the setting optimizer=on and legacy query optimizer are displayed at the end of the query plan. For example.
```
 Settings:  optimizer=on
 Optimizer status: legacy query optimizer
```
When the server configuration parameter OPTIMIZER is off, these lines are displayed at the end of a query plan.
```
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
```
These plan items appear only in the EXPLAIN plan output generated by the Pivotal Query Optimizer. The items are not supported in a legacy optimizer query plan.
- Assert operator
- Sequence operator
- DynamicIndexScan
- DynamicTableScan
- Table Scan
When a query against a partitioned table is generated by Pivotal Query Optimizer, the EXPLAIN plan displays only the number of partitions that are being eliminated is listed. The scanned partitions are not shown. The EXPLAIN plan generated by the legacy optimizer lists the scanned partitions.

The log file contains messages that indicate which query optimizer was used. If Greenplum Database falls back to the legacy optimizer, a message with NOTICE information is added to the log file that indicates the unsupported feature. Also, the label Planner produced plan: appears before the query in the query execution log message when Greenplum Database falls back to the legacy optimizer.

Note: You can configure Greenplum Database to display log messages on the psql command line by setting the Greenplum Database server configuration parameter client_min_messages to LOG. See the Greenplum Database Reference Guide for information about the parameter.

Examples

This example shows the differences for a query that is run against partitioned tables when the Pivotal Query Optimizer is enabled.

This CREATE TABLE statement creates a table with single level partitions:

CREATE TABLE sales (trans_id int, date date, 
    amount decimal(9,2), region text)
   DISTRIBUTED BY (trans_id)
   PARTITION BY RANGE (date)
      (START (date '20110101') 
       INCLUSIVE END (date '20120101') 
       EXCLUSIVE EVERY (INTERVAL '1 month'),
   DEFAULT PARTITION outlying_dates );

This query against the table is supported by the Pivotal Query Optimizer and does not generate errors in the log file:

select * from sales ;

The EXPLAIN plan output lists only the number of selected partitions.

 ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=50 width=4)
       Partitions selected:  13 (out of 13)

If a query against a partitioned table is not supported by the Pivotal Query Optimizer. Greenplum Database falls back to the legacy optimizer. The EXPLAIN plan generated by the legacy optimizer lists the selected partitions. This example shows a part of the explain plan that lists some selected partitions.

 ->  Append  (cost=0.00..0.00 rows=26 width=53)
     ->  Seq Scan on sales2_1_prt_7_2_prt_usa sales2  (cost=0.00..0.00 rows=1 width=53)
     ->  Seq Scan on sales2_1_prt_7_2_prt_asia sales2  (cost=0.00..0.00 rows=1 width=53)
     ...

This example shows the log output when the Greenplum Database falls back to the legacy query optimizer from the Pivotal Query Optimizer.

When this query is run, Greenplum Database falls back to the legacy query optimizer.

explain select * from pg_class;

A message is added to the log file. The message contains this NOTICE information that indicates the reason the Pivotal Query Optimizer did not execute the query:

NOTICE,""Feature not supported by the Pivotal Query Optimizer: Queries on master-only tables"