由于JSON类型上没有提供比较函数,所以只能在JSON类型的列上建立函数索引,不能直接建索引。
1.建表
create table test(
id int,
doc json
);
2.建函数索引
(1) 函数定义
create or replace function rand_string(integer)
returns text as
$body$
select array_to_string(
ARRAY( select substring('qwertyuiopasdfghjklzxcvbnm' FROM(ceil(random()*62))::int FOR 1) FROM generate_series(1,$1)),
'')
$body$
LANGUAGE sql VOLATILE;
(2) 函数索引
create index on test using btree (json_extract_path_text(doc,'company'));
3.插入数据
insert into test select t.seq,('{"info":{"name":"April","address":"Chengdu"},"company":"'||rand_string(10)||'"}')::json FROM
generate_series(1,10000) as t(seq);
4.执行计划
先看一下没有走索引的执行计划
explain analyze verbose select * from test where doc->>'company'='gdz';
再看一下走索引的执行计划
explain analyze verbose select * from test where json_extract_path_text(doc,'company')='gdz';
由上图可知,没有走索引花费了6.472ms,走索引花费了0.058ms,相差111.5倍。