Determining the Query Optimizer that is Used
Determining the Query Optimizer that is Used
- 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: Pivotal Optimizer (GPORCA)
When Greenplum Database falls back to the Postgres Planner to generate the plan, the query plan ends with:Optimizer: 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
- 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 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"