关键字：
Hint、索引、Index Scan、Index Only Scan、Bitmap Scan、人大金仓、KingbaseES
不同索引类型支持的扫描方式
set enable_hint = on;
set hint_debug_print = on;
set hint_message_level = log;
set client_min_messages =log;
(1) Btree索引和Hash索引
① 准备数据——建表和索引，其中：index_part_a01是Btree索引，index_part_a02是Hash索引
drop table if exists t_part_01; drop table if exists t_part_02; drop table if exists t_part; create table if not exists t_part ( a int, b CHAR(3), c TEXT) PARTITION BY RANGE(a); create table t_part_01 PARTITION OF t_part for values from (1) to (10); create table t_part_02 PARTITION OF t_part for values from (10) to (20); create index index_part_a01 on t_part USING btree(a); create index index_part_a02 on t_part USING hash(a); insert into t_part values(generate_series(1,19),'kb','abc'); |
表结构如图所示：
② Btree索引
explain analyze select /*+IndexScan(t_part index_part_a01)*/ a FROM t_part WHERE a>5; explain analyze select /*+IndexOnlyScan(t_part index_part_a01)*/ a FROM t_part WHERE a>5; explain analyze select /*+BitmapScan(t_part index_part_a01)*/ a FROM t_part WHERE a>5; |
Hint指定Index Scan
Hint指定Index Only Scan
Hint指定Bitmap Scan
【小结】Btree类型索引支持使用Hint指定Index Scan、Index Only Scan (当满足Index Only Scan的使用条件时，Hint指定的Index Only Scan会生效，否则将使用Index Scan) 和Bitmap Scan。
③ Hash索引
explain analyze select /*+IndexScan(t_part index_part_a02)*/ a FROM t_part WHERE a=5; explain analyze select /*+IndexOnlyScan(t_part index_part_a02)*/ a FROM t_part WHERE a=5; explain analyze select /*+BitmapScan(t_part index_part_a02)*/ a FROM t_part WHERE a=5; |
Hint指定Index Scan
Hint指定Index Only Scan
Hint指定Bitmap Scan
【小结】Hash类型索引支持使用Hint指定Index Scan、Index Only Scan (当满足Index Only Scan的使用条件时，Hint指定的Index Only Scan会生效，否则将使用Index Scan) 和Bitmap Scan。但需要注意，只有使用等值条件，执行计划才会使用指定的索引扫描。
如图所示，从代价分析，可以发现Hint是生效的，但由于where后面是非等值条件，所以执行计划还是全表扫描。
(2) GIN索引
drop extension sys_trgm CASCADE; drop index idx_gin; drop table if exists gin; create extension sys_trgm; create table if not exists gin(doc text); create index idx_gin ON gin USING GIN(doc gin_trgm_ops); insert into gin values('kingbase'); insert into gin values('aaaaaaaaaaaa'); insert into gin values('aaabcd'); insert into gin values('aaa1234'); insert into gin values('aaaaaabcdefg'); insert into gin values('abcdefghijk'); insert into gin select 'kingbase'||generate_series(1, 100); explain analyze select doc FROM gin WHERE doc like '%aaa%'; explain analyze select /*+IndexScan(gin)*/ doc FROM gin WHERE doc like '%aaa%'; explain analyze select /*+IndexOnlyScan(gin)*/ doc FROM gin WHERE doc like '%aaa%'; explain analyze select /*+BitmapScan(gin)*/ doc FROM gin WHERE doc like '%aaa%'; |
Hint指定Bitmap Scan
如图所示，从代价分析，可以发现Hint是生效的，但由于GIN类型索引不使用IndexScan和IndexOnlyScan，所以执行计划还是全表扫描。
【小结】Hash类型索引只支持使用Hint指定Bitmap Scan。
(3) Gist索引
drop INDEX idx_gist; drop table if exists gen; create table if not exists gen(id int, pos point); create INDEX idx_gist ON gen USING GiST(pos); explain analyze select /*+IndexScan(gen)*/ pos FROM gen WHERE circle '((100,100) 10)' @>pos; explain analyze select /*+IndexOnlyScan(gen)*/ pos FROM gen WHERE circle '((100,100) 10)' @>pos; explain analyze select /*+BitmapScan(gen)*/ pos FROM gen WHERE circle '((100,100) 10)' @>pos; |
Hint指定Index Scan
Hint指定Index Only Scan
Hint指定Bitmap Scan
【小结】Gist类型索引支持使用Hint指定Index Scan、Index Only Scan (当满足Index Only Scan的使用条件时，Hint指定的Index Only Scan会生效，否则将使用Index Scan) 和Bitmap Scan。
(4) SP-Gist索引
drop table if exists site; create TABLE IF NOT EXISTS site(url text); create INDEX idx_site ON site USING SPGiST(url); insert into site select 'kingbase'||generate_series(1, 100); explain analyze select /*+IndexScan(site)*/ url FROM site WHERE url like '%kingbase%'; explain analyze select /*+BitmapScan(site)*/ url FROM site WHERE url like '%kingbase%'; explain analyze select /*+IndexOnlyScan(site)*/ url FROM site WHERE url like '%kingbase%'; |
如图所示，从代价分析，可以发现Hint是生效的，但由于SP-Gist类型索引不使用IndexScan和BitmapScan，所以执行计划还是全表扫描。
Hint指定Index Only Scan
【小结】SP-Gist类型索引只支持使用Hint指定Index Only Scan。
(5) BRIN索引
drop table if exists brin; create TABLE IF NOT EXISTS brin(scheduled_time timestamptz); create INDEX idx_brin ON brin USING BRIN(scheduled_time); INSERT INTO brin VALUES(generate_series(to_date('20230719','yyyymmdd'),to_date('20230720','yyyymmdd'),'3 hours')); explain analyze select /*+BitmapScan(brin)*/ scheduled_time FROM brin WHERE scheduled_time > '2023-07-19 06:00:00+08'; explain analyze select /*+IndexScan(brin)*/ scheduled_time FROM brin WHERE scheduled_time > '2023-07-19 06:00:00+08'; explain analyze select /*+IndexOnlyScan(brin)*/ scheduled_time FROM brin WHERE scheduled_time > '2023-07-19 06:00:00+08'; |
Hint指定Bitmap Scan
如图所示，从代价分析，可以发现Hint是生效的，但由于SP-Gist类型索引不使用Index Scan和Index Only Scan，所以执行计划还是全表扫描。
【小结】BRIN类型索引只支持使用Hint指定Bitmap Scan。
(6) Bitmap索引
drop table if exists test; create table test(a int, b int); create index idx_t on test using bitmap(a); insert into test select round(random()*3),round(random()*100) from generate_series(1,1000000); explain analyze select a from test where a = 1; explain analyze select /*+IndexScan(test)*/ a from test where a = 1; explain analyze select /*+IndexOnlyScan(test)*/ a from test where a = 1; explain analyze select /*+BitmapScan(test)*/ a from test where a = 1; |
【小结】Bitmap类s型索引支持使用Hint指定Index Scan和Bitmap Scan。
2. 总结
扫描方式 索引类型 | Index Scan | Index Only Scan | Bitmap Scan | 备注 |
Btree索引 | √ | √ | √ | |
Hash索引 | √ | √ | √ | 等值条件 |
GIN索引 | √ | |||
Gist索引 | √ | √ | √ | |
SP-Gist索引 | √ | |||
BRIN索引 | √ | |||
Bitmap索引 | √ | √ |
参考资料
无
