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

[Giuseppe Broccolo]

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> 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
> tap/postgis/03_build_gist_index.sql
> <https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/pgtap/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
> <https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/pgtap/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
>
> _______________________________________________
> postgis-users mailing list
> 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/e9c8ccb3/attachment.html>