[OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug

Frederik Ramm frederik at remote.org
Sun Aug 7 14:13:22 BST 2011


Hi,

    this is about a bug in osm2pgsql that will affect you if you

* run diff updates (--slim --append)
* run PostgreSQL 8.4 or 9.0
* use an osm2pgsql SVN revision >= 25198 (2011-01-31) and < 26475 (today)
* are not using non-standard index tablespaces (-i or 
--tablespace-slim-index)

Running diff updates with these PostgreSQL versions requires FASTUPDATE 
to be turned off (see 
http://lists.openstreetmap.org/pipermail/dev/2011-January/021704.html 
for defaults).

I had introduced a fix in r25198 that would automatically switch off 
FASTUPDATE, but only today I discovered that accidentally this was 
conditional to setting an index tablespace; if you didn't, then 
FASTUPDATE was left on which means that your updates will be much slower 
than need be.

To check if your indexes have been created with or without FASTUPDATE, run

pg_dump mydatabase --schema-only | grep -i fastupdate

you should see something like this:

CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) 
WITH (fastupdate=off);
CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) 
WITH (fastupdate=off);

if you don't see anything then your indexes have been created with 
FASTUPDATE set to on (the default), and you will have to follow the 
procedure in the posting quoted above to fix that (or install a new 
osm2pgsql and re-import).

A quick fix without any index regeneration is to set your work_mem in 
postgresql.conf to a ridiculously low value (e.g. 1M instead of 1G) 
which will thwart the FASTINDEX operation and make things run much faster.

Sorry for the cock-up.

Bye
Frederik

PS: If this should prompt you to download a new version of osm2pgsql 
from SVN, be aware that I have recently renamed the old osm2pgsql to 
"osm2pgsql-intarray" and what you will now find in the osm2pgsql 
directory is the new version that works without intarray. You cannot use 
that to update a database that has been created with the old, intarray 
code (it will detect that and abort); but then again to get rid of the 
above fastupdate problem you wold have to do a new import anyway.

-- 
Frederik Ramm  ##  eMail frederik at remote.org  ##  N49°00'09" E008°23'33"



More information about the dev mailing list