ORCA优化器浅析——How to use ORCA on Greenplum

--- Login to the database
02:13 $ psql
Pager usage is off.
Timing is on.
psql (9.4.26)
Type "help" for help.

--- Create any table
CREATE TABLE foo (id int, name text) DISTRIBUTED RANDOMLY;

-- Set the configuration parameter OPTIMIZER to enable / disable use of ORCA
-- optimizer = on; enable ORCA
-- optimizer = off; disable ORCA
-- When ORCA is enabled, if ORCA fails to evaluate a plan, the query will then be sent to Legacy Planner

SET OPTIMIZER=on;

--- **** Pivotal Optimizer (GPORCA) **** indicates that the plan is generated by ORCA
EXPLAIN SELECT * FROM foo;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..431.00 rows=2 width=12)
   ->  Seq Scan on foo  (cost=0.00..431.00 rows=1 width=12)
 Optimizer: Pivotal Optimizer (GPORCA)

SET OPTIMIZER=off;

--- **** Postgres query optimizer **** indicates that the plan is generated by Legacy Planner
EXPLAIN SELECT * FROM foo;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..596.00 rows=49600 width=36)
   ->  Seq Scan on foo  (cost=0.00..596.00 rows=24800 width=36)
 Optimizer: Postgres query optimizer

In cases where ORCA is not able to generate a plan, it may fall back to Legacy Planner to generate a plan

--- ORCA is enabled
SHOW OPTIMIZER;
 optimizer
-----------
on

--- Set configuration parameter to see fallback messages (if any)
SET optimizer_trace_fallback=on;

--- **** Optimizer: Postgres query optimizer **** indicates that the plan is generated by Legacy Planner
--- even though ORCA is enabled.
EXPLAIN SELECT * FROM pg_class;
INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  Feature not supported: Queries on master-only tables
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..11.37 rows=437 width=202)
 Optimizer: Postgres query optimizer

Where does Query Parsing and Planning occur?

Query Parsing and Planning occurs at Greenplum Master (QD, i.e. Query Dispatcher)

• SQL submitted to Master Node
• Master validates SQL and parses query
• Plan is produced at Master by ORCA or Legacy Planner
• Master Node obtains metadata from Catalog and annotates the query plan with metadata that segments need for execution

Who informs the Segments about the Query Plan?

Master dispatches the plan to the Greenplum Segments (QE, i.e. Query Executor). All QE run the same plan.

MPP Query Flow (Master -> Segments) showing location of Parsing, Query Optimization, Query Dispatch & Query Execution