From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | adam <adhamm31(at)hotmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: posgres optimizer not using the index on hstore HELP |
Date: | 2016-07-19 22:06:50 |
Message-ID: | 16532.1468966010@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
adam <adhamm31(at)hotmail(dot)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=7 width=32)
Recheck Cond: ((f1 -> 'bar'::text) = 'baz'::text)
-> Bitmap Index Scan on foo_expr_idx (cost=0.00..4.21 rows=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=7 width=32)
Recheck Cond: ((f1 -> 'bar'::text) = 'baz'::text)
-> Bitmap Index Scan on foo_expr_idx (cost=0.00..4.21 rows=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
From | Date | Subject | |
---|---|---|---|
Next Message | Herby Raynaud | 2016-07-20 21:55:34 | Wiindow Aggregate function |
Previous Message | Steve Midgley | 2016-07-19 22:04:02 | Re: posgres optimizer not using the index on hstore HELP |