案例：使用全局二级索引加速查询

全局二级索引仅索引扫描

在对基表非分布列进行查询时，因无法进行DN下推查询或利用索引扫描，产生性能问题。

场景一：普通查询。

      
       
         
         
           gaussdb=# CREATE TABLE t (a INT, b INT, c INT) DISTRIBUTE BY HASH(a);
CREATE TABLE
gaussdb=# INSERT INTO t VALUES(generate_series(1,1000), generate_series(1,1000), generate_series(1,1000));
INSERT 0 1000
gaussdb=# CREATE INDEX i ON t(b);
CREATE INDEX
gaussdb=# SET MAX_DATANODE_FOR_PLAN = 1;
SET
gaussdb=# SET ENABLE_FAST_QUERY_SHIPPING = TRUE;
SET
gaussdb=# SET ENABLE_LIGHT_PROXY = TRUE;
SET
gaussdb=# SET ENABLE_STREAM_OPERATOR = FALSE;
SET
gaussdb=# -- 点查，查询条件不是分布列，下推所有DN，在DN上使用普通索引进行查询
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF)  SELECT b FROM t WHERE b = 1;
                     QUERY PLAN                     
----------------------------------------------------
 Data Node Scan
   Output: t.b
   Node/s: All datanodes
   Remote query: SELECT b FROM public.t WHERE b = 1

 Remote SQL: SELECT b FROM public.t WHERE b = 1
 Datanode Name: datanode1
   [Bypass]
   Index Only Scan using i on public.t
     Output: b
     Index Cond: (t.b = 1)

(12 rows)

gaussdb=# -- 范围查询，在所有DN进行顺序扫描
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF)  SELECT b FROM t WHERE b <= 10;
                      QUERY PLAN                      
------------------------------------------------------
 Data Node Scan
   Output: t.b
   Node/s: All datanodes
   Remote query: SELECT b FROM public.t WHERE b <= 10

 Remote SQL: SELECT b FROM public.t WHERE b <= 10
 Datanode Name: datanode1
   Seq Scan on public.t
     Output: b
     Filter: (t.b <= 10)

(11 rows)

          

        

      
     

通过创建全局二级索引，使上述查询进行DN下推，利用索引扫描加速查询。（使用GSI进行查询优化，需要开启ENABLE_GSISCAN参数以及ENABLE_FAST_QUERY_SHIPPING参数）

gaussdb=# DROP INDEX i;
DROP INDEX
gaussdb=# -- 创建全局二级索引
gaussdb=# CREATE GLOBAL INDEX gsi_t ON t(b);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'b' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE GLOBAL INDEX
gaussdb=# -- 点查，查询条件落到全局二级索引分布列上，下推单个DN，并使用索引扫描查询
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT b FROM t WHERE b = 1;
                         QUERY PLAN                          
-------------------------------------------------------------
 Data Node Scan
   Output: gsi_t.b
   Node/s: datanode1
   Remote query: SELECT b FROM ONLY public.gsi_t WHERE b = 1

 Remote SQL: SELECT b FROM ONLY public.gsi_t WHERE b = 1
 Datanode Name: datanode1
   [Bypass]
   Index Only Scan using gsi_t on public.gsi_t
     Output: b
     Index Cond: (gsi_t.b = 1)

(12 rows)

gaussdb=# -- 范围查询，下推所有DN，并使用索引扫描查询
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT b FROM t WHERE b <= 10;
                          QUERY PLAN                           
---------------------------------------------------------------
 Data Node Scan
   Output: gsi_t.b
   Node/s: All datanodes
   Remote query: SELECT b FROM ONLY public.gsi_t WHERE b <= 10

 Remote SQL: SELECT b FROM ONLY public.gsi_t WHERE b <= 10
 Datanode Name: datanode1
   [Bypass]
   Index Only Scan using gsi_t on public.gsi_t
     Output: b
     Index Cond: (gsi_t.b <= 10)

(12 rows)

场景二：多表关联查询。

gaussdb=# DCREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT) DISTRIBUTE BY HASH(c1);
CREATE TABLE
gaussdb=# DINSERT INTO t1 VALUES (GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10));
INSERT 0 10
gaussdb=# DCREATE TABLE t2(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT) DISTRIBUTE BY HASH(c1);
CREATE TABLE
gaussdb=# DINSERT INTO t2 VALUES (3, GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10));
INSERT 0 10
gaussdb=# -- 创建普通索引
gaussdb=# CREATE INDEX i_t1 ON t1(c2);
CREATE INDEX
gaussdb=# -- 两表JOIN，查询使用STREAM计划，未下推
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT t1.c2 FROM t1, t2 WHERE t1.c2 = t2.c1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Streaming (type: GATHER)
   Output: t1.c2
   Node/s: All datanodes
   ->  Hash Join
         Output: t1.c2
         Hash Cond: (t2.c1 = t1.c2)
         ->  Seq Scan on public.t2
               Output: t2.c1, t2.c2, t2.c3, t2.c4, t2.c5
               Distribute Key: t2.c1
         ->  Hash
               Output: t1.c2
               ->  Streaming(type: REDISTRIBUTE)
                     Output: t1.c2
                     Distribute Key: t1.c2
                     Spawn on: All datanodes
                     Consumer Nodes: All datanodes
                     ->  Index Only Scan using i_t1 on public.t1
                           Output: t1.c2
                           Distribute Key: t1.c1
