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=10000000000.00..10000000001.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°