#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 by , 10 years ago
comment:2 by , 10 years ago
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 by , 10 years ago
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 by , 10 years ago
comment:6 by , 10 years ago
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 by , 10 years ago
Milestone: | → 2.0 |
---|---|
Resolution: | → fixed |
Status: | new → closed |
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.