Обсуждение: posgres optimizer not using the index on hstore HELP

I have a simple query select on the hstore attribute "EMAIL_ADDRESS". but the
thing is i'm using a parameter in the query to specify which hstore
attribute I want to access, and when using a parameter the index which I
created is suppressed; whereas when I hard code the attribute
"EMAIL_ADDRESS" then the index is used. this is the index I created:

create index indx_email on contact using btree
((contact_attributes->'EMAIL_ADDRESS'::text));


the query is:

select * from contact where contact_attributes->$parameter =
"fqwefd@hotmail.com';


when I hardcode the attribute like below it the index is used.

select * from contact where contact_attributes->'EMAIL_ADDRESS' =
"fqwefd@hotmail.com';



--
View this message in context:
http://postgresql.nabble.com/posgres-optimizer-not-using-the-index-on-hstore-HELP-tp5912464.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

adam <adhamm31@hotmail.com> writes:
> I have a simple query select on the hstore attribute "EMAIL_ADDRESS". but the
> thing is i'm using a parameter in the query to specify which hstore
> attribute I want to access, and when using a parameter the index which I
> created is suppressed; whereas when I hard code the attribute
> "EMAIL_ADDRESS" then the index is used.

WFM:

regression=# create extension hstore;
CREATE EXTENSION
regression=# create table foo(f1 hstore);
CREATE TABLE
regression=# create index on foo ((f1->'bar'));
CREATE INDEX
regression=# explain select * from foo where f1->'bar' = 'baz';                               QUERY PLAN
                
 
---------------------------------------------------------------------------Bitmap Heap Scan on foo  (cost=4.21..14.37
rows=7width=32)  Recheck Cond: ((f1 -> 'bar'::text) = 'baz'::text)  ->  Bitmap Index Scan on foo_expr_idx
(cost=0.00..4.21rows=7 width=0)        Index Cond: ((f1 -> 'bar'::text) = 'baz'::text)
 
(4 rows)

regression=# prepare p as select * from foo where f1->$1 = $2;         
PREPARE
regression=# explain execute p('bar', 'baz');                               QUERY PLAN

---------------------------------------------------------------------------Bitmap Heap Scan on foo  (cost=4.21..14.37
rows=7width=32)  Recheck Cond: ((f1 -> 'bar'::text) = 'baz'::text)  ->  Bitmap Index Scan on foo_expr_idx
(cost=0.00..4.21rows=7 width=0)        Index Cond: ((f1 -> 'bar'::text) = 'baz'::text)
 
(4 rows)

As a general rule, when you haven't mentioned the PG version you're using
nor provided an *exact* example of what you're doing, it's difficult to
offer useful help.
        regards, tom lane