<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div><br>
</div>
<div>
<div><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"></span></div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"></span>
<div id="divRplyFwdMsg" dir="ltr"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Hei</span><br>
</div>
<div>
<div>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>From:
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">postgis</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">-users
<postgis-users-bounces</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">@lists.osgeo.org</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>
on behalf of </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Marco</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Boeringa</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
<marco</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">@boeringa.demon.nl</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>Sent: Wednesday, March 4, 2020 2:49 PM</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>To:
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">postgis</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">-users</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">@lists.osgeo.org</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
<postgis-users</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">@lists.osgeo.org</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>Subject: Re: [</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">postgis</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">-users]
How does PostGIS / PostgreSQL distribute parallel work to cores for GIS type data?</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Hi</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Paul</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
and </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Darafei</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">,</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>Thanks for the insightful answers.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>I may attempt to do a comparative test using both my
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">multi</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">-threaded</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>implementation using the vertex count load balancing between threads,</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>and having postgreSQL do its parallel work. But it will require some</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">>custom development, so I don't know when I will be able to show results.</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">></span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Hi</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">We at
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Nibio</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
have been using content based grids (Developed by </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Nibio</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">)
for while where we group things </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">spatially and content based.</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">The cell's we generate, may have different size but the number of geometries are limited to your selected max.</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">We here only use
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">bbox</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
and not the number of points, because then we don't need to open each geometries at
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">this first stage.</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">The first stage where generate cells re single threaded and it has also been working
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ok</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
with not counting points in our used cases. </span><span style="color: rgb(0, 0, 0); font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background: var(--white);">(There is no problem adding a counter based on number of points)</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Here is example from https://github.com/larsop/find-overlap-and-gap where processing time is cut to the
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">half when the number threads are duplicated.</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);"> </span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">with 20 parallel threads (a more normal sized data set)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">time
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">psql</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">sl</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">-c"CALL
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">find_overlap_gap_run('org_ar.ar250_flate','</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geo</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">',4258,'sl_lop.ar250_flate_res',20,1000);"
2>> </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">/</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">tmp</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">/analyze.log;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Time: 2128491.119
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(35:28.491)</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">with 40 parallel threads (a more normal sized data set)</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">time
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">psql</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">sl</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">-c"CALL
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">find_overlap_gap_run('org_ar.ar250_flate','</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">geo</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">',4258,'sl_lop.ar250_flate_res',40,1000);"
2>> </span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">/</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">tmp</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">/analyze.log;</span></p>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Time: 1076647.282
</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">ms</span><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">
(17:56.647)</span></p>
<p style="margin: 0px; min-height: 14px;"><br>
</p>
<p style="margin: 0px; font-stretch: normal; line-height: normal;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; line-height: normal; color: rgb(0, 0, 0);">Lars</span></p>
</div>
<font size="2"></font></div>
</div>
</body>
</html>