GIN 索引

GIN(Generalized Inverted Index, 通用倒排索引) 是一个存储对(key, posting list)集合的索引结构，其中key是一个键值，而posting list 是一组出现过key的位置。如(‘hello', '14:2 23:4')中，表示hello在14:2和23:4这两个元祖出现过，在PG中这些位置实际上就是元组的tid(行号，包括数据块ID（32bit）,以及item point(16 bit) )。

对于表中的每一个属性，在建立对应 gin 索引时，都可能会被解析为多个键值，所以同一个元组的tid可能会出现在多个key的posting list中。

一、索引的逻辑结构

GIN索引在逻辑上可以看成一个relation，该relation有两种结构：

1. 只索引基表的一列的情况：

key	value
Key1	Posting list( or posting tree)
Key2	Posting list( or posting tree)
…	…

2. 索引基表的多列(复合、多列索引)：

column_id	key	value
Column1 num	Key1	Posting list( or posting tree)
Column2 num	Key1	Posting list( or posting tree)
Column3 num	Key1	Posting list( or posting tree)
...	...	...

这种结构，对于基表中不同列的相同的key，在GIN索引中也会当作不同的key来处理。

二、索引的物理结构

GIN索引在物理存储上包含如下内容：
1. Entry：GIN索引中的一个元素，可以认为是一个词位，也可以理解为一个key
2. Entry tree：在Entry上构建的B树
3. posting list：一个Entry出现的物理位置(heap ctid, 堆表行号)的链表
4. posting tree：在一个Entry出现的物理位置链表(heap ctid, 堆表行号)上构建的B树，所以posting tree的KEY是ctid，而entry tree的KEY是被索引的列的值
5. pending list：索引元组的临时存储链表，用于fastupdate模式的插入操作

具体结构如下：

索引实际例子如下：

 三、GIN索引使用例子

1、前后模糊查询

创建测试数据：

?

1 2 3 4 5 6 7 8 9 10

test=# create extension sys_trgm; CREATE EXTENSION   test=# create table t1_text(doc text); CREATE TABLE test=# insert into t1_text select short_desc from pg_settings; INSERT 0 410   test=# create index ind_t1_text on t1_text using gin(doc gin_trgm_ops); CREATE INDEX

查看执行计划：

?

1 2 3 4 5 6 7 8

test=# explain select * from t1_text where doc like '%mod%';                                          QUERY PLAN                                 ---------------------------------------------------------------------------  Bitmap Heap Scan on t1_text  (cost=12.06..17.16 rows=8 width=55)    Recheck Cond: (doc ~~ '%mod%'::text)    ->  Bitmap Index Scan on ind_t1_text  (cost=0.00..12.06 rows=8 width=0)          Index Cond: (doc ~~ '%mod%'::text) (4 rows)

结论：可以看到，GIN 索引支持前后模糊查询。

注意：要使用gin索引，必须至少要有三个字符，如以上例子 mod 是三个字符。

2、全文检索

GIN 索引实际上更多的用于全文检索的情景。

准备数据：

?

1 2 3

alter table t1_text add (doc_ts tsvector); update t1_text set doc_ts=to_tsvector(doc); create index ind_t1_ts on t1_text using gin(doc_ts);

查看执行结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

test=# explain select * from t1_text where doc_ts @@ to_tsquery('command');                                QUERY PLAN                               ------------------------------------------------------------------------  Bitmap Heap Scan on t1_text  (cost=8.32..25.71 rows=9 width=179)    Recheck Cond: (doc_ts @@ to_tsquery('command'::text))    ->  Bitmap Index Scan on ind_t1_ts  (cost=0.00..8.32 rows=9 width=0)          Index Cond: (doc_ts @@ to_tsquery('command'::text)) (4 rows)   test=# select doc from t1_text where doc_ts @@ to_tsquery('command');                                     doc                                    ---------------------------------------------------------------------------  Sets the shell command that will be executed at every restart point.  Sets the shell command that will be called to archive a WAL file.  Allows archiving of WAL files using archive_command.  Logs each replication command.  Sets the shell command that will be executed once at the end of recovery.  Sets the shell command that will retrieve an archived WAL file.  Command to obtain passphrases for SSL.  Also use ssl_passphrase_command during server reload.  Updates the process title to show the active SQL command. (9 rows)   test=#  select doc from t1_text where doc_ts @@ to_tsquery('comman');  doc ----- (0 rows)

四、gin 索引可用于超长的字段

?

1 2 3 4 5 6 7 8 9 10 11

test=# create table tab1(id1 text,id2 text );              CREATE TABLE   test=# alter table tab1 alter column id2 set storage external;                       ALTER TABLE<br> test=# insert into tab1 select *,repeat(id1,10000) from generate_series(1,10000) id1; INSERT 0 10000<br> test=# create index ind_tab1 on tab1(id2);                                           ERROR:  index row requires 10016 bytes, maximum size is 8191<br> test=# create index ind_tab1 on tab1 using gin(id2 gin_trgm_ops); CREATE INDEX

gin 索引之所以支持超长数据，这是因为gin 索引的 key 是关键词位，而非整条记录。