LOWER LIKE vs iLIKE

本文介绍了LOWER LIKE vs iLIKE的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

How does the performance of the following two query components compare?

低价

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

iLIKE

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

推荐答案

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

The answer depends on many factors like Postgres version, encoding and locale - LC_COLLATE in particular.

简单表达式 lower（description）LIKE'％abc％'是通常比 description ILIKE'％abc％'快一点，或者比等价的正则表达式快一点： description〜*'abc' 。这对于顺序扫描很重要，在顺序扫描中，必须为每个测试行评估表达式。

The bare expression lower(description) LIKE '%abc%' is typically a bit faster than description ILIKE '%abc%', and either is a bit faster than the equivalent regular expression: description ~* 'abc'. This matters for sequential scans where the expression has to be evaluated for every tested row.

但是对于像您在答案中所示的大表肯定会使用索引。对于任意模式（不仅是锚定的），我建议使用附加模块 pg_trgm 。然后我们谈论的是毫秒而不是秒，并且上述表达式之间的差异为零。

But for big tables like you demonstrate in your answer one would certainly use an index. For arbitrary patterns (not only left-anchored) I suggest a trigram index using the additional module pg_trgm. Then we talk about milliseconds instead of seconds and the difference between the above expressions is nullified.

GIN和GiST索引（使用 gin_trgm_ops 或 gist_trgm_ops 运算符类）支持 Like （ ~~ ）， ILIKE （ ~~ * ），〜，〜* （以及更多其他变体）。在说明上使用Trigram GIN索引（通常比GiST大，但读取速度更快），查询将使用 description ILIKE'case_insensitive_pattern'。

GIN and GiST indexes (using the gin_trgm_ops or gist_trgm_ops operator classes) support LIKE (~~), ILIKE (~~*), ~, ~* (and some more variants) alike. With a trigram GIN index on description (typically bigger than GiST, but faster for reads), your query would use description ILIKE 'case_insensitive_pattern'.

相关：

• PostgreSQL LIKE查询性能差异
• 相似的UTF-8字符串用于自动填充字段

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

Postgres中模式匹配的基础：

Basics for pattern matching in Postgres:

• 与LIKE，SIMILAR TO或PostgreSQL中的正则表达式

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

在使用上述Trigram索引时，通常 更加实用：

When working with said trigram index it's typically more practical to work with:

description ILIKE '%abc%'

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

Or with the case-insensitive regexp operator (without % wildcards):

description ~* 'abc'

（说明）上的索引不支持对 lower（说明）的查询，例如：

An index on (description) does not support queries on lower(description) like:

lower(description) LIKE '%abc%'

反之亦然。

谓词在 lower（description） 排他上，表达式索引是

With predicates on lower(description) exclusively, the expression index is the slightly better option.

在所有其他情况下，最好使用（说明）上的索引，因为它支持 区分大小写和不区分大小写的谓词。

In all other cases, an index on (description) is preferable as it supports both case-sensitive and -insensitive predicates.