Query planner issue

Hi there,

I am currently running Postgres 8.3.1.

I've got a table called DETAILS, the primary key is : DE_ID char(12),
there is another field CO_ID char (12).
DE_ID and CO_ID are indexed with a btree. This table is about 140
millions of records.

If I execute an "explain select * from details where co_id =
'010076015372';" it uses the index. Here follows the plan:

"Index Scan using idx_co_id on details (cost=0.00..34.37 rows=2
width=741)"
" Index Cond: ((co_id)::bpchar = '010076015372'::bpchar)"

If I run "explain analyze select * from details where co_id || co_id =
'0100760153722324445';" it runs a sequential scan not using the index
(which takes about 1000000 times than using the index):

Seq Scan on details (cost=0.00..8755139.52 rows=819131 width=741)
Filter: (((co_id)::text || (co_id)::text) =
'010076015372010076015372'::text)

Same thing if I try to trick it using a view or a stored procedure.
Query planner is not easy to trick!

My question is : is there a way to use the index or isn't it possible?

Thanks in advance.

Andrea Moretto

Andrea Moretto
moretto(dot)andrea(at)gmail(dot)com
-------------------------------------------------------
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message.
-------------------------------------------------------