金仓数据库KingbaseES GIN and RUM 索引性能比较

最新推荐文章于 2024-03-11 13:07:44 发布

沉舟侧畔千帆过_

最新推荐文章于 2024-03-11 13:07:44 发布

阅读量164

点赞数

分类专栏：数据库文章标签：数据库 gin java kingbase 金仓数据库

本文链接：https://blog.csdn.net/arthemis_14/article/details/128114326

版权

数据库专栏收录该内容

550 篇文章 32 订阅

订阅专栏

gin索引字段entry构造的TREE，在末端posting tree|list 里面存储的是entry对应的行号. 别无其他信息。

rum索引，与GIN类似，但是在posting list|tree的每一个ctid(itempoint)后面会追加一些属性值。

因此，有些场景，使用rum 索引，性能会优很多。以下举个例子比较下。

一、构造数据

create table t1 as select name,short_desc from sys_settings;
alter table t1 add column tsv tsvector;
update t1 set tsv=to_tsvector(short_desc);
--number for 紧邻的只有一条
test=# select short_desc from t1 where to_tsvector(short_desc) @@ to_tsquery('number <-> for');
       short_desc       
-------------------------
 Top SQL number for kddm
(1 row)
--同时包含number and for 的有7条
test=# select short_desc from t1 where to_tsvector(short_desc) @@ to_tsquery('number & for');
                                  short_desc
-------------------------------------------------------------------------------
Sets the number of digits displayed for floating-point values.
Sets the maximum number of simultaneously open files for each server process.
Sets the number of connection slots reserved for superusers.
Top SQL number for kddm
Sets the number of disk-page buffers in shared memory for WAL.
Sets the number of WAL files held for standby servers.
Sets the number of locks used for concurrent xlog insertions.
(7 rows)

二、例子1：距离查询

1、gin 索引

创建gin 索引：

create index ind_t1_gin on t1 using gin(tsv);

查看gin索引执行计划：通过索引返回7条记录，也就是索引没有包含位置的信息，需要访问表数据。

test=# explain analyze select short_desc from t1 where tsv @@ to_tsquery('number <-> for');                   
                                                     QUERY PLAN                                                    
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=12.32..30.68 rows=9 width=55) (actual time=0.111..0.195 rows=1 loops=1)
   Recheck Cond: (tsv @@ to_tsquery('number <-> for'::text))
   Rows Removed by Index Recheck: 6
   Heap Blocks: exact=4
   ->  Bitmap Index Scan on ind_t1_gin  (cost=0.00..12.32 rows=9 width=0) (actual time=0.058..0.058 rows=7 loops=1)
         Index Cond: (tsv @@ to_tsquery('number <-> for'::text))
 Planning Time: 0.199 ms
 Execution Time: 0.227 ms
(8 rows)

2、rum 索引

创建索引：

test=# create extension rum;
CREATE EXTENSION
test=# create index ind_t1_rum on t1 using rum(tsv);      
CREATE INDEX

查看执行计划：可以看到索引返回的记录就一条，也就是索引包含有位置信息。

test=# explain analyze select short_desc from t1 where tsv @@ to_tsquery('number <-> for');
                                                     QUERY PLAN                                                    
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=12.32..30.68 rows=9 width=55) (actual time=0.041..0.042 rows=1 loops=1)
   Recheck Cond: (tsv @@ to_tsquery('number <-> for'::text))
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on ind_t1_rum  (cost=0.00..12.32 rows=9 width=0) (actual time=0.038..0.039 rows=1 loops=1)
         Index Cond: (tsv @@ to_tsquery('number <-> for'::text))
 Planning Time: 0.297 ms
 Execution Time: 0.068 ms
(7 rows)

三、例子2：相关性排序

1、gin 索引

需要所有符合的数据，再进行排序

test=# create index ind_t1_gin on t1 using gin(tsv);
CREATE INDEX
test=# explain analyze select short_desc from t1 where tsv @@ to_tsquery('number & for') order by tsv <=> to_tsquery('number & for') limit 1; 
                                                           QUERY PLAN                                                          
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=33.00..33.00 rows=1 width=59) (actual time=0.134..0.135 rows=1 loops=1)
   ->  Sort  (cost=33.00..33.02 rows=9 width=59) (actual time=0.133..0.134 rows=1 loops=1)
         Sort Key: ((tsv <=> to_tsquery('number & for'::text)))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on t1  (cost=12.32..32.95 rows=9 width=59) (actual time=0.102..0.125 rows=7 loops=1)
               Recheck Cond: (tsv @@ to_tsquery('number & for'::text))
               Heap Blocks: exact=4
               ->  Bitmap Index Scan on ind_t1_gin  (cost=0.00..12.32 rows=9 width=0) (actual time=0.084..0.084 rows=7 loops=1)
                     Index Cond: (tsv @@ to_tsquery('number & for'::text))
 Planning Time: 0.237 ms
 Execution Time: 0.177 ms
(11 rows)

2、rum 索引

test=# explain analyze select short_desc from t1 where tsv @@ to_tsquery('number & for') order by tsv <=> to_tsquery('number & for') limit 1; 
                                                      QUERY PLAN                                                      
-----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=8.25..12.52 rows=1 width=59) (actual time=0.077..0.077 rows=1 loops=1)
   ->  Index Scan using ind_t1_rum on t1  (cost=8.25..46.68 rows=9 width=59) (actual time=0.076..0.076 rows=1 loops=1)
         Index Cond: (tsv @@ to_tsquery('number & for'::text))
         Order By: (tsv <=> to_tsquery('number & for'::text))
 Planning Time: 0.236 ms
 Execution Time: 0.101 ms
(6 rows)