MogDB&openGauss中的Bitmap Index Scan

  • 开发
  • 10

测试数据库版本MogDB5.0.6

orcl=> select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 (MogDB 5.0.6 build 8b0a6ca8) compiled at 2024-03-27 11:05:29 commit 0 last mr 1804  on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

测试表test01，test02表结构和索引结构

orcl=> \d test01
                    Table "scott.test01"
     Column     |              Type              | Modifiers 
----------------+--------------------------------+-----------
 owner          | character varying(30)          | 
 object_name    | character varying(128)         | 
 subobject_name | character varying(30)          | 
 object_id      | numeric                        | 
 data_object_id | numeric                        | 
 object_type    | character varying(19)          | 
 created        | timestamp(0) without time zone | 
 last_ddl_time  | timestamp(0) without time zone | 
 timestamp      | character varying(19)          | 
 status         | character varying(7)           | 
 temporary      | character varying(1)           | 
 generated      | character varying(1)           | 
 secondary      | character varying(1)           | 
 namespace      | numeric                        | 
 edition_name   | character varying(30)          | 
Indexes:
    "idx_test01_name" btree (object_name) TABLESPACE pg_default
    "idx_test01_objectid" btree (object_id) TABLESPACE pg_default
    "idx_test01_objectype" btree (object_type) TABLESPACE pg_default
    "idx_test01_owner" btree (owner) TABLESPACE pg_default

orcl=> \d test02
                    Table "scott.test02"
     Column     |              Type              | Modifiers 
----------------+--------------------------------+-----------
 owner          | character varying(30)          | 
 object_name    | character varying(128)         | 
 subobject_name | character varying(30)          | 
 object_id      | numeric                        | 
 data_object_id | numeric                        | 
 object_type    | character varying(19)          | 
 created        | timestamp(0) without time zone | 
 last_ddl_time  | timestamp(0) without time zone | 
 timestamp      | character varying(19)          | 
 status         | character varying(7)           | 
 temporary      | character varying(1)           | 
 generated      | character varying(1)           | 
 secondary      | character varying(1)           | 
 namespace      | numeric                        | 
 edition_name   | character varying(30)          | 
Indexes:
    "idx_test02_objectype" btree (object_type) TABLESPACE pg_default
    "idx_test02_owner" btree (owner) TABLESPACE pg_default

数据量

orcl=> select count(*) from test01;
  count   
----------
 44537344
(1 row)

orcl=> select count(*) from test02;
 count 
-------
 86987
(1 row)

场景一：

where (col1,col2) in ((),(),())

orcl=> explain analyze select * from test01 where (object_id,owner) in ((10,'SYS'),(20,'SYS'),(30,'SYS'));
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test01  (cost=36.70..5969.07 rows=666 width=195) (actual time=0.480..3.367 rows=1536 loops=1)
   Recheck Cond: ((object_id = 10::numeric) OR (object_id = 20::numeric) OR (object_id = 30::numeric))
   Filter: ((owner)::text = 'SYS'::text)
   Heap Blocks: exact=1536
   ->  BitmapOr  (cost=36.70..36.70 rows=1527 width=0) (actual time=0.324..0.324 rows=0 loops=1)
         ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=509 width=0) (actual time=0.125..0.125 rows=512 loops=1)
               Index Cond: (object_id = 10::numeric)
         ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=509 width=0) (actual time=0.092..0.092 rows=512 loops=1)
               Index Cond: (object_id = 20::numeric)
         ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.07 rows=509 width=0) (actual time=0.106..0.106 rows=512 loops=1)
               Index Cond: (object_id = 30::numeric)
 Total runtime: 3.704 ms
(12 rows)

场景二：

a join b on a.col1=b.col1 or a.col2=b.col2

