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 <raduzugrav...@gmail.com> To: Richard Huxton <d...@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? On Mon, Jul 8, 2013 at 1:27 PM, Richard Huxton <d...@archonet.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 raduzugrav...@gmail.com radu.zugr...@yahoo.com
