'喜欢'拒绝使用索引??? [英] 'like' refuses to use an index???
问题描述
大家好！
我在7.3.4上遇到了一个奇怪的问题。
这是一个简单的测试用例：
rapidb = #create table nametab（name text）;
CREATE TABLE
rapidb =＃name name name_idx on nametab（name）;
CREATE INDEX
rapidb =＃set enable_seqscan = false;
SET
rapidb =＃set enable_sort = false;
SET
rapidb =＃explain select * from nametab其中名称如''blah％'';
QUERY PLAN
------------------------------------------------ ------------------------
Seq Scan on nametab（cost = 100000000.00..100000022.50 rows = 5 width = 32）
过滤:(名字~~'''blah％''::文字）
（2行）
rapidb = ＃explain name from nametab，其名称如''blah％''按名称排序;
QUERY PLAN
------------- -------------------------------------------------- -----------
使用name_id进行索引扫描名称框上的x（费用= 0.00..54.50行= 5宽= 32）
过滤:(名称~~''blah％'':: text）
（ 2行）
请参阅 - 第一个查询想要使用seqscan，即使我是明确的
告诉它不要。
第二个查询确实使用索引进行排序（好），但仍然没有
条件。
有没有人知道这里有什么问题？
我非常感谢...
非常感谢！
迪玛。
PS我不觉得这与桌子是空的有什么关系 -
首先这只是一个简单的测试用例，我真正的桌子有大约120美元bbb百万行（我刚刚在几分钟前对它进行了分析）....
问题似乎只是''喜欢'' - 如果用''='替换''like'' ''在
上面的查询然后它*将*使用索引，即使表是
仍然是空的
---- -----------------------（播出结束）---------------------- -----
提示2：您可以使用取消注册命令一次性取消所有列表
（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）
Hi, everybody!
I just ran into a weird problem on 7.3.4.
Here is a simple testcase:
rapidb=# create table nametab (name text);
CREATE TABLE
rapidb=# create index name_idx on nametab(name);
CREATE INDEX
rapidb=# set enable_seqscan=false;
SET
rapidb=# set enable_sort=false;
SET
rapidb=# explain select * from nametab where name like ''blah%'';
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on nametab (cost=100000000.00..100000022.50 rows=5 width=32)
Filter: (name ~~ ''blah%''::text)
(2 rows)
rapidb=# explain select * from nametab where name like ''blah%'' order by name;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using name_idx on nametab (cost=0.00..54.50 rows=5 width=32)
Filter: (name ~~ ''blah%''::text)
(2 rows)
See - the first query wants to use seqscan, even though I am explicitly
telling it not to.
The second query does use the index for sorting (good), but still not
for the condition.
Does anyone have any idea what could be wrong here?
I''d greatly appreciate that...
Thanks a lot!
Dima.
P.S. I don''t think this has anything to do with the table being empty -
first of all this is just a simple testcase, my real table has about 120
million rows (and I just analyzed it a few minutes ago).... also the
problem seems to only be with ''like'' - if you replace ''like'' with ''='' in
the above query then it *will* use the index, even though the table is
still empty
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
推荐答案
Dima Tkach< dm **** @ openratings.com>写道：
Dima Tkach <dm****@openratings.com> writes:
有没有人知道这里有什么问题？
Does anyone have any idea what could be wrong here?
你在C语言环境中没有initdb ...
问候，汤姆小巷
----------------------- ----（广播结束）---------------------------
提示1：订阅和取消订阅命令转到 ma*******@postgresql.org
You didn''t initdb in C locale ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
Tom Lane写道：
Tom Lane wrote:
Dima Tkach< dm **** @ openratings.com>写道：
Dima Tkach <dm****@openratings.com> writes:
有谁知道这里有什么不对吗？
Does anyone have any idea what could be wrong here?
你没有C语言环境中的initdb ...
问候，tom lane
You didn''t initdb in C locale ...
regards, tom lane
哎哟！
有什么方法可以修复它而不重新创建数据库？
另外，你确定吗？因为文本比较运算符确实很好......
名称如''blah％''不起作用，但名称> = ''blah''和名字< ''blai''
*确实* ......这些也不依赖于这些地方吗？
非常感谢你的帮助！
迪玛
--------------------------- （广播结束）---------------------------
提示1：订阅和取消订阅命令转到< a href =mailto：ma ******* @ postgresql.org> ma ******* @ postgresql.org
Ouch!
Is there any way to fix that without recreating the database?
Also, are you sure about this? Because the text comparison operators do
seem to work fine...
name like ''blah%'' does not work, but name >= ''blah'' and name < ''blai''
*does*... aren''t these locale-dependent too?
Thanks a lot for your help!
Dima
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
引用Dima Tkach< dm **** @ openratings.com>：
Quoting Dima Tkach <dm****@openratings.com>:
Tom Lane写道：
Tom Lane wrote:
Dima Tkach< dm **** @ openratings.com>写道：
Dima Tkach <dm****@openratings.com> writes:
有谁知道这里有什么不对吗？
Does anyone have any idea what could be wrong here?
你没有在C语言环境中的initdb ...
问候，tom lane
You didn''t initdb in C locale ...
regards, tom lane
哎哟！
有没有办法解决这个问题而不重建数据库？还有，你确定吗？因为文本比较运算符似乎工作得很好......
名称''blah％''不起作用，但名称> ='''blah''和名称< ; ''blai''
*确实* ......这些也不依赖于这些地方吗？
非常感谢你的帮助！
迪马<
---------------------------（广播结束）----------- ----------------
提示1：订阅和取消订阅命令转到 ma*******@postgresql.org
我也想知道这个因为我注意到和通配符一样使用
似乎与正则表达式类似，因为索引未被使用。
这就是我所拥有的......
ethernet = #select version（）;
版
--------------------------- -------------------------------------------
i6486-pc-linux-gnu上的PostgreSQL 7.4，由GCC gcc（GCC）编译3.2.2
（1行）
ethernet =＃\\ \\ d供应商
表public.vendors
列|输入|修饰符
--------- + ----------------------- + ------- ----
标题|字符（6）|
公司|字符变化（80）|
索引：
" vender_id_idx" btree（标题）
ethernet =＃explain select *来自卖家的标题如''000423'';
QUERY PLAN
--- -------------------------------------------------- ----------------------------
使用vender_id_idx对供应商进行索引扫描（成本= 0.00..113.26行） = 36宽度= 68）
指数条件:( header ='''000423'':: bpchar）
过滤:(标题~~'''000423''::文本）
（3行）
好的，这是有道理的 -
ethernet =＃explain select * from vendor where header比如''％000423％'';
查询计划
----------------------- -----------------------------------
Seq扫描供应商（成本= 0.00..151.15行= 3宽度= 68）
过滤:(标题~~''％000423％''::文字）
（2行）
这个没有意义，直到我做到了......
ethernet =＃explain select * from vendor where headers like''- 0004％ '';
查询计划
--------------------------------------------- ------------------------------------
使用vender_id_idx对供应商进行索引扫描（成本= 0.00..113.26行= 36宽度= 68）
指数条件：（（标题> =''0004'':: bpchar）AND（标题< ''0005'':: bpchar））
过滤:(标题~~''0004％''::文字）
（3行）
由于标题的大小而且两者都有意义 -
ethernet =＃explain select * from vendors where header~ *''0004 '';
查询计划
----------------------------- ------------------------------
Seq扫描供应商（成本= 0.00..151.15行= 58宽度= 68）
过滤:(标题〜*''0004''::文字）
（2行）
ethernet =＃explain select * from vendor where headers〜*''000423'';
QUERY PLAN
----------- -----------------------------------------------
Seq扫描供应商（成本= 0.00..151.15行= 3宽度= 68）
过滤:( header~ *''000423'':: text）
（2行）
按顺序扫描，这意味着正则表达式不使用索引。这是
对吗？
-
Keith C. Perry，MS EE
网络与管理总监申请
VCSN，Inc。
http://vcsn.com
____________________________________
此电子邮件帐户由以下人员主持：
VCSN，Inc： http://vcsn.com
---------- -----------------（广播结束）---------------------------
提示7：别忘了增加免费空间地图设置
I wanted to know this too because I notice that using like with wildcards
appears to be similar to a regular expression in that the index is not used.
This is what I have...
ethernet=# select version();
version
----------------------------------------------------------------------
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
(1 row)
ethernet=# \d vendors
Table "public.vendors"
Column | Type | Modifiers
---------+-----------------------+-----------
header | character(6) |
company | character varying(80) |
Indexes:
"vender_id_idx" btree (header)
ethernet=# explain select * from vendors where header like ''000423'';
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 width=68)
Index Cond: (header = ''000423''::bpchar)
Filter: (header ~~ ''000423''::text)
(3 rows)
Ok, that made sense-
ethernet=# explain select * from vendors where header like ''%000423%'';
QUERY PLAN
----------------------------------------------------------
Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68)
Filter: (header ~~ ''%000423%''::text)
(2 rows)
This didn''t make sense until I did...
ethernet=# explain select * from vendors where header like ''0004%'';
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 width=68)
Index Cond: ((header >= ''0004''::bpchar) AND (header < ''0005''::bpchar))
Filter: (header ~~ ''0004%''::text)
(3 rows)
which again made sense because of the header''s size but both-
ethernet=# explain select * from vendors where header ~* ''0004'';
QUERY PLAN
-----------------------------------------------------------
Seq Scan on vendors (cost=0.00..151.15 rows=58 width=68)
Filter: (header ~* ''0004''::text)
(2 rows)
ethernet=# explain select * from vendors where header ~* ''000423'';
QUERY PLAN
----------------------------------------------------------
Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68)
Filter: (header ~* ''000423''::text)
(2 rows)
are sequentially scanned which means that regex''s do not use indexes. Is that
right also?
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings
这篇关于'喜欢'拒绝使用索引???的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