(19 rows)

通过在关联列上创建全局二级索引，使查询下推到某个数据节点。

gaussdb=# -- 创建GSI
gaussdb=# CREATE GLOBAL INDEX gsi_t1 ON t1(c2);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c2' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE GLOBAL INDEX
gaussdb=# -- 两表JOIN，使用GSI下推所有DN
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT t1.c2 FROM t1, t2 WHERE t1.c2 = t2.c1;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Data Node Scan
   Output: gsi_t1.c2
   Node/s: All datanodes
   Remote query: SELECT gsi_t1.c2 FROM ONLY public.gsi_t1, public.t2 WHERE gsi_t1.c2 = t2.c1

 Remote SQL: SELECT gsi_t1.c2 FROM ONLY public.gsi_t1, public.t2 WHERE gsi_t1.c2 = t2.c1
 Datanode Name: datanode1
   Hash Join
     Output: gsi_t1.c2
     Hash Cond: (t2.c1 = gsi_t1.c2)
     ->  Seq Scan on public.t2
           Output: t2.c1, t2.c2, t2.c3, t2.c4, t2.c5
     ->  Hash
           Output: gsi_t1.c2
           ->  Index Only Scan using gsi_t1 on public.gsi_t1
                 Output: gsi_t1.c2

(17 rows)

场景三：子查询。

gaussdb=# CREATE TABLE t3(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT) DISTRIBUTE BY HASH(c1);
CREATE TABLE
gaussdb=# INSERT INTO t3 VALUES (GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10));
INSERT 0 10
gaussdb=# CREATE TABLE t4(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT) DISTRIBUTE BY HASH(c1);
CREATE TABLE
gaussdb=# INSERT INTO t4 VALUES (3, GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10), GENERATE_SERIES(1,10));
INSERT 0 10
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT t4.c2 FROM t4, (SELECT c2 FROM t3) aa WHERE aa.c2 = t4.c1;
                 QUERY PLAN                  
---------------------------------------------
 Streaming (type: GATHER)
   Output: t4.c2
   Node/s: All datanodes
   ->  Hash Join
         Output: t4.c2
         Hash Cond: (t3.c2 = t4.c1)
         ->  Streaming(type: REDISTRIBUTE)
               Output: t3.c2
               Distribute Key: t3.c2
               Spawn on: All datanodes
               Consumer Nodes: All datanodes
               ->  Seq Scan on public.t3
                     Output: t3.c2
                     Distribute Key: t3.c1
         ->  Hash
               Output: t4.c2, t4.c1
               ->  Seq Scan on public.t4
                     Output: t4.c2, t4.c1
                     Distribute Key: t4.c1
(19 rows)

通过创建全局二级索引，使查询下推数据节点。此时，查询可能需要增加GSI force hint来使用全局二级索引。

gaussdb=# CREATE GLOBAL INDEX gsi_t3 ON t3(c2) CONTAINING(c3, c4) DISTRIBUTE BY HASH(c2);
CREATE GLOBAL INDEX
gaussdb=# -- 使用GSI下推所有DN，需要使用GSI force hint
gaussdb=# -- 不适用GSI force hint，无法下推
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT t4.c2 FROM t4, (SELECT c2 FROM t3) aa WHERE aa.c2 = t4.c1;
                         QUERY PLAN                          
-------------------------------------------------------------
 Streaming (type: GATHER)
   Output: t4.c2
   Node/s: All datanodes
   ->  Hash Join
         Output: t4.c2
         Hash Cond: (t4.c1 = t3.c2)
         ->  Seq Scan on public.t4
               Output: t4.c1, t4.c2, t4.c3, t4.c4, t4.c5
               Distribute Key: t4.c1
         ->  Hash
               Output: t3.c2
               ->  Index Only Scan using gsi_t3 on public.t3
                     Output: t3.c2
                     Distribute Key: t3.c2
(14 rows)

