在 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

我想知道是否有辦法重寫它以提高性能。所以我可以避免or和/或like？

您的查詢幾乎是最佳的。語法不會變得更短，查詢不會變得更快：

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

如果您真的想縮片語法，請使用帶分支的正則表達式：

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

或者稍微快一點，使用字元類：

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

對我來說，沒有索引的快速測試比任何一種情況都產生更快的結果SIMILAR TO。

有了適當的 B-Tree 索引，LIKE就能以數量級贏得這場比賽。

閱讀手冊中有關模式匹配的基礎知識。

卓越性能指數

如果您關心性能，請為更大的表創建這樣的索引以支持左錨搜尋模式（從字元串的開頭匹配）：

CREATE INDEX spelers_name_special_idx ON spelers (name COLLATE "C");

需要在 Postgres 9.1 中添加每列排序規則支持。

看：

• text_pattern_ops 和 COLLATE “C” 之間有區別嗎？
• B-Tree索引好像沒有用？

在使用“C”語言環境（非典型）執行的數據庫中，普通的 B 樹索引可以完成這項工作。

在舊版本中（或者如果你堅持的話，今天仍然如此），你可以使用特殊的操作符類text_pattern_ops來達到同樣的目的：

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

SIMILAR TO或具有基本左錨表達式的正則表達式也可以使用此索引。但不適用於分支(B|D)或字元類[BD]（至少在我對 PostgreSQL 9.0 的測試中）。

Trigram 匹配或文本搜尋使用特殊的 GIN 或 GiST 索引。

模式匹配運算符概述

• LIKE( ~~) 簡單快速，但功能有限。

ILIKE( ~~*) 不區分大小寫的變體。

pg_trgm 擴展了對兩者的索引支持。

• ~（正則表達式匹配）功能強大但更複雜，並且對於基本表達式以外的任何內容都可能很慢。
• SIMILAR TO只是毫無意義。LIKE正則表達式的一種奇特混血兒。我從不使用它。見下文。
• %是附加模組提供的“相似性”運算符pg_trgm。見下文。
• @@是文本搜尋運算符。見下文。

pg_trgm - 三元組匹配

從PostgreSQL 9.1開始，您可以使用 GIN 或 GiST 索引來促進擴展pg_trgm，為任何 LIKE/ILIKE模式（以及帶有 的簡單正則表達式模式）提供索引支持。~

詳細資訊、範例和連結：

• LIKE 是如何實現的？

pg_trgm還提供了這些運算符：

• %- “相似性”運算符
• <%(commutator: %>) - Postgres 9.6 或更高版本中的“word_similarity”運算符
• <<%(commutator: %>>) - Postgres 11 或更高版本中的“strict_word_similarity”運算符

文本搜尋

是一種特殊類型的模式匹配，具有單獨的基礎結構和索引類型。它使用字典和詞幹提取，是在文件中查找單詞的好工具，尤其是對於自然語言。

還支持前綴匹配：

• 從 GIN 索引的 TSVECTOR 列中獲取部分匹配

以及自 Postgres 9.6 以來的片語搜尋：

• 如何在 PostgreSQL 全文搜尋中搜尋連字元？

考慮手冊中的介紹以及運算符和功能的概述。

用於模糊字元串匹配的附加工具

附加模組fuzzystrmatch提供了更多選項，但性能通常不如上述所有選項。

特別地，該levenshtein()功能的各種實現可能是有幫助的。

為什麼正則表達式 ( ~) 總是比 快SIMILAR TO？

答案很簡單。SIMILAR TO表達式在內部被重寫為正則表達式。因此，對於每個SIMILAR TO表達式，至少有一個更快的正則表達式（這樣可以節省重寫表達式的成本）。SIMILAR TO 使用ever沒有性能提升。

無論如何，可以用LIKE( ~~) 完成的簡單表達式更快。LIKE

SIMILAR TO僅在 PostgreSQL 中受支持，因為它最終出現在 SQL 標準的早期草案中。他們仍然沒有擺脫它。但是有計劃將其刪除並包含正則表達式匹配 - 至少我聽說過。

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或使用區域設置C）的索引，使用以下文本模式運算符重寫簡單的左錨定表達式：~>=~, ~<=~, ~>~, ~<~。或類似情況~就是這種情況。~~``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

與普通使用~or相比，收益~~很小。如果性能不是您最重要的要求，您應該堅持使用標準運算符 - 達到您在問題中已有的內容。

引用自：https://dba.stackexchange.com/questions/10694