[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 pramsey):

 I tried this test case and didn't see a substantial difference in
 performance between inserting into a table with a functional index on
 ST_Transform and inserting into a table with an ordinary index
 {{{
 drop table if exists t1;
 drop table if exists t2;
 drop table if exists t3;

 create table t1 (g geometry);
 create table t2 (g geometry);
 create table t3 (g geometry);

 -- no index on t1
 create index t2_g_x on t2 using gist (g);
 create index t3_g_x on t3 using gist (st_transform(g, 4326));

 \timing
 -- just transform
 select st_transform(st_setsrid(st_makepoint(360.0*random()-180,
 180*random()-90), 4326), 3857) as g from generate_series(1,10000);
 -- transform + insert, no index
 insert into t1 select
 st_transform(st_setsrid(st_makepoint(360.0*random()-180, 180*random()-90),
 4326), 3857) as g from generate_series(1,10000);
 -- transform + insert + index
 insert into t2 select
 st_transform(st_setsrid(st_makepoint(360.0*random()-180, 180*random()-90),
 4326), 3857) as g from generate_series(1,10000);
 -- transform + insert + functional index
 insert into t3 select
 st_transform(st_setsrid(st_makepoint(360.0*random()-180, 180*random()-90),
 4326), 3857) as g from generate_series(1,10000);
 }}}

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