Re: [Maposmatic-dev] Re: PostGIS help for MapOSMatic

[Vincent de Chateau-Thierry]

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