写点什么

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

  • 2022 年 4 月 25 日
  • 本文字数:5807 字

    阅读完需:约 19 分钟

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


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

1. 返回结果集很大

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

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

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


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

没有 analyze

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


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

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

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


示例: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 没有走索引。

postgres=# explain verbose select * from test where a + 1 = 102;                            QUERY PLAN                             ------------------------------------------------------------------- Streaming (type: GATHER)  (cost=0.19..18.25 rows=6 width=14)   Output: a, b, c   Node/s: All datanodes   ->  Seq Scan on public.test  (cost=0.00..12.25 rows=6 width=14)         Output: a, b, c         Distribute Key: a         Filter: ((test.a + 1) = 102)(7 rows)
postgres=# postgres=# explain verbose select * from test where a = 101; QUERY PLAN ------------------------------------------------------------------------------------- Streaming (type: GATHER) (cost=0.06..14.27 rows=1 width=14) Output: a, b, c Node/s: datanode1 -> Index Scan using test_a_idx on public.test (cost=0.00..8.27 rows=1 width=14) Output: a, b, c Distribute Key: a Index Cond: (test.a = 101)(7 rows)
postgres=# postgres=# explain verbose select * from test where a = 102 - 1; QUERY PLAN ------------------------------------------------------------------------------------- Streaming (type: GATHER) (cost=0.06..14.27 rows=1 width=14) Output: a, b, c Node/s: datanode1 -> Index Scan using test_a_idx on public.test (cost=0.00..8.27 rows=1 width=14) Output: a, b, c Distribute Key: a Index Cond: (test.a = 101)(7 rows)
复制代码

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


场景 2:使用函数


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

postgres=# explain verbose select * from test where to_char(c, 'yyyyMMdd') = to_char(CURRENT_DATE,'yyyyMMdd');                                                           QUERY PLAN                                                            --------------------------------------------------------------------------------------------------------------------------------- Streaming (type: GATHER)  (cost=0.19..21.00 rows=6 width=14)   Output: a, b, c   Node/s: All datanodes   ->  Seq Scan on public.test  (cost=0.00..15.00 rows=6 width=14)         Output: a, b, c         Distribute Key: a         Filter: (to_char(test.c, 'yyyyMMdd'::text) = to_char(('2021-03-16'::date)::timestamp with time zone, 'yyyyMMdd'::text))(7 rows)
postgres=# postgres=# explain verbose select * from test where c = current_date; QUERY PLAN ------------------------------------------------------------------------------------- Streaming (type: GATHER) (cost=0.06..14.27 rows=1 width=14) Output: a, b, c Node/s: All datanodes -> Index Scan using test_c_idx on public.test (cost=0.00..8.27 rows=1 width=14) Output: a, b, c Distribute Key: a Index Cond: (test.c = '2021-03-16'::date)(7 rows)
复制代码

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


场景 3:隐式类型转化    


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

postgres=# explain verbose select * from test where b = 2;                            QUERY PLAN                             ------------------------------------------------------------------- Streaming (type: GATHER)  (cost=0.06..18.25 rows=1 width=14)   Output: a, b, c   Node/s: All datanodes   ->  Seq Scan on public.test  (cost=0.00..12.25 rows=1 width=14)         Output: a, b, c         Distribute Key: a         Filter: ((test.b)::bigint = 2)(7 rows)
postgres=# postgres=# postgres=# explain verbose select * from test where b = '2'; QUERY PLAN ------------------------------------------------------------------------------------- Streaming (type: GATHER) (cost=0.06..14.27 rows=1 width=14) Output: a, b, c Node/s: All datanodes -> Index Scan using test_b_idx on public.test (cost=0.00..8.27 rows=1 width=14) Output: a, b, c Distribute Key: a Index Cond: (test.b = '2'::text)(7 rows)
postgres=#
复制代码

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


4. 使用 nestloop + indexscan 代替 hashjoin


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


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

