Postgres Pro
Downloads
Home   >   mailing lists

Thread: hstore equality-index performance question

hstore equality-index performance question

From
Stefan Keller
Date:
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?

-S.

-- Little test database
CREATE TABLE mytable (
  id serial PRIMARY KEY,
  kvp HSTORE
);
CREATE INDEX mytable_kvp_idx ON mytable USING GIN(kvp);
INSERT INTO mytable (kvp) VALUES ('a=>x, b=>y');
INSERT INTO mytable (kvp) VALUES ('a=>y, c=>z, d=>a');

Re: hstore equality-index performance question

From
Sergey Konoplev
Date:
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

Re: hstore equality-index performance question

From
Stefan Keller
Date:
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
>

Re: hstore equality-index performance question

From
Sergey Konoplev
Date:
> 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

Re: hstore equality-index performance question

From
Stefan Keller
Date:
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>:
>> 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
>

Re: hstore equality-index performance question

From
Tom Lane
Date:
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