Hi all Thank you to all who answered: That worked: CREATE INDEX planet_osm_point_tags_amenity ON planet_osm_point ((tags->'amenity')) WHERE (tags->'amenity') IS NOT NULL; My problem is, that in fact I don't know which tag to index since I'm running a web admin application where users can enter arbitrary queries. Yours, Stefan 2011/5/25 Pierre C <lists@xxxxxxxxxx>: >> 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. > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
