Re: hstore equality-index performance question

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