Re: Index scan with like expressions

Thanks heikki, for your quick and very precise answer.

I understood the problem soon after sending the e-mail, but
I didn't know nothing about the prepareThreshold=0 parameter.

thanks again
tonio

On Wed, 2007-07-18 at 09:41 +0100, Heikki Linnakangas wrote:
> Tonio Caputo wrote:
> > I'm having some trouble with index scan using like expressions, using
> > JDBC, but not in psql where the index is used.
> > ...
> > My query:
> > select beings_id from inscriptions
> > where code like '999999';
> > ...
> > If I do it from my java application explicitly writing my code value
> > in the sql-string I get the correct plan.
>
> Index can only be used for a LIKE expression if there's no % or _ in the
> beginning of the string. For example, "LIKE 'foobar'" and "LIKE 'foo%'"
> can use the index, looking for the string 'foobar', or anything that
> begins with 'foo'. But for expressions like "LIKE '%bar'", the index
> can't be used.
>
> If you use a parameter marker, "LIKE ?", the planner doesn't know if the
> string you're going to give as parameter is such that the index can be
> used, so it has no choice but choose a plan that works regardless of the
> parameter value, which is a seq scan in this case.
>
> You can use prepareThreshold=0 connection parameter to disable
> server-side prepared statements, so that the query is planned every time
> it's executed. That way the planner can check the parameter value each
> time, and use the index when possible.
>