"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
Re: "between" is using index but "like" is not |
Список | 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 по дате отправления: