[OSM-dev] Patch to osm2pgsql for faster updates

[Erik Burrows]

While I had both my 8.4 and 9.0 databases up and running (not
concurrently), I tested rendering a section of the map using
generate_tiles_multiprocess.py against each database to see which
version was faster.

8.4 is 2.4 times faster than 9.0!

This is almost the exact same data set, same hardware, and same tile
set. 37 minutes for PG 8.4, and 87 minutes for 9.0. I ran the test
twice!

The databases are configured identically. The diff of postgresql.conf
shows only comment differences. The databases started with the same
planet.osm load, but a different amount of diff updates applied. I did
do a vacuum analyze before the tests.

I don't have an explanation. All I can think of to try to debug is to
set "log statement duration=0", and use mapnik_tiles.py to trace each
command, and compare the times. I'll do this over the next day or so.

-Erik

> On 23-1-2011 18:50, Tom Hughes wrote:
> 
> > Do we actually know that 9.0 is ok though? I was basing my comments on a
> > single report of some preliminary tests at SOTM last year.
> 
> I've been using it on 2 servers for months. While I haven't done proper 
> testing, it does feel faster than 8.4 which had been on those servers 
> for at least over a year before.
> 
> It seems to depend mostly on your autovacuum settings, since 
> fastupdate=on will apply the updated index only during a vacuum, or when 
> the list has grown larger than work_mem.
> 
> YMMV
> 
> http://www.postgresql.org/docs/8.4/static/gin-implementation.html
> http://www.postgresql.org/docs/9.0/static/gin-implementation.html
> 
> 9.0 did add the following:
> 
> "Use red-black binary trees for GIN index creation (Teodor Sigaev)
> 
> Red-black trees are self-balancing. This avoids slowdowns in cases where 
> the input is in nonrandom order."
> 
> http://www.postgresql.org/docs/9.0/interactive/release-9-0.html
> 
>