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.