orcl=> explain analyze select * from test02 a join test01 b on a.object_id=b.object_id or a.object_name=b.object_name where a.data_object_id<100;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=10000000043.97..1000000936164.67 rows=179252 width=390) (actual time=0.212..108.800 rows=54784 loops=1)
   ->  Seq Scan on test02 a  (cost=10000000000.00..1000000261633.75 rows=121 width=195) (actual time=0.034..6.344 rows=107 loops=1)
         Filter: (data_object_id < 100::numeric)
         Rows Removed by Filter: 86880
   ->  Bitmap Heap Scan on test01 b  (cost=43.97..5560.26 rows=1438 width=195) (actual time=15.764..91.421 rows=54784 loops=107)
         Recheck Cond: ((a.object_id = object_id) OR ((a.object_name)::text = (object_name)::text))
         Heap Blocks: exact=54784
         ->  BitmapOr  (cost=43.97..43.97 rows=1438 width=0) (actual time=11.154..11.154 rows=0 loops=107)
               ->  Bitmap Index Scan on idx_test01_objectid  (cost=0.00..12.11 rows=514 width=0) (actual time=6.737..6.737 rows=54784 loops=107)
                     Index Cond: (a.object_id = object_id)
               ->  Bitmap Index Scan on idx_test01_name  (cost=0.00..31.15 rows=924 width=0) (actual time=4.319..4.319 rows=54784 loops=107)
                     Index Cond: ((a.object_name)::text = (object_name)::text)
 Total runtime: 111.192 ms
(13 rows)

场景三：

where  a.col1='xxx' and a.col2='xxxx'

orcl=> explain analyze select * from test01 where object_type='TABLE' and owner='PM';
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test01  (cost=32733.65..43652.98 rows=2853 width=195) (actual time=284.860..300.625 rows=1536 loops=1)
   Recheck Cond: (((owner)::text = 'PM'::text) AND ((object_type)::text = 'TABLE'::text))
   Rows Removed by Index Recheck: 9157
   Heap Blocks: exact=10354
   ->  BitmapAnd  (cost=32733.65..32733.65 rows=2853 width=0) (actual time=283.267..283.267 rows=0 loops=1)
         ->  Bitmap Index Scan on idx_test01_owner  (cost=0.00..1532.88 rows=82217 width=0) (actual time=3.688..3.688 rows=22528 loops=1)
               Index Cond: ((owner)::text = 'PM'::text)
         ->  Bitmap Index Scan on idx_test01_objectype  (cost=0.00..31199.10 rows=1545446 width=0) (actual time=270.632..270.632 rows=1524736 loops=1)
               Index Cond: ((object_type)::text = 'TABLE'::text)
 Total runtime: 301.094 ms
(10 rows)

场景四：

where a.col1='xxx' or a.col2='xxxx'

orcl=> explain analyze select * from test01 where object_type='TABLE' or owner='PM';
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test01  (cost=33544.38..1319775.96 rows=1624810 width=195) (actual time=309.248..2131.274 rows=1545728 loops=1)
   Recheck Cond: (((object_type)::text = 'TABLE'::text) OR ((owner)::text = 'PM'::text))
   Rows Removed by Index Recheck: 9962574
   Heap Blocks: exact=233378 lossy=185029
   ->  BitmapOr  (cost=33544.38..33544.38 rows=1627663 width=0) (actual time=246.804..246.804 rows=0 loops=1)
         ->  Bitmap Index Scan on idx_test01_objectype  (cost=0.00..31199.10 rows=1545446 width=0) (actual time=242.247..242.247 rows=1524736 loops=1)
               Index Cond: ((object_type)::text = 'TABLE'::text)
         ->  Bitmap Index Scan on idx_test01_owner  (cost=0.00..1532.88 rows=82217 width=0) (actual time=4.554..4.554 rows=22528 loops=1)
               Index Cond: ((owner)::text = 'PM'::text)
 Total runtime: 2198.976 ms
(10 rows)

场景一，场景二是很有用的SQL优化手段

场景三可以用组合索引代替BitmapAnd，组合索引性能更高

场景四具体情况具体分析，有时候全表扫描性能更好，有时候BitmapOr性能更好

Bitmap Index Scan会扫描完所有符合where过滤条件的数据生成位图信息，然后再回表查询数据

当SQL语句中有limit，且通过索引返回数据量很大时，走Bitmap Index Scan性能很差

举个例子：

where条件是owner='SYS'，返回数据量很大，再加上limit 10，限制只返回10行数据

禁止indexscan，禁止走全表扫描，这个时候只能走Bitmap Index Scan

