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

 Yup, I can't reproduce the 4x slowdown with your test case neither.

 Here is a step by step method to reproduce my test case:
 - Download the fixture (https://raw.githubusercontent.com/CartoDB
 /Windshaft-
 cartodb/master/test/support/sql/ported/populated_places_simple_reduced.sql)
 - Create a clean database, install postgis and import the table (ignore
 the errors).
 {{{
 psql -d proj_test -f /tmp/populated_places_simple_reduced.sql
 }}}
 - Create the table:
 {{{
 CREATE TABLE analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4
 AS
  SELECT * FROM (SELECT
  ST_Buffer(the_geom::geography, 50000)::geometry
 the_geom,scalerank,natscale,labelrank,featurecla,name,namepar,namealt,diffascii,nameascii,adm0cap,capalt,capin,worldcity,megacity,sov0name,sov_a3,adm0name,adm0_a3,adm1name,iso_a2,note,latitude,longitude,changed,namediff,diffnote,pop_max,pop_min,pop_other,geonameid,meganame,ls_name,ls_match,checkme,cartodb_id,created_at,updated_at
  FROM (select * from populated_places_simple_reduced) _camshaft_buffer)
 _analysis_create_table_query
  LIMIT 0
 }}}

 - Create the index:
 {{{
 CREATE INDEX ON
 analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4 USING GIST
 (ST_Transform(the_geom, 3857))
 }}}


 - Insert (this is using PROJ 5):
 {{{
 # INSERT INTO analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4
 SELECT
  ST_Buffer(the_geom::geography, 50000)::geometry
 the_geom,scalerank,natscale,labelrank,featurecla,name,namepar,namealt,diffascii,nameascii,adm0cap,capalt,capin,worldcity,megacity,sov0name,sov_a3,adm0name,adm0_a3,adm1name,iso_a2,note,latitude,longitude,changed,namediff,diffnote,pop_max,pop_min,pop_other,geonameid,meganame,ls_name,ls_match,checkme,cartodb_id,created_at,updated_at
  FROM (select * from populated_places_simple_reduced) _camshaft_buffer;

 INSERT 0 7313
 Time: 1801.362 ms (00:01.801)
 }}}

 * Change to PROJ 6 and truncate the table (I restarted postgresql to make
 sure it's clean from the old .so's):
 {{{
 # truncate analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4;
 FATAL:  terminating connection due to administrator command
 server closed the connection unexpectedly
         This probably means the server terminated abnormally
         before or while processing the request.
 The connection to the server was lost. Attempting reset: Succeeded.
 Time: 4.003 ms
 proj_test=# truncate
 analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4;
 TRUNCATE TABLE
 Time: 60.029 ms
 }}}


 * Insert using the same query, now using PROJ 6:
 {{{
 # INSERT INTO analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4
 SELECT
  ST_Buffer(the_geom::geography, 50000)::geometry
 the_geom,scalerank,natscale,labelrank,featurecla,name,namepar,namealt,diffascii,nameascii,adm0cap,capalt,capin,worldcity,megacity,sov0name,sov_a3,adm0name,adm0_a3,adm1name,iso_a2,note,latitude,longitude,changed,namediff,diffnote,pop_max,pop_min,pop_other,geonameid,meganame,ls_name,ls_match,checkme,cartodb_id,created_at,updated_at
  FROM (select * from populated_places_simple_reduced) _camshaft_buffer;
 INSERT 0 7313
 Time: 7515.885 ms (00:07.516)
 }}}



 Technically it's doing the same as yours, inserting points, but without
 doing transformations in the query and instead calling ST_Buffer, but I've
 tried removing ST_Transform in your queries and I still didn't see the
 slowdown.

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