PostgreSQL の LIKE クエリが非常に遅いのはなぜですか? どうすれば高速化できますか?

PostgreSQL LIKE クエリの最適化: パフォーマンスの問題への対処

部分文字列の一致に使用される

PostgreSQL の LIKE クエリは、ミリ秒から数秒の範囲で予測できないパフォーマンスを示すことがよくあります。 この記事では、根本原因と効果的な最適化戦略について説明します。

パフォーマンスの変動を理解する

パフォーマンスの不一致は、LIKE クエリに固有のリソース要求に起因します。 インデックスを効果的に利用する等価性チェックとは異なり、LIKE クエリでは通常、テーブル全体のスキャンが必要です。

インデックスの制限

B ツリー インデックスはパフォーマンスにとって重要ですが、本質的に LIKE 操作には適していません。 列のインデックス (owner1 など) は完全一致 (SELECT * FROM parcels WHERE owner1 = 'John Doe') を強化しますが、LIKE クエリの利点は限られています。

パフォーマンス向上テクニック

いくつかの手法により、LIKE クエリのパフォーマンスを大幅に向上させることができます。

全文検索 (FTS)

PostgreSQL の FTS は効率的なテキスト検索を提供します。 ただし、LIKE 演算子は直接サポートされておらず、任意の部分文字列ではなく単語を操作します。

プレフィックス マッチングの最適化

先頭にワイルドカードがないパターン (例: 'foo%') の場合は、B ツリー インデックスを備えた text_pattern_ops や varchar_pattern_ops などの演算子クラスを利用します。これらは、左アンカーのパターン マッチングを最適化します。

柔軟なマッチングのためのトライグラムインデックス

pg_trgm 拡張機能は、GIN または GiST インデックスと組み合わせることで、トライグラム インデックスを提供します。これは、ワイルドカードの位置に関係なく、すべての LIKE および ILIKE パターンをサポートします。

さらなる考慮事項

• 文字列長: 3 文字未満のインデックス付き単語は機能しますが、3 文字未満のパターンでも完全なインデックス スキャンがトリガーされる可能性があります。
• PostgreSQL 11 以降: PostgreSQL 11 では、SP-GiST インデックスとの効率的なプレフィックス マッチングのための ^@ 演算子と starts_with() 関数が導入されました。
• 照合順序とインデックス付け: 特定の照合順序に合わせて調整されたインデックスまたは演算子クラスで COLLATE "C" を使用すると、特定のシナリオでのプレフィックス マッチングと LIKE クエリのパフォーマンスを最適化できます。

以上がPostgreSQL の LIKE クエリが非常に遅いのはなぜですか? どうすれば高速化できますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。