标签

PostgreSQL , json , gin , rum

---

背景

用户在使用JSON类型时，常见的一些JSON搜索包括：

1、存在，JSON中是否存在某个KEY，某些KEY，某些KEY的任意一个

存在某个KEY(TOP LEVEL)

'{"a":1, "b":2}'::jsonb ? 'b'

存在所有KEY

'{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'c']

存在任意KEY、元素

'["a", "b"]'::jsonb ?| array['a', 'b']

2、等值，JSON中是否存在指定的key:value对（支持嵌套JSON）

'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

3、包含，JSON中某个路径下的VALUE（数组）中，是否包含指定的所有元素。

postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,3]}}';   ?column?   ----------   t  (1 row)

4、相交，JSON中某个路径下的VALUE（数组）中，是否包含指定的任意元素。

postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2]}}'   or  jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[3]}}'  ;     ?column?   ----------   t  (1 row)

或(注意1,2,3需要双引号，作为text类型存储，因为操作符?| ?&暂时只支持了text[]，如果是numeric匹配不上)

postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?& array['2','3','4'] ;   ?column?   ----------   f  (1 row)    postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?| array['2','3','4'] ;   ?column?   ----------   t  (1 row)

5、范围查找，JSON中某个路径下的VALUE，是否落在某个范围内。

(js ->> 'key1' )::numeric between xx and xx    (js ->> 'key2' )::numeric between xx and xx

这些操作如何加速，或者如何使用索引加速？

一、json 索引支持

GIN的两个OPS，分别支持JSON：

The default GIN operator class for jsonb supports queries with top-level key-exists operators ?, ?& and ?| operators and path/value-exists operator @>.    The non-default GIN operator class jsonb_path_ops supports indexing the @> operator only.

1、支持 @> 操作符的索引如下（jsonb_path_ops只支持@>操作符，但是效率高）

postgres=# create table tbl(id int, js jsonb);  CREATE TABLE  postgres=# create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);  CREATE INDEX

2、支持除范围查询以外的所有查询的索引如下

postgres=# create table tbl(id int, js jsonb);  CREATE TABLE  postgres=# create index idx_tbl_1 on tbl using gin (js);  -- 使用默认ops即可  CREATE INDEX

二、JSON KEY VALUE值范围查询加速

某些使用，需要对VALUE使用范围查询，比如时间(如果要建索引，请使用numeric表示，否则需要自定义immutable函数)，数值都有这些需求。

通常的做法，把范围查询的类型提取出来，创建btree表达式索引，如果有任意组合的范围查询，使用gin或rum表达式索引。

例子

create index idx1 on tbl ( ((js->>'k1')::float8) );  create index idx2 on tbl ( ((js->>'k2')::numeric) );  ...  create index idxn on tbl ( ((js->>'kn')::float8) );

或

create extension btree_gin;  create index idx1 on tbl using gin( ((js->>'k1')::float8), ((js->>'k2')::numeric), ... ((js->>'kn')::float8) );

或

create extension rum;  create index idx1 on tbl using rum( ((js->>'k1')::float8), ((js->>'k2')::numeric), ... ((js->>'kn')::float8) );

或

create or replace function to_timestamp(text) returns timestamp as $$    select $1::timestamp;  $$ language sql strict immutable;      create index idx1 on tbl using gin( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ... ((js->>'kn')::float8) );  或  create index idx1 on tbl using rum( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ... ((js->>'kn')::float8) );

三、索引使用例子

create table tbl(id int, js jsonb);  create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);  create index idx_tbl_2 on tbl using gin (js);  create index idx_tbl_3 on tbl using rum( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ((js->>'k3')::numeric) );    postgres=# explain select * from tbl where js ? 'a';                                 QUERY PLAN                                 ------------------------------------------------------------------------   Bitmap Heap Scan on tbl  (cost=2.21..3.32 rows=1 width=36)     Recheck Cond: (js ? 'a'::text)     ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..2.21 rows=1 width=0)           Index Cond: (js ? 'a'::text)  (4 rows)    postgres=# explain select * from tbl where js @> '{"a":"b"}';                                 QUERY PLAN                                 ------------------------------------------------------------------------   Bitmap Heap Scan on tbl  (cost=2.21..3.32 rows=1 width=36)     Recheck Cond: (js @> '{"a": "b"}'::jsonb)     ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..2.21 rows=1 width=0)           Index Cond: (js @> '{"a": "b"}'::jsonb)  (4 rows)  postgres=# explain select * from tbl where to_timestamp(js->>'k2') between '2018-01-01' and '2018-01-02';                                                                                               QUERY PLAN                                                                                                --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tbl_3 on tbl  (cost=5.50..12.22 rows=6 width=36)   Index Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::text)) <= '2018-01-02 00:00:00'::timestamp without time zone))(2 rows)postgres=# explain select * from tbl where to_timestamp(js->>'k2') between '2018-01-01' and '2018-01-02' and ((js->>'k3')::numeric) between 1 and 200;                                                                                                                                                     QUERY PLAN                                                                                                                                                      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tbl_3 on tbl  (cost=9.90..12.11 rows=1 width=36)   Index Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::text)) <= '2018-01-02 00:00:00'::timestamp without time zone) AND (((js ->> 'k3'::text))::numeric >= '1'::numeric) AND (((js ->> 'k3'::text))::numeric <= '200'::numeric))(2 rows)  postgres=# select * from tbl where js @> '{"a": {"b":"c"}}';   id | js   ----+----  (0 rows)    SELECT doc->'site_name' FROM websites    WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';    postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}';                                 jsonb                                 -------------------------------------------------------------------   {"a": 1, "b": {"c": [1, 2, 3], "d": ["k", "y", "z"]}, "d": "kbc"}  (1 row)    postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,3]}}';   ?column?   ----------   t  (1 row)    postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,4]}}';   ?column?   ----------   f  (1 row)      postgres=# explain select * from tbl where js @> '{"b":{"c":[2,4]}}';                                 QUERY PLAN                                 ------------------------------------------------------------------------   Bitmap Heap Scan on tbl  (cost=3.31..4.42 rows=1 width=36)     Recheck Cond: (js @> '{"b": {"c": [2, 4]}}'::jsonb)     ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..3.31 rows=1 width=0)           Index Cond: (js @> '{"b": {"c": [2, 4]}}'::jsonb)  (4 rows)

参考