Re: BUG #2050: Bad plan by using of LIKE

On 11/17/05, Johannes <postgres@arltus.de> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      2050
> Logged by:          Johannes
> Email address:      postgres@arltus.de
> PostgreSQL version: 8.0.3
> Operating system:   i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-=
gcc
> (GCC) 3.3.5-20050130 (Gentoo 3.3.5.20050130-r1, ssp-3.3.5.20050130-1,
> pie-8.7.7.1
> Description:        Bad plan by using of LIKE
> Details:
>
> I use this table:
> CREATE TABLE content (
>    title character(64) NOT NULL,
>    content_htm character(128) NOT NULL,
>    id serial NOT NULL
> );
> ALTER TABLE ONLY content ADD CONSTRAINT p_key PRIMARY KEY (id);
> CREATE INDEX idx1 ON content USING btree (title);
>
> I have filling this with 1000000 rows by dbmonster and use this statement,
> after analyze und reindex:
>
> SELECT title FROM content WHERE title LIKE 'teane%';
>
> It uses to long time, I compare this with sybase
> and I was disappointed, but explain shows the reason.
>
>
> EXPLAIN SELECT title FROM content WHERE title LIKE 'teane%';
> Seq Scan on content  (cost=3D0.00..75647.59 rows=3D1 width=3D68)
>   Filter: (title ~~ 'teane%'::text)
>
> Now I change this SQL to:
> SELECT title FROM content WHERE title >=3D 'teane' AND title < 'teanez';
>
> I think it means the same but it works very fast by using my index.
> (1600 ms up to 2 ms !! sybase uses 4 ms)
>
> Explain shows the reason:
>
> EXPLAIN SELECT title FROM content WHERE title >=3D 'teane' AND title <
> 'teanez';
>  Index Scan using idx1 on content  (cost=3D0.00..4.02 rows=3D1 width=3D68)
>   Index Cond: ((title >=3D 'teane'::bpchar) AND (title < 'teanez'::bpchar=
))
>
> I'am not sure is this a bug or not, but without some modifications in the
> postgres.conf the difference are more significant ( 22.530 ms ! to 2 ms).
>

Maybe you are using a non C-locale? they are known to not use indexes
in LIKE querys... instead, you have to create an index with appropiate
class operator...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)