[OSM-dev] osm2pgsql: diffs involving relations now work also

Martijn van Oosterhout kleptog at gmail.com
Wed Jul 16 07:58:31 BST 2008


On Wed, Jul 16, 2008 at 1:39 AM, Jon Burgess <jburgess777 at googlemail.com> wrote:
> I'm afraid the process to apply the diff has not gone well. I left the
> osm2pgsql process run for 3 hours. During this time the query taking all
> the time was node_changed_mark(). GDB and explain analyze both show that
> this query takes something in the order of 1 minute per node. In the
> whole 3 hours osm2pgsql had only processed 238 lines of the .osc file!

Ok, that's very bad. Out of curiosity, what shared_buffers have you
got on the server (SHOW ALL will tell you).

> It looks like the gist index is not coping well with this data. The
> bitmap index scan above fetches 136147 rows which looks way too high to
> me. In general the number of ways for each node should be very small.
>
> I'm currently trying to building a gin index to see if that does any
> better.

GIN may be better. I avoided it primarily because it only exists in
postgres 8.2+ which isn't the default everywhere. But if that's what
we need, then so be it.

> I suspect that we may need to add 'ways integer[]' into the nodes table
> to efficiently mark the updated ways. What do you think?

The problem being, how do you set that field? Updating that field
while inserting the ways will probably be nearly as expensive as what
we're doing here...

Another way would be to save up the changed nodes and update them all
in one statement. Probably a good idea anyway, but at a minute per
execute, even that won't save you. And if we switched to a btree, we'd
have to rearrange the DB structure which will take at least double the
space it does now...

Thanks for testing this.

Have a nice day,
-- 
Martijn van Oosterhout <kleptog at gmail.com> http://svana.org/kleptog/




More information about the dev mailing list