Hi, I imported a large area of planet.osm file into a postgresql database using pgsnapshot schema. I need to extract nodes along a line that have certain keys in the tags column. To do that I use the following query:
$geom1 and $geom2 are geometries for start and end points of my line. The query given above works but it is too slow. I guess searching for a key in tags column takes too much time. I read about GIN and GIST indexes and I generated a GIN index using the following query:
After creating the index I searched again for nodes using the same first query but there is no change in performance. How can I properly use GIN and GIST to index tags column so I can faster search for nodes that have a certain key in tags column? Thank you asked 06 Jul '13, 13:37 raduzugravu |
That is the correct way to create an index on tags, and your query looks fine. Make sure you run ANALYZE after creating it. Without any more detail such as EXPLAIN ANALYZE results, it's pretty much impossible to say what's going on. Another option is a composite index, but that really depends on what you're doing. See http://lists.openstreetmap.org/pipermail/osmosis-dev/2013-January/001485.html for more information. answered 13 Jul '13, 10:55 pnorman |
I think this is really a Stack Exchange query: it is really asking about fundamental performing tuning issues for PostgreSQL.
I suspect that when you have a predicate that uses a GIST index then that is the only index which will be used: therefore indexing the tags column is unlikely to give you any benefit whatever index strategy you use. I am no expert on these aspects of PostgreSQL so can't comment further: however, the place to start is with running EXPLAIN on your queries before you do anything else.