Thank you Sergey for your reply.
I'm not sure how your partial index makes a difference. Obviously the
? operator gets indexed:
# EXPLAIN SELECT id, (kvp->'a') FROM mytable WHERE kvp ? 'a';
Index Scan using mytable_kvp_idx on mytable (cost=0.00..8.27 rows=1 width=36)
Index Cond: (kvp ? 'a'::text)"
My question is, if one can get also index support for the '->' operator?
-S.
2010/3/29 Sergey Konoplev <gray.ru@gmail.com>:
> On 29 March 2010 02:57, Stefan Keller <sfkeller@gmail.com> wrote:
>> Documentation at "F.13.3. Indexes" says that "hstore has index support
>> for @> and ? operators..."
>> => Therefore no index does support equality-indexes?
>>
>> If so, then I suppose that following (potentially slow) query
>> which contains an equality test for all keys 'a' and returns all values...
>>
>> SELECT id, (kvp->'a') FROM mytable;
>>
>> ... can be accelerated nevertheless by adding following where clause:
>>
>> SELECT id, (kvp->'a') FROM mytable WHERE kvp ? 'a';
>>
>> => Is this correct?
>>
>
> May be you are looking for something like this?
>
> postgres@localhost test=#
> CREATE TABLE hstore_partial_index_table (id serial PRIMARY KEY, h hstore);
> NOTICE: CREATE TABLE will create implicit sequence
> "hstore_partial_index_table_id_seq" for serial column
> "hstore_partial_index_table.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "hstore_partial_index_table_pkey" for table
> "hstore_partial_index_table"
> CREATE TABLE
>
> postgres@localhost test=#
> CREATE INDEX i_hstore_partial_index_table__h_a ON
> hstore_partial_index_table (id) WHERE h ? 'a';
> CREATE INDEX
>
> postgres@localhost test=#
> EXPLAIN SELECT * FROM hstore_partial_index_table WHERE h ? 'a';
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------
> Index Scan using i_hstore_partial_index_table__h_a on
> hstore_partial_index_table (cost=0.00..8.27 rows=1 width=36)
> (1 row)
>
>
> --
> Sergey Konoplev
>
> Blog: http://gray-hemp.blogspot.com /
> Linkedin: http://ru.linkedin.com/in/grayhemp /
> JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
>
