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

From: Jesper Krogh <jesper(at)krogh(dot)cc>
To: pgsql-novice(at)postgresql(dot)org
Subject: "between" is using index but "like" is not
Date: 2004-08-25 13:30:50
Message-ID: cgi4aa$mpn$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)krogh(dot)cc
Jabber ID: jesper(at)jabbernet(dot)dk

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Berkus 2004-08-25 17:38:37 Re: "between" is using index but "like" is not
Previous Message William Yu 2004-08-25 03:09:37 Re: DISTINCT ordering