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

Sun Nov 28 16:53:28 PST 2021

Many thanks to you Giuseppe for suggesting the idea and taking on the job of being a GSOC mentor.  Wouldn’t have been possible without your initiative.

For those who are interested in the underlying details, Han gave a presentation of his work at FOSS4G 2021.

You can view it here:

https://www.youtube.com/watch?v=Jjgghz_EzWQ <https://www.youtube.com/watch?v=Jjgghz_EzWQ&t=698s> &t=698s

Thanks,

Regina

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Giuseppe Broccolo
Sent: Sunday, November 28, 2021 5:30 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Experiences with the new faster GiST building in PostGIS 3.2 and PostgreSQL 14?

That's really a fantastic news! Thanks Han again for your work on this, and thanks to all the people that helped with this!

Giuseppe. 

On Sun, 28 Nov 2021, 20:26 Regina Obe, <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

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 <https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/pgtap/postgis/03_build_gist_index.sql> 
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 <https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/pgtap/postgis/04_index_intersect.sql> 
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 <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 <mailto: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 <mailto:postgis-users at lists.osgeo.org> 
> https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211128/b41e53d3/attachment.html>