Practice of using kingbase index-gin

GIN index is an inverted index, which is suitable for data values containing multiple constituent values, such as arrays. The inverted index contains a single item for each component value, which can efficiently process queries that test whether a specified component value exists.
It is used to support indexed queries using the following operators:
<@ ,@>, =, &&
In kingbase, gin index is generally used in the following cases:
When searching VALUE within a multivalued type, it is suitable for multivalued types, such as arrays, full-text retrieval, and TOKEN. (Searches for intersection, inclusion, greater, left, right, etc. are supported according to different types)
Unlike pg, gin index of kingbase can not support the type supported by ordinary BTREE (without extension such as btree_gin), which also leads to the inability to use gin in many scenarios, such as single-valued sparse data search, multi-column arbitrary search.
Let's look at the use of gin index in kingbase.

kingbase=# create table t_gin1 (id int, arr int[]);    
CREATE TABLE
kingbase=# do language plsql $$    
kingbase$# declare    
kingbase$# begin    
kingbase$#  for i in 1..10000 loop    
kingbase$#   insert into t_gin1 select i, array(select random()*1000 from generate_series(1,10));    
kingbase$#  end loop;    
kingbase$# end;    
kingbase$# $$;    
DO
kingbase=# select * from t_gin1 limit 3;  
 id |                    arr                    
----+-------------------------------------------
  1 | {925,489,834,158,868,36,827,765,177,244}
  2 | {986,326,596,865,682,852,444,771,972,454}
  3 | {76,274,127,234,941,797,847,424,377,885}
(3 Row record)

Note that the langue here in kingbase uses plsql, not plpgsql!

kingbase=#  create index idx_t_gin1_1 on t_gin1 using gin (arr);   
CREATE INDEX
kingbase=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin1 where arr && array[1,2];    
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on PUBLIC.t_gin1  (cost=20.77..145.43 rows=100 width=36) (actual time=0.108..0.640 rows=192 loops=1)
   Output: id, arr
   Recheck Cond: (t_gin1.arr && '{1,2}'::INTEGER[])
   Heap Blocks: exact=100
   Buffers: shared hit=105
   ->  Bitmap Index Scan on idx_t_gin1_1  (cost=0.00..20.75 rows=100 width=0) (actual time=0.086..0.086 rows=192 loops=1)
         Index Cond: (t_gin1.arr && '{1,2}'::INTEGER[])
         Buffers: shared hit=5
 Planning time: 0.352 ms
 Execution time: 0.767 ms
(10 Row record)

kingbase=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin1 where arr @> array[1,2];    
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on PUBLIC.t_gin1  (cost=20.00..24.01 rows=1 width=36) (actual time=0.051..0.052 rows=1 loops=1)
   Output: id, arr
   Recheck Cond: (t_gin1.arr @> '{1,2}'::INTEGER[])
   Heap Blocks: exact=1
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_t_gin1_1  (cost=0.00..20.00 rows=1 width=0) (actual time=0.040..0.041 rows=1 loops=1)
         Index Cond: (t_gin1.arr @> '{1,2}'::INTEGER[])
         Buffers: shared hit=5
 Planning time: 0.174 ms
 Execution time: 0.092 ms
(10 Row record)

Generally speaking, gin index in kingbase still has some drawbacks: scenarios are limited to multi-valued search.

Added by r_honey on Sat, 05 Oct 2019 09:18:39 +0300