postgresql在json类型上建索引进行测试

建表：create table jtest01(id int,jdoc json);----------------------------------------------------------------------------------create or replace function random_string(INTEGER)RETURNS TEXT AS$BODY$select array_to_string(array(select substring('0123456789ABCDEFGHIGKLMNOPQRSTUVWXYZabcdefghijgklmnopqrstuvwxyz' from (ceil(random()*62))::int FOR 1)from generate_series(1,$1)),'')$BODY$LANGUAGE sql VOLATILE;------------------------------------------------------------------------------------------插入测试数据：insert into  jtest01 select t.seq,('{"a":{"a1":"a1a1","a2":"a2a2"},"name":"'|| random_string(10) || '", "b":"bbbbbbbb"}')::json from generate_series(1,100000) as t(seq);select * from jtest01 limit 30000;-----------------------------------------------------------------------------------------建函数索引：create index on jtest01 USING btree (json_extract_path_text(jdoc,'name'));-----------------------------------------------------------------------------------------ANALYZE jtest01;-----------------------------------------------------------------------------------------没有走函数索引的执行计划：explain ANALYZE VERBOSE select * from jtest01 where jdoc->>'name'='lnBtcJLR85';testdb2=# explain ANALYZE VERBOSE select * from jtest01 where jdoc->>'name'='lnBtcJLR85';                                                   QUERY PLAN                                                   ---------------------------------------------------------------------------------------------------------------- Seq Scan on public.jtest01  (cost=0.00..2834.00 rows=500 width=80) (actual time=94.029..94.029 rows=0 loops=1)   Output: id, jdoc   Filter: ((jtest01.jdoc ->> 'name'::text) = 'lnBtcJLR85'::text)   Rows Removed by Filter: 100000 Planning time: 0.297 ms Execution time: 94.052 ms(6 rows)-------------------------------------------------------------------------------------------------------------走了函数索引的执行计划：explain ANALYZE VERBOSE select * from jtest01 where json_extract_path_text( jdoc,'name')='lnBtcJLR85';testdb2=# explain ANALYZE VERBOSE select * from jtest01 where json_extract_path_text( jdoc,'name')='lnBtcJLR85';                                                                     QUERY PLAN                                                                     ----------------------------------------------------------------------------------------------------------------- Index Scan using jtest01_json_extract_path_text_idx on public.jtest01  (cost=0.42..8.44 rows=1 width=80) (actual time=0.047..0.047 rows=0 loops=1)   Output: id, jdoc   Index Cond: (json_extract_path_text(jtest01.jdoc, VARIADIC '{name}'::text[]) = 'lnBtcJLR85'::text) Planning time: 0.307 ms Execution time: 0.088 ms(5 rows)---------------------------------------------------------------------------------------------------------------再看看在JSONB类型上建索引的例子建测试表：create table jtest02(id int,jdoc jsonb);create table jtest03(id int,jdoc jsonb);---------------------------------------------------------------------------------------插入测试数据insert into jtest02 select id,jdoc::jsonb from jtest01;insert into jtest03 select * from jtest02;--------------------------------------------------------------------------------------建gin索引create index idx_jtest02_jdoc on jtest02 USING gin (jdoc);create index idx_jtest03_jdoc on jtest03 USING gin (jdoc jsonb_path_ops);------------------------------------------------------------------------------------对表进行分析：analyze jtest02;analyze jtest03;select * from jtest02 where jdoc @> '{"name":"sHWIXgOREa"}';select * from jtest03 where jdoc @> '{"name":"sHWIXgOREa"}';---------------------------------------------------------------------------------------查看执行计划：explain analyze verbose select * from jtest02 where jdoc @> '{"name":"sHWIXgOREa"}';"Bitmap Heap Scan on public.jtest02  (cost=28.77..344.97 rows=100 width=91) (actual time=0.066..0.066 rows=1 loops=1)""  Output: id, jdoc""  Recheck Cond: (jtest02.jdoc @> '{"name": "sHWIXgOREa"}'::jsonb)""  Heap Blocks: exact=1""  ->  Bitmap Index Scan on idx_jtest02_jdoc  (cost=0.00..28.75 rows=100 width=0) (actual time=0.059..0.059 rows=1 loops=1)""        Index Cond: (jtest02.jdoc @> '{"name": "sHWIXgOREa"}'::jsonb)""Planning time: 0.048 ms""Execution time: 0.083 ms"explain analyze verbose select * from jtest03 where jdoc @> '{"name":"sHWIXgOREa"}';"Bitmap Heap Scan on public.jtest03  (cost=16.77..332.97 rows=100 width=91) (actual time=0.018..0.019 rows=1 loops=1)""  Output: id, jdoc""  Recheck Cond: (jtest03.jdoc @> '{"name": "sHWIXgOREa"}'::jsonb)""  Heap Blocks: exact=1""  ->  Bitmap Index Scan on idx_jtest03_jdoc  (cost=0.00..16.75 rows=100 width=0) (actual time=0.009..0.009 rows=1 loops=1)""        Index Cond: (jtest03.jdoc @> '{"name": "sHWIXgOREa"}'::jsonb)""Planning time: 0.052 ms""Execution time: 0.036 ms"------------------------------------------------------------------------------------------------------------------------查看索引的大小select pg_indexes_size('jtest02');testdb2=# select pg_indexes_size('jtest02'); pg_indexes_size -----------------         8224768(1 row)select pg_indexes_size('jtest03');testdb2=# select pg_indexes_size('jtest03'); pg_indexes_size -----------------         5980160(1 row)可看出jsonb_path_ops类型的索引要比jsonb_ops的小。