[postgis-users] Slow construction of GiST index, but better with smaller # of big rows

[Paul Norman]

On 2019-01-15 1:45 p.m., Felix Kunde wrote:
> For optimal performance your spatial can be clustered on disk. For 
> this, I created a functional btree index transforming the geometries 
> with ST_GeoHash. Btree indexes can be created in parallel with the 
> latest Postgres version (took me 30min). Unfortunately, the CLUSTER 
> command then takes a long time (8hrs on my machine). 


If you're loading up the data a method faster than CLUSTER is to create 
a new table with CREATE TABLE foo AS SELECT * FROM foo_tmp ORDER BY 
ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10) COLLATE "C"

With CLUSTER you have to create a temporary index, rewrite the table, 
and then recreate that temporary index, only to drop it again.