测试数据库版本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 要注意观察最终返回的数据量,返回数据量少性能较高,返回数据量大,走全表扫描