如何使用 GIN 索引优化 Postgres JSONB 数组查询？

如何在 Postgres 中使用带有 JSONB 数组的 GIN 索引

问题：
查询 JSONB 数组时Postgres，使用 events_gin_idx ON some_table USING 的 GIN 索引GIN（事件）；可能无法优化搜索特定数组元素的查询。

原因：
查询正在使用 jsonb_array_elements(events) AS e 将数组元素转换为 JSON 数组元素，这会破坏索引。

Postgres 12 或的正确索引稍后：
要创建支持路径表达式查询的 GIN 索引，请使用 jsonb_path_ops 运算符类：

CREATE INDEX locations_events_gin_idx ON locations USING gin (events jsonb_path_ops);

Postgres 12 或更高版本的查询语法：
使用@？运算符来测试与条件匹配的数组元素：

SELECT *
FROM locations
WHERE events @? '$[*] ? (@.event_slug == "test_1")
                      ? (@.end_time.datetime() < "2014-10-13".datetime()'

旧版本 Postgres 的查询语法：
对于 Postgres 12 之前的版本，您可以使用 @> ;带有部分 JSON 对象的运算符：

SELECT *
FROM locations
WHERE events @> '[{ "event_slug": "test_1"}]';

使用物化视图的高级解决方案：
如果基本索引解决方案性能不够，请考虑创建物化视图：

CREATE MATERIALIZED VIEW loc_event AS
SELECT l.location_id, e.event_slug, e.end_time  -- start_time not needed
FROM locations l, jsonb_populate_recordset(null::event_type, l.events) e;

CREATE INDEX loc_event_idx ON loc_event (event_slug, end_time, location_id);

物化视图查询：

SELECT *
FROM loc_event
WHERE event_slug = 'test_1'
AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;

以上是如何使用 GIN 索引优化 Postgres JSONB 数组查询？的详细内容。更多信息请关注PHP中文网其他相关文章！