如何让json里面的value支持索引范围检索
背景
JSON,JSONB是PG从9就开始引入的数据类型,开发人员非常喜欢,对于JSON里面的内容的检索,我们可以使用GIN索引,目前直接对JSON字段创建GIN索引,可以支持任意PATH的等值、包含检索。
https://www.postgresql.org/docs/10/static/datatype-json.html#JSON-INDEXING
但是,在多数场景中,我们可能还需要范围检索的需求,例如消费范围,年龄范围,收入范围等等。
使用表达式组合索引,很好的满足需求。
例子
单KEY,直接用btree索引
postgres=# create table test (id int, js jsonb);
CREATE TABLE
postgres=# create index idx_test_2 on test using btree (((js->>'key1')::int));
CREATE INDEX
postgres=# explain select * from test where (js->>'key1')::int between 1 and 10 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Index Scan using idx_test_2 on test (cost=0.15..24.27 rows=6 width=36)
Index Cond: ((((js ->> 'key1'::text))::integer >= 1) AND (((js ->> 'key1'::text))::integer <= 10))
(2 rows)
多KEY混合,使用btree_gin, 表达式索引
例如需要查询key1,key2,key3的任意组合范围查询。
postgres=# create extension btree_gin;
CREATE EXTENSION
postgres=# create index idx_test_1 on test using gin (((js->>'key1')::int), ((js->>'key2')::int), ((js->>'key3')::int));
CREATE INDEX
postgres=# explain select * from test where (js->>'key1')::int between 1 and 10
postgres-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=24.07..33.64 rows=6 width=36)
Recheck Cond: ((((js ->> 'key1'::text))::integer >= 1) AND (((js ->> 'key1'::text))::integer <= 10))
-> Bitmap Index Scan on idx_test_1 (cost=0.00..24.06 rows=6 width=0)
Index Cond: ((((js ->> 'key1'::text))::integer >= 1) AND (((js ->> 'key1'::text))::integer <= 10))
(4 rows)
postgres=# explain select * from test where (js->>'key1')::int between 1 and 10 or (js->>'key2')::int between 1 and 15;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=48.13..59.32 rows=13 width=36)
Recheck Cond: (((((js ->> 'key1'::text))::integer >= 1) AND (((js ->> 'key1'::text))::integer <= 10)) OR ((((js ->> 'key2'::text))::integer >= 1) AND (((js ->> 'key2'::text))::integer <= 15)))
-> BitmapOr (cost=48.13..48.13 rows=13 width=0)
-> Bitmap Index Scan on idx_test_1 (cost=0.00..24.06 rows=6 width=0)
Index Cond: ((((js ->> 'key1'::text))::integer >= 1) AND (((js ->> 'key1'::text))::integer <= 10))
-> Bitmap Index Scan on idx_test_1 (cost=0.00..24.06 rows=6 width=0)
Index Cond: ((((js ->> 'key2'::text))::integer >= 1) AND (((js ->> 'key2'::text))::integer <= 15))
(7 rows)
postgres=# explain select * from test where (js->>'key1')::int between 1 and 10 and (js->>'key2')::int between 1 and 15;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=40.00..44.05 rows=1 width=36)
Recheck Cond: ((((js ->> 'key1'::text))::integer >= 1) AND (((js ->> 'key1'::text))::integer <= 10) AND (((js ->> 'key2'::text))::integer >= 1) AND (((js ->> 'key2'::text))::integer <= 15))
-> Bitmap Index Scan on idx_test_1 (cost=0.00..40.00 rows=1 width=0)
Index Cond: ((((js ->> 'key1'::text))::integer >= 1) AND (((js ->> 'key1'::text))::integer <= 10) AND (((js ->> 'key2'::text))::integer >= 1) AND (((js ->> 'key2'::text))::integer <= 15))
(4 rows)
参考
https://www.postgresql.org/docs/10/static/datatype-json.html#JSON-INDEXING
https://www.postgresql.org/docs/10/static/btree-gin.html