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

 Inverting the order I get the same result (proj6 is 4x slower):
 {{{
 # create database proj_test2;
 CREATE DATABASE
 postgres=# \c proj_test2;
 You are now connected to database "proj_test2" as user "postgres".
 proj_test2=# \timing on
 Timing is on.
 proj_test2=# create extension postgis;
 CREATE EXTENSION
 Time: 566.929 ms
 proj_test2=# \q
 }}}


 {{{
  psql -d proj_test2 -f /tmp/populated_places_simple_reduced.sql
 CREATE TABLE
 CREATE SEQUENCE
 ALTER SEQUENCE
 ALTER TABLE
 [...]
 }}}

 {{{
 $ psql -U postgres proj_test2
 psql (11.2)
 Type "help" for help.

 proj_test2=# \timing on
 Timing is on.
 proj_test2=# CREATE TABLE
 analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4 AS
 proj_test2-#  SELECT * FROM (SELECT
 proj_test2(#  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
 proj_test2(#  FROM (select * from populated_places_simple_reduced)
 _camshaft_buffer) _analysis_create_table_query
 proj_test2-#  LIMIT 0;
 SELECT 0
 Time: 37.848 ms
 proj_test2=# CREATE INDEX ON
 analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4 USING GIST
 (ST_Transform(the_geom, 3857))
 proj_test2-# ;
 CREATE INDEX
 Time: 5.513 ms
 proj_test2=# INSERT INTO
 analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4 SELECT
 proj_test2-#  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
 proj_test2-#  FROM (select * from populated_places_simple_reduced)
 _camshaft_buffer;
 INSERT 0 7313
 Time: 1813.043 ms (00:01.813)
 proj_test2=# \q
 }}}

 Install proj6 + postgis compiled against it:
 {{{
 $ sudo pacman -U proj-git/proj-git-5.2.0.r710.gfb125618f-1-x86_64.pkg.tar
 postgis-git/postgis-
 git-3.0_svn_trunk_LTO.12686.cb0e3bc69-1-x86_64.pkg.tar.proj6
 [...]
 }}}

 {{{
 $ psql -U postgres proj_test2
 psql (11.2)
 Type "help" for help.

 proj_test2=# truncate
 analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4 ;
 TRUNCATE TABLE
 proj_test2=# \timing on
 Timing is on.
 proj_test2=# 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: 7516.018 ms (00:07.516)
 proj_test2=#
 }}}

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