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 |
Thread: | |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2005-11-18 14:57:56 | Re: BUG #2052: Federal Agency Tech Hub Refuses to Accept Postgresql on Network because of Security Vulnerabilities |
Previous Message | Tom Lane | 2005-11-18 14:42:12 | Re: BUG #2050: Bad plan by using of LIKE |