列举GaussDB(DWS)常见的查询时索引失效场景

转x33g5p2x  于2022-04-26 转载在 其他  
字(7.7k)|赞(0)|评价(0)|浏览(742)

本文分享自华为云社区《GaussDB(DWS)之查询时索引失效原因》，作者： 飞不起来的小松鼠 。

使用GaussDB(DWS)时，有时为了加快查询速度，需要对表建立索引。有时我们会遇到明明建立了索引，查询计划中却发现索引没有被使用的情况。本文将列举几种常见的场景和优化方法。

1. 返回结果集很大

  以行存表的Seq Scan和Index Scan为例：

  Seq Scan：就是按照表的记录的排列顺序从头到尾依次检索扫描，每次扫描要取到所有的记录。这也是最简单最基础的扫表方式，扫描的代价比较大；

  Index Scan：对于给定的查询，先扫描一遍索引，从索引中找到符合要求的记录的位置(指针)，再定位到表中具体的Page去取，即先走索引，再读表数据；

  因此，根据两种扫描方式的特点可以看出，大多数情况下，Index Scan要比Seq Scan快。但是如果获取的结果集占所有数据的比重很大时（超过70%），这时Index Scan 因为要先扫描索引再读表数据反而不如直接全表扫描来的快。

2. 没有analyze

  analyze会更新表的统计信息，如果表未做analyze或上次做完analyze之后表进行过数据量较大的增删操作，会导致统计信息不准，这时候也可能导致表没有走索引。

  优化方法：对表进行analyze更新统计信息即可。

3. 过滤条件使用了函数或隐式类型转化导致没有走索引

  如果再过滤条件中使用了计算、函数、隐式类型转化，都可能导致无法选择索引。

示例：create table test(a int, b text, c date); 且在a，b，c三列上都分别创建了索引。

  场景1：使用计算

**    **从下面的执行结果可以看到，where a = 101，where a = 102 - 1都能使用a列上的索引，但是where a + 1 = 102没有走索引。

  1. postgres=# explain verbose select * from test where a + 1 = 102;
  2.                             QUERY PLAN                             
  3. -------------------------------------------------------------------
  4.  Streaming (type: GATHER)  (cost=0.19..18.25 rows=6 width=14)
  5.    Output: a, b, c
  6.    Node/s: All datanodes
  7.    ->  Seq Scan on public.test  (cost=0.00..12.25 rows=6 width=14)
  8.          Output: a, b, c
  9.          Distribute Key: a
  10.          Filter: ((test.a + 1) = 102)
  11. (7 rows)
  13. postgres=# 
  14. postgres=# explain verbose select * from test where a  = 101;
  15.                                      QUERY PLAN                                      
  16. -------------------------------------------------------------------------------------
  17.  Streaming (type: GATHER)  (cost=0.06..14.27 rows=1 width=14)
  18.    Output: a, b, c
  19.    Node/s: datanode1
  20.    ->  Index Scan using test_a_idx on public.test  (cost=0.00..8.27 rows=1 width=14)
  21.          Output: a, b, c
  22.          Distribute Key: a
  23.          Index Cond: (test.a = 101)
  24. (7 rows)
  26. postgres=# 
  27. postgres=# explain verbose select * from test where a  = 102 - 1;
  28.                                      QUERY PLAN                                      
  29. -------------------------------------------------------------------------------------
  30.  Streaming (type: GATHER)  (cost=0.06..14.27 rows=1 width=14)
  31.    Output: a, b, c
  32.    Node/s: datanode1
  33.    ->  Index Scan using test_a_idx on public.test  (cost=0.00..8.27 rows=1 width=14)
  34.          Output: a, b, c
  35.          Distribute Key: a
  36.          Index Cond: (test.a = 101)
  37. (7 rows)

  优化方式：此类场景的优化方式是尽量使用常量代替表达式，或者常量计算尽量写在等号的右侧。

  场景2：使用函数

**    **从下面的执行结果可以看到，在索引列上使用函数也会导致无法选择索引：

  1. postgres=# explain verbose select * from test where to_char(c, 'yyyyMMdd') = to_char(CURRENT_DATE,'yyyyMMdd');
  2.                                                            QUERY PLAN                                                            
  3. ---------------------------------------------------------------------------------------------------------------------------------
  4.  Streaming (type: GATHER)  (cost=0.19..21.00 rows=6 width=14)
  5.    Output: a, b, c
  6.    Node/s: All datanodes
  7.    ->  Seq Scan on public.test  (cost=0.00..15.00 rows=6 width=14)
  8.          Output: a, b, c
  9.          Distribute Key: a
  10.          Filter: (to_char(test.c, 'yyyyMMdd'::text) = to_char(('2021-03-16'::date)::timestamp with time zone, 'yyyyMMdd'::text))
  11. (7 rows)
  13. postgres=# 
  14. postgres=# explain verbose select * from test where c = current_date;
  15.                                      QUERY PLAN                                      
  16. -------------------------------------------------------------------------------------
  17.  Streaming (type: GATHER)  (cost=0.06..14.27 rows=1 width=14)
  18.    Output: a, b, c
  19.    Node/s: All datanodes
  20.    ->  Index Scan using test_c_idx on public.test  (cost=0.00..8.27 rows=1 width=14)
  21.          Output: a, b, c
  22.          Distribute Key: a
  23.          Index Cond: (test.c = '2021-03-16'::date)
  24. (7 rows)

  优化方法：尽量减少索引列上没有必要的函数调用。

