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(a)gmail.com>
An: Map integration <maps-l(a)lists.wikimedia.org>
CC: Peter Körner <osm-lists(a)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.