hstore equality-index performance question

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

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>:

My question is, if one can get also index support for the '->' operator?

I am not sure what do you mean.

 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?

These queries could return completely different result sets. First
query returns all the records with the value of kvp->'a' if kvp has
'a' key and NULL otherwise. Second one returns only those records
where kvp has 'a' key.

--
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

You are right, my negligence.

I'm trying to optimize the latter query:
# SELECT id, (kvp->'a') FROM mytable WHERE kvp ? 'a';

...or something like this (which also involves the '->' operator)
# SELECT id FROM mytable WHERE (kvp->'a') = 'x';

-S.

2010/3/29 Sergey Konoplev <gray.ru@gmail.com>:

Stefan Keller <sfkeller@gmail.com> writes:

I'm trying to optimize the latter query:
# SELECT id, (kvp->'a') FROM mytable WHERE kvp ? 'a';

The hstore gist and gin opclasses contain support for that.

...or something like this (which also involves the '->' operator)
# SELECT id FROM mytable WHERE (kvp->'a') = 'x';

You could transform this into a gist/gin indexable query

kvp @> ('a' => 'x')

although I think the actually indexed part of it is just the search for
rows that contain key 'a', so it's not really any better than

kvp ? 'a' AND (kvp->'a') = 'x'

performance-wise.

regards, tom lane