**    场景3：隐式类型转化**

**    **此类场景是经常遇到的场景，例如b的类型是text类型，过滤条件是where b = 2，在生成计划时，text类型会隐式转化为bigint类型，实际的过滤条件变成where b::bigint = 2，导致b列上的索引失效：

  1. postgres=# explain verbose select * from test where b = 2;
  2.                             QUERY PLAN                             
  3. -------------------------------------------------------------------
  4.  Streaming (type: GATHER)  (cost=0.06..18.25 rows=1 width=14)
  5.    Output: a, b, c
  6.    Node/s: All datanodes
  7.    ->  Seq Scan on public.test  (cost=0.00..12.25 rows=1 width=14)
  8.          Output: a, b, c
  9.          Distribute Key: a
  10.          Filter: ((test.b)::bigint = 2)
  11. (7 rows)
  13. postgres=# 
  14. postgres=# 
  15. postgres=# explain verbose select * from test where b = '2';
  16.                                      QUERY PLAN                                      
  17. -------------------------------------------------------------------------------------
  18.  Streaming (type: GATHER)  (cost=0.06..14.27 rows=1 width=14)
  19.    Output: a, b, c
  20.    Node/s: All datanodes
  21.    ->  Index Scan using test_b_idx on public.test  (cost=0.00..8.27 rows=1 width=14)
  22.          Output: a, b, c
  23.          Distribute Key: a
  24.          Index Cond: (test.b = '2'::text)
  25. (7 rows)
  27. postgres=#

  优化方法：索引条件上的常量尽可能使用和索引列相同类型的常量，避免发生隐式类型转化。

4. 使用nestloop + indexscan 代替 hashjoin

  此类语句的特征是两个表关联的时候，其中一个表上where条件过滤之后的结果集行数很小，同时，最终满足条件的结果集行数也很小。此时，使用nestloop+indexscan的效果往往要由于hashjoin。较优的执行计划如下：

  可以看到，第5层的Index Cond: (t1.b = t2.b)已经把join条件下推到了基表扫描上。

  1. postgres=# explain verbose select t1.a,t1.b from t1,t2 where t1.b=t2.b and t2.a=4;
  2.  id |                    operation                     | E-rows | E-distinct | E-memory | E-width | E-costs 
  3. ----+--------------------------------------------------+--------+------------+----------+---------+---------
  4.   1 | ->  Streaming (type: GATHER)                     |     26 |            |          |       8 | 17.97
  5.   2 |    ->  Nested Loop (3,5)                         |     26 |            | 1MB      |       8 | 11.97
  6.   3 |       ->  Streaming(type: BROADCAST)             |      2 |            | 2MB      |       4 | 2.78
  7.   4 |          ->  Seq Scan on public.t2               |      1 |            | 1MB      |       4 | 2.62
  8.   5 |       ->  Index Scan using t1_b_idx on public.t1 |     26 |            | 1MB      |       8 | 9.05
  9. (5 rows)
  11.  Predicate Information (identified by plan id) 
  12. -----------------------------------------------
  13.    4 --Seq Scan on public.t2
  14.          Filter: (t2.a = 4)
  15.    5 --Index Scan using t1_b_idx on public.t1
  16.          Index Cond: (t1.b = t2.b)
  17. (4 rows)
  19.  Targetlist Information (identified by plan id) 
  20. ------------------------------------------------
  21.    1 --Streaming (type: GATHER)
  22.          Output: t1.a, t1.b
  23.          Node/s: All datanodes
  24.    2 --Nested Loop (3,5)
  25.          Output: t1.a, t1.b
  26.    3 --Streaming(type: BROADCAST)
  27.          Output: t2.b
  28.          Spawn on: datanode2
  29.          Consumer Nodes: All datanodes
  30.    4 --Seq Scan on public.t2
  31.          Output: t2.b
  32.          Distribute Key: t2.a
  33.    5 --Index Scan using t1_b_idx on public.t1
  34.          Output: t1.a, t1.b
  35.          Distribute Key: t1.a
  36. (15 rows)
  38.    ====== Query Summary =====    
  39. ---------------------------------
  40.  System available mem: 9262694KB
  41.  Query Max mem: 9471590KB
  42.  Query estimated mem: 5144KB
  43. (3 rows)

  如果优化器没有选择这种执行计划，可以通过以下方式优化：

  set enable_index_nestloop = on;

  set enable_hashjoin = off;

  set enable_seqscan = off;

