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
      select (ST_DumpRings((ST_Dump(geom)).geom)).geom;

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.

  • Have you compared the configuration settings for the two installs? You can see them by typing Show ALL into a pgadmin query window. Also, have checked the output for explain analyze select st_dump_mwe(geom) from _tmp1 for both installs?
    – jgm_GIS
    Jan 11, 2021 at 19:52
  • Thanks for looking into this @jgm_GIS! I've compared the configuration for both installs - they are identical (makes sense- they are built from the same Dockerfile, only changing postgresql-13-postgis-2.5 and postgresql-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) ``` Jan 11, 2021 at 22:06
  • If nothing else, the actual time listed by explain analyze should be different for the two query plans. I like to use this tool to visually display query plans and diagnose performance issues. tatiyants.com/pev/#/plans
    – jgm_GIS
    Jan 12, 2021 at 12:23
  • Thanks for this @jgm_GIS. I'm having a hard time using the PEV tool to get at the differences between these queries. The SQL I'm running is select st_dump_mwe(geom) from _tmp1;, which is a simple seq 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). Jan 13, 2021 at 14:16
  • I've used 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! Jan 13, 2021 at 14:17


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.