forwarding to dev,
we're talking about a standard osm2pgsql setup. Does anyone know what's going on with the "pending" index?
Peter
-------- Original-Nachricht -------- Betreff: Re: [Maps-l] OSM Toolserver - spatial_ref_sys Datum: Mon, 22 Feb 2010 16:19:02 +0000 Von: Kai Krueger kakrueger@gmail.com An: Map integration maps-l@lists.wikimedia.org CC: Peter Körner osm-lists@mazdermind.de
On 22/02/10 12:39, Peter Körner wrote:
So we're nearly done, we're sth. around one hour behind the main DB.
Great, but again it seems rather slow.
The problem is, that the "Going over pending ways / relations" phase takes too long for small window sizes.
Importing 2 Minutes of changes takes around 15 seconds for the "Processing: Node(k) Way(k) Relation(k)" phase and 5 minutes for the "Going over pending.." phase. That's just too long.
The "Going over pending.." phase amongst others executes a SELECT id FROM planet_osm_ways WHERE pending; which seems to require a seq_scan on planet_osm_ways.
However, a single seq scan on ways seems to take on the order of 8 minutes in my test.
Likewise, seq_scans on the other tables also take very long. A sec_scan on planet_osm_point takes about a minute, on planet_osm_polygon about 3 minutes and on planet_osm_line it seems to take over ten minutes for a single seq_scan. Given that for low zoom levels (before the spatial indices kick in), there are several queries per tile requiring full table scans, this doesn't look good for rendering performance. I don't have any comparison numbers from other servers that have the full planet loaded to see if that is normal, but perhaps we can do some comparisons with cassini?
Kai
P.S. there does seem to be an index on "pending" but trying to run some queries from psql, it didn't seem to be used.
I'm constantly adjusting the window size to find the best match between runtime and window size but as I'd like to have a 1 minute window, as we have it on cassini, we'll need to tweak a little somewhere.