<html><head><meta http-equiv="Content-Type" content="text/html; charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">You could also consider loading your csv with ogr2ogr, that should allow you to set the correct SRID on load. <div class="">ogr should use the fast PG_USE_COPY option by default.<br class="">If that works well and your data allows it, you could try more optimization by splitting it up with ogr2ogr's -sql option - and run the load in parallel jobs, something like noted here: <a href="http://longwayaround.org.uk/notes/loading-postgis/" class="">http://longwayaround.org.uk/notes/loading-postgis/</a>. Syntax for `parallel` on the single file might be tricky, I'd probably use python's multiprocessing module.<br class=""><br class=""><div><br class=""><blockquote type="cite" class=""><div class=""><br class=""></div><div class=""><div class=""><br class="">Message: 7<br class="">Date: Thu, 1 Nov 2018 15:57:41 +0800<br class="">From: jerry73204 <<a href="mailto:jerry73204@gmail.com" class="">jerry73204@gmail.com</a>><br class="">To: <a href="mailto:postgis-users@lists.osgeo.org" class="">postgis-users@lists.osgeo.org</a><br class="">Subject: [postgis-users] On UpdateGeometrySRID() performance<br class="">Message-ID: <<a href="mailto:69efd569-bac0-d092-eb17-895dd02d20bc@gmail.com" class="">69efd569-bac0-d092-eb17-895dd02d20bc@gmail.com</a>><br class="">Content-Type: text/plain; charset=utf-8; format=flowed<br class=""><br class="">Hi all,<br class=""><br class="">I'm stuck in the low performance of UpdateGeometrySRID().<br class=""><br class="">I get started with a 50GB polygon dataset in CSV in EPSG:4326 <br class="">coordinates. Since I find no way to `\copy` the csv while preserving the <br class="">SRID, the data is imported with null SRID and then `SELECT <br class="">UpdateGeometrySRID('table', 'column', 4326)`.<br class=""><br class="">The `UpdateGeometrySRID()` takes as long time as that of `\copy`, which <br class="">turns out to be approx two hours. The dataset has two geometry columns <br class="">and thus I have to take triple time to finish this data.<br class=""><br class="">I profiled the postgresql daemon. The avg disk writing speed is 30MB/s, <br class="">while occasionally peaks to 100MB/s. The SSD, F2FS formatted disk is <br class="">capable of up to 150MB/s. The daemon does not utilize the 4-core <br class="">i5-7600k CPU. It seems to be a single process task with avg CPU load <br class="">20%, while other workers are idle. I wonder if there's a room for <br class="">improving the performance. Also, I'm looking for if it's possible to <br class="">preserve SRID with `\copy`.<br class=""><br class="">Jerry Lin<br class=""><br class=""><br class=""></div></div></blockquote></div><br class=""></div></body></html>