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

独坐苍茫

假设我有下parents表：
6 f3 `) P/ K* x0 ]1 [; o: tcreate table parents (  id       integer not null constraint parents_pkey primary key, name     text    not null, children jsonb   not null);以下结构children的 json数组    在哪里：
"name": "child1",       "age":  10    }"name": "child2",       "age": 12    }例如，我需要让所有的父母都有10到12岁的孩子。
我创建以下查询：
select distinct  p.*from  parents p,jsonb_array_elements(p.children) cwhere  (c->>'age')::int between 10 and 12;当表parents很大的时候(比如1M记录)，可以很好的工作，但是很慢。children字段上使用’gin’索引，但这无济于事。
2 A/ x. p! y: v. _; g: F那么，有没有办法加快这种查询呢？或者，可能还有另一个解决方案来查询/索引 字段    中 嵌套的JSON阵列    ？
" s, l$ n* S; V6 X+ i查询计划    ：
- J5 D  R/ Z# m7 e. yUnique  (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 第一个直接措施是使查询速度更快：
! g3 X) O, Y. K; |) I7 ?SELECT *FROM   parents pWHERE  EXISTS (   SELECT FROM jsonb_array_elements(p.children) c   WHERE (c->>'age')::int BETWEEN 10 AND 12   )；EXISTS当多个数组对象匹配时，半连接避免了中间表的重复-以及DISTINCT ON需要外部查询。但这只是快一点。
核心问题是你想测试 整数值范围
，而现有的jsonb操作符不提供此类功能。
: j4 l# c2 e+ U& [  b! L) H" l有很多解决方案。我不知道。这里有一个智能解决方案的例子。诀窍是将范围划分为不同的值并使用它jsonbcontainer运算符@>：
  j# t" X' Y  u0 j" TSELECT *FROM   parents pWHERE (p.children @> '[{"age": 10}]'OR     p.children @> '[{"age": 11}]'OR     p.children @> '[{"age": 12}]');由jsonb_path_opsGIN索引支持：
CREATE INDEX parents_children_gin_idx ON parents USING gin (children jsonb_path_ops);但是，如果你的范围跨越了一个不完整的整数值，你需要一些更常见的东西。往常一样
! n$ Z" N, V/ W5 a3 p) G1 n，最佳解决方案取决于整体情况：数据分布、值频率、典型的查询范围NULL值，行大小，读写模式，每个
8 j$ E# T, t$ c3 Qjsonb一个或多个匹配值age键吗？…