BUG #2050: Bad plan by using of LIKE

From: "Johannes" <postgres(at)arltus(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2050: Bad plan by using of LIKE
Date: 2005-11-17 18:00:35
Message-ID: 20051117180035.CE633F0B89@svr2.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 2050
Logged by: Johannes
Email address: postgres(at)arltus(dot)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=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).

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2005-11-17 18:27:19 Re: Huge query stalls at PARSE/BIND stage (1)
Previous Message Mikael Carneholm 2005-11-17 16:55:04 Re: Autovacuum deadlock - bug or not?