Lists: | pgsql-bugs |
---|
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 |
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).
From: | Jaime Casanova <systemguards(at)gmail(dot)com> |
---|---|
To: | Johannes <postgres(at)arltus(dot)de> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #2050: Bad plan by using of LIKE |
Date: | 2005-11-18 14:41:22 |
Message-ID: | c2d9e70e0511180641i4231c16ft9791292911c6379b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On 11/17/05, Johannes <postgres(at)arltus(dot)de> wrote:
>
> 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).
>
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 ;)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Johannes" <postgres(at)arltus(dot)de> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #2050: Bad plan by using of LIKE |
Date: | 2005-11-18 14:42:12 |
Message-ID: | 5891.1132324932@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Johannes" <postgres(at)arltus(dot)de> writes:
> 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)
Apparently you're using a non-C locale. LIKE can only use an index if
you're in the C locale or you make the index with a special index
operator class. See
http://www.postgresql.org/docs/8.0/static/indexes-opclass.html
regards, tom lane
From: | Bernhard Weisshuhn <bkw(at)weisshuhn(dot)de> |
---|---|
To: | Johannes <postgres(at)arltus(dot)de> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #2050: Bad plan by using of LIKE |
Date: | 2005-11-18 14:52:51 |
Message-ID: | 20051118145251.GA10668@weisshuhn.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On Thu, Nov 17, 2005 at 06:00:35PM +0000, Johannes <postgres(at)arltus(dot)de> wrote:
> 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)
You might want to take a look at
http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html
and build your indexes on text fields with *_pattern_ops from now on.
Does the trick.
cheers,
bkw