[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: 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_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

Attachment: signature.asc
Description: OpenPGP digital signature

reply via email to

[Prev in Thread] Current Thread [Next in Thread]