<div dir="ltr"><div>Hello,</div><div><br></div><div>The PG_USE_COPY parameter doesn't seem to work when appending several files in the same table (replace the old data or something like that).</div><div><br></div><div>Postponing the creation of the spatial index does not seem to improve things either.</div><div><br></div><div>I didn't try increasing the number of rows per transaction but I don't think that the main problem is there.</div><div><br></div><div>I wonder if using partitioned tables won't be a good choice ?</div><div><br></div><div>Regards</div><div>Alexandre<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Le lun. 23 mai 2022 à 21:57, Alexandre Gacon <<a href="mailto:alexandre.gacon@gmail.com">alexandre.gacon@gmail.com</a>> a écrit :<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto">Hi,<div dir="auto"><br></div><div dir="auto">Thank you for the input. I will give a try to different combination and share my results.</div><div dir="auto"><br></div><div dir="auto">Alexandre </div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Le lun. 23 mai 2022 à 19:43, Bo Victor Thomsen <<a href="mailto:bo.victor.thomsen@gmail.com" target="_blank">bo.victor.thomsen@gmail.com</a>> a écrit :<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>Hi Alexandre -</p>
<p>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.</p>
<p> The base gpkg layer is all the buildings i Denmark, ca. 5.8
million objects.</p>
<p>For my layer I had the following results:</p>
<ol>
<li>PG_USE_COPY yes: 182 secs.</li>
<li>PG_USE_COPY yes, -gt 10000000 (5.8 mill. objects in single
transaction) : 181.5 secs.</li>
<li>PG_USE_COPY yes, -gt 10000000, UNLOGGED ON, SPATIAL_INDEX NO
: 152 secs.</li>
<li>PG_USE_COPY no : 15 min.</li>
</ol>
<p>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.<br>
</p>
<p>Using insert commands is an obvious "no-no" <br>
</p>
<p>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<br>
</p>
<p><br>
</p>
<p>===================</p>
<p><font face="monospace"><font size="2">@echo on<br>
set pgclientencoding=UTF-8<br>
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 <br>
<br>
echo %time%<br>
%cc% --config PG_USE_COPY yes -nln bygning1<br>
echo %time%<br>
%cc% --config PG_USE_COPY yes -gt 10000000 -nln bygning2<br>
echo %time%<br>
%cc% --config PG_USE_COPY yes -gt 10000000 -lco UNLOGGED=ON
-lco SPATIAL_INDEX=NO -nln bygning3<br>
echo %time%<br>
%cc% --config PG_USE_COPY no -nln bygning4<br>
echo %time%<br>
pause</font><br>
</font></p>
<p>===================</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<pre cols="72">Med venlig hilsen / Kind regards
Bo Victor Thomsen</pre>
<div>Den 23-05-2022 kl. 15:03 skrev
Alexandre Gacon:<br>
</div>
<blockquote type="cite">
<div dir="ltr">
<div>Hello,</div>
<div><br>
</div>
<div>I am using ogr2ogr to upload data from several geopackages
to a postgis database. Some tables contain thousands of rows
(buildings for example).</div>
<div><br>
</div>
<div>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).</div>
<div><br>
</div>
<div>How could I data insertion for the other files? Force
PG_USE_COPY ? Increase the value of GT ? Postpone spatial
index creation ?</div>
<div><br>
</div>
<div>Should I concatenate all the geopackages first and then
insert the data in Postgis?<br>
</div>
<div><br>
</div>
<div>Thank you for your help<br>
</div>
<div><br>
-- <br>
<div dir="ltr">Alexandre Gacon</div>
</div>
</div>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
gdal-dev mailing list
<a href="mailto:gdal-dev@lists.osgeo.org" rel="noreferrer" target="_blank">gdal-dev@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
</div>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" rel="noreferrer" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</blockquote></div>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature">Alexandre Gacon</div>