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

[Emilie Laffray]

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

and more precisely in the case statement. In this case, it is difficult
to know what is going on as you are rebuilding the area in the line.
I don't think the case statement is such a good idea in the first place.
There are only 26594 rows but it is taking a very long time to process
which is not normal. Doing && is usually not necessary with
ST_Intersects. In addition, ST_intersects received some improvement
starting with postgis 1.4 thanks to prepared geometry.
Also as I mentioned earlier, you may want to your ST_Transform in a
different place as retroprojection can be quite costly.

Emilie Laffray

signature.asc
Description: OpenPGP digital signature