kingbase索引使用实践——sp-gist

和GiST相似，SP-GiST索引为支持多种搜索提供了一种基础结构。SP-GiST 允许实现 众多不同的非平衡的基于磁盘的数据结构，例如四叉树、k-d树和radix树。作为一个例 子，KingbaseES的标准捐献包中包含了一个用于二维点的SP-GiST操作符类，它用于 支持使用下列操作符的索引化查询:
<< 、>>、 ~= 、<@ 、<^、 >^

例子:

kingbase=# create table t_spgist (id int, rg int4range);
CREATE TABLE
kingbase=#  insert into t_spgist select id, int4range(id, id+(random()*200)::int) from generate_series(1,100000) t(id);    
INSERT 0 100000
kingbase=# select * from t_spgist  limit 3;
 id |   rg    
----+---------
  1 | [1,72)
  2 | [2,158)
  3 | [3,57)
(3 行记录)

kingbase=# create index idx_t_spgist_1 on t_spgist using spgist (rg); 
CREATE INDEX
kingbase=# explain (analyze,verbose,timing,costs,buffers) select * from t_spgist where rg && int4range(1,100);    
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on PUBLIC.t_spgist  (cost=48.03..725.12 rows=1000 width=36) (actual time=0.082..0.134 rows=99 loops=1)
   Output: id, rg
   Recheck Cond: (t_spgist.rg && '[1,100)'::INT4RANGE)
   Heap Blocks: exact=1
   Buffers: shared hit=5
   ->  Bitmap Index Scan on idx_t_spgist_1  (cost=0.00..47.78 rows=1000 width=0) (actual time=0.057..0.057 rows=99 loops=1)
         Index Cond: (t_spgist.rg && '[1,100)'::INT4RANGE)
         Buffers: shared hit=4
 Planning time: 0.264 ms
 Execution time: 0.217 ms
(10 行记录)

kingbase=#  set enable_bitmapscan=off; 
SET
kingbase=#  explain (analyze,verbose,timing,costs,buffers) select * from t_spgist where rg && int4range(1,100);    
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Seq Scan on PUBLIC.t_spgist  (cost=0.00..1887.00 rows=1000 width=36) (actual time=0.034..37.313 rows=99 loops=1)
   Output: id, rg
   Filter: (t_spgist.rg && '[1,100)'::INT4RANGE)
   Rows Removed by Filter: 99901
   Buffers: shared hit=637
 Planning time: 0.124 ms
 Execution time: 37.389 ms
(7 行记录)