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@xxxxxxxxx>
To: Richard Huxton <dev@xxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Sent: Monday, July 8, 2013 2:01 PM
Subject: Re: 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?
On Mon, Jul 8, 2013 at 1:27 PM, Richard Huxton <dev@xxxxxxxxxxxx> wrote:
On 08/07/13 10:20, Radu-Stefan Zugravu wrote:So you have to call this query 1000 times with different start and end points?
Any improvement is welcomed. The overall performance of the application
is not very good. It takes about 200 seconds to compute a path for not
so far star and end points.
There's only two ways I can see to get this much below 20ms. This will only work if you want a very restricted range of tags.
> I want to improve this query as much as I can.
Drop the tag index and create multiple geometry indexes instead:
CREATE INDEX node_geo_tourist_idx <index details> WHERE tags ? 'tourist';
CREATE INDEX node_geo_tourist_idx <index details> WHERE tags ? 'history';
etc.
This will only work if you have a literal WHERE clause that checks the tag. It should be fast though.
The second way would be to delete all the nodes that aren't tagged tourist or history. That assumes you are never interested in them of course.Not important - I was just curious.
How exactly should I post the explain without the index? Do I have to
drop all created indexes for the tags column? It takes some time to
create them back.
--
Richard Huxton
Archonet Ltd
Radu-Stefan Zugravu
0755 950 1450760 903 464
raduzugravu90@xxxxxxxxx
radu.zugravu@xxxxxxxxx
