Re: Index scan with like expressions

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com