Re: [GENERAL] question about hstore and indexes
Matteo Beccati wrote: > Hi Andreas, > > On 27/09/2013 09:24, Andreas Kretschmer wrote: > > This query can't use the index: > > > > test=*# explain select * from my_hstore where werte->'key1' = 'val1'; > > QUERY PLAN > > -- > > Seq Scan on my_hstore (cost=100.00..101.06 rows=1 > > width=36) > >Filter: ((werte -> 'key1'::text) = 'val1'::text) > > (2 rows) > > According to the docs: > > hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators > > http://www.postgresql.org/docs/9.3/static/hstore.html Ahh yes, there is no support for =, i understand. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] question about hstore and indexes
Hi Andreas, On 27/09/2013 09:24, Andreas Kretschmer wrote: > This query can't use the index: > > test=*# explain select * from my_hstore where werte->'key1' = 'val1'; > QUERY PLAN > -- > Seq Scan on my_hstore (cost=100.00..101.06 rows=1 width=36) >Filter: ((werte -> 'key1'::text) = 'val1'::text) > (2 rows) According to the docs: hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators http://www.postgresql.org/docs/9.3/static/hstore.html Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question about hstore and indexes
Hi @all, i have this table with some rows: test=*# \d my_hstore; Table "public.my_hstore" Column | Type | Modifiers +-+ id | integer | not null default nextval('my_hstore_id_seq'::regclass) werte | hstore | Indexes: "my_hstore_pkey" PRIMARY KEY, btree (id) "idx_hstore" gist (werte) test=*# select * from my_hstore; id | werte +-- 1 | "key1"=>"val1", "key2"=>"val2" 2 | "key1"=>"val11", "key2"=>"val22" 3 | "key1"=>"val21" 4 | "key3"=>"val3" (4 rows) i have set enable_seqscan to false. This query can't use the index: test=*# explain select * from my_hstore where werte->'key1' = 'val1'; QUERY PLAN -- Seq Scan on my_hstore (cost=100.00..101.06 rows=1 width=36) Filter: ((werte -> 'key1'::text) = 'val1'::text) (2 rows) I have to add this condition: werte ? 'key1' test=*# explain select * from my_hstore where werte ? 'key1' and werte->'key1' = 'val1'; QUERY PLAN - Index Scan using idx_hstore on my_hstore (cost=0.13..8.15 rows=1 width=36) Index Cond: (werte ? 'key1'::text) Filter: ((werte -> 'key1'::text) = 'val1'::text) (3 rows) Why? I'm searching only for werte->'key1' with the condition = 'val1', why i have to add the redundant where-condition? I'm using 9.3., havn't try it with 9.2 or other versions. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general