操作指导

前提条件

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

gaussdb=# CREATE TABLE t1(a INT, b INT, c INT, d INT);
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);
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_nestloop=on;
SET
gaussdb=# SET enable_mergejoin=on;
SET
gaussdb=# SET enable_hashjoin=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
-----------------------------------------------------------------------------------
 [Parameterized]
 Merge Join  (cost=50.26..53.37 rows=200 width=32)
   Merge Cond: (t1.a = t2.c)
   ->  Index Scan using idx_t1_a on t1  (cost=0.00..239.45 rows=10000 width=16)
   ->  Sort  (cost=50.16..50.66 rows=200 width=16)
         Sort Key: t2.c
         ->  Index Scan using idx_t2_b on t2  (cost=0.00..42.52 rows=200 width=16)
               Index Cond: (b <= 100)
(8 rows)

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

gaussdb=# EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.c AND t2.b <= 3000;
                             QUERY PLAN
---------------------------------------------------------------------
 [Parameterized]
 Hash Join  (cost=269.00..520.50 rows=6000 width=32)
   Hash Cond: (t2.c = t1.a), (Expression Flatten Optimized)
   ->  Seq Scan on t2  (cost=0.00..169.00 rows=6000 width=16)
         Filter: (b <= 3000), (Expression Flatten Optimized)
   ->  Hash  (cost=144.00..144.00 rows=10000 width=16)
         ->  Seq Scan on t1  (cost=0.00..144.00 rows=10000 width=16)
(7 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