Re: hstore equality-index performance question

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(dot)ru(at)gmail(dot)com>:
> On 29 March 2010 02:57, Stefan Keller <sfkeller(at)gmail(dot)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(at)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(at)localhost test=#
> CREATE INDEX i_hstore_partial_index_table__h_a ON
> hstore_partial_index_table (id) WHERE h ? 'a';
> CREATE INDEX
>
> postgres(at)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(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802
>