[postgis-devel] PostGIS performance regression

[Paul Ramsey]

Incidentally, there is a workaround which avoids the problem (largely). 

SELECT p1.g
FROM points p1
CROSS JOIN LATERAL (
 SELECT p1.g FROM points p2
 ORDER BY st_transform(p1.g, 3005) <-> st_transform(p2.g, 3005)
 LIMIT 1
) g1

The problem is that your wrapper function is making the proj system re-initialize itself for each function call. The init overhead is about 40ms. You have 20 rows in your table, so 40ms * 20 inits = 800ms which is more-or-less exactly the speed I'm seeing. If you use the lateral join the system is able to do the nearest-neighbor-per-row calculation with only one init, so the whole thing takes 40ms.

P.

> On May 11, 2021, at 4:30 PM, David Boone <dboone at gmail.com> wrote:
> 
> Hi Paul,
> 
> Sorry for direct email, but I am unable to post on the OSGeo issue tracker.  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 fired up a Ubuntu docker instance, added the focal-pgdg apt source, and installed the postgresql-13 package using apt, then ran the above bit of SQL, switching between PostGIS 2.5 & 3.1:
> 
> apt install postgresql-13-postgis-2.5
> 
> POSTGIS="2.5.5" [EXTENSION] PGSQL="130" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 6.3.1, February 10th, 2020" GDAL="GDAL 3.0.4, released 2020/01/28" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" RASTER
>  Planning Time: 0.123 ms
>  Execution Time: 8.223 ms
> 
> 
> apt purge postgresql-13-postgis-*
> apt install postgresql-13-postgis-3
> 
> POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="130" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
>  Planning Time: 0.103 ms
>  Execution Time: 404.618 ms
> 
> 
> One more data point from my Debian box:
> 
> POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="130" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1" WAGYU="0.5.0 (Internal)"
>  Planning Time: 0.094 ms
>  Execution Time: 6.238 ms
> 
> 
> 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.
> 
> Thank-you!
> 
> - David Boone
> 604-316-3693
>