[gdal-dev] ogr2ogr Postgres upload performance

[Alexandre Gacon]

Hi,

Thank you for the input. I will give a try to different combination and
share my results.

Alexandre

Le lun. 23 mai 2022 à 19:43, Bo Victor Thomsen <bo.victor.thomsen at gmail.com>
a écrit :

> 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 listgdal-dev at lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/gdal-dev
>
> _______________________________________________
> 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/e7f04b2a/attachment.htm>