假设我有以下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
第一个立即措施是使查询更快:
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
codment Operator @>
:
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);
但是,如果您的范围跨度超过整数值的手,则需要更通用的东西。如始终,最佳解决方案取决于完整的情况:数据分布,值频率,典型范围的查询,零值可能?,行大小,读/写模式, em em> jsonb
值具有一个或多个匹配的age
密钥?...
使用专业的,非常快的索引相关的答案:
- 搜索具有更大运算符的JSONB数组中的嵌套值
相关:
- 在JSON数组中查找元素的索引
- 在Postgresql中使用JSON数组中的索引
我建议您尝试这种方式(这是我的经验(。
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
)
希望它有效。