<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Regina,</p>
<p>I can now partially answer my question about performance myself:
<br>
</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.<br>
</p>
<p> In fact, processing is marginally slower, but only by 5-10% or
so, compared to the original query. <br>
</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.</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.</p>
<p>So for datasets having small geometries, just sticking to the
original query like:</p>
<p>UPDATE <MY_TABLE> SET area =
ST_Area(<GEOMETRY_COLUMN>), area_perimeter =
ST_Area(<GEOMETRY_COLUMN>) /
ST_Perimeter(<GEOMETRY_COLUMN>)</p>
<p>is fine for those datasets. <br>
</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.<br>
</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...</p>
<p>Marco<br>
</p>
<p>*** Dataset with small geometries (most < 200 vertices)
*********<br>
</p>
<p>- Single-threaded using ORIGINAL QUERY: 8m45s</p>
<p>- Single-threaded using SUBQUERY (FROM): 8m52s</p>
<p>- Single-threaded using CTE (WITH): 9m13s<br>
</p>
<p>- Multi-threaded using ORIGINAL QUERY: 9m27s</p>
<p>- Multi-threaded using SUBQUERY (FROM): 9m44s</p>
<p>- Multi-threaded using CTE (WITH): 9m50s</p>
<p>*******************************************************</p>
<br>
<div class="moz-cite-prefix">Op 28-9-2020 om 09:36 schreef Marco
Boeringa:<br>
</div>
<blockquote type="cite"
cite="mid:1c5f57e9-c5a4-b4fd-1d90-e7892d58285b@boeringa.demon.nl">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<p>Regina,</p>
<p>Thanks for your suggestion.<br>
</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?</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:</p>
<p>UPDATE <MY_TABLE> SET area =
ST_Area(<GEOMETRY_COLUMN>), area_perimeter =
ST_Area(<GEOMETRY_COLUMN>) /
ST_Perimeter(<GEOMETRY_COLUMN>)</p>
<p>in your code, instead of more elaborate construct involving a
join.<br>
</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 28-9-2020 om 03:26 schreef Regina
Obe:<br>
</div>
<blockquote type="cite"
cite="mid:000001d69536$60e8a400$22b9ec00$@pcorp.us">
<meta http-equiv="Content-Type" content="text/html;
charset=UTF-8">
<meta name="Generator" content="Microsoft Word 15 (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: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",serif;}
span.EmailStyle20
{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">I
prefer doing it in the FROM and not bothering using a CTE.<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">So
something like<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>
<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>
<br>
</div>
</blockquote>
</blockquote>
</body>
</html>