Search Postgresql Archives

Re: hstore equality-index performance question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux