GIN又名“杜松子酒”,是烈性酒。但是我们今天讨论的不是酒,而是 PostgreSQL中的GIN索引,引用PostgreSQL源代码README对 GIN索引的描述。GIN是广义反向索引。应被认为是精灵,不是酒。
Gin stands for Generalized Inverted Index and should be considered as a genie,not a drink.
倒排索引是什么意思?让我们通过以下示例来描述。这里是英文书“Mastering PostgreSQL 12 Third Edition”,我们把书直接拖到最后几页。这会有一个Index。与书籍前几章目录中的 Index不同,这里的 Index是各种各样的关键词。先把它按A-Z来排列,然后A下面会有很多a开头的单词,然后把这些单词所在的书页上标注出来。你看,通过这个关键字能很快找到书中的相关位置。因此, GIN索引经常用于全文搜索。
GIN的索引结构如下:
对应到上面的图就如下结果:
接下来我们来解释一下,首先是 entry tree,它是用于组织和存储(key, posting list)键值对的B树结构。key是键值, Posting list或Posting tree表示出现过key的位置。为何这里又是 Postinglist,又是Posting tree呢?查看资料发现,如果tid的列表很小,则可以和元素放在一个页面,称为Posting list。如果tid的列表很长,则需要有效的数据结构(B树)来存储,这样的树位于单独的数据页上,称作Posting Tree。
首先创建一个拥有1000万用户的用户表,其中包括first_name和last_name两个列。
CREATE TABLE users (
first_name text,
last_name text
)
pgbench=# insert into users SELECT md5(random()::text), md5(random()::text) FROM (SELECT * FROM generate_series(1,10000000) AS id) AS x;
INSERT 0 10000000
执行单列模糊查询,执行时长3755ms。
执行两个列上的模糊查询,执行时长3842ms。
立即创建 GIN索引。需要在创建前安装pg_trgm插件。
pgbench=# CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION
pgbench=# CREATE INDEX idx_user_search ON users USING gin (first_name gin_trgm_ops, last_name gin_trgm_ops);
CREATE INDEX
创建完索引之后,再次执行模糊查询。
单列模糊查询,执行时间居然从3755ms下降到12ms。而多列模糊查询,执行时间从3842ms下降到19ms,性能提升巨大。
今天先研究到这里,明天继续GIN。