[OSM-dev] Patch to osm2pgsql for faster updates

[Erik Burrows]

In my 8.4 database, when I re-create the planet_osm_ways_nodes and
planet_osm_rels_parts indexes with fastupdate=off, diff import times go
from being progressively slower after a vacuum, to being consistent in
terms of import time, accounting for the variation of diff size
hour-to-hour.

This is the same positive change in behavior I saw when setting
fastupdate=off in 9.0.2. 

My 8.4 db, on a SATA disk is now just better than 1:1 for applying
hourly diffs, and my 9.0.2 db on SSD is about 4:1. (diff size to apply
time)

-Erik

> 8.3 didn't have the fastupdate function at all, so I would imagine it is
> possible that the fastupdate introduction in 8.4 was the problem, since
> it is on by default in 8.4 and 9.0.
> 
> I still have my 8.4 database, so I started it up and am re-creating the
> GIN indexes with fastupdate=off, to see if that fixes the problem in 8.4
> also.
> 
> It's on my SATA drive, so it will take about a day to complete. I'll let
> you know on Monday.
> 
> -Erik
> 
> On Sun, 2011-01-23 at 18:13 +0100, Lennard wrote:
> > On 23-1-2011 17:44, Tom Hughes wrote:
> > > On 23/01/11 16:24, Erik Burrows wrote:
> > >
> > >> I was having great difficulty with applying the daily diffs to my
> > >> PostgreSQL database (8.4 and 9.0). I would get good performance right
> > [...]
> > > I wonder if in fact this is the performance problem that we saw using an
> > > 8.4 database. The general consensus was that it was somehow related to
> > > the GIN indexes but we never managed to work out exactly what it was.
> > 
> > Erik mentions encountering the same issue in 9.0. So either 
> > fastupdate=on is more intelligent in 9.0, or we're not on the right 
> > track to solve our 8.4 slowdown troubles.
> > 
> 
> 
> 
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/dev
>