orcl=> explain analyze select /*+ set(enable_indexscan off) no tablescan(test01) */ * from test01 where owner='SYS' limit 10;
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=366859.97..366860.62 rows=10 width=195) (actual time=1717.458..1717.464 rows=10 loops=1)
   ->  Bitmap Heap Scan on test01  (cost=366859.97..1625932.64 rows=19430158 width=195) (actual time=1717.456..1717.460 rows=10 loops=1)
         Recheck Cond: ((owner)::text = 'SYS'::text)
         Rows Removed by Index Recheck: 10
         Heap Blocks: lossy=1
         ->  Bitmap Index Scan on idx_test01_owner  (cost=0.00..362002.43 rows=19430158 width=0) (actual time=1655.260..1655.260 rows=19348992 loops=1)
               Index Cond: ((owner)::text = 'SYS'::text)
 Total runtime: 1720.485 ms
(8 rows)

强制走index scan

orcl=> explain analyze select /*+ indexscan(test01) */ * from test01 where owner='SYS' limit 10;
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..34.95 rows=10 width=195) (actual time=0.025..0.028 rows=10 loops=1)
   ->  Index Scan using idx_test01_owner on test01  (cost=0.00..67903941.68 rows=19430158 width=195) (actual time=0.024..0.026 rows=10 loops=1)
         Index Cond: ((owner)::text = 'SYS'::text)
 Total runtime: 0.203 ms
(4 rows)

 从上面的例子看到，当where条件有limit，不要走Bitmap Index Scan，limit无法刹车

参数force_bitmapand默认是off

orcl=> show force_bitmapand;
 force_bitmapand 
-----------------
 off
(1 row)

只有在性能比较好的时候才会走BitmapAnd

例如下面这个SQL走BitmapAnd性能很差，默认不会走BitmapAnd

orcl=> explain analyze select /*+ no tablescan(test01) */ * from test01 where object_type='TABLE' and owner='SYS';
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test01  (cost=31367.65..1318662.84 rows=674226 width=195) (actual time=312.484..1952.144 rows=527360 loops=1)
   Recheck Cond: ((object_type)::text = 'TABLE'::text)
   Rows Removed by Index Recheck: 9971731
   Filter: ((owner)::text = 'SYS'::text)
   Rows Removed by Filter: 997376
   Heap Blocks: exact=232995 lossy=185029
   ->  Bitmap Index Scan on idx_test01_objectype  (cost=0.00..31199.10 rows=1545446 width=0) (actual time=248.890..248.890 rows=1524736 loops=1)
         Index Cond: ((object_type)::text = 'TABLE'::text)
 Total runtime: 1978.530 ms
(9 rows)

想要强制走BitmapAnd，需要设置参数force_bitmapand为on

orcl=> explain analyze select /*+ no tablescan(test01) set(force_bitmapand on)  */ * from test01 where object_type='TABLE' and owner='SYS';
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test01  (cost=393538.89..1570552.75 rows=674226 width=195) (actual time=2224.240..4265.460 rows=527360 loops=1)
   Recheck Cond: (((owner)::text = 'SYS'::text) AND ((object_type)::text = 'TABLE'::text))
   Rows Removed by Index Recheck: 16497594
   Heap Blocks: exact=66723 lossy=293166
   ->  BitmapAnd  (cost=393538.89..393538.89 rows=674226 width=0) (actual time=2205.842..2205.842 rows=0 loops=1)
         ->  Bitmap Index Scan on idx_test01_owner  (cost=0.00..362002.43 rows=19430158 width=0) (actual time=1675.964..1675.964 rows=19348992 loops=1)
               Index Cond: ((owner)::text = 'SYS'::text)
         ->  Bitmap Index Scan on idx_test01_objectype  (cost=0.00..31199.10 rows=1545446 width=0) (actual time=397.919..397.919 rows=1524736 loops=1)
               Index Cond: ((object_type)::text = 'TABLE'::text)
 Total runtime: 4291.262 ms
(10 rows)

没有force_bitmapor参数，当where条件中有or，想要强制走BitmapOr，禁止全表扫描就行

