為什麼 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 索引結合，提供 trigram 索引。這支援所有 LIKE 和 ILIKE 模式，無論通配符位置為何。

進一步考慮

• 字串長度：短於三個字元的索引單字將起作用，但少於三個字元的模式仍可能會觸發完整索引掃描。
• PostgreSQL 11 及更高版本： PostgreSQL 11 引入了 ^@ 運算子和 starts_with() 函數，用於與 SP-GiST 索引進行高效前綴匹配。
• 排序規則和索引：使用 COLLATE "C" 以及針對特定排序規則定制的索引或運算符類別可以優化特定場景的前綴匹配和 LIKE 查詢效能。

以上是為什麼 PostgreSQL LIKE 查詢如此緩慢，以及如何使它們更快？的詳細內容。更多資訊請關注PHP中文網其他相關文章！