Postgres で数百万行の JSONB 配列を効率的にクエリするにはどうすればよいですか?

Postgres クエリでの JSONB 演算子と GIN インデックスの利用

Postgres では、JSON 配列要素にアクセスするには、これまでに説明したアプローチとは異なる構文が必要です。 JSON 配列の最初の要素にアクセスするには、以前の試行の代わりに、インデックスがゼロの式を使用します。

このシナリオで推奨されるクエリ構文は次のとおりです。

e->0->>'event_slug'

さらに、Postgres では次のことが可能です。 jsonb データ型の包括的なサポート。 Postgres 12 以降では、jsonb 列を使用した「より大きい」または「より小さい」比較に SQL/JSON パス機能を利用できます。

特定のクエリの場合、推奨されるアプローチは、次を使用して GIN インデックスを作成することです。 jsonb_path_ops 演算子クラス:

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

このインデックスを配置すると、次のクエリはインデックスを利用して効率的なクエリを実行できるようになります。 results:

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

jsonb の直接の「より大きい」または「より小さい」演算子を持たない古いバージョンの Postgres では、マテリアライズド ビューを使用して同様のパフォーマンスを達成できます。これには、関連する属性を正規化された形式で保存することが含まれ、効率的なクエリを実行するための従来の btree インデックスを作成できるようになります。

それぞれに約 10 個のイベントを含む数百万行のユースケースを考慮すると、GIN インデックスとマテリアライズド ビューのアプローチの両方が実行可能です。ソリューション。最適なオプションは、クエリの複雑さ、更新の頻度、望ましいパフォーマンス特性などの要因によって異なります。

以上がPostgres で数百万行の JSONB 配列を効率的にクエリするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。