orcl=> explain analyze select /*+ no tablescan(test01) */ * from test01 where object_type='TABLE' or owner='SYS';
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test01  (cost=403352.22..1763663.64 rows=20301378 width=195) (actual time=1872.535..7452.448 rows=20346368 loops=1)
   Recheck Cond: (((object_type)::text = 'TABLE'::text) OR ((owner)::text = 'SYS'::text))
   Rows Removed by Index Recheck: 17144302
   Heap Blocks: exact=230865 lossy=551600
   ->  BitmapOr  (cost=403352.22..403352.22 rows=20975604 width=0) (actual time=1809.041..1809.041 rows=0 loops=1)
         ->  Bitmap Index Scan on idx_test01_objectype  (cost=0.00..31199.10 rows=1545446 width=0) (actual time=248.296..248.296 rows=1524736 loops=1)
               Index Cond: ((object_type)::text = 'TABLE'::text)
         ->  Bitmap Index Scan on idx_test01_owner  (cost=0.00..362002.43 rows=19430158 width=0) (actual time=1560.741..1560.741 rows=19348992 loops=1)
               Index Cond: ((owner)::text = 'SYS'::text)
 Total runtime: 8269.155 ms
(10 rows)

只有在返回少量数据的情况下BitmapOr性能才高，返回的数据量大，全表扫描性能更高

orcl=> explain analyze select * from test01 where object_type='TABLE' or owner='SYS';
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on test01  (cost=0.00..1450525.16 rows=20301378 width=195) (actual time=0.022..5755.405 rows=20346368 loops=1)
   Filter: (((object_type)::text = 'TABLE'::text) OR ((owner)::text = 'SYS'::text))
   Rows Removed by Filter: 24190976
 Total runtime: 6556.389 ms
(4 rows)

总结：

1. where (col1,col2) in ((),(),()) 走Bitmap Index Scan 可能性能较高

2. a join b on a.col1=b.col1 or a.col2=b.col2 走Bitmap Index Scan 可能性能较高

3. BitmapAnd 建议用组合索引代替

4. BitmapOr 要注意观察最终返回的数据量，返回数据量少性能较高，返回数据量大，走全表扫描

阅读全部

相关推荐

  1. nlp中的transformer中的mask

    2024-05-05 04:28:03       43 阅读

  2. js中（）的作用

    2024-05-05 04:28:03       36 阅读

  9. Mongodb中的ObjectId

    2024-05-05 04:28:03       34 阅读

  10. Springboot中的RestTemplate

    2024-05-05 04:28:03       37 阅读

最近更新

  4. TCP协议是安全的吗?

    2024-05-05 04:28:03       18 阅读

  12. 阿里云服务器执行yum，一直下载docker-ce-stable失败

    2024-05-05 04:28:03       19 阅读

  13. 【Python教程】压缩PDF文件大小

    2024-05-05 04:28:03       18 阅读

  18. 通过文章id递归查询所有评论（xml）

    2024-05-05 04:28:03       20 阅读

热门阅读

  4. spingMVC一个controller最多可以同时响应多少请求

    2024-05-05 04:28:03       10 阅读

  5. STM32的外设总了解

    2024-05-05 04:28:03       10 阅读

  6. Spring从零开始学使用系列（四)--Spring框架中的Bean作用域：Singleton与Prototype详解

    2024-05-05 04:28:03       9 阅读

  10. 经典面试题：你觉得 Go 在什么时候会抢占 P？

    2024-05-05 04:28:03       11 阅读

  16. 论文复现４，Awe：Waypoint-Based Imitation Learning for Robotic Manipulation

    2024-05-05 04:28:03       12 阅读

  18. 【DevOps】GCP IAM 深入剖析：云端资源访问控制的守护者

    2024-05-05 04:28:03       16 阅读

  22. 1.Spring Security介绍

    2024-05-05 04:28:03       22 阅读

  23. Vue框架知识点表格总结

    2024-05-05 04:28:03       20 阅读

  24. 使用Spring Boot快速构建Spring应用

    2024-05-05 04:28:03       11 阅读

  26. linux定时运行脚本

    2024-05-05 04:28:03       13 阅读