Re: How to properly index hstore tags column to faster search for keys

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(at)gmail(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-performance(at)postgresql(dot)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?

On Mon, Jul 8, 2013 at 1:27 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:

On 08/07/13 10:20, Radu-Stefan Zugravu wrote:
>
>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.
>>
>
So you have to call this query 1000 times with different start and end points?
>
>
>> I want to improve this query as much as I can.
>
>
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.
>
>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.
>
>
>
>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.
>>
>
Not important - I was just curious.
>
>
>--
>  Richard Huxton
>  Archonet Ltd
>

--

Radu-Stefan Zugravu0755 950 145
0760 903 464
raduzugravu90(at)gmail(dot)com
radu(dot)zugravu(at)yahoo(dot)com