Speeding up OpenStreetMap PostGIS querying

Question

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

One method would be to query for the tags you are interested in and place those records in a new table. Then you will only need to query the new table instead of all 53 million records. If you are trying to keep your database updated, you could have this query run every time you get new data from OSM.

Answer 2

You can try to create an index for your hstore column,

CREATE INDEX nodes_tags_idx ON nodes USING GIST(tags)

and then use the ? operator to limit the query to only that rows:

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'
  AND 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));

Answer 3

The st_within and _st_within functions are not known for their speed. The && operator might help as it will check bbox instead of geometry

You might try the following:

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'
  AND tags->'man_made'='surveillance' 
  AND geom && ST_SetSRID('BOX3D(4 52,5 53)'::box3d,4326);

For more performance tips check: http://postgis.refractions.net/docs/ch06.html

Answer 4

The problem with your query is the tags->'man_made'='surveillance' clause. This forces Postgres to expand the tags hstore and doesn't allow it to make use of the index. If you rewrite this using @> (contains) it will allow index usage.

Because you're querying a rectangle, you can use && instead of ST_Within. This will have a small gain, as the ST_Within isn't that complicated to evaluate, and ST_Within implicitly does a && check.

An additional speed increase would be to use a GIN index on tags instead of a GIST index. GIN indexes take longer to build but are faster.

The entire query would be

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 @> hstore('man_made', 'surveillance') AND 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);

If you know you're going to be querying a particular tag a lot you can create a partial index on it with CREATE INDEX ON nodes ( tags->'man_made' ) WHERE (tags->'man_made' IS NOT NULL);.

This will allow the WHERE condition tags->'man_made'='surveillance' to use the index. Unfortunately, that index can't help @> queries and the GIN or GIST indexes can't help tags->'foo' queries, so you have to match the queries to the indexes you have.

Answer 5

try this instead:

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'::hstore 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));