[解決済み] LOWER LIKE vs iLIKE

質問

次の2つのクエリコンポーネントのパフォーマンスはどのように比較されますか？

LOWER LIKE

... LOWER(description) LIKE '%abcde%' ...

アイライク

... description iLIKE '%abcde%' ...

解決方法は？

答えは、Postgres のバージョン、エンコーディング、ロケールなど多くの要因に依存します -。 LC_COLLATE 特に

裸の表現 lower(description) LIKE '%abc%' は通常 description ILIKE '%abc%' また、同等の正規表現よりも若干速くなります。 description ~* 'abc' . これは、テストされた行ごとに式を評価する必要があるシーケンシャルスキャンでは重要です。

しかし あなたの回答で示されたような大きなテーブルでは、確かにインデックスを使用します。任意のパターン（左寄せだけでなく）については、私は追加モジュールを使用したtrigramインデックスをお勧めします。 pg_trgm . そうすると、秒ではなくミリ秒の話になり、上記の表現の違いは無効化されます。

GINとGiSTインデックス（を使用します。 gin_trgm_ops または gist_trgm_ops 演算子クラス) をサポートします。 LIKE ( ~~ ), ILIKE ( ~~* ), ~ , ~* (およびいくつかのより多くのバリエーション)を同様にします。トリグラムGINインデックスで description (一般的にGiSTより大きいが、読み込みが速い)クエリは、以下のようになります。 description ILIKE 'case_insensitive_pattern' .

関連する

• PostgreSQL LIKE クエリのパフォーマンスのバリエーション
• オートコンプリートフィールドの類似UTF-8文字列

Postgresのパターンマッチの基本。

• PostgreSQLにおけるLIKE、SIMILAR TO、正規表現によるパターンマッチング

前記トリグラムインデックスを扱う場合、それは 通常 の方が実用的です。

description ILIKE '%abc%'

また、大文字・小文字を区別しない正規表現演算子で( % のワイルドカードを使用します)。

description ~* 'abc'

のインデックスを作成します。 (description) に対するクエリはサポートしません。 lower(description) のようなものです。

lower(description) LIKE '%abc%'

その逆もしかり。

に対する述語で lower(description) 専ら の場合、エクスプレッションインデックスの方が若干有利です。

他のすべてのケースでは (description) をサポートするため、より望ましい。 両方とも 大文字小文字を区別する述語としない述語がある。