hstore equality-index performance question

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: pgsql-general List <pgsql-general(at)postgresql(dot)org>
Subject: hstore equality-index performance question
Date: 2010-03-28 22:57:20
Message-ID: 25bc040b1003281557l72df790csd818d1b258043744@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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');

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Filip Rembiałkowski 2010-03-28 23:37:20 Re: optimizing import of large CSV file into partitioned table?
Previous Message David Kerr 2010-03-28 22:25:17 Re: Connection Pooling