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

[PostGIS]

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

Comment (by SAbernethy):

 Done some further testing (on the same machine with same version of
 postgres v11 and same postgres conf) and the results are as follows.

 On a table that I'm inserting rows into I do the following sql via trigger
 to produce the shape geometry. The data i'm pulling for the inputs is not
 changing between runs either.

 {{{
 CREATE OR REPLACE FUNCTION ot2.create_shape_for_marker_radius(_lat double
 precision, _lon double precision, _radius integer)
  RETURNS geometry
  LANGUAGE plpgsql
 AS $function$
 DECLARE
 BEGIN
         RETURN postgis.st_transform(
                 postgis.st_buffer(
 postgis.st_transform(postgis.st_setsrid(postgis.st_makepoint(_lon,
 _lat),4269), 26986),
                         GREATEST(1, _radius * 0.3048)::integer),
                 4326);
 END;
 $function$
 }}}

 On 2.4.8 I get following results
 Doing 500000 inserts takes 4 minutes and 55 secs and query times below
 {{{
       avg time       |       max time      |       min time
 ---------------------+---------------------+-----------------
  00:00:00.000526     | 00:00:00.033375     | 00:00:00.000489
 }}}

 On 2.5.5 I get following results
 Doing 500000 inserts takes 4 minutes and 19 secs and query times below
 {{{
       avg time       |       max time      |       min time
 ---------------------+---------------------+-----------------
  00:00:00.000516     | 00:00:00.032929     | 00:00:00.000481
 }}}

 On 3.0.3 I get following results
 Doing 500000 inserts takes 1 hour and 4 secs and query times below
 {{{
       avg time       |       max time      |       min time
 ---------------------+---------------------+-----------------
  00:00:00.007205     | 00:00:00.054506     | 00:00:00.007068
 }}}

 On 3.1.1 I get the following
 Doing 500000 inserts takes 1 hour and 57 secs and query times below
 {{{
        avg time      |       max time        |       min time
 ---------------------+-----------------------+-----------------
  00:00:00.007306     | 00:00:00.054673       | 00:00:00.007076
 }}}

 As you can see this timing difference is a large and this is a blocker for
 us upgrading as we can be calling this sql at least 2-3 mil times per day.
 A large slow down like this makes this unreasonable to move forward with
 upgrading either version 3.0.3 or 3.1.1 of postgis.

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