ST_Length(geography) leaks memory

[komzpa]

I have a query that splits all the osm roads into two-point segments and measures their length. When I do ST_Length(geography) on them, query takes up to 300 gigs of RAM and swap. When I replace it with ST_Length(ST_Transform(z.seg_geom, 3857)) *cosd(ST_X(ST_StartPoint(o.geom))), query fits into expected work_mem.

22:51:24 [gis] > select postgis_full_version();
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                                                            postgis_full_version                                                                                                                             │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ POSTGIS="3.1.0alpha1 rf7a3648" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " SFCGAL="1.3.7" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.2, released 2019/06/28" LIBXML="2.9.4" LIBJSON="0.13.1" LIBPROTOBUF="1.3.1" WAGYU="0.4.3 (Internal)" TOPOLOGY RASTER │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

Time: 1254,020 ms (00:01,254)

Observed on 3.0 too.

[pramsey]

Trying to repro this isolating just ST_Length, but not seeing it:

select sum(st_length(st_makeline(st_makepoint(a%80, a%80), st_makepoint((0.0001+a%80), 0.0001+(a%80)))::geography))
from generate_series(1,100000000) a;

[Algunenano]

Maybe the leak is in the cast, and not in the ST_Length call. Have you tested with PROJ6+?

Do you have some kind of repro we can test?

[robe]

komzpa,

Have anymore to add to this — I'll leave it at 3.0.2 for a day or so and if still no addition I will push to 3.0.3.

[komzpa]

We have this fixed. Reason was likely the postgres not freeing results of lateral joined functiona, that was fixed in one of point releases of Postgres.