Обсуждение: question about hstore and indexes

Hi @all,

i have this table with some rows:

test=*# \d my_hstore;
                         Table "public.my_hstore"
 Column |  Type   |                       Modifiers
--------+---------+--------------------------------------------------------
 id     | integer | not null default
nextval('my_hstore_id_seq'::regclass)
 werte  | hstore  |
Indexes:
    "my_hstore_pkey" PRIMARY KEY, btree (id)
    "idx_hstore" gist (werte)


test=*# select * from my_hstore;
 id |              werte
----+----------------------------------
  1 | "key1"=>"val1", "key2"=>"val2"
  2 | "key1"=>"val11", "key2"=>"val22"
  3 | "key1"=>"val21"
  4 | "key3"=>"val3"
(4 rows)



i have set enable_seqscan to false.


This query can't use the index:

test=*# explain select * from my_hstore where werte->'key1' = 'val1';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on my_hstore  (cost=10000000000.00..10000000001.06 rows=1 width=36)
   Filter: ((werte -> 'key1'::text) = 'val1'::text)
(2 rows)


I have to add this condition: werte ? 'key1'

test=*# explain select * from my_hstore where werte ? 'key1' and werte->'key1' = 'val1';
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using idx_hstore on my_hstore  (cost=0.13..8.15 rows=1 width=36)
   Index Cond: (werte ? 'key1'::text)
   Filter: ((werte -> 'key1'::text) = 'val1'::text)
(3 rows)


Why? I'm searching only for werte->'key1' with the condition = 'val1', why i have to add
the redundant where-condition?

I'm using 9.3., havn't try it with 9.2 or other versions.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Hi Andreas,

On 27/09/2013 09:24, Andreas Kretschmer wrote:
> This query can't use the index:
>
> test=*# explain select * from my_hstore where werte->'key1' = 'val1';
>                                   QUERY PLAN
> ------------------------------------------------------------------------------
>  Seq Scan on my_hstore  (cost=10000000000.00..10000000001.06 rows=1 width=36)
>    Filter: ((werte -> 'key1'::text) = 'val1'::text)
> (2 rows)

According to the docs:

hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators

http://www.postgresql.org/docs/9.3/static/hstore.html


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Matteo Beccati <php@beccati.com> wrote:

> Hi Andreas,
>
> On 27/09/2013 09:24, Andreas Kretschmer wrote:
> > This query can't use the index:
> >
> > test=*# explain select * from my_hstore where werte->'key1' = 'val1';
> >                                   QUERY PLAN
> > ------------------------------------------------------------------------------
> >  Seq Scan on my_hstore  (cost=10000000000.00..10000000001.06 rows=1 width=36)
> >    Filter: ((werte -> 'key1'::text) = 'val1'::text)
> > (2 rows)
>
> According to the docs:
>
> hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators
>
> http://www.postgresql.org/docs/9.3/static/hstore.html

Ahh yes, there is no support for =, i understand.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°