[gdal-dev] OSM to Postgis very slow without PG_USE_COPY YES

[Even Rouault]

Le mercredi 29 mai 2013 10:14:35, Jukka Rahkonen a écrit :
> Hi,
> 
> I was comparing OSM -> PostGIS conversion with and without
> --config PG_USE_COPY YES
> 
> With PG_USE_COPY YES - 1 minute
> Without PG_USE_COPY YES - I canceled the conversion after an hour
> 
> The difference is so big that I wonder if it really comes only from the
> write method or if there is something else going on as well.
> 
> I had -gt 20000 included in both tests as you can see from my command
> below. For the slow run I just cut away the PG_USE_COPY config option.
> 
> ogr2ogr -f PostgreSQL PG:"dbname='db' host='host' port='4326' user='user'
> password='passwd'" finland.osm.pbf -gt 20000 -progress --config
> OSM_COMPRESS_NODES YES  --config PG_USE_COPY YES
> 
> I have no access to the PostgreSQL configuration but I can ask from the DB
> admins if there is something that should be checked from the
> postgresql.conf file.

Jukka,

Here are the timings I get:

$ time ogr2ogr -f postgresql pg:dbname=autotest finland.osm.pbf -progress --
config OSM_COMPRESS_NODES YES --config PG_USE_COPY YES -overwrite -gt 20000
0...10...20...30...40...50...60...70...80...90...100 - done.

real	0m54.851s
user	0m20.480s
sys	0m0.510s
$ time ogr2ogr -f postgresql pg:dbname=autotest finland.osm.pbf -progress --
config OSM_COMPRESS_NODES YES -overwrite -gt 20000
0...10...20...30...40...50...60...70...80...90...100 - done.

real	4m13.079s
user	0m47.790s
sys	0m7.850s

So COPY mode is approximatively 4x faster than doing INSERTs, which is 
reasonnable.

I'm not sure why you observe so poor performance with INSERTs. My configuration 
of the server is the stock one delivered by the PostgreSQL 8.4 package of 
Ubuntu 10.04. No tweaking at all.

Best regards,

Even

-- 
Geospatial professional services
http://even.rouault.free.fr/services.html