I'm migrating our PostGIS 2.5 stack to PostGIS 3.1. In general it's been really smooth - a few of our operations on larger geometries are much faster, and the rest of our performance is about the same. The one snag is that one of our user-defined functions is now taking 11 seconds to run, when it previously took 1.5 seconds. Here's an MWE:
-- create initial geom table of a large rectangle over Kansas & Nebraska in the US
create table _tmp1 as
select st_setsrid(st_geomfromgeojson('{"type":"Polygon","coordinates":[[[-104.4140625,37.78808138412046],[-94.74609375,37.78808138412046],[-94.74609375,42.09822241118974],[-104.4140625,42.09822241118974],[-104.4140625,37.78808138412046]]]}
'), 4326) as geom;
-- duplicate this geom into a bunch of additional rows to better compare performance
insert into _tmp1 (geom)
SELECT geom FROM _tmp1 CROSS JOIN generate_series(1,1000) as x;
-- create our MWE function- much simpler than the actual code
create or replace function st_dump_mwe(geom geometry)
returns geometry
language sql immutable as
$func$
select (ST_DumpRings((ST_Dump(geom)).geom)).geom;
$func$;
When I call this function in PostGIS 3.1 via select st_dump_mwe(geom) from _tmp1;
, it takes ~11 seconds. In PostGIS 2.5, it takes 1.5 seconds.
PostGIS 3.1 full version:
POSTGIS="3.1.0 5e2af69" [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)"
PostGIS 2.5 full version:
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
Both versions are running locally on my machine in Docker and were installed with the standard postgresql-13-postgis-3
and postgresql-13-postgis-2.5
ubuntu packages.
Any idea what's going on here? Are these changes expected?
Finally, are there any external resources that cover potential pitfalls associated with this upgrade? I've read the v3.0 changelog but haven't been able to identify where this might originate.
postgresql-13-postgis-2.5
andpostgresql-13-postgis-2.5-scripts
to postgis-3. The query plans look very similar- here's the PostGIS 3.1: ``` Result (cost=0.00..267762.77 rows=1000000 width=32) -> ProjectSet (cost=0.00..5262.77 rows=1000000 width=32) -> ProjectSet (cost=0.00..5.27 rows=1000 width=32) -> Result (cost=0.00..0.01 rows=1 width=0) ```select st_dump_mwe(geom) from _tmp1;
, which is a simpleseq scan
. PEV doesn't show any difference between PostGIS versions except for different execution times (PostGIS 2.5: 2 s, PostGIS 3.1: 33 seconds) and costliest node (PostGIS 2.5: 279.26, PostGIS 3.1: 690.84).SET auto_explain.log_nested_statements = ON
to log within-function statements, but am not sure how to do this with the JSON format required by PEV. Any help appreciated!