6

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column called tags. 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

  • 2
    The query is "too slow" but explain analyze in the comments says it takes 2.3ms. What duration do you expect? – Daniel Vérité Jul 7 '13 at 21:24
4

You could try indexing the keys like in the following examples and see how that works:

CREATE INDEX nodes_tags_key_historic_idx on nodes ( (tags->'historic') );
CREATE INDEX nodes_tags_key_tourist_idx on nodes ( (tags->'tourist') );
ANALYZE nodes;

What does your query plan look like when run with EXPLAIN ANALYZE?

[edit]: Alternative indexes:

CREATE INDEX nodes_tags_key_tourist_idx2 on nodes ( (tags ? 'tourist') );
CREATE INDEX nodes_tags_key_historic_idx2 on nodes ( (tags ? 'historic') );

The latter shouldn't require any changes to your query.

  • Comments are not for extended discussion; this conversation has been moved to chat. – Paul White Aug 29 '17 at 10:13
  • ...ON nodes((tags->'historic')) worked for me. And ...USING GIN(tags) didn't. – Kjetil S. Jan 10 at 8:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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