From: Greg Stark <gsstark@mit.edu>
To: Jozsef Szalay <jszalay@storediq.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Like 'name%' is not using index
Date: 02 Mar 2006 23:01:39 -0500
Message-ID: <877j7cuqfw.fsf@stark.xeocode.com> (raw)
In-Reply-To: <E387E2E9622FDD408359F98BF183879E312F05@dc1.storediq.com>
References: <E387E2E9622FDD408359F98BF183879E312F05@dc1.storediq.com>
"Jozsef Szalay" <jszalay@storediq.com> writes:
> One would
> think that Postgres will use the index to look up the matches, but
> apparently that is not the case. It performs a full table scan. My
> query looks something like this:
>
> SELECT * FROM table WHERE name LIKE 'smith%';
There are two possible answers here:
First, what does this output on your database?
db=> show lc_collate;
If it's not "C" then the index can't be used. You would have to make a second
special-purpose index specifically for use with LIKE.
Secondly, please send "explain analyze" output for your query. It will show if
the optimizer is simply estimating that the index won't help enough to be
faster than the full table scan.
--
greg
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: gsstark@mit.edu, jszalay@storediq.com
Subject: Re: Like 'name%' is not using index
In-Reply-To: <877j7cuqfw.fsf@stark.xeocode.com>
* 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