[gdal-dev] ogr2ogr Postgres upload performance

[Bo Victor Thomsen]

Hi Alexandre -

I made a DOS script ages ago to try the different use cases. It's 
inserted as text below. It doesn't test every setup combination, only 
the most pertinent.

The base gpkg layer is all the buildings i Denmark, ca. 5.8 million objects.

For my layer I had the following results:

 1. PG_USE_COPY yes: 182 secs.
 2. PG_USE_COPY yes,  -gt 10000000 (5.8 mill. objects in single
    transaction) : 181.5 secs.
 3. PG_USE_COPY yes,  -gt 10000000, UNLOGGED ON, SPATIAL_INDEX NO : 152
    secs.
 4. PG_USE_COPY no : 15 min.

So the "PG_USE_COPY yes" is crucial and using unlogged tables and no 
spatial index reduces the time with an extra 30 sec. But you have to 
build a spatial index afterwards.

Using insert commands is an obvious "no-no"

As far as I remember, I used the "PG_USE_COPY no" option without any 
other refinements. But I didn't append data to an existing table. YMMV


===================

@echo on
set pgclientencoding=UTF-8
set cc= ogr2ogr -progress -lco OVERWRITE=YES -lco SCHEMA=fot -a_srs 
EPSG:25832 -nlt PROMOTE_TO_MULTI -f "PostgreSQL" PG:"host=localhost 
port=5432 user=xxx password=yyy dbname=zzz" d:/tmp/geodanmark.gpkg bygning

echo %time%
%cc% --config PG_USE_COPY yes -nln bygning1
echo %time%
%cc% --config PG_USE_COPY yes -gt 10000000 -nln bygning2
echo %time%
%cc% --config PG_USE_COPY yes -gt 10000000 -lco UNLOGGED=ON -lco 
SPATIAL_INDEX=NO -nln bygning3
echo %time%
%cc% --config PG_USE_COPY no  -nln bygning4
echo %time%
pause

===================




Med venlig hilsen / Kind regards

Bo Victor Thomsen

Den 23-05-2022 kl. 15:03 skrev Alexandre Gacon:
> Hello,
>
> I am using ogr2ogr to upload data from several geopackages to a 
> postgis database. Some tables contain thousands of rows (buildings for 
> example).
>
> The import of the first file is quite fast (tables are created for the 
> first file so PG_USE_COPY is used) but the following file is much 
> slower (using INSERT instead of COPY).
>
> How could I data insertion for the other files? Force PG_USE_COPY ? 
> Increase the value of GT ? Postpone spatial index creation ?
>
> Should I concatenate all the geopackages first and then insert the 
> data in Postgis?
>
> Thank you for your help
>
> -- 
> Alexandre Gacon
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20220523/ab07a423/attachment.htm>