文档首页/ 云数据库 GaussDB/ 开发指南（分布式_V2.0-10.x）/ 特性使用指导/ CBO优化器/ 常见问题
更新时间：2026-07-28 GMT+08:00
查看PDF
分享

常见问题

  • 场景一：为什么不能生成索引扫描路径？ 
    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=# 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 enable_fast_query_shipping=off;
SET
gaussdb=# SET enable_stream_operator = on;
SET
gaussdb=# SET explain_perf_mode=normal;
SET

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

    gaussdb=# EXPLAIN SELECT /*+indexscan(t1)*/* FROM t1;
WARNING:  unused hint: IndexScan(t1)
                            QUERY PLAN
------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=4.00..233.05 rows=5000 width=16)
   Node/s: All datanodes
   ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
(3 rows)

gaussdb=# EXPLAIN SELECT /*+indexscan(t1)*/* FROM t1 WHERE t1.b >= 100;
WARNING:  unused hint: IndexScan(t1)
                            QUERY PLAN
------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=4.56..232.96 rows=4900 width=16)
   Node/s: All datanodes
   ->  Seq Scan on t1  (cost=0.56..28.84 rows=4900 width=16)
         Filter: (b >= 100)
(4 rows)

gaussdb=# EXPLAIN SELECT /*+indexscan(t1)*/* FROM t1 ORDER BY c;
WARNING:  unused hint: IndexScan(t1)
                             QUERY PLAN
--------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=117.88..326.42 rows=5001 width=16)
   Merge Sort Key: c
   Node/s: All datanodes
   ->  Sort  (cost=113.88..118.05 rows=5001 width=16)
         Sort Key: c
         ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
(6 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);
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, 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);
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, 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=on;
SET
gaussdb=# SET enable_hashjoin=on;
SET
gaussdb=# SET enable_mergejoin=on;
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
-----------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=187.03..576.53 rows=5000 width=32)
   Node/s: All datanodes
   ->  Hash Left Join  (cost=183.03..368.15 rows=5000 width=32)
         Hash Cond: (t1.b = t2.b)
         ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..162.20 rows=5000 width=16)
               Spawn on: All datanodes
               ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
         ->  Hash  (cost=162.20..162.20 rows=5001 width=16)
               ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..162.20 rows=5000 width=16)
                     Spawn on: All datanodes
                     ->  Seq Scan on t2  (cost=0.00..24.67 rows=5000 width=16)
(11 rows)

    通常有两种办法来控制选择NestloopJoin计划：使用GUC控制或者使用HINT控制。使用GUC控制只能通过关闭其他路径生成的方式来提高Nestloop Join被选中的概率，而使用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
-----------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=4.00..42220.27 rows=5000 width=32)
   Node/s: All datanodes
   ->  Nested Loop Left Join  (cost=0.00..42011.90 rows=5000 width=32)
         Join Filter: (t1.b = t2.b)
         ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..162.20 rows=5000 width=16)
               Spawn on: All datanodes
               ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
         ->  Materialize  (cost=0.00..170.53 rows=5000 width=16)
               ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..162.20 rows=5000 width=16)
                     Spawn on: All datanodes
                     ->  Seq Scan on t2  (cost=0.00..24.67 rows=5000 width=16)
(11 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
-----------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=4.00..42220.27 rows=5000 width=32)
   Node/s: All datanodes
   ->  Nested Loop Left Join  (cost=0.00..42011.90 rows=5000 width=32)
         Join Filter: (t1.b = t2.b)
         ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..162.20 rows=5000 width=16)
               Spawn on: All datanodes
               ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
         ->  Materialize  (cost=0.00..170.53 rows=5000 width=16)
               ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..162.20 rows=5000 width=16)
                     Spawn on: All datanodes
                     ->  Seq Scan on t2  (cost=0.00..24.67 rows=5000 width=16)
(11 rows)

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

    gaussdb=# SET enable_nestloop=off;
SET
gaussdb=# SET enable_hashjoin=on;
SET
gaussdb=# SET enable_mergejoin=off;
SET
gaussdb=# EXPLAIN SELECT /*+leading((t2 t1))*/* FROM t1 LEFT JOIN t2 ON t1.b = t2.b;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=187.03..576.53 rows=5000 width=32)
   Node/s: All datanodes
   ->  Hash Right Join  (cost=183.03..368.15 rows=5000 width=32)
         Hash Cond: (t2.b = t1.b)
         ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..162.20 rows=5000 width=16)
               Spawn on: All datanodes
               ->  Seq Scan on t2  (cost=0.00..24.67 rows=5000 width=16)
         ->  Hash  (cost=162.20..162.20 rows=5001 width=16)
               ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..162.20 rows=5000 width=16)
                     Spawn on: All datanodes
                     ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
(11 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”章节。

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

    以下面的SQL场景为例：

    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, 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);
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, 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
---------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=4.00..1166996.01 rows=24995000 width=32)
   Node/s: All datanodes
   ->  Nested Loop Left Join  (cost=0.00..125537.63 rows=24995000 width=32)
         Join Filter: (t1.b <> t2.b)
         ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
         ->  Materialize  (cost=0.00..500.46 rows=15000 width=16)
               ->  Streaming(type: BROADCAST)  (cost=0.00..475.46 rows=15000 width=16)
                     Spawn on: All datanodes
                     ->  Seq Scan on t2  (cost=0.00..24.67 rows=5000 width=16)
(9 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
---------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=4.00..1166996.01 rows=24995000 width=32)
   Node/s: All datanodes
   ->  Nested Loop Left Join  (cost=0.00..125537.63 rows=24995000 width=32)
         Join Filter: (t1.b <> t2.b)
         ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
         ->  Materialize  (cost=0.00..500.46 rows=15000 width=16)
               ->  Streaming(type: BROADCAST)  (cost=0.00..475.46 rows=15000 width=16)
                     Spawn on: All datanodes
                     ->  Seq Scan on t2  (cost=0.00..24.67 rows=5000 width=16)
(9 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
---------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=4.00..1166996.01 rows=24995000 width=32)
   Node/s: All datanodes
   ->  Nested Loop Left Join  (cost=0.00..125537.63 rows=24995000 width=32)
         Join Filter: (t1.b <> t2.b)
         ->  Seq Scan on t1  (cost=0.00..24.67 rows=5000 width=16)
         ->  Materialize  (cost=0.00..500.46 rows=15000 width=16)
               ->  Streaming(type: BROADCAST)  (cost=0.00..475.46 rows=15000 width=16)
                     Spawn on: All datanodes
                     ->  Seq Scan on t2  (cost=0.00..24.67 rows=5000 width=16)
(9 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优化器

相关文档