postgres=# explain verbose select t1.a,t1.b from t1,t2 where t1.b=t2.b and t2.a=4; id |                    operation                     | E-rows | E-distinct | E-memory | E-width | E-costs ----+--------------------------------------------------+--------+------------+----------+---------+---------  1 | ->  Streaming (type: GATHER)                     |     26 |            |          |       8 | 17.97  2 |    ->  Nested Loop (3,5)                         |     26 |            | 1MB      |       8 | 11.97  3 |       ->  Streaming(type: BROADCAST)             |      2 |            | 2MB      |       4 | 2.78  4 |          ->  Seq Scan on public.t2               |      1 |            | 1MB      |       4 | 2.62  5 |       ->  Index Scan using t1_b_idx on public.t1 |     26 |            | 1MB      |       8 | 9.05(5 rows)
Predicate Information (identified by plan id) ----------------------------------------------- 4 --Seq Scan on public.t2 Filter: (t2.a = 4) 5 --Index Scan using t1_b_idx on public.t1 Index Cond: (t1.b = t2.b)(4 rows)
Targetlist Information (identified by plan id) ------------------------------------------------ 1 --Streaming (type: GATHER) Output: t1.a, t1.b Node/s: All datanodes 2 --Nested Loop (3,5) Output: t1.a, t1.b 3 --Streaming(type: BROADCAST) Output: t2.b Spawn on: datanode2 Consumer Nodes: All datanodes 4 --Seq Scan on public.t2 Output: t2.b Distribute Key: t2.a 5 --Index Scan using t1_b_idx on public.t1 Output: t1.a, t1.b Distribute Key: t1.a(15 rows)
====== Query Summary ===== --------------------------------- System available mem: 9262694KB Query Max mem: 9471590KB Query estimated mem: 5144KB(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 是无法生效的:

postgres=# explain verbose select/*+ indexscan(test)*/ b from test where b = '1';WARNING:  unused hint: IndexScan(test)                             QUERY PLAN                             -------------------------------------------------------------------- Streaming (type: GATHER)  (cost=3.12..16.88 rows=100 width=2)   Output: b   Node/s: All datanodes   ->  Seq Scan on public.test  (cost=0.00..10.88 rows=100 width=2)         Output: b         Distribute Key: a         Filter: (test.b = '1'::text)(7 rows)
postgres=# postgres=# explain verbose select/*+ indexonlyscan(test)*/ b from test where b = '1'; QUERY PLAN -------------------------------------------------------------------------------------------- Streaming (type: GATHER) (cost=3.12..56.51 rows=100 width=2) Output: b Node/s: All datanodes -> Index Only Scan using test_b_idx on public.test (cost=0.00..50.51 rows=100 width=2) Output: b Distribute Key: a Index Cond: (test.b = '1'::text)(7 rows)
复制代码

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


6. 全文检索 gin 索引


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

create index idxb on test using gin(to_tsvector('english',b));
复制代码

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

postgres=# explain verbose select  * from test where to_tsvector(b) @@ to_tsquery('cat') order by 1;                                QUERY PLAN                                -------------------------------------------------------------------------- Streaming (type: GATHER)  (cost=22.23..27.87 rows=12 width=14)   Output: a, b, c   Merge Sort Key: test.a   Node/s: All datanodes   ->  Sort  (cost=21.86..21.87 rows=12 width=14)         Output: a, b, c         Sort Key: test.a         ->  Seq Scan on public.test  (cost=0.00..21.78 rows=11 width=14)               Output: a, b, c               Distribute Key: a               Filter: (to_tsvector(test.b) @@ '''cat'''::tsquery)(11 rows)
postgres=# postgres=# explain verbose select * from test where to_tsvector('english',b) @@ to_tsquery('cat') order by 1; QUERY PLAN --------------------------------------------------------------------------------------------------- Streaming (type: GATHER) (cost=16.09..22.03 rows=2 width=14) Output: a, b, c Merge Sort Key: test.a Node/s: All datanodes -> Sort (cost=16.03..16.03 rows=2 width=14) Output: a, b, c Sort Key: test.a -> Bitmap Heap Scan on public.test (cost=12.00..16.02 rows=1 width=14) Output: a, b, c Distribute Key: a Recheck Cond: (to_tsvector('english'::regconfig, test.b) @@ '''cat'''::tsquery) -> Bitmap Index Scan on idxb (cost=0.00..12.00 rows=1 width=0) Index Cond: (to_tsvector('english'::regconfig, test.b) @@ '''cat'''::tsquery)(13 rows)
复制代码

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


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

发布于: 刚刚阅读数: 3
用户头像

提供全面深入的云计算技术干货 2020.07.14 加入

华为云开发者社区,提供全面深入的云计算前景分析、丰富的技术干货、程序样例,分享华为云前沿资讯动态,方便开发者快速成长与发展,欢迎提问、互动,多方位了解云计算! 传送门:https://bbs.huaweicloud.com/

评论

发布
暂无评论
列举GaussDB(DWS)常见的查询时索引失效场景_索引_华为云开发者社区_InfoQ写作社区