<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Depends on what your interests are...<br>
</p>
<p>And there is definitely reason to subdivide this dataset. The
generalization processing of these OpenStreetMap woodland involves
dissolving, so some of resulting geometries of that process step
are absolutely huge (well over 1M vertices), and need subdividing
to avoid subsequent processing issues.<br>
</p>
<p>I was just interested to see if in a real world scenario like
this, the 'subdividing-to-avoid-TOAST' could also mean the data
might be displayed quicker in a GIS like QGIS, which is always a
welcome enhancement, as not having to go through de-toasting,
despite a larger number of records. For this specific dataset, the
answer is no. There may well be situations, datasets, and
scenario's, e.g. the ones you point out, where it is useful to set
such small limit so as to avoid TOAST.<br>
</p>
<p>I thought it might just be interesting for others to read.</p>
<p>Marco</p>
<div class="moz-cite-prefix">Op 19-2-2022 om 17:04 schreef Darafei
"Komяpa" Praliaskouski:<br>
</div>
<blockquote type="cite"
cite="mid:CAC8Q8tLMFPBz7omszYJb_ROYnofi92D-DixfPqdrBL5jrErGWg@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">Hi Marko,
<div><br>
</div>
<div>The scenarios you benchmarked don't hit any of the reasons
to use the Subdivide. The interesting ones are KNN K=1 and
exists(where ST_Intersects()).</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Thu, Feb 17, 2022 at 9:49
PM Marco Boeringa <<a href="mailto:marco@boeringa.demon.nl"
moz-do-not-send="true" class="moz-txt-link-freetext">marco@boeringa.demon.nl</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi
all,<br>
<br>
As a follow up to the mail that I attached below and that
unfortunately <br>
presented bogus results due to a processing error, I have now
revisited <br>
the issue and can now present some realistic figures for the
case of <br>
TOAST overhead versus optimal ST_Subdivide default (which as
Paul <br>
pointed out would be 96 vertices to avoid spilling geometries
over to <br>
TOAST).<br>
<br>
The timings are for zooming to either the full data extent or
a partial <br>
extent in QGIS.<br>
<br>
The data represents generalized woodland polygons designed for
a display <br>
scale of 1:100k to 1:250k. This is a custom generalization
routine, that <br>
included smoothing as well, and gives more pleasing and
cartographically <br>
sound results than just running ST_Simplify alone.<br>
<br>
As you can see from the stats, the dataset subdivided at 96
vertices has <br>
understandably a lot more records, almost triple the one
subdivided with <br>
a 5000 vertices limit, although the disk size is only about
20% larger <br>
(as displayed by DBeaver).<br>
<br>
Clearly, from the result, even though fetching a single
non-toasted <br>
record is significantly faster (almost 3x as much records are
retrieved <br>
in only about 1,6x longer time period), compared to records
that have <br>
been subdivided with a much larger limit, the significantly
larger <br>
number of records to process does in fact mean that in day to
day usage <br>
you may not see this benefit, and the net result of <br>
'subdividing-to-avoid-TOAST' may in fact be negative in terms
of total <br>
processing / display time, depending on the nature of the
dataset.<br>
<br>
Note that for the 5000 vertices dataset, there are only
191,005 records <br>
(14%) with > 96 vertices, and only 141,899 records (11%)
with > 128 <br>
vertices (another figure Paul mentioned as a possible <br>
'spill-over-to-TOAST' limit), so the vast majority of records
(86% at 96 <br>
limit) are still below the TOAST limit even for the dataset
subdivided <br>
with 5000 vertices limit.<br>
<br>
Also note that the data resided on a NVMe based array, so
access time <br>
overhead for TOAST are likely limited compared to HDD.<br>
<br>
The result are as follows:<br>
<br>
* Stats: *<br>
96 vertices: 3,754,257 records: 3.3GB disk size<br>
5000 vertices: 1,332,258 records: 2.7GB disk size<br>
<br>
* Zooming to full extent of the data: *<br>
96 vertices: 68s / 68s /65s / 64s: 66s average<br>
5000 vertices: 41s / 40s / 38s / 41: 40s average<br>
<br>
* Zooming to partial extent of the data: *<br>
96 vertices: 4,51s / 4,12s /4,30s / 4,32s<br>
5000 vertices: 2,21s / 2,14s / 2,51s / 2,53s<br>
<br>
Marco<br>
<br>
Op 26-1-2022 om 10:32 schreef Marco Boeringa:<br>
> Hi all,<br>
><br>
> After Paul's remarks here on the list about the cost of
TOAST in <br>
> relation to the optimal default for ST_Subdivide's vertex
limit (96 <br>
> according to Paul's tests), I got a bit fascinated and
wanted to do <br>
> some testing myself.<br>
><br>
> Until Paul's remark, I never gave much thought about
TOAST overhead in <br>
> relation to my OpenStreetMap database. I just simply
assumed it as a <br>
> fact, as it was likely big geometries needed TOASTing in
many cases.<br>
><br>
> However, since Paul gave a clear guideline to prevent
TOASTing, I gave <br>
> it a try and collected some rough statistics.<br>
><br>
> The data is from generalized OpenStreetMap woodland
polygons, some of <br>
> which are absolutely huge before ST_Subdivide kicks in in
the <br>
> generalization processing (> 100k vertices), as I
amalgamate them to <br>
> bigger structures in the generalization processing.<br>
><br>
> I now tested with two subdivide limits: the default 5000
I had been <br>
> using up to now, which seemed a reasonable compromise
between limiting <br>
> the number of vertices in a polygon and the number of
output polygons <br>
> at the same time: not to small to generate large amounts
of splits, <br>
> but also not to big to cause issues with display times.<br>
><br>
> Next, I used Paul's recommended "prevent TOAST" limit of
96 vertices. <br>
> I subsequently looked at display times for the entire
dataset in QGIS <br>
> by zooming to the dataset's extent and timing the display
time.<br>
><br>
> The result are as follows:<br>
><br>
> 96 vertices: 1,996,226 records: 1.8GB disk size: 33s /
32s /33s / 32s<br>
> 5000 vertices: 1,332,258 records: 2.7GB disk size: 45s /
39s / 38s / 39s<br>
><br>
> A few take aways:<br>
><br>
> - What I never realised before, is also the disk size
cost of TOAST: <br>
> as can be seen, the '5000' limit size, which requires
many geometries <br>
> to be TOASTed, results in an almost 40% larger disk size
for the <br>
> relation according to DBeaver (2.7 versus 1.8 GB for
'5000' versus <br>
> '96' vertex limit).<br>
><br>
> - Non-TOASTed records have an about 20-35% faster
retrieval time, <br>
> although it seems that especially the initial time for
TOASTed has a <br>
> bigger delay (45s), I guess this is because the
de-TOASTed records are <br>
> subsequently cached. Even taking that in account, the
overhead seems <br>
> to plateau at 20% minimum.<br>
><br>
> - Counter-intuitively, displaying almost 600k
(non-TOASTed) records <br>
> more due to much smaller ST_Subdivide vertex limit, is
still <br>
> considerably faster than the displaying the smaller (in
terms of <br>
> records) dataset that did get TOASTed.<br>
><br>
> Does this all seem about right? And does this fit other
users <br>
> experiences?<br>
><br>
> Of course, despite the gains of non TOASTing, you still
have to <br>
> evaluate for each dataset whether subdividing even makes
sense: it is <br>
> usually the last step in processing, and if you actually
need the <br>
> entire polygon for e.g. labelling purposes in QGIS, than
subdividing <br>
> in such small pieces as to prevent TOASTing, doesn't make
sense at all.<br>
><br>
> Marco<br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org"
target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">postgis-users@lists.osgeo.org</a><br>
> <a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank"
moz-do-not-send="true" class="moz-txt-link-freetext">postgis-users@lists.osgeo.org</a><br>
<a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></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>