EXPLAIN

--创建一个表customer_address_p1。
MogDB=# CREATE TABLE customer_address_p1 AS TABLE customer_address;

--修改explain_perf_mode为normal
MogDB=# SET explain_perf_mode=normal;

--显示表简单查询的执行计划。
MogDB=# EXPLAIN SELECT * FROM customer_address_p1;
QUERY PLAN
--------------------------------------------------
Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
Node/s: All dbnodes
(2 rows)

--以JSON格式输出的执行计划（explain_perf_mode为normal时）。
MogDB=# EXPLAIN(FORMAT JSON) SELECT * FROM customer_address_p1;
              QUERY PLAN
--------------------------------------
 [                                   +
   {                                 +
     "Plan": {                       +
       "Node Type": "Data Node Scan",+
       "Startup Cost": 0.00,         +
       "Total Cost": 0.00,           +
       "Plan Rows": 0,               +
       "Plan Width": 0,              +
       "Node/s": "All dbnodes"     +
     }                               +
   }                                 +
 ]
(1 row)

--如果有一个索引，当使用一个带索引WHERE条件的查询，可能会显示一个不同的计划。
MogDB=# EXPLAIN SELECT * FROM customer_address_p1 WHERE ca_address_sk=10000;
QUERY PLAN
--------------------------------------------------
Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
Node/s: dn_6005_6006
(2 rows)

--以YAML格式输出的执行计划（explain_perf_mode为normal时）。
MogDB=# EXPLAIN(FORMAT YAML) SELECT * FROM customer_address_p1 WHERE ca_address_sk=10000;
           QUERY PLAN
---------------------------------
 - Plan:                        +
     Node Type: "Data Node Scan"+
     Startup Cost: 0.00         +
     Total Cost: 0.00           +
     Plan Rows: 0               +
     Plan Width: 0              +
     Node/s: "dn_6005_6006"
(1 row)

--禁止开销估计的执行计划。
MogDB=# EXPLAIN(COSTS FALSE)SELECT * FROM customer_address_p1 WHERE ca_address_sk=10000;
       QUERY PLAN
------------------------
 Data Node Scan
   Node/s: dn_6005_6006
(2 rows)

--带有聚集函数查询的执行计划。
MogDB=# EXPLAIN SELECT SUM(ca_address_sk) FROM customer_address_p1 WHERE ca_address_sk<10000;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Aggregate  (cost=18.19..14.32 rows=1 width=4)
   ->  Streaming (type: GATHER)  (cost=18.19..14.32 rows=3 width=4)
         Node/s: All dbnodes
         ->  Aggregate  (cost=14.19..14.20 rows=3 width=4)
               ->  Seq Scan on customer_address_p1  (cost=0.00..14.18 rows=10 width=4)
                     Filter: (ca_address_sk < 10000)
(6 rows)

--创建一个二级分区表。
MogDB=# CREATE TABLE range_list
MogDB-# (
MogDB(#     month_code VARCHAR2 ( 30 ) NOT NULL ,
MogDB(#     dept_code  VARCHAR2 ( 30 ) NOT NULL ,
MogDB(#     user_no    VARCHAR2 ( 30 ) NOT NULL ,
MogDB(#     sales_amt  int
MogDB(# )
MogDB-# PARTITION BY RANGE (month_code) SUBPARTITION BY LIST (dept_code)
MogDB-# (
MogDB(#   PARTITION p_201901 VALUES LESS THAN( '201903' )
MogDB(#   (
MogDB(#     SUBPARTITION p_201901_a values ('1'),
MogDB(#     SUBPARTITION p_201901_b values ('2')
MogDB(#   ),
MogDB(#   PARTITION p_201902 VALUES LESS THAN( '201910' )
MogDB(#   (
MogDB(#     SUBPARTITION p_201902_a values ('1'),
MogDB(#     SUBPARTITION p_201902_b values ('2')
MogDB(#   )
MogDB(# );
CREATE TABLE

--执行带有二级分区表的查询语句。
--Iterations 和 Sub Iterations分别标识遍历了几个一级分区和二级分区。
--Selected Partitions标识哪些一级分区被实际扫描，Selected Subpartitions: (p:s)标识第p个一级分区下s个二级分区被实际扫描，如果一级分区下所有二级分区都被扫描则s显示为ALL。
MogDB=# EXPLAIN SELECT * FROM range_list WHERE dept_code = '1';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..13.81 rows=2 width=238)
   Iterations: 2, Sub Iterations: 2
   ->  Partitioned Seq Scan on range_list  (cost=0.00..13.81 rows=2 width=238)
         Filter: ((dept_code)::text = '1'::text)
         Selected Partitions:  1..2
         Selected Subpartitions:  1:1, 2:1
(6 rows)

--删除表customer_address_p1。
MogDB=# DROP TABLE customer_address_p1;