MogDBopenGauss中的Bitmap Index Scan

测试数据库版本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 test01Table "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_defaultorcl=> \d test02Table "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: 9157Heap 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: 9962574Heap 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: 10Heap 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: 9971731Filter: ((owner)::text = 'SYS'::text)Rows Removed by Filter: 997376Heap 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: 16497594Heap 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: 17144302Heap 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: 24190976Total 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 要注意观察最终返回的数据量，返回数据量少性能较高，返回数据量大，走全表扫描