That''s smarter, indeed. Requires more disk space though, but comes without table locks.
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.
So then, creating an GeoHash (Z-Curve) ordered table and BRIN-index the geom column is the fastest option
... in case your data is static. Otherwise performance of the index will degrade sooner or later.
Gesendet: Dienstag, 15. Januar 2019 um 23:07 Uhr
Von: "Paul Norman" <penor...@mac.com>
An: postgis-users@lists.osgeo.org
Betreff: Re: [postgis-users] Slow construction of GiST index, but better with smaller # of big rows
Von: "Paul Norman" <penor...@mac.com>
An: postgis-users@lists.osgeo.org
Betreff: Re: [postgis-users] Slow construction of GiST index, but better with smaller # of big rows
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.
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
> 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.
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
