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
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 |