[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:  medium      |  Milestone:  PostGIS 3.1.2
 Component:  postgis     |    Version:  3.1.x
Resolution:              |   Keywords:
-------------------------+---------------------------

Comment (by robe):

 Test from David Boone that exercises the issue:

 I’ve been fighting a performance issue for a while with some installs of
 Postgres/PostGIS, and finally managed to create a small reproducible test
 case.  It shows a simple query’s execution time going from 10 to 400 ms
 when upgrading PostGIS from 2.5 -> 3.1.  This may be the same issue or
 related to: https://trac.osgeo.org/postgis/ticket/4890


 {{{


 DROP EXTENSION IF EXISTS postgis CASCADE;
 DROP TABLE IF EXISTS points;

 CREATE EXTENSION postgis;

 CREATE TABLE points AS SELECT * FROM (VALUES ('SRID=4326;POINT(-124.9921
 49.6851)'::geometry), ('SRID=4326;POINT(-119.4032 50.0305)'::geometry),
 ('SRID=4326;POINT(-122.799 49.1671)'::geometry),
 ('SRID=4326;POINT(-122.3379 49.0597)'::geometry),
 ('SRID=4326;POINT(-123.1264 49.2671)'::geometry),
 ('SRID=4326;POINT(-122.7132 49.0519)'::geometry),
 ('SRID=4326;POINT(-124.3475 49.3042)'::geometry),
 ('SRID=4326;POINT(-119.389 49.8891)'::geometry),
 ('SRID=4326;POINT(-123.126 49.281)'::geometry),
 ('SRID=4326;POINT(-122.6606 49.1134)'::geometry),
 ('SRID=4326;POINT(-124.3233 49.312)'::geometry),
 ('SRID=4326;POINT(-124.0478 49.2397)'::geometry),
 ('SRID=4326;POINT(-119.2683 50.266)'::geometry),
 ('SRID=4326;POINT(-121.9705 49.081)'::geometry),
 ('SRID=4326;POINT(-123.8854 49.482)'::geometry),
 ('SRID=4326;POINT(-123.1528 49.77)'::geometry),
 ('SRID=4326;POINT(-120.8051 50.488)'::geometry),
 ('SRID=4326;POINT(-122.6403 49.1652)'::geometry),
 ('SRID=4326;POINT(-122.7717 49.2433)'::geometry),
 ('SRID=4326;POINT(-121.9587 49.1661)'::geometry))
 p(g);

 CREATE INDEX ON points USING gist(g);

 CREATE OR REPLACE FUNCTION get_closest(p geometry(POINT)) RETURNS
 geometry(POINT) AS $$
     SELECT g FROM points ORDER BY st_transform(p, 3005) <->
 st_transform(points.g, 3005) LIMIT 1
 $$
 STABLE
 LANGUAGE SQL;

 SELECT postgis_full_version();

 EXPLAIN (ANALYZE) SELECT *, get_closest(g) FROM points ;


 }}}

 I hope this is useful, please feel free to add it to the issue tracker or
 any other place / person it can be of use.  Please let me know if I can
 help further or contribute in any way.

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