Hi Stefan
1 - If you have a fixed data that does not change a lot, like I assume is your fixed 'map' try implementing in your app the hashtrie method. This looks as better approach as your query is quite fast. Usually I am starting to query my queries (or the query planner) when they start to take more the 2 seconds. The fact that you continuously call it for your next node it might not be the best approach.
2 - As mentioned by Richard, try either to delete the nodes that does not belong to "historic" / "tourist" or simply split the table in 2. One that have only them and the rest to the other table. Assuming this will not change a lot the other already implemented queries in your app (because you'll have to make a 1-to-1 now) it might save your day.
Danny
From: Radu-Stefan Zugravu <raduzugravu90@gmail.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Sent: Monday, July 8, 2013 2:01 PM
Subject: Re: [PERFORM] How to properly index hstore tags column to faster search for keys
I do call the query for each neighbour node to find which one is better in building my path.
I think I will try the first way you mentioned. I also found some references using BTREE indexes:
CREATE INDEX nodes_tags_btree_historic_idx on nodes USING BTREE ((tags ? 'historic'));
CREATE INDEX nodes_tags_btree_tourist_idx on nodes USING BTREE ((tags ? 'tourist));
Do you think this could make a difference?