Re: Like 'name%' is not using index

"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