Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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
add comment

1 Answer

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.

share|improve this answer
    
thank you for your comment. I indexed the way you told but there are no improvements. EXPLAIN ANALYZE returns the following: "Index Scan using idx_nodes_geom on nodes (cost=0.00..107.41 rows=1 width=17) (actual time=2.935..2.935 rows=0 loops=1)" " Index Cond: (geom && '0103000...'::geometry)" " Filter: ((tags ? '{$type}'::text) AND ('0102000...'::geometry && st_expand(geom, 0.001::double precision)) AND _st_dwithin(geom, '0102000...'::geometry, 0.001::double precision))" " Rows Removed by Filter: 114" "Total runtime: 2.992 ms" –  Radu-Stefan Zugravu Jul 7 '13 at 19:06
    
After creating nodes_tags_key_historic_idx and nodes_tags_key_tourist_idx do I have to rewrite my query in order to use thoses indexes or the query remains the same? –  Radu-Stefan Zugravu Jul 7 '13 at 19:11
    
@Radu-StefanZugravu try the original query - you will see if it needs modification. –  dezso Jul 7 '13 at 19:31
1  
1  
@CraigRinger The application I work on computes a path bettween a start and end points. For that I use A* (A Star) routing algorithm. I started by finding the shorthest path and the execution time was about 14-15 seconds. After I changed the algorithm to find the path which maximises nodes with keys like 'historic' or 'tourism' the execution time increased at about 200 seconds. The only modification I did was to add the query mentioned above. The query executes every time I choose the best next neighbour in my path. 200 seconds are way too much so any improvements are welcomed. –  Radu-Stefan Zugravu Jul 7 '13 at 23:28
show 13 more comments

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.