PostgreSQL中JSONB的索引
JSONB类型的列上可以直接建索引,支持BTree索引和GIN索引。其中,GIN索引可以高效的从JSONB内部的key/value对中搜索数据,因此JSONB类型上常用GIN索引。
JSONB上创建GIN索引的方式有两种:
(1)使用默认的jsonb_ops操作符创建。
(2)使用jsonb_path_ops操作符创建。
两者的区别是:
(1)jsonb_ops的GIN索引中,JSONB数据中的每个key和value都是作为一个单独的索引项。
(2)jsonb_path_ops只为每一个value创建一个索引项。
例如,一个项{“foo”:{"bar":"baz"}},jsonb_ops会分别为“foo”,"bar","baz"创建索引项,共创建了三个。jsonb_path_ops则是把“foo”,"bar","baz"组合成一个hash值作为索引项。因此,通常jsonb_path_ops的索引较小。
1.建表
create table test2(
id int,
doc jsonb
);
2.插入数据
insert into test2 select id,doc::jsonb from test;
注:test表是上文《PostgreSQL中JSON的索引》中创建的表,此处复制了test表中的数据。
3.建索引
使用默认的jsonb_ops操作符创建索引。
create index idx_test2 on test2 using gin(doc);
使用jsonb_path_ops操作符创建索引。
create index idx_test2 on test2 using gin(doc jsonb_path_ops);
4.查看索引大小
select pg_indexes_size('test2');
jsonb_ops下的执行结果:
jsonb_path_ops下的执行结果:
jsonb_path_ops类型的索引比jsonb_ops的小。
5.执行计划
explain analyze verbose select * from test2 where doc @> '{"company":"gdz"}';
jsonb_ops下的执行结果:
jsonb_path_ops下的执行结果: