'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 по дате отправления:

Предыдущее
От: "D. Dante Lorenso"
Дата:
Сообщение: Re: Is my MySQL Gaining ?
Следующее
От: Jason Godden
Дата:
Сообщение: Re: Improving/Adding to Docs (was Is my MySQL Gaining ? or someone missed a date with a dolphin)