[postgis-tickets] [PostGIS] #4372: PROJ 6: 4x slowdown on indexes over ST_Transform

[PostGIS]

#4372: PROJ 6: 4x slowdown on indexes over ST_Transform
-------------------------+---------------------------
  Reporter:  Algunenano  |      Owner:  Algunenano
      Type:  defect      |     Status:  assigned
  Priority:  high        |  Milestone:  PostGIS 3.0.0
 Component:  postgis     |    Version:  trunk
Resolution:              |   Keywords:
-------------------------+---------------------------

Comment (by Algunenano):

 Doing tests using svn-trunk as it is right now:

 {{{
 # Select count(*) from populated_places_simple_reduced;
  count
 -------
   7313
 (1 row)
 projtest=# \d populated_places_simple_reduced
                                                 Table
 "public.populated_places_simple_reduced"
         Column        |           Type           | Collation | Nullable |
 Default

 ----------------------+--------------------------+-----------+----------+------------------------------------------------------------------
 ---
  the_geom             | geometry(Geometry,4326)  |           |          |
  scalerank            | integer                  |           |          |
  natscale             | integer                  |           |          |
  labelrank            | integer                  |           |          |
  featurecla           | text                     |           |          |
  name                 | text                     |           |          |
  namepar              | text                     |           |          |
  namealt              | text                     |           |          |
  diffascii            | integer                  |           |          |
  nameascii            | text                     |           |          |
  adm0cap              | double precision         |           |          |
  capalt               | double precision         |           |          |
  capin                | text                     |           |          |
  worldcity            | double precision         |           |          |
  megacity             | integer                  |           |          |
  sov0name             | text                     |           |          |
  sov_a3               | text                     |           |          |
  adm0name             | text                     |           |          |
  adm0_a3              | text                     |           |          |
  adm1name             | text                     |           |          |
  iso_a2               | text                     |           |          |
  note                 | text                     |           |          |
  latitude             | double precision         |           |          |
  longitude            | double precision         |           |          |
  changed              | double precision         |           |          |
  namediff             | integer                  |           |          |
  diffnote             | text                     |           |          |
  pop_max              | integer                  |           |          |
  pop_min              | integer                  |           |          |
  pop_other            | integer                  |           |          |
  geonameid            | double precision         |           |          |
  meganame             | text                     |           |          |
  ls_name              | text                     |           |          |
  ls_match             | integer                  |           |          |
  checkme              | integer                  |           |          |
  cartodb_id           | integer                  |           | not null |
 nextval('populated_places_simple_reduced_cartodb_id_seq'::regclas
 s)
  created_at           | timestamp with time zone |           | not null |
 now()
  updated_at           | timestamp with time zone |           | not null |
 now()
  the_geom_webmercator | geometry(Geometry,3857)  |           |          |
 Indexes:
     "populated_places_simple_reduced_pkey" PRIMARY KEY, btree (cartodb_id)
     "populated_places_simple_reduced_cartodb_id_key" UNIQUE CONSTRAINT,
 btree (cartodb_id)
     "populated_places_simple_reduced_the_geom_webmercator_idx" gist
 (the_geom_webmercator)
     "the_geom_ffebe516_1e32_11e4_ae44_6476ba93d4f6" gist (the_geom)

 # Select count(1), st_geometrytype(the_geom_webmercator) as t from
 populated_places_simple_reduced group by t ;
  count |    t
 -------+----------
      1 |
   7312 | ST_Point
 (2 rows)

 }}}

 PROJ 5.2:
 {{{
 Select st_transform(the_geom_webmercator, 4234) from
 populated_places_simple_reduced;
 Time: 22.917 ms (first time)
 Time: 14.330 ms (second time)
 }}}

 PROJ 6.1:
 {{{
 Time: 69.540 ms (first time)
 Time: 51.091 ms (second time)
 }}}


 This slowdown also appears when doing 3857 -> 4326.

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