<div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">Hey,<br></div>I<div style="font-family:monospace,monospace;display:inline" class="gmail_default">'m afraid you may not use the most efficient approach.<br><br></div><div style="font-family:monospace,monospace;display:inline" class="gmail_default">Assuming you want to find for each node of table x the closest node of table y,<br></div><div style="font-family:monospace,monospace;display:inline" class="gmail_default">it takes less than 1 second on my computer.<br></div><div style="font-family:monospace,monospace;display:inline" class="gmail_default"><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 <br><br></div><div style="font-family:monospace,monospace;display:inline" class="gmail_default">--option 3 : you could use a recursive CTE to avoid creating a dedicated dummy function, <br></div><div style="font-family:monospace,monospace;display:inline" class="gmail_default">-- this would be a pain to write though<br><br><br></div><div style="font-family:monospace,monospace;display:inline" class="gmail_default">Cheers,<br></div><div style="font-family:monospace,monospace;display:inline" class="gmail_default">Rémi-C</div><div class="gmail_default" style="font-family:monospace,monospace"> <br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2017-01-08 16:36 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_-6223587942799679539WordSection1"><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">Hi,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" 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)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" 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 <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<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">LOOP<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<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<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" 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 style="font-size:10.0pt;font-family:"Verdana","sans-serif"">LOOP<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""> END LOOP;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"">....<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif""><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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" 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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" 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,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" 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">Olivier<u></u><u></u></span></p></div>
<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><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>