LOWER LIKE与iLIKE的区别

Question

LOWER LIKE与iLIKE的区别

performancepostgresqlpattern-matchingdatabase-performance

83

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

LOWER LIKE

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

iLIKE

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

- user664833

1

你的目标是什么？你有一个缓慢的SQL命令想要加速吗，还是只是关于PostgreSQL的一般性问题？ - Martin Strejc

Stack Overflow目前还没有（或许以后会有？）#hashtags（除非你在问C或C++问题）。 - user529758

3

我的目的是就PostgreSQL提出一个一般性问题。在这两个现成的解决方案中做出选择时，我想知道应该使用哪一个。 - user664833

3个回答

38

根据我的测试（每个查询测试10次），LOWER LIKE 比 iLIKE 大约快 17%。说明我创建了一百万行包含一些随机混合文本数据的数据：

require 'securerandom'
inserts = []
1000000.times do |i|
        inserts << "(1, 'fake', '#{SecureRandom.urlsafe_base64(64)}')"
end
sql = "insert into books (user_id, title, description) values #{inserts.join(', ')}"
ActiveRecord::Base.connection.execute(sql)

验证行数：

my_test_db=# select count(id) from books ;
  count  
---------
 1000009

(是的，我有来自其他测试的九个额外行 - 这不是问题。)

查询示例和结果：

my_test_db=# SELECT "books".* FROM "books" WHERE "books"."published" = 'f'
my_test_db=# and (LOWER(description) LIKE '%abcde%') ;
   id    | user_id | title |                                      description                                       | published 
---------+---------+-------+----------------------------------------------------------------------------------------+------
 1232322 |       1 | fake  | 5WRGr7oCKABcdehqPKsUqV8ji61rsNGS1TX6pW5LJKrspOI_ttLNbaSyRz1BwTGQxp3OaxW7Xl6fzVpCu9y3fA | f
 1487103 |       1 | fake  | J6q0VkZ8-UlxIMZ_MFU_wsz_8MP3ZBQvkUo8-2INiDIp7yCZYoXqRyp1Lg7JyOwfsIVdpPIKNt1uLeaBCdelPQ | f
 1817819 |       1 | fake  | YubxlSkJOvmQo1hkk5pA1q2mMK6T7cOdcU3ADUKZO8s3otEAbCdEcmm72IOxiBdaXSrw20Nq2Lb383lq230wYg | f

LOWER LIKE的结果

my_test_db=# EXPLAIN ANALYZE SELECT "books".* FROM "books" WHERE "books"."published" = 'f' and (LOWER(description) LIKE '%abcde%') ;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Seq Scan on books  (cost=0.00..32420.14 rows=1600 width=117) (actual time=938.627..4114.038 rows=3 loops=1)
   Filter: ((NOT published) AND (lower(description) ~~ '%abcde%'::text))
   Rows Removed by Filter: 1000006
 Total runtime: 4114.098 ms

iLIKE的结果

my_test_db=# EXPLAIN ANALYZE SELECT "books".* FROM "books" WHERE "books"."published" = 'f' and (description iLIKE '%abcde%') ;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Seq Scan on books  (cost=0.00..29920.11 rows=100 width=117) (actual time=1147.612..4986.771 rows=3 loops=1)
   Filter: ((NOT published) AND (description ~~* '%abcde%'::text))
   Rows Removed by Filter: 1000006
 Total runtime: 4986.831 ms

数据库信息泄露

Postgres版本：

my_test_db=# select version();
                                                                                 version
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.4 on x86_64-apple-darwin12.4.0, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00), 64-bit

排序设置：

my_test_db=# select datcollate from pg_database where datname = 'my_test_db';
 datcollate  
-------------
 en_CA.UTF-8

表定义：

my_test_db=# \d books 
                                      Table "public.books"
   Column    |            Type             |                       Modifiers
