Skip site navigation (1) Skip section navigation (2)

Re: LIKE search with ending % not optimized in v8

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Aditya Damle <aditya(at)skillnetinc(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: LIKE search with ending % not optimized in v8
Date: 2005-06-28 05:16:26
Message-ID: 20050628051626.GA18240@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Jun 22, 2005 at 02:03:29AM -0700, Aditya Damle wrote:
>
> Hello. I believe in earlier versions, a query of the
> form 
> select attrib from ttt where attrib like 'foo%' would
> be able to take advantage of an index. I have seen
> this in the past. Currently I am using v8.0.3. From
> what I can see is that the execultion plan seems to
> use a seq scan and to totally ignore the index. Is
> this the case?

8.0.3 can certainly use indexes for LIKE queries, but the planner
will choose a sequential scan if it thinks that would be faster.
Have you vacuumed and analyzed your tables?  Could you post the
EXPLAIN ANALYZE output of a query, once with enable_seqscan turned
on and once with it turned off?

See also "Operator Classes" in the "Indexes" chapter of the
documentation:

http://www.postgresql.org/docs/8.0/static/indexes-opclass.html

What locale are you using?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In response to

pgsql-performance by date

Next:From: Gnanavel ShanmugamDate: 2005-06-28 05:27:14
Subject: Re: How can I speed up this function?
Previous:From: David MitchellDate: 2005-06-28 04:55:00
Subject: Re: How can I speed up this function?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group