回答

收藏

加快对象嵌套jsonb测试数组中键值的范围

技术问答 技术问答 205 人阅读 | 0 人回复 | 2023-09-13

假设我有下parents表:1 r; ~6 E- E5 H- U
create table parents (  id       integer not null constraint parents_pkey primary key, name     text    not null, children jsonb   not null);以下结构children的 json数组    在哪里:9 A, H1 I( p% J( M1 Y8 N
"name": "child1",       "age":  10    }"name": "child2",       "age": 12    }例如,我需要让所有的父母都有10到12岁的孩子。
, r: v7 E# T; \+ h3 W9 J我创建以下查询:0 d3 E- ?5 p, P8 e# ~2 N1 n0 j
select distinct  p.*from  parents p,jsonb_array_elements(p.children) cwhere  (c->>'age')::int between 10 and 12;当表parents很大的时候(比如1M记录),可以很好的工作,但是很慢。children字段上使用’gin’索引,但这无济于事。
* H. A" @& N. Z' x7 @; b& ]那么,有没有办法加快这种查询呢?或者,可能还有另一个解决方案来查询/索引 字段    中 嵌套的JSON阵列    ?3 u% n' c" F7 M7 [/ n/ g) u3 l9 |
查询计划    :
# ~3 b$ b9 p  d& R" QUnique  (cost=1793091.18..1803091.18 rows=1000000 width=306) (actual time=4070.866..5106.998 rows=399947 loops=1)  ->  Sort  (cost=1793091.18..1795591.18 rows=1000000 width=306) (actual time=4070.864..4836.241 rows=497313 loops=1)          Sort Key: p.id,p.children,p.name        Sort Method: external merge  Disk: 186040kB        ->  Gather  (cost=1000.00..1406321.34 rows=1000000 width=306) (actual time=0.892..1354.147 rows=497313 loops=1)                Workers Planned:                                                             Workers Launched:                                                             ->  Nested Loop  (cost=0.00..1305321.34 rows=416667 width=306) (actual time=0.162..1794.134 rows=165771 loops=3)3)                      ->  Parallel Seq Scan on parents p  (cost=0.00..51153.67 rows=416667 width=306) (actual time=0.075..239.786 rows=333333 loops=3)3)                      ->  Function Scan on jsonb_array_elements c  (cost=0.00..3.00 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1000000000)                                           Filter: ((((value ->> 'age'::text))::integer >= 10) AND (((value ->> 'age'::text))::integer 第一个直接措施是使查询速度更快:
: B" \: o4 _9 i) w0 g  B8 j5 A% ~SELECT *FROM   parents pWHERE  EXISTS (   SELECT FROM jsonb_array_elements(p.children) c   WHERE (c->>'age')::int BETWEEN 10 AND 12   );EXISTS当多个数组对象匹配时,半连接避免了中间表的重复-以及DISTINCT ON需要外部查询。但这只是快一点。
! D. K. q+ ?& n1 s: ]( W$ l5 V核心问题是你想测试 整数值范围
# S# Y: p2 i$ y,而现有的jsonb操作符不提供此类功能。( ^7 \+ d* s# w: a, I
有很多解决方案。我不知道。这里有一个智能解决方案的例子。诀窍是将范围划分为不同的值并使用它jsonbcontainer运算符@>:
7 C# Q6 O; C3 L; ^6 lSELECT *FROM   parents pWHERE (p.children @> '[{"age": 10}]'OR     p.children @> '[{"age": 11}]'OR     p.children @> '[{"age": 12}]');由jsonb_path_opsGIN索引支持:- g! i# X( ~+ U& M
CREATE INDEX parents_children_gin_idx ON parents USING gin (children jsonb_path_ops);但是,如果你的范围跨越了一个不完整的整数值,你需要一些更常见的东西。往常一样
8 ]7 N: t/ k, X! E- e+ K2 h,最佳解决方案取决于整体情况:数据分布、值频率、典型的查询范围NULL值,行大小,读写模式,每个% G* Q' y+ B4 J% t( ^; G. U9 R; D
jsonb一个或多个匹配值age键吗?…
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则