-------------+-----------------------------+-------------------------------------------------------
 id          | integer                     | not null default nextval('books_id_seq'::regclass)
 user_id     | integer                     | not null
 title       | character varying(255)      | not null
 description | text                        | not null default ''::text
 published   | boolean                     | not null default false
Indexes:
    "books_pkey" PRIMARY KEY, btree (id)

- user664833

7

您的测试用例是片面的，数据中只有大写字母。此外，在实际应用中，您将使用索引进行操作，这会改变整个评估。而且，重要的细节未公开：Postgres版本、您的排序设置和确切的表定义。 - Erwin Brandstetter

1

@ErwinBrandstetter 我重新运行了测试，并更新了我的答案以反映混合大小写的数据。我还添加了有关我的数据库的详细信息。LOWER``LIKE仍然比iLIKE快约17％（从25％下降）。 - user664833

4

现在好多了。我不会说“x比y快17%”，因为这只对特定的测试用例成立。字符串的长度也是相关的，顺便提一下。 - Erwin Brandstetter

1

@ErwinBrandstetter - 我知道精确的百分比结果会有所不同，但我也认为仅仅说“x比y快”太过笼统。我认为通过你的评论，那些足够好奇的人将会得到更完整的图片。顺便问一下，您是否知道字符串长度、排序设置或其他条件，会导致iLIKE始终优于LOWER LIKE？ - user664833

2

不确定。我经常看到更接近的结果。在Postgres 9.1中对一个真实的表进行了快速测试，该表有105万行和真实的“描述”，COLLATON de_AT.UTF-8，操作系统为Debian Linux。LOWER / LIKE比其他方法快约2％。 - Erwin Brandstetter

显示剩余2条评论

3

在我的 Rails 项目中，ILIKE 比 LOWER LIKE 快近 10 倍，我在 entities.name 列上添加了一个 GIN 索引。

> Entity.where("LOWER(name) LIKE ?", name.strip.downcase).limit(1).first
Entity Load (2443.9ms)  SELECT  "entities".* FROM "entities" WHERE (lower(name) like 'baidu') ORDER BY "entities"."id" ASC LIMIT $1  [["LIMIT", 1]]

> Entity.where("name ILIKE ?", name.strip).limit(1).first
Entity Load (285.0ms)  SELECT  "entities".* FROM "entities" WHERE (name ilike 'Baidu') ORDER BY "entities"."id" ASC LIMIT $1  [["LIMIT", 1]]

# explain analyze SELECT  "entities".* FROM "entities" WHERE (name ilike 'Baidu') ORDER BY "entities"."id" ASC LIMIT 1;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3186.03..3186.04 rows=1 width=1588) (actual time=7.812..7.812 rows=1 loops=1)
   ->  Sort  (cost=3186.03..3187.07 rows=414 width=1588) (actual time=7.811..7.811 rows=1 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 26kB
         ->  Bitmap Heap Scan on entities  (cost=1543.21..3183.96 rows=414 width=1588) (actual time=7.797..7.805 rows=1 loops=1)
               Recheck Cond: ((name)::text ~~* 'Baidu'::text)
               Rows Removed by Index Recheck: 6
               Heap Blocks: exact=7
               ->  Bitmap Index Scan on index_entities_on_name  (cost=0.00..1543.11 rows=414 width=0) (actual time=7.787..7.787 rows=7 loops=1)
                     Index Cond: ((name)::text ~~* 'Baidu'::text)
 Planning Time: 6.375 ms
 Execution Time: 7.874 ms
(12 rows)

GIN索引对于提高ILIKE的性能非常有帮助。

- lfx_cool

2

在 name 上的索引不支持对 lower(name) 的查询。我在我的答案中提到了这一点。这很可能是性能差异10倍的原因。（您将看到顺序扫描而不是（位图）索引扫描。） - Erwin Brandstetter

@ErwinBrandstetter，谢谢你。我刚在我的项目中学到了这个知识点，所以我把它放在这里以展示索引如何改变性能规则。 :) - lfx_cool

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

答案取决于许多因素，如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'。