performance - 较低的喜欢与 iLIKE

Question

以下两个查询组件的性能比较如何？

点赞

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

我喜欢

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

Answer 1

答案取决于许多因素，例如 Postgres 版本、编码和语言环境 - LC_COLLATE特别是。

裸表达式 lower(description) LIKE '%abc%' 通常比 description ILIKE '%abc%' 快一点，两者都快一点比等效的正则表达式:description ~* 'abc'。这对于必须为每个测试行评估表达式的顺序扫描很重要。

但是对于像您在回答中展示的大表，肯定会使用索引。对于任意模式(不仅是左锚定的)，我建议使用附加模块 pg_trgm 的三元组索引。 .然后我们讨论毫秒而不是秒，并且上述表达式之间的差异被取消。

GIN 和 GiST 索引(使用 gin_trgm_ops 或 gist_trgm_ops 运算符类)支持 LIKE (~~) 、ILIKE(~~*)、~、~*(以及更多变体)。在 description 上使用 trigram GIN 索引(通常比 GiST 大，但读取速度更快)，您的查询将使用 description ILIKE 'case_insensitive_pattern'。

相关:

• PostgreSQL LIKE query performance variations
• Similar UTF-8 strings for autocomplete field

Postgres 中模式匹配的基础知识:

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

当使用所述三元组索引时，通常更实用:

description ILIKE '%abc%'

或者使用不区分大小写的正则表达式运算符(没有 % 通配符):

description ~* 'abc'

(description) 上的索引不支持对 lower(description) 的查询，例如:

lower(description) LIKE '%abc%'

反之亦然。

对于 lower(description) exclusively 的谓词，表达式索引是更好的选择。

在所有其他情况下，(description) 上的索引更可取，因为它支持区分大小写和不区分大小写的谓词。