PostgreSQL中JSON的索引

由于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';