From: Pierre C <lists@peufeu.com>
To: Stefan Keller <sfkeller@gmail.com>
Cc: mmoncure@gmail.com, pgsql-performance@postgresql.org, "Robert Haas" <robertmhaas@gmail.com>
Subject: Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Date: Wed, 25 May 2011 18:59:51 +0200
Message-ID: <op.vv1qt1fdeorkce@apollo13> (raw)
In-Reply-To: <BANLkTimiphaS_5yBLjr-jDPFjWV3Uc1etA@mail.gmail.com>
References: <BANLkTi=LjFTnn8hLwBZc1c87Vq+pw02+jQ@mail.gmail.com>
<4DD06EF3.7030003@postnewspapers.com.au>
<BANLkTimdGR4X0uMMyMwtvy9Uex-zeMtVCQ@mail.gmail.com>
<BANLkTintNbJZw-hnVpy8q5jLW1__E9BY1w@mail.gmail.com>
<99EECF01B5C87A498318776AB7E4A78D3640F59E26@sid00102.hsr.ch>
<op.vvxkj7o8eorkce@apollo13>
<BANLkTimiphaS_5yBLjr-jDPFjWV3Uc1etA@mail.gmail.com>
> You wrote
>> Try to create a btree index on "(bench_hstore->bench_id) WHERE
>> (bench_hstore->bench_id) IS NOT NULL".
>
> What do you mean exactly?
> => CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps) WHERE
> ??? IS NOT NULL;
>
> My table's def is:
>> CREATE TABLE myhstore ( id bigint PRIMARY KEY, kvps hstore NOT NULL );
> So I'm doing something like:
> CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps);
Hello ;
I meant a plain old btree index like this :
CREATE INDEX foo ON myhstore((kvps->'yourkeyname')) WHERE
(kvps->'yourkeyname') IS NOT NULL;
The idea is that :
- The reason to use hstore is to have an arbitrary number of keys and use
the keys you want, not have a fixed set of columns like in a table
- Therefore, no hstore key is present in all rows (if it was, you'd make
it a table column, and maybe index it)
- You'll probably only want to index some of the keys/values (avoiding to
index values that contain serialized data or other stuff that never
appears in a WHERE clause)
So, for each key that corresponds to a searchable attribute, I'd use a
conditional index on that key, which only indexes the relevant rows. For
keys that never appear in a WHERE, no index is needed.
gist is good if you want the intersecton of a hstore with another one (for
instance), btree is good if you want simple search or range search.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, sfkeller@gmail.com, robertmhaas@gmail.com
Subject: Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
In-Reply-To: <op.vv1qt1fdeorkce@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox