A newer version of this documentation is available. Use the version menu above to view the most up-to-date release of the Greenplum 6.x documentation.

Determining the Query Optimizer that is Used

When GPORCA is enabled (the default), you can determine if Greenplum Database is using GPORCA or is falling back to the Postgres Planner.

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

When GPORCA generates the query plan, the setting optimizer=on and GPORCA version are displayed at the end of the query plan. For example.
```
 Settings:  optimizer=on
 Optimizer status: Pivotal Optimizer (GPORCA) version 1.584
```
When Greenplum Database falls back to the Postgres Planner to generate the plan, the setting optimizer=on and Postgres query optimizer are displayed at the end of the query plan. For example.
```
 Settings:  optimizer=on
 Optimizer status: Postgres 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: Postgres query optimizer
```
These plan items appear only in the EXPLAIN plan output generated by GPORCA. The items are not supported in a Postgres Planner query plan.
- Assert operator
- Sequence operator
- DynamicIndexScan
- DynamicSeqScan
When a query against a partitioned table is generated by GPORCA, 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 Postgres Planner lists the scanned partitions.

The log file contains messages that indicate which query optimizer was used. If Greenplum Database falls back to the Postgres Planner, 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 Postgres 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 GPORCA 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 '20160101') 
       INCLUSIVE END (date '20170101') 
       EXCLUSIVE EVERY (INTERVAL '1 month'),
   DEFAULT PARTITION outlying_dates );

This query against the table is supported by GPORCA 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 GPORCA. Greenplum Database falls back to the Postgres Planner. The EXPLAIN plan generated by the Postgres Planner 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 Postgres Planner from GPORCA.

When this query is run, Greenplum Database falls back to the Postgres Planner.

explain select * from pg_class;

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

NOTICE,""Feature not supported: Queries on master-only tables"