I have OpenStreetMap data for the Netherlands loaded into a PostGIS database (PostgreSQL 8.3 / PostGIS 1.3.3) using the osmosis schema. This means all tags are stored in a hstore field. In addition to the GIST index that osmosis creates on the geometry field, I created an additional GIST index on the tags field.
Trying to query using both a spatial constraint and a constraint on the tags field, I find that it is slower than I would like. A query like this one:
SELECT n.geom,n.tags,n.tstamp,u.name FROM nodes AS n INNER JOIN users AS u ON n.user_id = u.id WHERE tags->'man_made'='surveillance' AND ST_Within(geom, ST_GeomFromText('POLYGON((4.0 52.0,5.0 52.0,5.0 53.0,4.0 53.0,4.0 52.0))',4326));
takes 22 seconds to return 78 records.
There are around 53 million records in this table.
Is there a way to significantly speed this up? I've heard that hstore is implemented significantly better in PostgreSQL 9, would upgrading help?