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
