[postgis-tickets] [PostGIS] #4890: ST_Transform large slow down from upgrade to 3.1.1 from 2.4

Tue May 18 18:54:06 PDT 2021

#4890: ST_Transform large slow down from upgrade to 3.1.1 from 2.4
-------------------------+---------------------------
  Reporter:  SAbernethy  |      Owner:  pramsey
      Type:  defect      |     Status:  new
  Priority:  medium      |  Milestone:  PostGIS 3.1.2
 Component:  postgis     |    Version:  3.1.x
Resolution:              |   Keywords:
-------------------------+---------------------------

Comment (by robe):

 I've redone the test trying to break out better the tests that can take
 advantage of cache verses not.

 Here are the benmarks I have from our various bots.
 General summary - Proj >= 6 startup time is just much worse than lower
 versions.
 PostGIS 3.1 has not been patched yet. PostGIS 3.2 is after pramsey's
 patch.
 regress_proj_4890 - It's clear from below that the patch improves
 significantly the speed of Daniel Boone's test listed above because of the
 improved proj caching for proj >= 6 and no noticeable impact on proj < 6.

 regress_proj_basic - This is a test of custom proj added and standard proj
 and reuse of these projs. Again improvement about 2 fold (it's a bit
 better because about 72ms of that time is proj startup costs when you flip
 projections). Again only impact for proj >= 6 and no impact for lower
 proj.

 regress_proj_adhoc - has input string proj4 and retransforms.  Again a bit
 better with the patch, no impact for lower proj
 regress_proj_cache_overflow - is cycling thru 12 or so projections -- so
 the slow upstart of newer proj api really shows here.  The patch improves
 the case slightly but not by much.  The only concern I have is that (and I
 checked a couple of runs), for the proj 6.1 case, the patch version does
 show a slight degradation.  Given 6.1.1 isn't even the latest proj micro,
 I'm not that concerned even if there is degradation.  All other proj > 6
 seem to have improved slightly.

 {{{
 PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.0-16)
 10.2.0, 64-bit
   Postgis 3.1.2dev - (e1856ea) - 2021-05-19 00:44:06
   scripts 3.1.2dev e1856ea
   raster scripts 3.1.2dev e1856ea
   GEOS: 3.8.2dev-CAPI-1.13.3
   PROJ: 7.1.1
   SFCGAL: 1.3.9
   GDAL: GDAL 3.1.4, released 2020/10/20

  ./regress/core/regress_proj_basic .. ok in 100 ms
  ./regress/core/regress_proj_adhoc .. ok in 402 ms
  ./regress/core/regress_proj_cache_overflow .. ok in 1353 ms
  ./regress/core/regress_proj_4890 .. ok in 506 ms

  PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6)
 10.2.1 20210110, 64-bit
   Postgis 3.2.0dev - (fa69ef0) - 2021-05-19 00:32:19
   scripts 3.2.0dev fa69ef0
   raster scripts 3.2.0dev fa69ef0
   GEOS: 3.9.2dev-CAPI-1.14.2
   PROJ: 7.1.1
   SFCGAL: 1.3.9
   GDAL: GDAL 3.1.4, released 2020/10/20
  ./regress/core/regress_proj_basic .. ok in 44 ms
  ./regress/core/regress_proj_adhoc .. ok in 355 ms
  ./regress/core/regress_proj_cache_overflow .. ok in 1162 ms
  ./regress/core/regress_proj_4890 .. ok in 52 ms

  PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (Debian
 10.2.1-3) 10.2.1 20201224, 64-bit
   Postgis 3.1.2dev - (e1856ea) - 2021-05-19 00:43:59
   scripts 3.1.2dev e1856ea
   raster scripts 3.1.2dev e1856ea
   GEOS: 3.7.4dev-CAPI-1.11.4 2926110
   PROJ: Rel. 5.2.0, September 15th, 2018
   SFCGAL: 1.3.9
   GDAL: GDAL 2.4.4, released 2020/01/08

  ./regress/core/regress_proj_basic .. ok in 17 ms
  ./regress/core/regress_proj_adhoc .. ok in 19 ms
  ./regress/core/regress_proj_cache_overflow .. ok in 39 ms
  ./regress/core/regress_proj_4890 .. ok in 28 ms

  PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (Debian
 10.2.1-3) 10.2.1 20201224, 64-bit
   Postgis 3.1.2dev - (e1856ea) - 2021-05-19 00:43:56
   scripts 3.1.2dev e1856ea
   raster scripts 3.1.2dev e1856ea
   GEOS: 3.6.6dev-CAPI-1.10.6 4fe5ee7
   PROJ: Rel. 4.9.0, 13 September 2014
   SFCGAL: 1.3.9
   GDAL: GDAL 2.3.3, released 2018/12/14

  ./regress/core/regress_proj_basic .. ok in 16 ms
  ./regress/core/regress_proj_adhoc .. ok in 14 ms
  ./regress/core/regress_proj_cache_overflow .. ok in 39 ms
  ./regress/core/regress_proj_4890 .. ok in 24 ms

  PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (Debian
 10.2.1-3) 10.2.1 20201224, 64-bit
   Postgis 3.2.0dev - (fa69ef0) - 2021-05-19 00:32:13
   scripts 3.2.0dev fa69ef0
   raster scripts 3.2.0dev fa69ef0
   GEOS: 3.7.4dev-CAPI-1.11.4 2926110
   PROJ: Rel. 5.2.0, September 15th, 2018
   SFCGAL: 1.3.9
   GDAL: GDAL 2.4.4, released 2020/01/08

   ./regress/core/regress_proj_basic .. ok in 14 ms
  ./regress/core/regress_proj_adhoc .. ok in 14 ms
  ./regress/core/regress_proj_cache_overflow .. ok in 28 ms
  ./regress/core/regress_proj_4890 .. ok in 17 ms

   PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian
 10.2.0-16) 10.2.0, 64-bit
   Postgis 3.1.2dev - (e1856ea) - 2021-05-19 00:43:59
   scripts 3.1.2dev e1856ea
   raster scripts 3.1.2dev e1856ea
   GEOS: 3.7.3dev-CAPI-1.11.3 4ae53b7
   PROJ: 6.1.1
   SFCGAL: 1.3.9
   GDAL: GDAL 3.0.4, released 2020/01/28

  ./regress/core/regress_proj_basic .. ok in 79 ms
  ./regress/core/regress_proj_adhoc .. ok in 157 ms
  ./regress/core/regress_proj_cache_overflow .. ok in 2641 ms
  ./regress/core/regress_proj_4890 .. ok in 362 ms

  PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian
 10.2.0-16) 10.2.0, 64-bit
   Postgis 3.2.0dev - (fa69ef0) - 2021-05-19 00:32:30
   scripts 3.2.0dev fa69ef0
   raster scripts 3.2.0dev fa69ef0
   GEOS: 3.7.3dev-CAPI-1.11.3 4ae53b7
   PROJ: 6.1.1
   SFCGAL: 1.3.9
   GDAL: GDAL 3.0.4, released 2020/01/28

  ./regress/core/regress_proj_basic .. ok in 35 ms
  ./regress/core/regress_proj_adhoc .. ok in 153 ms
  ./regress/core/regress_proj_cache_overflow .. ok in 3055 ms
  ./regress/core/regress_proj_4890 .. ok in 45 ms
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4890#comment:17>
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.