常见问题

场景一：为什么不能生成索引扫描路径？

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=# INSERT INTO t1 (a, b, c) VALUES (generate_series(1, 5000), generate_series(1, 5000), floor(random()*10));
INSERT 0 5000
gaussdb=# ANALYZE t1;
ANALYZE
gaussdb=# SET explain_perf_mode=normal;
SET

建表语句如上面所示，执行以下SQL为什么没有办法走索引路径：

gaussdb=# EXPLAIN SELECT /*+indexscan(t1)*/* FROM t1;
WARNING:  unused hint: IndexScan(t1)
                      QUERY PLAN
-------------------------------------------------------
 [Parameterized]
 Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
(2 rows)

gaussdb=# EXPLAIN SELECT /*+indexscan(t1)*/* FROM t1 WHERE t1.b >= 100;
WARNING:  unused hint: IndexScan(t1)
                      QUERY PLAN
-------------------------------------------------------
 [Parameterized]
 Seq Scan on t1  (cost=1.69..84.50 rows=4900 width=16)
   Filter: (b >= 100), (Expression Flatten Optimized)
(3 rows)

gaussdb=# EXPLAIN SELECT /*+indexscan(t1)*/* FROM t1 ORDER BY c;
WARNING:  unused hint: IndexScan(t1)
                         QUERY PLAN
-------------------------------------------------------------
 [Parameterized]
 Sort  (cost=379.19..391.69 rows=5000 width=16)
   Sort Key: c
   ->  Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
(4 rows)

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

索引的优势主要在于数据组织的有序性以及对于过滤性好的条件能够快速查找，否则随机读写对于顺序扫描没有优势。上面三种SQL语句要么没有支持走索引的过滤条件，要么排序条件不在索引列上，因此通常在生成扫描路径时会将索引路径裁剪掉，减小搜索空间。

场景二：如何控制选择不同的连接路径和连接顺序？

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, 5000), generate_series(1, 5000), floor(random()*10));
INSERT 0 5000
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, 5000), generate_series(1, 5000), floor(random()*10));
INSERT 0 5000
gaussdb=# ANALYZE t2;
ANALYZE
gaussdb=# SET explain_perf_mode=normal;
SET

建表语句如上所示，假设有如下SQL语句：

 SELECT * FROM t1 LEFT JOIN t2 ON t1.b = t2.b;

优化器默认选择的执行计划如下所示：

gaussdb=# EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.b = t2.b;
                            QUERY PLAN
-------------------------------------------------------------------
 [Parameterized]
 Hash Left Join  (cost=134.50..275.25 rows=5000 width=32)
   Hash Cond: (t1.b = t2.b), (Expression Flatten Optimized)
   ->  Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
   ->  Hash  (cost=72.00..72.00 rows=5000 width=16)
         ->  Seq Scan on t2  (cost=0.00..72.00 rows=5000 width=16)
(6 rows)

通常有两种办法来控制选择NestloopJoin计划：使用GUC控制或者使用HINT控制。使用GUC控制只能通过关闭其他路径生成的方式来提高NestloopJoin被选中的概率，而使用HINT则能更加精准地指定连接的类型，以下是两种控制方式的示例：

---使用GUC参数控制生成NestloopJoin计划
gaussdb=# SET enable_nestloop=on;
SET
gaussdb=# SET enable_hashjoin=off;
SET
gaussdb=# SET enable_mergejoin=off;
SET
gaussdb=# EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.b = t2.b;
                                QUERY PLAN
--------------------------------------------------------------------------
 [Parameterized]
 Nested Loop Left Join  (cost=0.00..1507.90 rows=5000 width=32)
   ->  Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
   ->  Index Scan using idx_t2_b on t2  (cost=0.00..0.28 rows=1 width=16)
         Index Cond: (t1.b = b)
(5 rows)

---使用HINT控制生成NestloopJoin计划
gaussdb=# SET enable_hashjoin=on;
SET
gaussdb=# SET enable_mergejoin=on;
SET
gaussdb=# EXPLAIN SELECT /*+nestloop(t1 t2)*/* FROM t1 LEFT JOIN t2 ON t1.b = t2.b;
                                QUERY PLAN
--------------------------------------------------------------------------
 [Parameterized]
 Nested Loop Left Join  (cost=0.00..1507.90 rows=5000 width=32)
   ->  Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
   ->  Index Scan using idx_t2_b on t2  (cost=0.00..0.28 rows=1 width=16)
         Index Cond: (t1.b = b)
