7月 5, 2020
Articles
コメントはまだありません

PostgreSQLのLIKE、SIMILAR TO、または正規表現とのパターンマッチング

単純なクエリを作成して、で始まる人の名前を検索する必要がありました。 BまたはD：

SELECT s.name FROM spelers s WHERE s.name LIKE "B%" OR s.name LIKE "D%" ORDER BY 1

これを書き直してパフォーマンスを向上させる方法があるかどうか疑問に思っていたので、および/またはlike？

コメント

  • なぜ書き直し？パフォーマンス？きちんとした？s.nameはインデックス付けされていますか？
  • パフォーマンスのために書きたいのですが、s.nameはインデックス付けされていません。
  • 先行するワイルドカードを使用せずに検索し、追加の列を選択しない場合、パフォーマンスが気になる場合は、nameのインデックスがここで役立ちます。

回答

クエリはほぼ最適です。構文はそれほど短くならず、クエリはそれほど速くなりません：

SELECT name FROM spelers WHERE name LIKE "B%" OR name LIKE "D%" ORDER BY 1;

yの場合本当に構文を短くしたい場合は、ブランチで正規表現を使用します。

... WHERE name ~ "^(B|D).*"

または少し文字クラスを使用すると、より高速になります。

... WHERE name ~ "^[BD].*"

インデックスを使用しないクイックテストでは、どちらの場合も、私にとっては。
適切なBツリーインデックスを設定すると、LIKEがこのレースで桁違いに勝ちます。

マニュアルのパターンマッチングに関する基本事項をお読みください。

優れたパフォーマンスのインデックス

懸念がある場合パフォーマンスを向上させるために、より大きなテーブルに対して次のようなインデックスを作成します。

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

この種のクエリを桁違いに高速化します。ロケール固有のソート順には、特別な考慮事項が適用されます。 オペレータークラスの詳細については、マニュアルをご覧ください。標準の” C “ロケールを使用している場合（ほとんどの人は使用しません）、プレーンインデックス（デフォルトの演算子クラスを使用）は

このようなインデックスは、左に固定されたパターン（文字列の先頭から一致）にのみ適しています。

SIMILAR TOまたは基本的な左アンカー式の正規表現でもこのインデックスを使用できますが、ブランチ(B|D)または文字クラス[BD] 使用できません。 / div>（少なくともPostgreSQL 9.0での私のテストでは）。

トライグラム一致またはテキスト検索では、特別なGINまたはGiSTインデックスを使用します。

パターンマッチング演算子の概要

  • LIKE （ ~~ ）はシンプルです高速ですが、機能が制限されています。
    ILIKE （ ~~* ）大文字と小文字を区別しないバリアント。
    pg_trgmは、両方のインデックスサポートを拡張します。

  • ~ （正規表現の一致）は強力ですが、より複雑であり、基本的な式以外では遅くなる可能性があります。

  • SIMILAR TO は 無意味 です。 LIKEと正規表現の独特な混血。私はそれを決して使用しません。以下を参照してください。

  • ％ は、追加モジュールpg_trgm

