On 11/17/05, Johannes <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 2050 > Logged by: Johannes > Email address: [EMAIL PROTECTED] > 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=0.00..75647.59 rows=1 width=68) > Filter: (title ~~ 'teane%'::text) > > Now I change this SQL to: > SELECT title FROM content WHERE title >= '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 >= 'teane' AND title < > 'teanez'; > Index Scan using idx1 on content (cost=0.00..4.02 rows=1 width=68) > Index Cond: ((title >= '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 ;) ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