5. 使用hint指定索引时指定的索引方式不对

  GaussDB(DWS)的plan hint当前支持指定的Scan方式有三种：tablescan、indexscan和indexonlyscan。

  tablescan：全表扫描，比如行存表的Seq Scan，列存表的CStore Scan

  indexscan：先扫索引，再根据索引取表记录

  indexonlyscan：覆盖索引扫描，所需的返回结果能被所扫描的索引全部覆盖。与index scan相比，index only scan所包含的字段集合，囊括了我们查询语句中的字段，这样，提取出相应的index ，就不必再根据索引取表记录了。

  因此，对于需要indexonlyscan的场景，如果hint指定了indexscan，该hint是无法生效的：

  1. postgres=# explain verbose select/*+ indexscan(test)*/ b from test where b = '1';
  2. WARNING:  unused hint: IndexScan(test)
  3.                              QUERY PLAN                             
  4. --------------------------------------------------------------------
  5.  Streaming (type: GATHER)  (cost=3.12..16.88 rows=100 width=2)
  6.    Output: b
  7.    Node/s: All datanodes
  8.    ->  Seq Scan on public.test  (cost=0.00..10.88 rows=100 width=2)
  9.          Output: b
  10.          Distribute Key: a
  11.          Filter: (test.b = '1'::text)
  12. (7 rows)
  14. postgres=# 
  15. postgres=# explain verbose select/*+ indexonlyscan(test)*/ b from test where b = '1';
  16.                                          QUERY PLAN                                         
  17. --------------------------------------------------------------------------------------------
  18.  Streaming (type: GATHER)  (cost=3.12..56.51 rows=100 width=2)
  19.    Output: b
  20.    Node/s: All datanodes
  21.    ->  Index Only Scan using test_b_idx on public.test  (cost=0.00..50.51 rows=100 width=2)
  22.          Output: b
  23.          Distribute Key: a
  24.          Index Cond: (test.b = '1'::text)
  25. (7 rows)

  优化方法：使用hint时正确指定indexscan和indexonlyscan。

6. 全文检索gin索引

  为了加速文本搜索，进行全文检索时可以创建GIN索引：

  1. create index idxb on test using gin(to_tsvector('english',b));

  创建索引时，必须使用to_tsvector的两参数版本，并且只有当查询时也使用了两参数版本，且参数值与索引中相同时，才会使用该索引：

  1. postgres=# explain verbose select  * from test where to_tsvector(b) @@ to_tsquery('cat') order by 1;
  2.                                 QUERY PLAN                                
  3. --------------------------------------------------------------------------
  4.  Streaming (type: GATHER)  (cost=22.23..27.87 rows=12 width=14)
  5.    Output: a, b, c
  6.    Merge Sort Key: test.a
  7.    Node/s: All datanodes
  8.    ->  Sort  (cost=21.86..21.87 rows=12 width=14)
  9.          Output: a, b, c
  10.          Sort Key: test.a
  11.          ->  Seq Scan on public.test  (cost=0.00..21.78 rows=11 width=14)
  12.                Output: a, b, c
  13.                Distribute Key: a
  14.                Filter: (to_tsvector(test.b) @@ '''cat'''::tsquery)
  15. (11 rows)
  17. postgres=# 
  18. postgres=# explain verbose select  * from test where to_tsvector('english',b) @@ to_tsquery('cat') order by 1;
  19.                                             QUERY PLAN                                             
  20. ---------------------------------------------------------------------------------------------------
  21.  Streaming (type: GATHER)  (cost=16.09..22.03 rows=2 width=14)
  22.    Output: a, b, c
  23.    Merge Sort Key: test.a
  24.    Node/s: All datanodes
  25.    ->  Sort  (cost=16.03..16.03 rows=2 width=14)
  26.          Output: a, b, c
  27.          Sort Key: test.a
  28.          ->  Bitmap Heap Scan on public.test  (cost=12.00..16.02 rows=1 width=14)
  29.                Output: a, b, c
  30.                Distribute Key: a
  31.                Recheck Cond: (to_tsvector('english'::regconfig, test.b) @@ '''cat'''::tsquery)
  32.                ->  Bitmap Index Scan on idxb  (cost=0.00..12.00 rows=1 width=0)
  33.                      Index Cond: (to_tsvector('english'::regconfig, test.b) @@ '''cat'''::tsquery)
  34. (13 rows)

   优化方式：查询时也使用了两参数版本，且保证参数值与索引中相同。

点击关注，第一时间了解华为云新鲜技术~​

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://huaweicloud.blog.csdn.net/article/details/124405218

内容来源于网络，如有侵权，请联系作者删除！

索引技术交流gaussdb隐式类型转化GIN索引analyze

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com