[postgis-devel] Much slower processing on GEOS 3.9.0 versus 3.8.0 for geodesic area calculation - Upgrade to PROJ 7.2.1 causes issue

[Paul Ramsey]

I just replicated Boone's test locally. PostGIS 2.5 versus PostGIS 3.1, exactly the same dependencies, including Proj6, and his example query runs 50x slower on 3.1. Almost certainly Proj setup overhead, but still to be confirmed.

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 ;




> On May 12, 2021, at 11:46 AM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> 
> I've gotten a message from David Boone with some instructions on replication on Debian docker images that might yield a profile finally. One thing I noticed immediately while doing a quick try at local replication of this setup was that his systems were surprisingly consistent on dependencies: in fact the GEOS/Proj/GDAL versions were constant, while PostGIS went from 2.5 to 3.1. Proj version was 6. 
> 
> The thing I noticed while trying to build locally was that... I couldn't build PostGIS 2.5 locally. Aha... until PostGIS 3.0 there was no support for the modern proj API. My local proj is proj 8, which has the old API removed, so I cannot build PostGIS 2.5. However, David's setup used Proj 6, and Proj 6 retains support for BOTH the old and new APIs. So you can build PostGIS 2.5 against and also PostGIS 3.0 against it. But you will be building against DIFFERENT APIs, even though the Proj version you are building against is constant. So this is a potential explanation for "I changed nothing but PostGIS version and kept my Proj version constant, and yet my projection performance changed". 
> 
> If this analysis holds up, this is all going to come back down to caching proj objects and the context within which they are cached, almost certainly. Moving the proj cache up into the transaction context seems like the only way to handle this. Unfortunately, since we did great work in ensuring the cache machinery of all our caches is *shared* (proj, preparedgeom, toastcache, rtreecache, etc, etc) that means busting things up in some way. Maybe can get away with just passing a context parameter that's variable depending on cache type.
> 
> I still do not have a reproduceable setup, but these last clues and directions might be enough to finally get there, at which point it's possible to muck out a fix and see if it actually *is* a fix.
> 
> P.
> 
>> On May 6, 2021, at 3:04 AM, Marco Boeringa <marco at boeringa.demon.nl> wrote:
>> 
>> Hi Paul and others,
>> 
>> Ok, so that issue with the wrong display of the PROJ version number when calling 'postgis_full_version' after the upgrade of PROJ from 6.3.1 to 7.2.1 using the UbuntuGIS repository available version, is insignificant.
>> 
>> Is there anything else you want me to do? Even if I had the experience, I do not have the infrastructure to debug PostGIS / PROJ or anything else involved, except for the PostgreSQL / PostGIS install on Ubuntu. You posted a GitHub link to a C file (https://gist.github.com/pramsey/493b2490a8736fd8c00e30efa62e4ec3), but I am unsure what to do with it. How do I run this?
>> 
>> And what is the status of the speculation about a possible compiler optimization setting issue with PROJ 7.2.1 as available from the UbuntuGIS apt repository? Is there any new information about this?
>> 
>> Marco
>> 
>> Op 30-4-2021 om 17:16 schreef Paul Ramsey:
>>> 
>>> 
>>>> On Apr 30, 2021, at 1:05 AM, Marco Boeringa <marco at boeringa.demon.nl> wrote:
>>>> 
>>>> Lastly, I am still a bit worried about the fact that 'SELECT postgis_full_version()' returns the old '6.3.1' PROJ version number after upgrading PROJ to '7.2.1'. Is there any logical explanation for this, e.g. 'postgis_full_version' only ever returning the version number of the PROJ it was originally compiled against, so never displaying the upgrade?
>>> 
>>> Sure, it’s fairly common, all you need is a library where the version information is communicated via compile-time #defines rather than a run-time getversion() function. Or a library that does both (like proj) and the implementing application uses the #defines. Early proj4 only had #defines, so it wouldn’t be surprising to find apps that still use them, even though modern proj has a runtime proj_version() available.
>>> 
>>> P
>>> 
>>> 
>>> _______________________________________________
>>> postgis-devel mailing list
>>> 
>>> postgis-devel at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20210512/515b88cf/attachment.html>