如何讓json裡面的value支援索引範圍檢索

标簽

PostgreSQL , json , 索引 , gin , btree_gin , 範圍檢索 , 表達式索引

https://github.com/digoal/blog/blob/master/201807/20180702_02.md#%E8%83%8C%E6%99%AF 背景

JSON，JSONB是PG從9就開始引入的資料類型，開發人員非常喜歡，對于JSON裡面的内容的檢索，我們可以使用GIN索引，目前直接對JSON字段建立GIN索引，可以支援任意PATH的等值、包含檢索。

https://www.postgresql.org/docs/10/static/datatype-json.html#JSON-INDEXING

但是，在多數場景中，我們可能還需要範圍檢索的需求，例如消費範圍，年齡範圍，收入範圍等等。

使用表達式組合索引，很好的滿足需求。

https://github.com/digoal/blog/blob/master/201807/20180702_02.md#%E4%BE%8B%E5%AD%90 例子

https://github.com/digoal/blog/blob/master/201807/20180702_02.md#%E5%8D%95key%E7%9B%B4%E6%8E%A5%E7%94%A8btree%E7%B4%A2%E5%BC%95 單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)  
           

https://github.com/digoal/blog/blob/master/201807/20180702_02.md#%E5%A4%9Akey%E6%B7%B7%E5%90%88%E4%BD%BF%E7%94%A8btree_gin-%E8%A1%A8%E8%BE%BE%E5%BC%8F%E7%B4%A2%E5%BC%95 多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://github.com/digoal/blog/blob/master/201807/20180702_02.md#%E5%8F%82%E8%80%83 參考

https://www.postgresql.org/docs/10/static/btree-gin.html