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 Zugravu 0755 950 145 0760 903 464 raduzugrav...@gmail.com radu.zugr...@yahoo.com