gaussdb=# -- 使用GSI force hint，下推所有DN
gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT /*+gsi()*/ t4.c2 FROM t4, (SELECT c2 FROM t3) aa WHERE aa.c2 = t4.c1;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Data Node Scan
   Output: t4.c2
   Node/s: All datanodes
   Remote query: SELECT/*+ gsi()*/ t4.c2 FROM public.t4, (SELECT gsi_t3.c2 FROM ONLY public.gsi_t3) aa WHERE aa.c2 = t4.c1

 Remote SQL: SELECT/*+ gsi()*/ t4.c2 FROM public.t4, (SELECT gsi_t3.c2 FROM ONLY public.gsi_t3) aa WHERE aa.c2 = t4.c1
 Datanode Name: datanode1
   Hash Join
     Output: t4.c2
     Hash Cond: (t4.c1 = gsi_t3.c2)
     ->  Seq Scan on public.t4
           Output: t4.c1, t4.c2, t4.c3, t4.c4, t4.c5
     ->  Hash
           Output: gsi_t3.c2
           ->  Index Only Scan using gsi_t3 on public.gsi_t3
                 Output: gsi_t3.c2

(17 rows)

全局二级索引回表扫描

STREAM分布式计划：创建全局二级索引的基表，查询时需要走全局二级索引回表的场景。如果谓词命中的行数比较多，比如范围查询，优先走STREAM分布式计划。示例如下。

gaussdb=#  -- 开启STEAM模式
gaussdb=#  SET ENABLE_STREAM_OPERATOR = ON;
SET
gaussdb=#  SET EXPLAIN_PERF_MODE=PRETTY;
SET
gaussdb=#  CREATE TABLE test(c1 INT, c2 INT, c3 INT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# -- 为test表在c2列上创建全局二级索引
gaussdb=#  CREATE GLOBAL INDEX gsi_test ON test(c2);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c2' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE GLOBAL INDEX
gaussdb=#  -- 走全局二级索引STREAM分布式计划
gaussdb=#  EXPLAIN(VERBOSE ON, COSTS OFF) SELECT /*+ gsitable(test gsi_test)*/ * FROM test WHERE c2 > 1;
WARNING:  Statistics in some tables or columns(public.test.c1, public.test.c2) are not collected.
HINT:  Do analyze for them in order to generate optimized plan.
 id |                        operation                         
----+----------------------------------------------------------
  1 | ->  Streaming (type: GATHER)
  2 |    ->  GSI Tid Scan on public.test
  3 |       ->  Streaming(type: REDISTRIBUTE GSI)
  4 |          ->  GSI Only Scan using gsi_test on public.test
(4 rows)
   Predicate Information (identified by plan id)   
---------------------------------------------------
   4 --GSI Only Scan using gsi_test on public.test
         Index Cond: (test.c2 > 1)
(2 rows)
  Targetlist Information (identified by plan id)   
---------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: c1, c2, c3
         Node/s: All datanodes
   2 --GSI Tid Scan on public.test
         Output: c1, c2, c3
         Distribute Key: c1
   3 --Streaming(type: REDISTRIBUTE GSI)
         Output: xc_node_hash, ctid
         Spawn on: All datanodes
         Consumer Nodes: All datanodes
   4 --GSI Only Scan using gsi_test on public.test
         Output: xc_node_hash, ctid
         Distribute Key: c2
(13 rows)

非STREAM分布式计划：创建全局二级索引的基表，查询时需要走全局二级索引回表的场景。如果谓词命中的行数比较少，比如点查，优先走非STREAM分布式计划。示例如下。

gaussdb=#  -- 关闭STEAM模式
gaussdb=#  SET ENABLE_STREAM_OPERATOR = OFF;
SET
gaussdb=#  CREATE TABLE test(c1 INT, c2 INT, c3 INT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# -- 为test表在c2列上创建全局二级索引
gaussdb=#  CREATE GLOBAL INDEX gsi_test ON test(c2);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c2' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE GLOBAL INDEX
gaussdb=#  -- 走全局二级索引非STREAM分布式计划
gaussdb=#  EXPLAIN(VERBOSE ON, COSTS OFF) SELECT /*+ gsitable(test gsi_test)*/ * FROM test WHERE c2 = 1;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Select on public.test
   Output: test.c1, test.c2, test.c3
   Node/s: All datanodes
   Remote query: SELECT c1, c2, c3 FROM ONLY public.test WHERE ctid = $2
   ->  Data Node Scan on gsi_test "REMOTE_TABLE_QUERY"
         Output: gsi_test.xc_node_hash, gsi_test.ctid
         Node/s: datanode1
         Remote query: SELECT xc_node_hash, ctid FROM public.gsi_test WHERE c2 = 1
(8 rows)

父主题： 实际调优案例

上一篇：案例：使用DN Gather减少计划中的Stream节点

下一篇：SQL参考

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！您可在我的云声建议查看反馈及问题处理状态。

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消

如您有其它疑问，您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问

案例：使用全局二级索引加速查询

全局二级索引仅索引扫描

全局二级索引回表扫描

相关文档

意见反馈

文档内容是否对您有帮助？

7*24

备案

专业服务

退订

建议反馈

售前咨询热线