为带有嵌套数组的jsonb添加和使用索引

Question

在我的PostgreSQL 11.11中，我有一个jsonb列，它包含这样的对象：

{
"dynamicFields":[
{
"name":"200",
"hidden":false,
"subfields":[
{
"name":"a",
"value":"Subfield a"
},
{
"name":"b",
"value":"Subfield b"
}           
]
}
]
}

dynamicFields是一个数组，subfields也是一个数组。当我点击这样的选择时，我会遇到性能问题：

select *
from my_table a
cross join lateral jsonb_array_elements(jsonb_column -> 'dynamicFields') df
cross join lateral jsonb_array_elements(df -> 'subfields') sf
where df ->> 'name' = '200' and sf ->> 'name' = 'a'

性能问题主要存在于subfield中。我已经添加了一个这样的索引：

CREATE INDEX idx_my_index ON my_table USING gin ((marc->'dynamicFields') jsonb_path_ops);

如何在dynamicFields中为subfields添加索引
上面的查询只是一个例子，我经常在与数据库中其他表的联接中使用它。我也知道@>算子。

Answer 1

您已经有了一个非常好的索引来支持您的查询
使用jsonb"；包含"；运算符">@>：

SELECT *
FROM   my_table
WHERE  marc->'dynamicFields' @> '[{"name": "200", "subfields":[{"name": "a"}]}]';

db<gt；小提琴这里

仔细匹配表中JSON对象的结构。然后使用索引廉价地选择行
然后，您可以从符合条件的行中提取所需的任何零件。

详细说明：

• 用于在JSON数组中查找元素的索引

如果其中一个过滤器本身具有很强的选择性，则可能会像原始过滤器一样更快地分离这两个条件。无论哪种方式，两种变体都应该快:

SELECT *
FROM   my_table
WHERE  marc->'dynamicFields' @> '[{"name": "200"}]'
AND    marc->'dynamicFields' @> '[{"subfields":[{"name": "a"}]}]';

Answer 2

索引用于增强表的查询性能。只能对表列执行索引，并考虑到将在表联接中使用的列以及where子句使索引变得重要。对于jsonb列，可以使用gin在table_name上创建索引(column_name，jsonb_path_ops)。