'like' refuses to use an index???
От | Dima Tkach |
---|---|
Тема | 'like' refuses to use an index??? |
Дата | |
Msg-id | 3FF1D5B5.6060603@openratings.com обсуждение исходный текст |
Ответы |
Re: 'like' refuses to use an index???
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: