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-based planner.

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

• The optimizer is listed at the end of the query plan. For example, when GPORCA generates the query plan, the query plan ends with:

   Optimizer: GPORCA
  

  When Greenplum Database falls back to the Postgres-based planner to generate the plan, the query plan ends with:

   Optimizer: Postgres-based planner
  

• These plan items appear only in the EXPLAIN plan output generated by GPORCA. The items are not supported in a Postgres-based planner query plan.

  • Assert operator
  • Sequence operator
  • Dynamic Index Scan
  • Dynamic Seq 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 Postgres-based 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-based 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.

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 Postgres-based planner. The EXPLAIN plan generated by the Postgres-based 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-based planner from GPORCA.

When this query is run, Greenplum Database falls back to the Postgres-based 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 run the query:

NOTICE,""Falling back to Postgres-based planner because GPORCA does not support the following feature: Queries on coordinator-only tables"