Обсуждение: hstore equality-index performance question
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');
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>: > 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 >
> 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>: >> 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 >
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