I'm trying to create a PL/psql function that cuts one user-defined geometry to a predefined border geometry. If the user-defined geometry does not intersect with the border geometry, it should return the user geometry, unmodified.
The pre-defined geometry consists of 47 polygons. and it has a geometry-index.
The issue isn't with creating the function, its with the speed of the function.
CREATE OR REPLACE FUNCTION public.clip_geometry( IN in_geom geometry, OUT out_geom geometry ) AS -- function returns unmodified input-geometry if it does not intersect with the border-geometry. -- if input-geometry intersects with the border-geometry, the input-geometry is cut according to the border-geometry $BODY$ select st_multi(coalesce( st_difference(a.geom ,dk.geom), a.geom)) from (select $1 as geom) a left JOIN LATERAL (select st_union(b.geom) geom from b.border b where st_intersects(a.geom, b.geom) ) dk on TRUE; $BODY$ LANGUAGE SQL STABLE;
I have tested this function (and a version that was written in PL/pgSQL), and it behaves as I want it to. However, the function is slow compared to a normal query. When I create a table using the function, with a table of road-geometries (approximately 2.3 million rows, with a geometry-index) the runtime is around 1 to 1:30 minute, using this query:
create table b.mod_roads as select clip_geometry(geom) from b.roads
If I wrote the functions content as a normal query, the runtime is around 15-25 seconds. the query:
create table b.mod_roads as select st_multi(coalesce( st_difference(a.geom ,dk.geom), a.geom)) from b.roads a left JOIN LATERAL (select st_union(b.geom) geom from b.border b where st_intersects(a.geom, b.geom) ) dk on TRUE;
The normal query uses the border-geometries' index. When I try to use EXPLAIN on the function-call, it doesn't show how it optimizes the query.
Is there a way to achieve the normal query's speed within the function?