Determining the Query Optimizer that is Used
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 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.When the server configuration parameter OPTIMIZER is off, these lines are displayed at the end of a query plan.Settings: optimizer=on Optimizer status: Postgres query optimizer
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.
Examples
This example shows the differences for a query that is run against partitioned tables when GPORCA is enabled.
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 );
select * from sales ;
-> 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.
explain select * from pg_class;
NOTICE,""Feature not supported: Queries on master-only tables"