<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 12 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Verdana","sans-serif";
font-variant:normal !important;
color:#1F497D;
text-transform:none;
text-shadow:none;
font-weight:normal;
font-style:normal;
text-decoration:none none;
vertical-align:baseline;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="2050" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=FR link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'>Hi,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'>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 ?<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'>Olivier<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'><o:p> </o:p></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:postgis-users-bounces@lists.osgeo.org] <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<o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal><span lang=EN-US style='font-family:"Courier New"'>Hey,<o:p></o:p></span></p></div><p class=MsoNormal><span lang=EN-US>I<o:p></o:p></span></p><div><p class=MsoNormal style='margin-bottom:12.0pt'><span lang=EN-US style='font-family:"Cambria Math","serif"'></span><span lang=EN-US style='font-family:"Courier New"'>'m afraid you may not use the most efficient approach.<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-family:"Courier New"'>Assuming you want to find for each node of table x the closest node of table y,<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-family:"Courier New"'>it takes less than 1 second on my computer.<o:p></o:p></span></p></div><div><p class=MsoNormal style='margin-bottom:12.0pt'><span lang=EN-US style='font-family:"Courier New"'><br><br><br>DROP TABLE IF EXISTS test_1; <br>CREATE TABLE test_1 AS<br>SELECT s AS gid, ST_makePoint(random()*1000,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_2.geom) AS sline<br>FROM test_1, test_2<br>ORDER BY test_1.gid ,ST_Distance(test_1.geom,test_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 <o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-family:"Courier New"'>--option 3 : you could use a recursive CTE to avoid creating a dedicated dummy function, <o:p></o:p></span></p></div><div><p class=MsoNormal style='margin-bottom:12.0pt'><span lang=EN-US style='font-family:"Courier New"'>-- this would be a pain to write though<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Courier New"'>Cheers,<o:p></o:p></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"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Courier New"'><o:p> </o:p></span></p></div></div><div><p class=MsoNormal><o:p> </o:p></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>>:<o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>Hi,</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>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><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>FOR row IN </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> SELECT code,ST_AsEwkt(ST_StartPoint(geom)) as geom1 FROM t_x</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>LOOP</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> FOR row1 IN</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> SELECT code, ST_Distance(ST_AsEwkt(geom)::text,row.geom1) FROM t_y order by ST_Distance(ST_AsEwkt(geom)::text,row.geom1) LIMIT 1</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> LOOP</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> END LOOP;</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>....</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>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><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>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><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>Thanks for any answer,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span lang=EN-US style='font-size:10.0pt;font-family:"Verdana","sans-serif"'>Olivier</span><o:p></o:p></p></div><p class=MsoNormal style='margin-bottom:12.0pt'><o:p> </o:p></p><div class=MsoNormal align=center style='text-align:center'><hr size=1 width="99%" noshade style='color:#909090' align=center></div><table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 style='border-collapse:collapse'><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 border=0 width=32 height=32 id="_x0000_i1026" src="cid:image001.png@01D26A80.2261F950" alt="Avast logo"></span></a><o:p></o:p></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> <o:p></o:p></span></p></td></tr></table><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><span lang=EN-US><br>_______________________________________________<br>postgis-users mailing list<br></span><a href="mailto:postgis-users@lists.osgeo.org"><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/mailman/listinfo/postgis-users</span></a><span lang=EN-US><o:p></o:p></span></p></div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div></div>
<br /><br />
<hr style='border:none; color:#909090; background-color:#B0B0B0; height: 1px; width: 99%;' />
<table style='border-collapse:collapse;border:none;'>
<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">
<img border=0 src="http://static.avast.com/emails/avast-mail-stamp.png" alt="Avast logo" />
</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">www.avast.com</a>
</p>
</td>
</tr>
</table>
<br />
</body></html>