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

[Jean-Christophe Arnu]

Hi,
do you have (good) news from our proposals for perf improvements ?

regards

2010/3/1 Vincent de Chateau-Thierry <address@hidden>:
>
>
> 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