低一点的“喜欢”和“喜欢”

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

低一点

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

我喜欢

... description iLIKE '%abcde%' ...
112860 次浏览
小开

根据我的测试(每个查询的 十) ，LOWER LIKE比 iLIKE快大约2。

解释

我创建了一百万行包含一些随机的混合文本数据:

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

(是的，我在其他测试中多了9行——没问题。)

查询和结果示例:

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

低像的结果

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)
小开
最佳答案

答案取决于许多因素，比如 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上的三元 GIN 索引(通常大于 GiST，但读取速度更快) ，您的查询将使用 description ILIKE 'case_insensitive_pattern'。

相关阅读:

Postgres 模式匹配的基本知识:

当使用上述三角形指数时，使用 一般来说更为实用:

description ILIKE '%abc%'

或者使用不区分大小写的 regexp 操作符(不带 %通配符) :

description ~* 'abc'

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

lower(description) LIKE '%abc%'

反之亦然。

对于 lower(description) 独家上的谓词，表达式索引是稍微好一点的选择。

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

小开

在我的 RailsProject.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的性能


提交答案