[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Maposmatic-dev] Re: PostGIS help for MapOSMatic
From: |
Emilie Laffray |
Subject: |
Re: [Maposmatic-dev] Re: PostGIS help for MapOSMatic |
Date: |
Mon, 01 Mar 2010 22:18:20 +0000 |
User-agent: |
Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.1.8) Gecko/20100216 Thunderbird/3.0.2 |
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