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?

asked 22 Mar '11, 16:06

mvexel's gravatar image

mvexel
73281523
accept rate: 0%


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.

permanent link

answered 15 Dec '14, 14:14

rorym's gravatar image

rorym
5.3k144898
accept rate: 11%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×144
×121
×114

question asked: 22 Mar '11, 16:06

question was seen: 5,281 times

last updated: 15 Dec '14, 14:14

powered by OSQA