agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedposgres optimizer not using the index on hstore HELP
3+ messages / 3 participants
[nested] [flat]
* posgres optimizer not using the index on hstore HELP
@ 2016-07-18 15:18 adam <adhamm31@hotmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: adam @ 2016-07-18 15:18 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: posgres optimizer not using the index on hstore HELP
@ 2016-07-19 22:04 Steve Midgley <science@misuse.org>
parent: adam <adhamm31@hotmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Midgley @ 2016-07-19 22:04 UTC (permalink / raw)
To: adam <adhamm31@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: posgres optimizer not using the index on hstore HELP
@ 2016-07-19 22:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: adam <adhamm31@hotmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2016-07-19 22:06 UTC (permalink / raw)
To: adam <adhamm31@hotmail.com>; +Cc: pgsql-sql
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=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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-07-19 22:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-07-18 15:18 posgres optimizer not using the index on hstore HELP adam <adhamm31@hotmail.com>
2016-07-19 22:04 ` Re: posgres optimizer not using the index on hstore HELP Steve Midgley <science@misuse.org>
2016-07-19 22:06 ` Re: posgres optimizer not using the index on hstore HELP Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox