Thread: 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.
On Mon, Jul 18, 2016 at 8:18 AM, adam <adhamm31@hotmail.com> wrote:
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';
Maybe you can share an EXPLAIN ANALYZE for both queries?
Steve
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