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

Question

假设我有以下的parents表格：

create 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) c
where
  (c->>'age')::int between 10 and 12;

当表parents很大时（例如1M条记录），它的工作效率很低。我尝试在children字段上使用'gin'索引，但这没有帮助。

那么有没有办法加速这样的查询？或者也许有另一种解决方案可以针对嵌套json数组中的字段进行查询/索引？

查询计划：

Unique  (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: 2
              Workers Launched: 2
              ->  Nested Loop  (cost=0.00..1305321.34 rows=416667 width=306) (actual time=0.162..1794.134 rows=165771 loops=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)
                    ->  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=1000000)
                          Filter: ((((value ->> 'age'::text))::integer >= 10) AND (((value ->> 'age'::text))::integer <= 12))
                          Rows Removed by Filter: 3
Planning time: 0.218 ms
Execution time: 5140.277 ms

- Cepr0

---

唯一彻底加速查询这些值的方法是规范化模型。 "Children" 亟需成为一个表格。 - klin

@klin 我知道。现在它是一个表格，但它将变成一个JSON数组)) - Cepr0

哦不，别这样对他们！ - klin

我也认为贫困儿童应该有自己的表。您可以添加一个VIEW返回JSON表示。这应该是快速和简单的。但是，也有方法可以帮助拥挤的jsonb值中的贫困儿童... - Erwin Brandstetter

@ErwinBrandstetter 你给了他们希望！孩子们非常爱他们的父母，让我们帮助他们和父母在一起吧！) - Cepr0

Answer 1

第一个立即采取的措施是使您的查询速度更快：

SELECT *
FROM   parents p
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(p.children) c
   WHERE (c->>'age')::int BETWEEN 10 AND 12
   );

EXISTS 半连接避免在中间表中出现重复行，当多个数组对象匹配时 - 并且在外部查询中需要使用 DISTINCT ON。但这只是稍微快一点。

核心问题是您想测试一组整数值，而 现有的jsonb运算符 不提供此功能。

有各种方法可以解决这个问题。如果不知道任何方法，这里有一个“聪明”的解决方案来解决给定的示例。诀窍是将范围分成不同的值，并使用 jsonb 包含运算符 @>：

SELECT *
FROM   parents p
WHERE (p.children @> '[{"age": 10}]'
OR     p.children @> '[{"age": 11}]'
OR     p.children @> '[{"age": 12}]');

由 jsonb_path_ops GIN 索引支持：

CREATE INDEX parents_children_gin_idx ON parents USING gin (children jsonb_path_ops);

如果您的范围涵盖超过一些整数值，您需要更通用的东西。如同往常一样，最佳解决方案取决于完整情况：数据分布、值频率、查询中的典型范围、可能存在NULL值、行大小、读/写模式，每个jsonb值是否都有一个或多个匹配的age键？...

相关专业化、非常快速的索引答案：

• 在jsonb数组中搜索嵌套值并使用greater运算符

相关内容：

• 查找JSON数组中的元素的索引
• 在PostgreSQL中使用json数组中的索引

- Erwin Brandstetter

---

非常感谢Erwin，像往常一样，你的解决方案非常出色！根据查询计划，使用“exists”的方法加速了超过2倍，但是带有“limit 20”的实际查询仅用了45毫秒就获取了数据 - 而且这是在我缓慢的家用电脑上完成的！ - Cepr0

@Cepr0：LIMIT是一个改变游戏规则的东西，通常可以支持完全不同的查询计划。Postgres没有针对jsonb列中嵌套值的真正统计数据，这可能会导致非标准分布的子优化查询计划。根据完整情况，即使没有LIMIT，查询仍然有更快的解决方案... - Erwin Brandstetter

我现在没有具体的例子 - 我们正在考虑简化数据库结构的可能性。我们正在使用Spring/Hibernate/PostgreSQL堆栈构建REST服务，并考虑将所有依赖（嵌入式）实体移动到主/父实体（聚合根）的表中，以优化/简化检索数据与分页。但不会丧失功能（对嵌套实体进行数据过滤）。如果您有任何建议，我将非常感激！ - Cepr0

@Cepr0：不太确定该怎么建议。这个相关的答案可能对分页有用：https://dev59.com/yFsX5IYBdhLWcg3wNdHj#34291099。祝迁移顺利！ - Erwin Brandstetter

@Cepr0：您可能会对添加的相关答案链接感兴趣。 - Erwin Brandstetter

谢谢你，Erwin！我已经看到了你在这个话题上几乎所有的重要回答。你的帮助对我们来说非常重要！ - Cepr0

Answer 2

我建议你尝试这种方式（这是根据我的经验）。

WITH t AS (SELECT id, jsonb_array_elements(children) as child_data FROM parents)
SELECT *  
  FROM parents 
 WHERE id IN (
              SELECT id
                FROM t
               WHERE (child_data->>'age')::int between 10 and 12
           )

希望它能正常工作。

- Mabu Kloesen

---

抱歉，但是您的查询比我的慢... - Cepr0