"between" is using index but "like" is not

Поиск
Список
Период
Сортировка
От Jesper Krogh
Тема "between" is using index but "like" is not
Дата
Msg-id cgi4aa$mpn$1@sea.gmane.org
обсуждение исходный текст
Ответы Re: "between" is using index but "like" is not  (Josh Berkus <josh@agliodbs.com>)
Re: "between" is using index but "like" is not  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-novice
I have a table with a text column and I'd like to find entries matching
"pattern*" in that column. When using a between i get:

sd=> explain analyze select id,name, shortname from tr where shortname between  'Run_' and 'RunZ';
                                                               QUERY PLAN
                 

-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tr_shortname_idx on traces  (cost=0.00..37.86 rows=10 width=42) (actual time=0.025..0.025 rows=0
loops=1)
   Index Cond: ((shortname >= 'Run_'::text) AND (shortname <= 'RunZ'::text))
 Total runtime: 0.052 ms
(3 rows)

But when using like:
sd=> explain analyze select id,name, shortname from tr where shortname like 'Run%';
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on tr  (cost=0.00..42379.94 rows=10 width=42) (actual time=988.416..988.416 rows=0 loops=1)
   Filter: (shortname ~~ 'Run%'::text)
 Total runtime: 988.473 ms
(3 rows

Why doesn't it use an index on the "like" operator when it doesn't contain a wildcard in the beginning of the pattern?

Thanks.

--
./Jesper Krogh, jesper@krogh.cc
Jabber ID: jesper@jabbernet.dk


В списке pgsql-novice по дате отправления:

Предыдущее
От: Hisashi Wakai
Дата:
Сообщение: Re: What to download?!! (MacOS X)
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: "between" is using index but "like" is not