On Wed, May 25, 2011 at 11:59 AM, Pierre C <lists@xxxxxxxxxx> wrote: >> 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. +1 on this approach. it works really well (unless, of course, you need 50 indexes...) merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
