<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<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 class="moz-signature" cols="72">Med venlig hilsen / Kind regards
Bo Victor Thomsen</pre>
<div class="moz-cite-prefix">Den 23-05-2022 kl. 15:03 skrev
Alexandre Gacon:<br>
</div>
<blockquote type="cite"
cite="mid:CAFJaAp4K0e8nuVd8YwJkRH+9wK_Nv4RH74jacuuazGxx1vZ1gw@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<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" class="gmail_signature"
data-smartmail="gmail_signature">Alexandre Gacon</div>
</div>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
gdal-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
</body>
</html>