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:

SELECT id, tags  
FROM nodes  
WHERE ST_DWithin(nodes.geom, ST_MakeLine('{$geom1}', '{$geom2}'), 0.001)  
AND tags ? '{$type}';

$geom1 and $geom2 are geometries for start and end points of my line.
The $type variable contains the key I want to search for. Now, it can have one of the following values: 'historic' or 'tourist'.

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:

CREATE INDEX nodes_tags_idx ON nodes USING GIN(tags);

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's gravatar image

raduzugravu
316610
accept rate: 0%

3

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.

(06 Jul '13, 15:06) SK53 ♦

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.

permanent link

answered 13 Jul '13, 10:55

pnorman's gravatar image

pnorman
2.4k52140
accept rate: 18%

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:

×168
×14
×13

question asked: 06 Jul '13, 13:37

question was seen: 6,836 times

last updated: 13 Jul '13, 10:55

powered by OSQA