PostgreSQL json 矩阵数据搜索优化 - partial index , recheck

digoal 2020-09-28

908

作者

digoal

日期

2020-09-28

背景

矩阵数据, 全矩阵所有行搜索.

```
create table b (id int, c1 jsonb);
insert into b values (1, '[{"a":1,"b":2},{"c":2,"d":4}]');
create index idx_b_1 on b using gin (c1);

postgres=> set enable_seqscan=off;
SET
postgres=> explain select * from b where c1 @> '{"c":2}' ;
QUERY PLAN

Bitmap Heap Scan on b (cost=3.30..4.41 rows=1 width=36)
Recheck Cond: (c1 @> '{"c": 2}'::jsonb)
-> Bitmap Index Scan on idx_b_1 (cost=0.00..3.30 rows=1 width=0)
Index Cond: (c1 @> '{"c": 2}'::jsonb)
(4 rows)
```

矩阵数据, 根据指定矩阵行搜索.

例如搜索第2行, 无法直接使用jsonb索引. (与array类型从1开始编号不同， json的array从0开始编号)

```
postgres=> explain select * from b where c1 -> 1 ->> 'b'='2';
QUERY PLAN

Seq Scan on b (cost=10000000000.00..10000000001.02 rows=1 width=36)
Filter: (((c1 -> 1) ->> 'b'::text) = '2'::text)
(2 rows)
```

优化方法

由于gin索引是基于KEY的单颗树，所以没有办法直接指定矩阵哪行来搜索。需要使用partial index:

优化方法1、（PS：当然，我们可以不顾哪行先搜索，然后再recheck的方式来完成这个需求，但是引入了额外的filter。）

``` select * from b where c1 @> '{"b" :1}' and c1->1->>'b' = '1';

postgres=> explain select * from b where c1 @> '{"b" :1}' and c1->1->>'b' = '1'; QUERY PLAN

Bitmap Heap Scan on b (cost=3.30..4.42 rows=1 width=36) Recheck Cond: (c1 @> '{"b": 1}'::jsonb) Filter: (((c1 -> 1) ->> 'b'::text) = '1'::text) -> Bitmap Index Scan on idx_b_1 (cost=0.00..3.30 rows=1 width=0) Index Cond: (c1 @> '{"b": 1}'::jsonb) (5 rows) ```

优化方法2、partial index方法的优化要求: 矩阵的行数有限, 太多了的话partial index会太多.

```
-- 每行创建一个partial index postgres=> create index idx_b_2 on b using gin (((c1->1)::jsonb));
CREATE INDEX
postgres=> create index idx_b_3 on b using gin (((c1->2)::jsonb));
CREATE INDEX

postgres=> explain select * from b where (c1 -> 1)::jsonb @> '{"b":1}';
QUERY PLAN

Bitmap Heap Scan on b (cost=3.30..4.42 rows=1 width=36)
Recheck Cond: ((c1 -> 1) @> '{"b": 1}'::jsonb)
-> Bitmap Index Scan on idx_b_2 (cost=0.00..3.30 rows=1 width=0)
Index Cond: ((c1 -> 1) @> '{"b": 1}'::jsonb)
(4 rows)
```