Обсуждение: Index scan with like expressions

Hi everybody,

I'm having some trouble with index scan using like expressions, using
JDBC, but not in psql where the index is used.

Configuration and tables:
  Postgres 8.1.4


My table "inscriptions":

         Column         |  Type   | Modifiers
------------------------+---------+-----------------------------------------------------------
 id                     | integer | not null default
 code                   | text    | not null
 beings_id              | integer | not null
 inscription_types_t_id | integer | not null
 emitters_t_id          | integer | not null
 date_from              | date    |
 date_to                | date    |
 localizations_id       | integer |
Indexes:
    "inscriptions_pkey" PRIMARY KEY, btree (id)
    "inscriptions_code_ikey" btree (code text_pattern_ops)

My query:
    select beings_id from inscriptions
    where code like '999999';

If I make an explain in psql:
                                        QUERY
PLAN
-------------------------------------------------------------------------------------------
 Index Scan using inscriptions_code_ikey on inscriptions
                 (cost=0.00..6.01 rows=1 width=4)
   Index Cond: (code ~=~ '99999'::text)
   Filter: (code ~~ '99999'::text)


If I do it from my java application with a parameter :
    select beings_id from inscriptions
    where code like ?;


2007-07-17 18:15:46,426 INFO  [isoa.entities.bean.EntitiesApiBean]
EXPLAIN: Seq Scan on inscriptions  (cost=100000000.00..100606290.60
                         rows=141577 width=4)
2007-07-17 18:15:46,426 INFO  [isoa.entities.bean.EntitiesApiBean]
EXPLAIN:   Filter: (code ~~ ($1)::text)


If I do it from my java application explicitly writing my code value
in the sql-string I get the correct plan.

I think I'm missing some important issue here, anyone can give me
a little help.

Thanks in advance
tonioc

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

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.
>