[PostGIS] ST_3DIntersection query hangs (PostGIS 3.4, PostgreSQL 15.12, SFCGAL 1.5.0)

[rmaron via postgis-users]

Hi List

We are encountering a problem when using ST_3DIntersections.

Context:
A quality-check identifies candidate geometries (error_multipoints). We then 
load the corresponding geo_edge.geom values and compute intersections. All 
input geometries are valid.

Query:
WITH id1 AS (
    SELECT 
        (string_to_array(involved_objects, ';'))[2]::int AS EDGE_A,
        (string_to_array(involved_objects, ';'))[4]::int AS EDGE_B
    FROM error_multipoints
    WHERE user_name = 'test_xxx'
)
SELECT 
    a.EDGE_A,
    a.EDGE_B,
    ST_AsText(
        ST_3DIntersection(
            (SELECT geom FROM geo_edge WHERE pk_id = a.EDGE_A),
            (SELECT geom FROM geo_edge WHERE pk_id = a.EDGE_B)
        )
    ) AS INTERSECTIONS_3d
FROM id1 AS a;

Problem/observation:
- CPU goes to 100% immediately, query never finishes.
- Client never receives a response.
- Process cannot be terminated inside PostgreSQL, only at OS level.
- With limit of 4000 records everything works within seconds.
- With limit of 5000 records the issue always appears → memory leak?

Setup:
- PostgreSQL 15.12
- Postgis 3.4 (USE_GEOS=1 USE_PROJ=1 USE_STATS=1)
- SFCGAL 1.5.0

Remarks:
- We are aware ST_3DIntersection is deprecated (PostGIS ≥ 3.5), but we still 
rely on it in some workflows.
- We are aware that the query could be written more efficiently using JOIN, but 
for the sake of reproducibility, we are sticking with this example for the time 
being.

Question:
Has anyone experienced similar hangs with ST_3DIntersection at scale?
Any ideas if this is related to SFCGAL memory handling, or known 
limitations/workarounds?
What exactly is happening that prevents me from terminating the process inside 
PostgreSQL?

Thanks for your input and ideas!
Roland