From: Michael Fuhr <mike@fuhr.org>
To: Aditya Damle <aditya@skillnetinc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: LIKE search with ending % not optimized in v8
Date: Mon, 27 Jun 2005 23:16:26 -0600
Message-ID: <20050628051626.GA18240@winnie.fuhr.org> (raw)
In-Reply-To: <20050622090329.34686.qmail@web32910.mail.mud.yahoo.com>
References: <20050622090329.34686.qmail@web32910.mail.mud.yahoo.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, aditya@skillnetinc.com
Subject: Re: LIKE search with ending % not optimized in v8
In-Reply-To: <20050628051626.GA18240@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox