以下两个查询组件的性能如何比较？
比较喜欢
... LOWER(description) LIKE '%abcde%' ...
我喜欢
... description iLIKE '%abcde%' ...
根据我的测试(每个查询中有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)
