较低的喜欢与iLIKE

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

较低的喜欢

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

iLIKE

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

答案取决于许多因素，例如 Postgres 版本、编码和区域设置 -LC_COLLATE尤其。

裸露的表情lower(description) LIKE '%abc%'通常比description ILIKE '%abc%'，并且任一都比等效的正则表达式快一点：description ~* 'abc'。这对于顺序扫描很重要，其中必须对每个测试行计算表达式。

But对于像您在答案中演示的大表，肯定会使用索引。对于任意模式（不仅仅是左锚定），我建议使用附加模块的三元组索引pg_trgm。然后我们讨论毫秒而不是秒，并且上述表达式之间的差异无效。

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

Related:

• PostgreSQL LIKE 查询性能变化
• 自动完成字段的类似 UTF-8 字符串

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

• 使用 LIKE、SIMILAR TO 或正则表达式进行模式匹配

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

description ILIKE '%abc%'

或者使用不区分大小写的正则表达式运算符（不带%通配符）：

description ~* 'abc'

上的索引(description)不支持查询lower(description) like:

lower(description) LIKE '%abc%'

反之亦然。

谓词 onlower(description) 只，表达式索引是稍微好一点的选择。

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