如何使用 GIN 索引最佳化 Postgres JSONB 陣列查詢？

如何在Postgres 中使用帶有JSONB 數組的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中文網其他相關文章！