類似性”演算子です。 。以下を参照してください。

  • @@ はテキスト検索演算子です。以下を参照してください。

    • pg_trgm-トリグラムマッチング

    PostgreSQL 9.1 拡張機能 pg_trgm を使用して、任意ののインデックスサポートを提供できます。 / em> LIKE / ILIKEパターン（および~を使用した単純な正規表現パターン） GINまたはGiSTインデックス。

    詳細、例、リンク：

    pg_trgmはこれらの演算子も提供します：

    • % -“類似性”演算子
    • <% （通勤者：%>）-” word_similarity “ポストグレス9.6以降の演算子
    • <<% （通勤者：%>>）-” strict_word_similarity ” Postgres11以降の演算子

    テキスト検索

    は、個別のインフラストラクチャとインデックスタイプを使用した特殊なタイプのパターンマッチングです。これは辞書とステミングを使用し、特に自然言語の場合、ドキュメント内の単語を見つけるための優れたツールです。

    プレフィックスマッチング もサポートされています：

    Postgres9.6以降の フレーズ検索 ：

    マニュアルの紹介と演算子と関数の概要。

    あいまい文字列マッチング用の追加ツール

    追加モジュール fuzzystrmatch にはさらにいくつかのオプションがありますが、パフォーマンスは一般に上記のすべてより劣ります。

    特に、さまざまなlevenshtein()関数の実装は役立つ場合があります。

    正規表現（~）が常に

    ？

    答えは簡単です。 SIMILAR TO式は内部で正規表現に書き直されます。したがって、すべてのSIMILAR TO式には、少なくともより高速な正規表現が1つあります（これにより、式を書き換えるオーバーヘッドが節約されます）。 SIMILAR TO ever を使用してもパフォーマンスは向上しません。

    また、LIKE（~~）で実行できる単純な式は、LIKEとにかく。

    SIMILAR TOは、SQL標準の初期ドラフトになってしまったため、PostgreSQLでのみサポートされています。彼らはまだそれを取り除いていません。しかし、それを削除し、代わりに正規表現の一致を含める計画があります-またはそう聞いたのです。

    EXPLAIN ANALYZEはそれを明らかにします。自分で任意のテーブルを試してみてください！

    EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO "B%";

    公開：

    ... Seq Scan on spelers (cost= ... Filter: (name ~ "^(?:B.*)$"::text)

    SIMILAR TOは正規表現（~）で書き直されました。

    この特定のケースの究極のパフォーマンス

    しかし、EXPLAIN ANALYZEはさらに多くのことを明らかにします。前述のインデックスを設定して試してください：

    EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ "^B.*;

    明らかに：

    ... -> Bitmap Heap Scan on spelers (cost= ... Filter: (name ~ "^B.*"::text) -> Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ... Index Cond: ((prod ~>=~ "B"::text) AND (prod ~<~ "C"::text))

    内部的に、ロケールに対応していないインデックス（text_pattern_ops）またはロケール）単純な左アンカー式は、次のテキストパターン演算子で書き直されます：~>=~、~<=~、~>~、~<~。これは~、~~またはSIMILAR TOも同様です。

    varcharタイプのインデックスについても同じことが言えます。 varchar_pattern_opsまたはcharとbpchar_pattern_opsを使用します。

    したがって、適用されます元の質問に対して、これは可能な限り最速の方法です：

    SELECT name FROM spelers WHERE name ~>=~ "B" AND name ~<~ "C" OR name ~>=~ "D" AND name ~<~ "E" ORDER BY 1;

    もちろん、隣接するイニシャルを検索した場合は、さらに簡略化できます。

    WHERE name ~>=~ "B" AND name ~<~ "D" -- strings starting with B or C

    ~または~~を単純に使用した場合のメリットはわずかです。パフォーマンスが最優先の要件ではない場合は、標準の演算子を使用する必要があります。つまり、すでに質問にあるものに到達します。

    コメント

    • OPには’名前のインデックスがありませんが、インデックスがある場合、元のクエリに2つの範囲シークとsimilarスキャン？
    • @MartinSmith：EXPLAIN ANALYZEを使用した簡単なテストでは、2つのビットマップインデックススキャンが示されています。複数のビットマップインデックススキャンは、かなり迅速に組み合わせることができます。
    • ありがとうございます。したがって、ORをUNION ALLに置き換えるか、name LIKE 'B%'をname LIKE 'B%'に置き換えることでマイレージが発生します。 Postgresのdivid = “1522b0f5c1”> ？
    • @MartinSmith：UNIONが勝ちました’ tただし、はい、範囲を1つのWHERE句に組み合わせると、クエリが高速化されます。私は私の答えにもっと追加しました。もちろん、ロケールを考慮に入れる必要があります。ロケール対応の検索は常に遅くなります。
    • @a_horse_with_no_name：そうは思わない。 GINインデックスを使用したpg_tgrmの新機能は、一般的なテキスト検索の扱いです。開始時に固定された検索は、すでにそれよりも高速です。

    回答

    列をに追加するのはどうですかテーブル。実際の要件に応じて：

    person_name_start_with_B_or_D (Boolean) person_name_start_with_char CHAR(1) person_name_start_with VARCHAR(30)

    PostgreSQLはSQLのベーステーブルで計算された列をサポートしていませんサーバーですが、新しい列はトリガーを介して維持できます。明らかに、この新しい列にはインデックスが付けられます。

    または、式のインデックスは、同じ、より安価なものになります。例：

    CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1));

    条件の式に一致するクエリでは、このインデックスを利用できます。

    このように、パフォーマンスヒットはデータの作成または修正時に発生するため、アクティビティの少ない環境（つまり、読み取りよりも書き込みがはるかに少ない）にのみ適している可能性があります。

    回答

    試してみる 

    SELECT s.name FROM spelers s WHERE s.name SIMILAR TO "(B|D)%" ORDER BY s.name

    Postgresで上記の式または元の式のいずれかがsargableであるかどうかはわかりません。

    提案されたインデックスを作成する場合は、その方法についても知りたいと思います。これは他のオプションと比較されます。

    SELECT name FROM spelers WHERE name >= "B" AND name < "C" UNION ALL SELECT name FROM spelers WHERE name >= "D" AND name < "E" ORDER BY name

    コメント

    • それは機能し、コストがかかりました1.19ここで私は1.25を持っていました。ありがとう！

    回答

    同様のパフォーマンスの問題に直面して、過去に行ったことは次のとおりです。最後の文字のASCII文字をインクリメントし、BETWEENを実行します。次に、LIKE機能のサブセットに対して最高のパフォーマンスが得られます。もちろん、これは特定の状況でのみ機能しますが、たとえば名前を検索するような超大規模なデータセットの場合、パフォーマンスがひどいものから許容できるものになります。

    回答

    非常に古い質問ですが、この問題に対する別の迅速な解決策を見つけました：

    SELECT s.name FROM spelers s WHERE ascii(s.name) in (ascii("B"),ascii("D")) ORDER BY 1

    関数ascii（ ）文字列の最初の文字のみを調べます。

    コメント

    • これは(name)？

    回答

    イニシャルのチェックには、（二重引用符付き）。移植性はありませんが、非常に高速です。内部的には、テキストをデトーストして最初の文字を返すだけで、タイプが1バイトの固定長であるため、「char」比較操作は非常に高速です。

    SELECT s.name FROM spelers s WHERE s.name::"char" =ANY( ARRAY[ "char" "B", "D" ] ) ORDER BY 1

    "char"へのキャストは@ Sole021によるascii()ソリューションよりも高速ですが、UTF8互換（またはその他のエンコーディング）ではないことに注意してください。重要なのは、最初のバイトだけを返すため、単純な古い7ビットASCII文字と比較する場合にのみ使用する必要があります。

    回答

    このような場合に対処するには、まだ言及されていない2つの方法があります。

    1. 部分的（またはパーティション化-フルレンジ用に手動で作成された場合）インデックス-次の場合に最も役立ちますデータのサブセットのみが必要です（たとえば、メンテナンス中やレポートの一時的な場合）：

      CREATE INDEX ON spelers WHERE name LIKE "B%"

    2. テーブル自体のパーティション化（最初の文字をパーティショニングキーとして使用）-この手法は特にワートですh PostgreSQL 10以降（パーティション分割の負担が少ない）および11以降（クエリ実行中のパーティションプルーニング）を検討します。

    さらに、テーブル内のデータを並べ替えると、次のことが可能になります。 BRINインデックス（最初の文字の上）を使用するとメリットがあります。

    回答

    1文字の比較を行う方がおそらく高速です：

    SUBSTR(s.name,1,1)="B" OR SUBSTR(s.name,1,1)="D"

    コメント

    • 本当に。 column LIKE 'B%'は、列で部分文字列関数を使用するよりも効率的です。

    コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です