Opened 7 years ago

Closed 7 years ago

Last modified 6 years ago

#5460 closed enhancement (fixed)

excessiv slowness of ogr2ogr -f PostgreSQL

Reported by: remic Owned by: warmerdam
Priority: normal Milestone: 2.0.0
Component: default Version: unspecified
Severity: normal Keywords:
Cc:

Description

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

Change History (8)

comment:1 Changed 7 years ago by 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.

comment:2 Changed 7 years ago by 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.

comment:3 Changed 7 years ago by 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.

comment:4 Changed 7 years ago by 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)"

comment:5 Changed 7 years ago by Even Rouault

trunk r27310 "Compilation fix (#5460)"

comment:6 in reply to:  5 Changed 7 years ago by 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

comment:7 Changed 7 years ago by Even Rouault

Milestone: 2.0
Resolution: fixed
Status: newclosed

comment:8 Changed 6 years ago by Even Rouault

Milestone: 2.02.0.0

Milestone renamed

Note: See TracTickets for help on using tickets.