[postgis-users] Experiences with the new faster GiST building in PostGIS 3.2 and PostgreSQL 14?

[Regina Obe]

I think Han and Kontur have some benchmarks.

https://git.osgeo.org/gitea/postgis/postgis-performance 

In my pgTap benchmarks the index build timing is approximately 3-4 times
faster with osm data of Belarus and China.  Han said the query performance
slowed a little, but I didn't see that in my pgTap tests (no really
noticeable difference with intersect checking), so might have been fixed by
Kontur in their cleanup before I ran these tests or I'm missing a test.

Here are the pgTap benchmarks based on alpha1 with PG 14.0rc1, nothing with
the index has changed since so should be representative. 

https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/pg
tap/postgis/03_build_gist_index.sql 
-- index building compare --
PostGIS 3.1.4
[08:45:51] pgtap/postgis/03_build_gist_index.sql .... 
1..3
ok 1 - Index built: multipolygons
ok 2 - Index built: lines
ok 3 - Index built: points
ok    70742 ms ( 0.01 usr  0.00 sys +  0.00 cusr  0.01 csys =  0.02 CPU)


PostGIS 3.2.0alpha1
[09:17:54] pgtap/postgis/03_build_gist_index.sql .... 
1..3
ok 1 - Index built: multipolygons
ok 2 - Index built: lines
ok 3 - Index built: points
ok    17029 ms ( 0.00 usr  0.00 sys +  0.00 cusr  0.01 csys =  0.01 CPU)


-- intersect checks compare
https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/pg
tap/postgis/04_index_intersect.sql

-- POSTGIS "3.1.4"
 [08:46:16] pgtap/postgis/04_index_intersect.sql ..... 
1..9
ok 1 - Check has osm_belarus.multipolygons has gist
ok 2 - Check has osm_belarus.lines geom gist
ok 3 - Check has osm_belarus.points geom gist
ok 4 - intersect multipolygon check no match with index
ok 5 - intersect multipolygon check match with index
ok 6 - intersect linestring check no match with index
ok 7 - intersect linestring check match with index
ok 8 - intersect pointcheck no match with index
ok 9 - intersect point check match with index
ok    24981 ms ( 0.00 usr  0.00 sys +  0.02 cusr  0.00 csys =  0.02 CPU)

-- POSTGIS="3.2.0alpha1 3.2.0alpha1"
[09:18:16] pgtap/postgis/04_index_intersect.sql ..... 
1..9
ok 1 - Check has osm_belarus.multipolygons has gist
ok 2 - Check has osm_belarus.lines geom gist
ok 3 - Check has osm_belarus.points geom gist
ok 4 - intersect multipolygon check no match with index
ok 5 - intersect multipolygon check match with index
ok 6 - intersect linestring check no match with index
ok 7 - intersect linestring check match with index
ok 8 - intersect pointcheck no match with index
ok 9 - intersect point check match with index
ok    22459 ms ( 0.00 usr  0.00 sys +  0.00 cusr  0.01 csys =  0.01 CPU)

> -----Original Message-----
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
Behalf
> Of Marco Boeringa
> Sent: Saturday, November 27, 2021 4:27 PM
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: [postgis-users] Experiences with the new faster GiST building in
> PostGIS 3.2 and PostgreSQL 14?
> 
> Hi all,
> 
> Now with the betas out for PostGIS 3.2, I was wondering if anyone has some
> real world comparative figures to share for creating spatial indexes using
GiST
> in PostGIS 3.2 and PostgreSQL 14, versus the older GiST implementation in
> previous versions?
> 
> Creating spatial indexes on e.g. OpenStreetMap sized datasets is still an
> expensive operation, and any gains in the latest releases would be highly
> welcome. Has someone done a comparison between releases that might give
> all of us that haven't yet upgraded, an idea of possible benefits of the
new
> implementation?
> 
> Marco
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users