From: | "Pierre C" <lists(at)peufeu(dot)com> |
---|---|
To: | "Stefan Keller" <sfkeller(at)gmail(dot)com> |
Cc: | mmoncure(at)gmail(dot)com, pgsql-performance(at)postgresql(dot)org, "Robert Haas" <robertmhaas(at)gmail(dot)com> |
Subject: | Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation) |
Date: | 2011-05-25 16:59:51 |
Message-ID: | op.vv1qt1fdeorkce@apollo13 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Reuven M. Lerner | 2011-05-25 16:59:58 | Speeding up loops in pl/pgsql function |
Previous Message | panam | 2011-05-25 16:42:29 | Re: Hash Anti Join performance degradation |