[postgis-tickets] [PostGIS] #4686: Faster bbox access for PG12+

[PostGIS]

#4686: Faster bbox access for PG12+
--------------------------+---------------------------
  Reporter:  Algunenano   |      Owner:  Algunenano
      Type:  enhancement  |     Status:  assigned
  Priority:  medium       |  Milestone:  PostGIS 3.1.0
 Component:  postgis      |    Version:  master
Resolution:               |   Keywords:
--------------------------+---------------------------

Comment (by Algunenano):

 PR:  https://github.com/postgis/postgis/pull/572

 Numbers:
   * ANALYZE:

 {{{
 ------------------ POINTS -------------------
 -- ANALYZE benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (the_geom, the_geom_webmercator);
 -- Master: 116.324
 -- Faster bbox: 117.262
 -- Perf: 0.99x

 ---------------- BIG POLYGONS -------------------
 -- ANALYZE benchmark_4c7214d90a79aa6760367a084a4d4a2f61fbe1c6cc4f7f9e76020
 (the_geom, the_geom_webmercator);
 -- Master: 70.258
 -- Faster bbox: 2.339
 -- Perf: 30.03x
 }}}

   * ST_PointOutside:

 {{{
 --------------- BIG POLYGONS ----------------
 ---------------------------------------------
 -- EXPLAIN ANALYZE Select _ST_PointOutside(the_geog) from test_geog;
 -- Master: 16.925
 -- Faster bbox: 0.158
 -- Perf: 107.12x
 }}}

   * ST_CombineBbox:

 {{{
 ---------------------------------------------
 -------------- SMALL POLYGONS ---------------
 EXPLAIN ANALYZE Select ST_CombineBbox(NULL::box2d, the_geom) from
 benchmark_af85af967525e69373b7b8678f3bd409a8e78f447671344b7535d;
 -- Master: 290.640
 -- Faster bbox: 311.152
 -- Perf: 0.93x

 -------------- BIG POLYGONS ----------------
 -- EXPLAIN ANALYZE Select ST_CombineBbox(NULL::box2d, the_geom) from
 benchmark_4c7214d90a79aa6760367a084a4d4a2f61fbe1c6cc4f7f9e76020;
 -- Master: 17.288
 -- Faster bbox: 0.165
 -- Perf: 104.77x
 }}}

   * ST_ClipByBox2D (fully inside):

 {{{
 -------------- SMALL POLYGONS ---------------
 -- EXPLAIN ANALYZE Select count(*) FROM
 benchmark_af85af967525e69373b7b8678f3bd409a8e78f447671344b7535d WHERE
 ST_ClipByBox2D(the_geom_webmercator, ST_TileEnvelope(0,0,0)) IS NOT NULL;
 -- Master: 155.528
 -- Faster bbox: 129.040
 -- Perf: 1.21x

 -------------- BIG POLYGONS ----------------
 -- EXPLAIN ANALYZE Select count(*) FROM
 benchmark_4c7214d90a79aa6760367a084a4d4a2f61fbe1c6cc4f7f9e76020 WHERE
 ST_ClipByBox2D(the_geom_webmercator, ST_TileEnvelope(0,0,0)) IS NOT NULL;
 -- Master: 19.266
 -- Faster bbox: 0.220
 -- Perf: 87.39x
 }}}

   * ST_ClipByBox2D (fully outside):

 {{{
 -------------- SMALL POLYGONS ---------------
 -- EXPLAIN ANALYZE Select count(*) FROM
 benchmark_af85af967525e69373b7b8678f3bd409a8e78f447671344b7535d WHERE
 ST_ClipByBox2D(the_geom_webmercator, ST_TileEnvelope(20,0,0)) IS NOT NULL;
 -- Master: 171.451
 -- Faster bbox: 147.365
 -- Perf: 1.16x

 -------------- BIG POLYGONS ----------------
 -- EXPLAIN ANALYZE Select count(*) FROM
 benchmark_4c7214d90a79aa6760367a084a4d4a2f61fbe1c6cc4f7f9e76020 WHERE
 ST_ClipByBox2D(the_geom_webmercator, ST_TileEnvelope(20,0,0)) IS NOT NULL;
 -- Master: 19.265
 -- Faster bbox: 0.226
 -- Perf: 85.24x
 }}}

   * ST_BoundingDiagonal (fully outside):

 {{{
 -------------- SMALL POLYGONS ---------------
 -- EXPLAIN ANALYZE Select ST_BoundingDiagonal(the_geom) from
 benchmark_af85af967525e69373b7b8678f3bd409a8e78f447671344b7535d;
 -- Master: 566.389
 -- Faster bbox: 446.435
 -- Perf: 1.26x

 -------------- BIG POLYGONS ----------------
 -- EXPLAIN ANALYZE Select ST_BoundingDiagonal(the_geom) from
 benchmark_4c7214d90a79aa6760367a084a4d4a2f61fbe1c6cc4f7f9e76020;
 -- Master: 19.279
 -- Faster bbox: 0.153
 -- Perf: 126x
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4686#comment:3>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.