Speeding up OpenStreetMap PostGIS querying

Question

I asked this question on gis.stackexchange.com as well, but hope to catch some more expertise here.

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?

Answer 1

I've never optimised a hstore column, so I can't speak to that. If you split out the man_made value into a separate column, you can add a WHERE clause to the geom index. I've seen big speed ups from that. i.e. CREATE INDEX blah ON nodes using gist (geom) WHERE man_made = 'surveillance'. You'll now have 2 geometry indexes, one on the geom, and a subset that only has rows where man_made = surveillance. IME PostgreSQL is smart enough to know to use the smaller index, which means scanning the index will be faster.

Speeding up OpenStreetMap PostGIS querying

Related questions