(5 rows)

通常连接顺序是根据生成的路径代价来选择，有些连接顺序的路径甚至无法生成，但对于可以生成的连接顺序，可以通过Leading Hint控制，仍以上面的SQL为例，让t2表做驱动表，其控制方式如下：

gaussdb=# EXPLAIN SELECT /*+leading((t2 t1))*/* FROM t1 LEFT JOIN t2 ON t1.b = t2.b;
                            QUERY PLAN
-------------------------------------------------------------------
 [Parameterized]
 Hash Right Join  (cost=134.50..275.25 rows=5000 width=32)
   Hash Cond: (t2.b = t1.b), (Expression Flatten Optimized)
   ->  Seq Scan on t2  (cost=0.00..72.00 rows=5000 width=16)
   ->  Hash  (cost=72.00..72.00 rows=5000 width=16)
         ->  Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
(6 rows)

该计划通过leading((t2 t1)) 这个HINT，强制指定t2表做驱动表，t1表做被驱动表。最后执行如下语句删除t1表和t2表：

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

更多关于Join顺序、Join方式的HINT使用可以参考：《SQL调优指南》中“使用Plan Hint进行调优 > Join顺序的Hint”和“使用Plan Hint进行调优 > Join方式的Hint”章节。

场景三：为什么无法生成HashJoin连接路径？

以下面的SQL场景为例：

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, 5000), generate_series(1, 5000), floor(random()*10));
INSERT 0 5000
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, 5000), generate_series(1, 5000), floor(random()*10));
INSERT 0 5000
gaussdb=# ANALYZE t2;
ANALYZE
gaussdb=# SET explain_perf_mode=normal;
SET
gaussdb=# SET enable_nestloop=off;
SET
gaussdb=# SET enable_hashjoin=on;
SET
gaussdb=# SET enable_mergejoin=on;
SET
gaussdb=# EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.b != t2.b;
                              QUERY PLAN
----------------------------------------------------------------------
 [Parameterized]
 Nested Loop Left Join  (cost=0.00..375156.50 rows=24995000 width=32)
   Join Filter: (t1.b <> t2.b), (Expression Flatten Optimized)
   ->  Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
   ->  Materialize  (cost=0.00..97.00 rows=5000 width=16)
         ->  Seq Scan on t2  (cost=0.00..72.00 rows=5000 width=16)
(6 rows)

gaussdb=# EXPLAIN SELECT /*+hashjoin(t1 t2)*/* FROM t1 LEFT JOIN t2 ON t1.b != t2.b;
WARNING:  unused hint: HashJoin(t1 t2)
                              QUERY PLAN
----------------------------------------------------------------------
 [Parameterized]
 Nested Loop Left Join  (cost=0.00..375156.50 rows=24995000 width=32)
   Join Filter: (t1.b <> t2.b), (Expression Flatten Optimized)
   ->  Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
   ->  Materialize  (cost=0.00..97.00 rows=5000 width=16)
         ->  Seq Scan on t2  (cost=0.00..72.00 rows=5000 width=16)
(6 rows)

gaussdb=# EXPLAIN SELECT /*+mergejoin(t1 t2)*/* FROM t1 LEFT JOIN t2 ON t1.b != t2.b;
WARNING:  unused hint: MergeJoin(t1 t2)
                              QUERY PLAN
----------------------------------------------------------------------
 [Parameterized]
 Nested Loop Left Join  (cost=0.00..375156.50 rows=24995000 width=32)
   Join Filter: (t1.b <> t2.b), (Expression Flatten Optimized)
   ->  Seq Scan on t1  (cost=0.00..72.00 rows=5000 width=16)
   ->  Materialize  (cost=0.00..97.00 rows=5000 width=16)
         ->  Seq Scan on t2  (cost=0.00..72.00 rows=5000 width=16)
(6 rows)

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

上述SQL，即使通过HINT或GUC参数也无法控制生成HashJoin计划或MergeJoin计划。实际上除了NestloopJoin是通用路径生成算法外，HashJoin和MergeJoin路径的生成都有一些约束条件。对于HashJoin来说，其算法原理是基于Hash表的，对于非等值连接的情况无法通过Hash表快速查找目标数据，也就不能生成HashJoin路径。

父主题： CBO优化器

上一篇：操作指导

下一篇：自适应计划选择