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(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 Zugravu
0755 950 145
0760 903 464
raduzugravu90(at)gmail(dot)com
radu(dot)zugravu(at)yahoo(dot)com