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 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 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: 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 GPORCA. 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 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 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.

Parent topic: About GPORCA

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 '2016­01­01') 
       INCLUSIVE END (date '2017­01­01') 
       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 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 GPORCA.

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 GPORCA did not execute the query:

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