<div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">Hey, <br></div><div class="gmail_default" style="font-family:monospace,monospace">option 2 is the fastest one ;-)<br></div><div class="gmail_default" style="font-family:monospace,monospace">, option 1 is simply a pure SQL rewrite of your query.<br></div><div class="gmail_default" style="font-family:monospace,monospace">The machine I used for test is way weaker than yours (I'd say 2x weaker).<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">postgres 9.4 is non-parallelised, what you see is that postgres use 100% of a core, but the core beig used is rotated,<br></div><div class="gmail_default" style="font-family:monospace,monospace">which mean the average usage of your cpu is 100% / 4 (your number of core),<br></div><div class="gmail_default" style="font-family:monospace,monospace">which might be the explaination for your 30%.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">What you see is expected : postgres is going to use the max amount of ram, then use the harddrive when no more ram is available.<br></div><div class="gmail_default" style="font-family:monospace,monospace">This is not the case with option 2, as very few ram is needed.<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">Don't forget to create the appropriate indexes on your table t_y.<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">Cheers,<br></div><div class="gmail_default" style="font-family:monospace,monospace">Rémi-C<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2017-01-09 13:56 GMT+01:00 Olivier Leprêtre <span dir="ltr"><<a href="mailto:o.lepretre@gmail.com" target="_blank">o.lepretre@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div link="blue" vlink="purple" lang="FR"><div class="m_3333977972502532352WordSection1"><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d" lang="EN-US">Hi,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d" lang="EN-US">Thanks for your answer and this way of calculation. I don't know which machine you have but on mine, Option 1 launched from pgadmin takes 2,564,423 ms  (about 45 mn) a bit less than my original code (2,823,272 ms). So if this takes less than 1 second on yours, there is definitively a problem on my configuration or my machine which is nevertheless a very slow one : Hp Envy (Windows 10, i7-6500 CPU 2,5 Ghz 8go). I'm using postgresql/postgis 9.4 with its standard configuration.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d" lang="EN-US">During the option 1 query, the processor used again 30/35% CPU during all time. Difference is about memory, it grows up to 1Gb (shared buffer is set to this value) and then begin using disk about 10 Mo/s.  So I'm still wondering why the cpu doesn't go further than 30% and why this take such a difference on your machine. Is it a linux OS ?<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d">Olivier<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1f497d"><u></u> <u></u></span></p><div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm"><p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">De :</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>] <b>De la part de</b> Rémi Cura<br><b>Envoyé :</b> lundi 9 janvier 2017 10:52<br><b>À :</b> PostGIS Users Discussion<br><b>Objet :</b> Re: [postgis-users] CPU tuning<u></u><u></u></span></p></div><div><div class="h5"><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal"><span style="font-family:"Courier New"" lang="EN-US">Hey,<u></u><u></u></span></p></div><p class="MsoNormal"><span lang="EN-US">I<u></u><u></u></span></p><div><p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-family:"Cambria Math","serif"" lang="EN-US">​</span><span style="font-family:"Courier New"" lang="EN-US">'m afraid you may not use the most efficient approach.<u></u><u></u></span></p></div><div><p class="MsoNormal"><span style="font-family:"Courier New"" lang="EN-US">Assuming you want to find for each node of table x the closest node of table y,<u></u><u></u></span></p></div><div><p class="MsoNormal"><span style="font-family:"Courier New"" lang="EN-US">it takes less than 1 second on my computer.<u></u><u></u></span></p></div><div><p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-family:"Courier New"" lang="EN-US"><br><br><br>DROP TABLE  IF EXISTS  test_1; <br>CREATE TABLE test_1 AS<br>SELECT s AS gid, ST_makePoint(random()*1000,<wbr>random()*1000) AS geom<br>FROM generate_series(1,12000) AS s ;<br>CREATE INDEX ON test_1 USING GIST(geom) ; <br>ALTER TABLE test_1 ADD PRIMARY KEY (gid) ; <br><br><br>DROP TABLE  IF EXISTS  test_2; <br>CREATE TABLE test_2 AS<br>SELECT s AS gid, ST_makePoint( random()*1000,random()*1000) AS geom<br>FROM generate_series(1,50000) AS s ;<br>CREATE INDEX ON test_2 USING GIST(geom) ; <br>ALTER TABLE test_2 ADD PRIMARY KEY (gid) ;  <br><br>-- option 1 : you have lots of ram : brute force<br>DROP TABLE IF EXISTS test_results ;<br>CREATE TABLE test_results AS <br>SELECT DISTINCT ON (test_1.gid ) test_1.gid AS gid1, test_2.gid AS gid2-- , ST_MakeLine(test_1.geom,test_<wbr>2.geom) AS sline<br>FROM test_1, test_2<br>ORDER BY test_1.gid ,ST_Distance(test_1.geom,test_<wbr>2.geom) ASC ;<br><br><br>--option2  : you have limited ram : snipping : force index use<br><br>DROP FUNCTION IF EXISTS test_sdist(geom1 geometry) ; <br>CREATE OR REPLACE FUNCTION test_sdist(geom1 geometry, OUT gid2 bigint, OUT geom2 geometry) AS<br> $$<br>    --given a point, find the closes one in test_2<br>    DECLARE <br>    BEGIN <br>        SELECT gid, geom INTO gid2, geom2<br>        FROM test_2<br>        ORDER BY ST_Distance(geom2,test_2.geom) ASC<br>        LIMIT 1  ;<br>        RETURN ; <br>    END;<br>$$ LANGUAGE plpgsql; <br><br>DROP TABLE IF EXISTS test_result_2 ;<br>CREATE TABLE IF NOT EXISTS test_result_2 AS <br>SELECT test_1.gid AS gid1, f.gid2 AS gid2<br>FROM test_1, test_sdist(test_1.geom) AS f <u></u><u></u></span></p></div><div><p class="MsoNormal"><span style="font-family:"Courier New"" lang="EN-US">--option 3 : you could use a recursive CTE to avoid creating a dedicated dummy function, <u></u><u></u></span></p></div><div><p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-family:"Courier New"" lang="EN-US">--           this would be a pain to write though<u></u><u></u></span></p></div><div><p class="MsoNormal"><span style="font-family:"Courier New"">Cheers,<u></u><u></u></span></p></div><div><p class="MsoNormal"><span style="font-family:"Courier New"">Rémi-C</span><span style="font-family:"Cambria Math","serif"">​</span><span style="font-family:"Courier New""><u></u><u></u></span></p></div><div><p class="MsoNormal"><span style="font-family:"Courier New""><u></u> <u></u></span></p></div></div><div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">2017-01-08 16:36 GMT+01:00 Olivier Leprêtre <<a href="mailto:o.lepretre@gmail.com" target="_blank">o.lepretre@gmail.com</a>>:<u></u><u></u></p><div><div><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">Hi,</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"> </span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">I have a pgsql postgis function that last about an hour on an Hp Envy (W10, i7-6500 CPU 2,5 Ghz 8go). This function is calculating minimum distance between each nodes of table x (12000 nodes) and table y (42000 nodes)</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"> </span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">FOR row IN </span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">                   SELECT code,ST_AsEwkt(ST_StartPoint(<wbr>geom)) as geom1  FROM t_x</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">LOOP</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">                   FOR row1 IN</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">                            SELECT code, ST_Distance(ST_AsEwkt(geom)::<wbr>text,row.geom1) FROM t_y order by ST_Distance(ST_AsEwkt(geom)::<wbr>text,row.geom1) LIMIT 1</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"> </span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">                   LOOP</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">                   END LOOP;</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">....</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"> </span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"> </span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">What I don't understand is that postgresql server process CPU is stable during all that time at 30/31% with no other process running. Details of each processor 1 to 3 shows that all 4 uses 30%. Disk and network usage are 0% (database is local) Ram usage is 47% and Postgresql server uses process only 7,4 Mo.</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"> </span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">My question is "Why postgres process uses such a precise and constant cpu and doesn't grow up to more than 30% ?" I didn't find any parameter forcing such a limit in order to reduce duration of this calculation.</span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"> </span><span lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">Thanks for any answer,</span><u></u><u></u></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"> </span><u></u><u></u></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">Olivier</span><u></u><u></u></p></div><p class="MsoNormal" style="margin-bottom:12.0pt"><u></u> <u></u></p><div class="MsoNormal" style="text-align:center" align="center"><hr style="color:#909090" width="99%" size="1" noshade align="center"></div><table class="m_3333977972502532352MsoNormalTable" style="border-collapse:collapse" cellspacing="0" cellpadding="0" border="0"><tbody><tr><td style="padding:0cm 11.25pt 0cm 6.0pt"><p class="MsoNormal"><a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank"><span style="text-decoration:none"><img id="m_3333977972502532352_x0000_i1026" src="cid:image001.png@01D26A80.2261F950" alt="Avast logo" width="32" height="32" border="0"></span></a><u></u><u></u></p></td><td style="padding:.75pt .75pt .75pt .75pt"><p><span style="font-family:"Calibri","sans-serif";color:#3d4d5a">L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast. <br><a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank">www.avast.com</a> <u></u><u></u></span></p></td></tr></tbody></table><p class="MsoNormal"><u></u> <u></u></p></div><p class="MsoNormal"><span lang="EN-US"><br>______________________________<wbr>_________________<br>postgis-users mailing list<br></span><a href="mailto:postgis-users@lists.osgeo.org" target="_blank"><span lang="EN-US">postgis-users@lists.osgeo.org</span></a><span lang="EN-US"><br></span><a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank"><span lang="EN-US">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</span></a><span lang="EN-US"><u></u><u></u></span></p></div><p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p></div></div></div></div><div><div class="h5">
<br><br>
<hr style="border:none;color:#909090;background-color:#b0b0b0;height:1px;width:99%">
<table style="border-collapse:collapse;border:none">
        <tbody><tr>
                <td style="border:none;padding:0px 15px 0px 8px">
                        <a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank">
                                <img src="http://static.avast.com/emails/avast-mail-stamp.png" alt="Avast logo" border="0">
                        </a>
                </td>
                <td>
                        <p style="color:#3d4d5a;font-family:"Calibri","Verdana","Arial","Helvetica";font-size:12pt">
                                L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
                                <br><a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank">www.avast.com</a>
                        </p>
                </td>
        </tr>
</tbody></table>
<br>
</div></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>