Hi Regina,
Thanks for that clear information.
Seeing a 3-4x speed up of GiST index build time, is definitely
significant and comforting, and thanks to all involved for realizing the
new functionality. This will be a welcome enhancement for many PostGIS
and OpenStreetMap data users once they have the opportunity to upgrade.
Marco
Op 28-11-2021 om 20:26 schreef 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-boun...@lists.osgeo.org] On
Behalf
Of Marco Boeringa
Sent: Saturday, November 27, 2021 4:27 PM
To: PostGIS Users Discussion <postgis-users@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@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
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
