excessiv slowness of ogr2ogr -f PostgreSQL

[remic]

Ogr2Ogr is excessively slow when converting from shapefile to postgis.

The shp2pgsql utility is 10 times faster.

This is impacting Grass GIS which relies on Ogr2Ogr.

Could it be switched from massiv "insert" command to "copy" command?

Cheers, Rémi-C

[Even Rouault]

Copy can already be used by turning the configuration option/environmenet variable PG_USE_COPY to YES. See ​http://gdal.org/ogr/drv_pg.html

There are perhaps situations where copy might not be desirable, for example if a field has a default value in the table definition and the features inserted don't include that field. If we use COPY, I think we must provide a value.

[Jukka Rahkonen]

Also if you use GDAL 1.10 or earlier the default value of -gt option is 200. Increasing it may have a huge difference in speed. Make a try with PG_USE_COPY YES and using a bigger -gt value.

[Jukka Rahkonen]

Hi remic,

Could you please make as suggested a new test by using parameter -gt 20000 and environment variable PG_USE_COPY=YES?

If no feedback comes in I suggest to close the ticket as invalid.

[Even Rouault]

trunk r27308 "PG: use COPY mode by default (unless PG_USE_COPY is set to NO) when inserting features in a newly create table (#5460)"

[Even Rouault]

trunk r27310 "Compilation fix (#5460)"

[remic]

Hey, thanks all for these very fast and accurate answers. As suggested : using PG_USE_COPY makes it 10 times faster ! I had tried the -gt option, with my config and my data, there was not much difference if -gt was big enough (>200 at least, up to one big transaction (40k in my case)).

I agree it should be the default because it is so much faster.

for the record it doesn't solve totally the grass problem, as they have rewritten the postgres driver in some use case (topology).

Thanks for this great tool ;-) Cheers, Rémi-C

[Even Rouault]

Milestone renamed