操作指导

前提条件

本节将结合示例展示优化器基本的计划选择过程，使用如下建表语句：

gaussdb=# CREATE TABLE t1(a INT, b INT, c INT, d INT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# CREATE INDEX idx_t1_a ON t1(a);
CREATE INDEX
gaussdb=# CREATE INDEX idx_t1_b ON t1(b);
CREATE INDEX
gaussdb=# INSERT INTO t1 (a, b, c) VALUES (generate_series(1, 10000), generate_series(1, 5000), floor(random()*10));
INSERT 0 10000
gaussdb=# ANALYZE t1;
ANALYZE
gaussdb=# CREATE TABLE t2(a INT, b INT, c INT, d INT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# CREATE INDEX idx_t2_a ON t2(a);
CREATE INDEX
gaussdb=# CREATE INDEX idx_t2_b ON t2(b);
CREATE INDEX
gaussdb=# INSERT INTO t2 (a, b, c) VALUES (generate_series(1, 10000), generate_series(1, 5000), floor(random()*10));
INSERT 0 10000
gaussdb=# ANALYZE t2;
ANALYZE
gaussdb=# SET explain_perf_mode=normal;
SET
gaussdb=# SET enable_hashjoin=on;
SET
gaussdb=# SET enable_mergejoin=on;
SET

操作示例

下面示例通过对同一个SQL的过滤条件取不同的常量值来展示代价模型对CBO优化器进行计划选择时的影响。首先，执行如下SQL语句并查看其选择的计划（过滤条件取值：t2.b <= 100）：

gaussdb=# EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.c AND t2.b <= 100;
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=13.95..16.88 rows=100 width=32)
   Node/s: All datanodes
   ->  Merge Join  (cost=11.89..12.76 rows=100 width=32)
         Merge Cond: (t1.a = t2.c)
         ->  Index Scan using idx_t1_a on t1  (cost=0.00..44.47 rows=5000 width=16)
         ->  Sort  (cost=11.84..12.09 rows=100 width=16)
               Sort Key: t2.c
               ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..8.52 rows=100 width=16)
                     Spawn on: All datanodes
                     ->  Index Scan using idx_t2_b on t2  (cost=0.00..3.73 rows=100 width=16)
                           Index Cond: (b <= 100)
(11 rows)

当过滤条件取值变为 t2.b <= 3000时，优化器选择生成计划如下所示：

gaussdb=# EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.c AND t2.b <= 3000;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=49.51..299.97 rows=3000 width=32)
   Node/s: All datanodes
   ->  Hash Join  (cost=45.51..174.97 rows=3000 width=32)
         Hash Cond: (t2.c = t1.a)
         ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..115.24 rows=3000 width=16)
               Spawn on: All datanodes
               ->  Seq Scan on t2  (cost=0.00..28.84 rows=3000 width=16)
                     Filter: (b <= 3000)
         ->  Hash  (cost=24.67..24.67 rows=5001 width=16)
               ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
(10 rows)

上述SQL中，t1表与t2表做INNER JOIN，连接条件为t1.a = t2.c AND t2.b <=100, 其中连接条件t1.a = t2.c不变，当t2.b <= ? 常量值取值100时执行计划选择Merge Join路径，利用t1表和t2表上的索引条件提升执行性能；但当常量值变为3000时，执行计划变成了Hash Join路径，其表明随着优化器会根据连接表的不同、约束条件的变化选择合适的查询计划，从而在实际生产环境中能够有效减少人工调优的场景，提升效率。

后置操作

gaussdb=# DROP TABLE t1;
DROP TABLE
gaussdb=# DROP TABLE t2;
DROP TABLE
gaussdb=# RESET explain_perf_mode;
RESET