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

[Vincent de Chateau-Thierry]

Hi all,

Jean-Christophe Arnu a écrit :
> Hi Thomas,
> could you run the (long running) queries with explain/explain analyze
> and post the results on the wiki ?
>
> Clue: EXPLAIN SELECT ...;
> or EXPLAIN ANALYZE SELECT ...;
>
> The information given by explain are quite useful to optimize queries.
>
>
>   
Maybe some suggestions below. I have not tested any of them yet since I 
do not have any PostGis base at home.

- before the run : using a table instead of a view for 
'cities_area_by_name' may be of interest in order to build a dedicated 
spatial index for city boundaries ? If this table/view is built only one 
time for each base update (daily) the time taken may be of interest,

- during the single city run, as explained by Thomas here : 
http://wiki.maposmatic.org/doku.php?id=dev:request_optimization there is 
a significant performance issue involving the "st_intersects(way, 
cities_area_by_name.area)" part of the query. A possible cause would be 
a large scan of the planet_osm_line in order to select the ways located 
inside the Toulouse_or_whatever_city  polygon without usage of a spatial 
index. You should already have a spatial index on the planet_osm_line 
table, but despite the note regarding indexes here :
http://postgis.refractions.net/documentation/manual-1.4/ST_Intersects.html
I have sometimes encountered problems with st_intersects where indexes 
were not taken into account.
A way to ensure that spatial indexes are used is to add a "&&" part to 
the query maybe like this (2nd line added) :

where trim(name) != '' and highway is not null
and way && cities_area_by_name.area
and case
when cities_area_by_name.area is null
then true
else st_intersects(way, cities_area_by_name.area)
end)

Hopte this helps,

vincent