<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Regina,</p>
<p>I now did a second test with two datasets of forest polygons, to
get an indication if the subquery has any measurable positive
effect on such a dataset, contrary to the previously shown result
for a dataset primarily consisting of low vertex count (<200)
polygons (results included in the older posts attached below).</p>
<p>The first dataset has records with geometries of up to about 100k
vertices, the second dataset is a generalized one with all
polygons subdivided to a maximum of 5000 vertices.</p>
<p>The results are included below. As you can see, using a subquery
still does not lead to a real measurable performance gain, neither
for the dataset with humongous big polygons, nor the one with
subdivided polygons containing a large set of 5000 max vertices
polygons. Only the single threaded result on the first dataset
with max 100k vertices per polygon, appears to have a tiny
measurable gain, but considering the slight variability of timing
results at each run even with the same settings, I am pretty sure
even this is not significant.</p>
<p>What is significant compared to the previous results of a dataset
with small polygons (majority < 200 vertices per polygon), is
that the multi-threaded processing is considerably faster than the
single threaded processing. So processing is clearly CPU limited
for these larger polygons, but whether that is really in the
PostGIS calculations, or overhead of PostgreSQL needing to
de-toast large polygons, I don't know.<br>
</p>
<p>It does appear though, that PostgreSQL somehow already manages to
"optimize" the calculations reusing calculated values, unless the
cost of ST_Area and ST_Perimeter calculations on really large
polygons is still only a fraction of the reading and writing of
the geometries, in which case the results of the query processing
simply do not show the inherent cost difference of the different
queries. <br>
</p>
<p>Note though, that although I showed:</p>
<p>UPDATE <MY_TABLE> SET area =
ST_Area(<GEOMETRY_COLUMN>), area_perimeter =
ST_Area(<GEOMETRY_COLUMN>) /
ST_Perimeter(<GEOMETRY_COLUMN>)</p>
<p>as an example query to illustrate the problem, the timing results
are actually for an UPDATE statement setting 4 columns that all
need 'ST_Area()' as part of the calculation. So in reality, the
potential benefits of the rewriting of the query, should have been
even bigger than for the example query above, as the polygon's
area is needed four times.<br>
</p>
<p>Marco<br>
</p>
<p><br>
</p>
<p>*** Dataset with very large polygon geometries (Max 100k
vertices) *********<br>
<br>
- Single-threaded using ORIGINAL QUERY: 32m05s<br>
<br>
- Single-threaded using SUBQUERY (FROM): 31m52s</p>
<p> - Multi-threaded using ORIGINAL QUERY: 18m16s </p>
<p>- Multi-threaded using SUBQUERY (FROM): 18m46s</p>
<p> *******************************************************<br>
<br>
*** Dataset SUBDIVIDED to polygons with max 5000 vertices!
*********<br>
<br>
- Single-threaded using ORIGINAL QUERY: 11m01s<br>
<br>
- Single-threaded using SUBQUERY (FROM): 11m11s<br>
</p>
<p>- Multi-threaded using ORIGINAL QUERY: 5m37s </p>
<p>- Multi-threaded using SUBQUERY (FROM): 5m46s</p>
<p> *******************************************************</p>
<p><br>
</p>
<div class="moz-cite-prefix">Op 28-9-2020 om 18:28 schreef Regina
Obe:<br>
</div>
<blockquote type="cite"
cite="mid:000501d695b4$728acfc0$57a06f40$@pcorp.us">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<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:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
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:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">ST_Area
and ST_Perimeter functions are relatively low cost, so that
fact you discovered is not surprising.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">I
think if you were doing something like ST_Distance then the
CTE or subquery would be more efficient than your direct
solution.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<div style="border:none;border-left:solid blue 1.5pt;padding:0in
0in 0in 4.0pt">
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif">
postgis-users [<a class="moz-txt-link-freetext"
href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Marco Boeringa<br>
<b>Sent:</b> Monday, September 28, 2020 10:15 AM<br>
<b>To:</b> <a class="moz-txt-link-abbreviated"
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<b>Subject:</b> Re: [postgis-users] Setting multiple
columns in one UPDATE request<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p>Hi Regina,<o:p></o:p></p>
<p>I can now partially answer my question about performance
myself: <o:p></o:p></p>
<p>It turns out that for datasets having relatively small
geometries (in terms of number of vertices, not area, e.g. a
few dozen to a few hundred vertices maximum) there is
actually *NO* benefit at all of rewriting the query either
with a WITH (CTE) or FROM (Subquery). This may be different
though for other datasets having much larger geometries, but
needs further testing.<o:p></o:p></p>
<p>In fact, processing is marginally slower, but only by 5-10%
or so, compared to the original query. <o:p></o:p></p>
<p>In my setup, I can also run the query both in a single
thread, or using a custom Python multi-threaded
implementation sending SQL statements in parallel to
PostgreSQL. Since the test system has a very limited 4 core
multi-threaded processor, the benefits of the
multi-threading versus single threaded processing in this
case are nil, obviously due to the overhead of the
multi-threading. The multi-threaded application is as fast
as the single threaded PostgreSQL worker, or even a bit
slower, but puts a far higher load on the processor. Of
course, with a more modern processor with high core count,
this experience likely changes.<o:p></o:p></p>
<p>There also appears to be virtually no difference between
using a CTE or the subquery as you suggested: subquery is
only very marginally faster than CTE.<o:p></o:p></p>
<p>So for datasets having small geometries, just sticking to
the original query like:<o:p></o:p></p>
<p>UPDATE <MY_TABLE> SET area =
ST_Area(<GEOMETRY_COLUMN>), area_perimeter =
ST_Area(<GEOMETRY_COLUMN>) /
ST_Perimeter(<GEOMETRY_COLUMN>)<o:p></o:p></p>
<p>is fine for those datasets. <o:p></o:p></p>
<p>I think this result is caused by the fact that the
retrieving and storing overhead of the geometries (tables
stored on SSD), is simply far bigger than the actual cost of
calculating the area or perimeter for such datasets where
the majority of geometries is of very limited size (e.g. OSM
buildings, simple landuse polygons). Additionally, there may
be an extra cost due to the needed join for the CTE and
subquery statements. Lastly, the cost of running ST_Area and
ST_Perimeter may just be to low as well. There may be other
functions in PostGIS with a much higher computational cost
that would show a benefit from rewriting the query.<o:p></o:p></p>
<p>I will attempt to run a second benchmark using a dataset
with much larger geometries though (some with well over >
10k vertices), to see if that gives the same result, and
report back. There may be a difference, but we will see...<o:p></o:p></p>
<p>Marco<o:p></o:p></p>
<p>*** Dataset with small geometries (most < 200 vertices)
*********<o:p></o:p></p>
<p>- Single-threaded using ORIGINAL QUERY: 8m45s<o:p></o:p></p>
<p>- Single-threaded using SUBQUERY (FROM): 8m52s<o:p></o:p></p>
<p>- Single-threaded using CTE (WITH): 9m13s<o:p></o:p></p>
<p>- Multi-threaded using ORIGINAL QUERY: 9m27s<o:p></o:p></p>
<p>- Multi-threaded using SUBQUERY (FROM): 9m44s<o:p></o:p></p>
<p>- Multi-threaded using CTE (WITH): 9m50s<o:p></o:p></p>
<p>*******************************************************<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Op 28-9-2020 om 09:36 schreef Marco
Boeringa:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p>Regina,<o:p></o:p></p>
<p>Thanks for your suggestion.<o:p></o:p></p>
<p>How is this performance wise? Is not using a CTE as in
your suggestion, supposedly faster than with using a CTE,
or is this just a syntax thing and performance is expected
to be equal?<o:p></o:p></p>
<p>It would still be nice though, if PostgreSQL somehow
handled this automatically, and one could use the most
basic form yet be sure it was optimized. It also reads
more easily to just see:<o:p></o:p></p>
<p>UPDATE <MY_TABLE> SET area =
ST_Area(<GEOMETRY_COLUMN>), area_perimeter =
ST_Area(<GEOMETRY_COLUMN>) /
ST_Perimeter(<GEOMETRY_COLUMN>)<o:p></o:p></p>
<p>in your code, instead of more elaborate construct
involving a join.<o:p></o:p></p>
<p>Marco<o:p></o:p></p>
<div>
<p class="MsoNormal">Op 28-9-2020 om 03:26 schreef Regina
Obe:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">I
prefer doing it in the FROM and not bothering using a
CTE.</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">So
something like</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> </span><o:p></o:p></p>
<pre>UPDATE <MY_TABLE> SET area = f.area, area_perimeter = f.area/f.perimeter<o:p></o:p></pre>
<pre>FROM (SELECT id, ST_Area(<GEOMETRY_COLUMN>) AS area, ST_Perimeter(<GEOMETRY COLUMN>) AS perimeter<o:p></o:p></pre>
<pre> FROM <MY TABLE> ) AS f<o:p></o:p></pre>
<pre>WHERE f.id = <MY TABLE>.id;<o:p></o:p></pre>
<pre> <o:p></o:p></pre>
<pre> <o:p></o:p></pre>
<p class="MsoNormal"><o:p> </o:p></p>
</blockquote>
</blockquote>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
</body>
</html>