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

[Marco Boeringa]

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-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