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