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@xxxxxxxxx>: > On 29 March 2010 02:57, Stefan Keller <sfkeller@xxxxxxxxx> 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@xxxxxxxxx / Skype: gray-hemp / ICQ: 29353802 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
