LIKE可以使用索引吗？ [英] Can LIKE use indexes or not?
问题描述
阅读档案和常见问题解答，似乎暗示LIKE可以
使用索引（而且ILIKE不能;所以要做不区分大小写的搜索你需要
在LOWER（字段）上创建一个功能索引并说：LOWER（field）LIKE
''foo％''）。
但是，EXPLAIN总是说seq扫描我正在使用的测试数据。我已经完成了'b $ b $ set set_seqscan to off''，它仍然说seq scan。我对
很好奇，索引将如何帮助这个查询：
db1 =>将enable_seqscan设置为off;
SET
时间：5.732 ms
db1 =>解释select * from t其中f喜欢''xx％'';
QUERY PLAN
----------------- --------------------------------------------------
Seq扫描t（成本= 100000000.00..100002698.90行= 89宽度= 14）
过滤:( f ~~''xx％'':: text）
（2行）
db1 =>解释select * from t where lower（f）like''xx％'';
QUERY PLAN
-------------- -------------------------------------------------- ----
Seq扫描t（成本= 100000000.00..100002893.68行= 390宽度= 14）
过滤:(下（f）~~''xx ％'':: text）
（2行）
表是：
db1 => ; \d t
表" public.t"
列|输入|修饰语
-------- + ------ + -----------
f |文字|
索引：
" i1" unique，btree（lower（f））
" i2"独特的，btree（f）
它包含+ - 250k行完全随机的10个字符长的字符串
（包含大写和小写字母和号）。这就是LIKE
的表现：
db1 => select * from t其中f喜欢''xx％'';
f
------------
xxEqfLZMkH
xxBRRnLetJ
...
xxFPYJEiYf
（98行）
时间：452.613毫秒
使用索引可能有助于提高此查询的性能，如果是，请使用
，如何强制使用Postgres使用索引？
db1 => select * from t where lower（f）like''mmm％'';
f
------------
MmmyEVmfSY
MMmzolhHtq
...
mMMWEQzlKm
（16行）
时间：634.470毫秒
-
dave
-------- -------------------（广播结束）-------------------------- -
提示4：不要杀了-9''邮政局长
David Garamond说：使用索引可能有助于此查询的性能，如果是，我该如何强制Postgres使用索引？
DB1 => select * from t where lower（f）like''mmm％'';
我怀疑你在列上指定了较低的函数
数据，即lower（f），暗示该函数必须应用于表中的每个
行，以便在测试之前计算该值
条件。
我不知道你能做什么，也不能做PG中的索引方式，
条款根据
列的计算（上/下）值创建索引。
但您可以考虑在表中添加一个额外的列触发器
，以便触发器将列UP或b $ b的UPPER或LOWER版本放入新列。
喜欢搜索然后是
select * from t其中new_upper_f喜欢上层（''MMM％''）;
如果新列上有一个索引new_upper_f，你应该
避免全表扫描。 （我想，我还没有测试过这个）...
John Sidney-Woollett
---------- -----------------（广播结束）---------------------------
提示6：您是否搜索了我们的列表档案？
http://archives.postgresql.org
试试这个：
CREATE [UNIQUE] INDEX my_index ON t（lower（f））;
John Sidney-Woollett写道：
David Garamond说：
使用索引可能有助于此查询的性能，如果是，我如何强制Postgres使用索引？
db1 => ; select * from t where lower（f）like''mmm％'';
我怀疑你在列上指定了较低的函数<数据，即lower（f），表示该函数必须应用于表中的每一行，以便在测试类似条件之前计算该值。
我不知道你能做什么，也不能做PG中的索引方式，在创建基于计算（上/下）值的索引的条件中
列。
但您可以考虑在表格中添加一个额外的列和一个触发器
，以便触发器将列的UP或LOWER版本放入f
新专栏。
喜欢搜索，然后选择*来自t，其中new_upper_f喜欢上层（''MMM％''）;
如果新列new_upper_f上有索引，则应该避免全表扫描。 （我想，我还没有测试过这个）......
John Sidney-Woollett
-------------- -------------（广播结束）---------------------------
提示6：您是否搜索了我们的列表档案？
http：//archives.postgresql .org
---------------------------（广播结束）---------------------------
提示8：解释分析是你的朋友
John Sidney-Woollett说：select * from t其中new_upper_f喜欢上层（''MMM％''）;
我想我的意思
select * from t其中new_upper_f喜欢''MMM％'';
或
选择*来自t其中new_upper_f喜欢上层（''mmm％''）;
John
---------------------------（广播结束）-------- -------------------
提示8：解释分析是你的朋友
Reading the archives and the FAQ, it seems to be implied that LIKE can
use index (and ILIKE can''t; so to do case-insensitive search you need to
create a functional index on LOWER(field) and say: LOWER(field) LIKE
''foo%'').
However, EXPLAIN always says seq scan for the test data I''m using. I''ve
done ''set enable_seqscan to off'' and it still says seq scan. I was
curious as to how the index will help this query:
db1=> set enable_seqscan to off;
SET
Time: 5.732 ms
db1=> explain select * from t where f like ''xx%'';
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on t (cost=100000000.00..100002698.90 rows=89 width=14)
Filter: (f ~~ ''xx%''::text)
(2 rows)
db1=> explain select * from t where lower(f) like ''xx%'';
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on t (cost=100000000.00..100002893.68 rows=390 width=14)
Filter: (lower(f) ~~ ''xx%''::text)
(2 rows)
The table is:
db1=> \d t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
f | text |
Indexes:
"i1" unique, btree (lower(f))
"i2" unique, btree (f)
It contains +- 250k rows of totally random 10-char-long strings
(containing upper- & lowercase letters and numbers). Here''s how the LIKE
performs:
db1=> select * from t where f like ''xx%'';
f
------------
xxEqfLZMkH
xxBRRnLetJ
...
xxFPYJEiYf
(98 rows)
Time: 452.613 ms
Would using an index potentially help the performance of this query, and
if yes, how do I force Postgres to use the index?
db1=> select * from t where lower(f) like ''mmm%'';
f
------------
MmmyEVmfSY
MMmzolhHtq
...
mMMWEQzlKm
(16 rows)
Time: 634.470 ms
--
dave
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
David Garamond said:Would using an index potentially help the performance of this query, and
if yes, how do I force Postgres to use the index?
db1=> select * from t where lower(f) like ''mmm%'';
I suspect the fact that you''re specifying the lower function on the column
data, ie lower(f), implies that the function has to be applied to every
row in the table in order to calculate the value prior to testing the like
condition.
I don''t know enough about what you can and cannot do index-wise in PG, in
terms of creating an index based on a computed (upper/lower) value of a
column.
But you could consider adding an extra column to the table and a trigger
so that the trigger places an UPPER or LOWER version of the column "f"
into the new column.
Like searches would then be
select * from t where new_upper_f like upper(''MMM%'');
Provided that there is an index on the new column, new_upper_f, you should
avoid the full table scan. (I think, I haven''t tested this out)...
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
try this:
CREATE [ UNIQUE ] INDEX my_index ON t ( lower(f));
John Sidney-Woollett wrote:
David Garamond said:Would using an index potentially help the performance of this query, and
if yes, how do I force Postgres to use the index?
db1=> select * from t where lower(f) like ''mmm%'';
I suspect the fact that you''re specifying the lower function on the column
data, ie lower(f), implies that the function has to be applied to every
row in the table in order to calculate the value prior to testing the like
condition.
I don''t know enough about what you can and cannot do index-wise in PG, in
terms of creating an index based on a computed (upper/lower) value of a
column.
But you could consider adding an extra column to the table and a trigger
so that the trigger places an UPPER or LOWER version of the column "f"
into the new column.
Like searches would then be
select * from t where new_upper_f like upper(''MMM%'');
Provided that there is an index on the new column, new_upper_f, you should
avoid the full table scan. (I think, I haven''t tested this out)...
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
John Sidney-Woollett said:select * from t where new_upper_f like upper(''MMM%'');
I think I meant
select * from t where new_upper_f like ''MMM%'';
or
select * from t where new_upper_f like upper(''mmm%'');
John
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
这篇关于LIKE可以使用索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
