[解決済み] PostgreSQLのLIKEクエリのパフォーマンスのばらつき

質問

について、レスポンスタイムに大きなばらつきがあることが分かっています。 LIKE クエリに関する応答時間に大きなばらつきがあります。200 ～ 400 ミリ秒 (非常に許容範囲内) で結果が得られることもあれば、結果を返すのに 30 秒もかかることもあります。

私は以下を理解しています。 LIKE クエリは非常にリソースを消費しますが、なぜ応答時間にこれほど大きな差が生じるのか理解できません。私は owner1 フィールドに btree インデックスを構築しましたが、これは LIKE クエリでは役に立たないと思います。どなたかアイデアをお持ちですか？

サンプルSQLです。

SELECT gid, owner1 FORM parcels
WHERE owner1 ILIKE '%someones name%' LIMIT 10

もやってみた。

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10

そして

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10

同様の結果で

テーブルの行数：約95,000。

どのように解決するのですか？

FTSがサポートしていない LIKE

は 前回承認された回答 は不正解でした。 全文検索 は、そのフルテキストインデックスで ではなく に対して LIKE 演算子を全く使用せず、独自の演算子を持っており、任意の文字列に対して動作しません。演算するのは 単語 を辞書とステミングに基づいて操作します。それは は サポート 単語のプレフィックスマッチング をサポートしていますが LIKE 演算子には適用されません。

• GINインデックス付きTSVECTORカラムから部分一致を得る

のトリグラムインデックス LIKE

追加モジュールのインストール pg_trgm の演算子クラスを提供する GINとGiSTの三文型インデックス をサポートするために すべて LIKE と ILIKE パターン のように、左アンカーだけでなく

インデックスの例です。

CREATE INDEX tbl_col_gin_trgm_idx  ON tbl USING gin  (col gin_trgm_ops);

または

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

• GiSTインデックスとGINインデックスの違い

クエリ例です。

SELECT * FROM tbl WHERE col LIKE '%foo%';   -- leading wildcard
SELECT * FROM tbl WHERE col ILIKE '%foo%';  -- works case insensitively as well

三文判は？短い文字列はどうですか？

のある単語 3文字以下 を含む単語は、インデックスされた値として機能します。 マニュアルに

各単語は、文字列に含まれるトリグラムのセットを決定する際に、2つのスペースが前置され、1つのスペースが後置されているとみなされます を含むとみなされます。

3文字以下の検索パターンも？ マニュアルを

両者とも LIKE と正規表現検索の両方において、抽出可能な三文型を持たない はフルインデックススキャンになることに留意してください。

インデックス/ビットマップインデックススキャンはまだ動作しますが（プリペアドステートメントのクエリプランは壊れません）、より良いパフォーマンスを得ることはできません。1 文字または 2 文字の文字列はほとんど選択的ではなく (基礎となるテーブルの数パーセント以上が一致)、インデックス サポートはそもそもパフォーマンスを改善しないため、一般的に大きな損失はありません。

text_pattern_ops または COLLATE "C" 接頭辞マッチングの場合

更新情報

Postgres 9.1以降。 COLLATE "C" の方が優れています。参照してください。

• text_pattern_opsとCOLLATE "C"は違うのですか？

オリジナルの回答

ただ 左寄せ パターン (先頭のワイルドカードなし) であれば、最適な 演算子クラス で最適になります。 text_pattern_ops または varchar_pattern_ops . どちらも標準的なPostgresの組み込み機能で、追加モジュールは必要ありません。同じような性能ですが、インデックスがかなり小さくなります。

インデックスの例です。

CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);

クエリの例です。

SELECT * FROM tbl WHERE col LIKE 'foo%';  -- no leading wildcard

または でデータベースを動作させる場合は 'C' ロケール (事実上 はありません。 ロケール) の場合、いずれにせよすべてはバイトオーダーに従ってソートされ、デフォルトの演算子クラスを持つ単純な btree インデックスがその役割を果たします。

さらに読む

• PostgreSQL における LIKE, SIMILAR TO あるいは正規表現によるパターン・マッチング
• LIKE はどのように実装されていますか？
• PostgreSQLで類似の文字列を素早く見つけるには？