Hi, do you have (good) news from our proposals for perf improvements ? regards
2010/3/1 Vincent de Chateau-Thierry <[email protected]>: > > > Emilie Laffray a écrit : >> >> On 01/03/2010 18:37, Thomas Petazzoni wrote: >> >>> >>> Hum, I'll need to have a look at PostgreSQL stored procedures, because >>> these things are new for me (I'm a database newbie). >>> >>> Using the result of explain sent by David Mentré, do you have a more >>> detailed analysis of the problem ? >>> >> >> While stored procedures can provide some improvement in terms of speed, >> they won't make much a difference in your case here. Most of the time >> spent is done while doing a join >> Nested Loop Left Join (cost=0.00..133531.07 rows=2920 width=26) (actual >> time=259.745..305070.067 rows=26594 loops=1) >> Filter: CASE WHEN >> (st_buildarea(public.planet_osm_line.way) IS NULL) THEN true ELSE >> ((public.planet_osm_line.way && st_buildarea(public.planet_osm_line.way)) >> AND _st_intersects(public.planet_osm_line.way, >> st_buildarea(public.planet_osm_line.way))) END >> >> > > A quick test would be to replace the "create or replace view > cities_area_by_name as..." with a "create table cities_area_by_name" > statement followed by a GIST index creation as mentionned yesterday. The > st_buildarea part will be computed only 1 time and the result used many > times. Another part to optimize / drop is the nested loop in itself but I > have no suggestions at this time. > > vincent > -- Jean-Christophe Arnu
