Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
Since this seems to be a database oriented question I encourage you to ask on dba.stackexchange.com –  jcolebrand Mar 24 '11 at 16:08

5 Answers 5

up vote 5 down vote accepted

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.

share|improve this answer
2  
Rather than creating a new table, you might consider creating a VIEW instead, that way you "query" is live linked to your original source data without the literal duplication of the data. –  RyanDalton Mar 22 '11 at 18:02
6  
A view will not necessarily improve query performance, unless it is a materialized view or equivalent (see SO question on this topic). I don't believe Postgresql supports materialized views directly, but they can be implemented using triggers. –  Adam Armour Mar 22 '11 at 18:12
2  
This is the workaround I'm currently using. After an update to osmosis tables, I re-create a few tables that are optimized for the queries that I want to run. I just feel there has to be a better way. The topic of triggers intrigues me, and how you could use them to implement material views. @Adam Armour, any chance you could share some insight about this? –  mvexel Mar 22 '11 at 20:26
3  
@mvexel Take a look at this wiki article, which covers the basics of materialized views and details how to implement them in PostgreSQL. –  Adam Armour Mar 22 '11 at 21:02

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));
share|improve this answer
    
Thanks! I did already create that index, only I wasn't using it. It only speeds up certain operations. In PostgreSQL 8.3 (which I am using) it's only @> and ?, in 9.0 it's @>, ?, ?& and ?|. –  mvexel Mar 23 '11 at 12:31
    
For the record, the query using the ? operator took 48 seconds compared to 88 seconds for my query (I don't know how I got 72 seconds yesterday, maybe the machine was doing something complicated this time while I performed the queries). So still not the performance I'm looking for, but I gained a deeper understanding of how the GIST indexes operate on hstore columns. I will still have to go with the other solution of creating a materialized view to get the performance I want. –  mvexel Mar 23 '11 at 12:44

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.