<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>That''s smarter, indeed. Requires more disk space though, but comes without table locks.</div>
<div>In my case, I wanted to track the execution times for CLUSTER operations, too - in order to tell up to which amount of rows it's a viable option.</div>
<div> </div>
<div>So then, creating an GeoHash (Z-Curve) ordered table and BRIN-index the geom column is the fastest option</div>
<div>... in case your data is static. Otherwise performance of the index will degrade sooner or later. </div>
<div> </div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Dienstag, 15. Januar 2019 um 23:07 Uhr<br/>
<b>Von:</b> "Paul Norman" <penorman@mac.com><br/>
<b>An:</b> postgis-users@lists.osgeo.org<br/>
<b>Betreff:</b> Re: [postgis-users] Slow construction of GiST index, but better with smaller # of big rows</div>
<div name="quoted-content">On 2019-01-15 1:45 p.m., Felix Kunde wrote:<br/>
> For optimal performance your spatial can be clustered on disk. For<br/>
> this, I created a functional btree index transforming the geometries<br/>
> with ST_GeoHash. Btree indexes can be created in parallel with the<br/>
> latest Postgres version (took me 30min). Unfortunately, the CLUSTER<br/>
> command then takes a long time (8hrs on my machine).<br/>
<br/>
<br/>
If you're loading up the data a method faster than CLUSTER is to create<br/>
a new table with CREATE TABLE foo AS SELECT * FROM foo_tmp ORDER BY<br/>
ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10) COLLATE "C"<br/>
<br/>
With CLUSTER you have to create a temporary index, rewrite the table,<br/>
and then recreate that temporary index, only to drop it again.<br/>
<br/>
_______________________________________________<br/>
postgis-users mailing list<br/>
postgis-users@lists.osgeo.org<br/>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div>
</div>
</div></div></body></html>