[postgis-tickets] [PostGIS] #5387: ST_Transform(geom, str, str) is very slow

[PostGIS]

#5387: ST_Transform(geom, str, str) is very slow
------------------------+---------------------------
 Reporter:  cdestigter  |      Owner:  pramsey
     Type:  defect      |     Status:  new
 Priority:  medium      |  Milestone:  PostGIS 3.3.3
Component:  postgis     |    Version:  3.3.x
 Keywords:              |
------------------------+---------------------------
 Using the string arguments to `ST_Transform()` causes a massive slowdown:


 {{{
 # SELECT st_extent(foo.geom) from (select ST_Transform("GEOMETRY",
 'EPSG:7844', 4326) AS geom FROM mytable LIMIT 200) foo;
 Time: 3852.815 ms (00:03.853)

 # SELECT st_extent(foo.geom) from (select ST_Transform("GEOMETRY",
 'EPSG:7844', 'EPSG:4326') AS geom FROM mytable LIMIT 200) foo;
 Time: 527.896 ms

 # SELECT st_extent(foo.geom) from (select ST_Transform("GEOMETRY",  4326)
 AS geom FROM mytable LIMIT 200) foo;
 Time: 22.951 ms
 }}}


 We're using the string argument form to force postGIS to use proj.db
 instead of `spatial_ref_sys` for CRS definitions.

 1. I had assumed that postGIS would cache the transform object, but from a
 quick look at the code it looks like it's probably just instantiating a
 new transform for every geometry, hence the slowness.

 2. I don't understand why the (str, int) form and the (str, str) form are
 so different in terms of timing.

 Could postgis maintain a cache of transform objects for (str, str) pairs,
 to achieve performance similar to the int argument form?
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5387>
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.