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 中国語 Web サイトの他の関連記事を参照してください。