Things to consider:
(K)NN searches are costly, and the key for performance is the use of the spatial index; any form of manipulation of the geometries will render that index useless.
Don't transform the geometries of the running table on-the-fly.
Spherical/Pseudo/Web Mercator is among the most uselss 'projections' there are when it comes to any form of measurement.
Don't bother transforming to EPSG:3857 at all.
Function calls are expensive, and this is a trigger function, so it will need to be called per row; needless to say, keep the function overhead minimal, avoid CTEs if not necessary.
- Also, it's cleaner to clearly isolate SQL and PL syntax.
CREATE OR REPLACE FUNCTION river_foo_mapper ()
LANGUAGE plpgsql AS
SELECT ST_Distance(ST_Centroid(NEW.geom)::GEOGRAPHY, rt.geom::GEOGRAPHY)
FROM rivers AS rt
NEW.geom <-> rt.geom
I use the
GEOGRAPHY type to get a highly precise distance measure in meter. This assumes your initial geometries are in a geographic reference system, and EPSG:4326 by default. Use
ST_DistanceSpheroid on projected geometries to get a similar precise result based on the projections reference geoid.
It strikes me unnecessary to find the closest river segment to the centroid of a building, except maybe the buildings are partially on the river; if you need that, use
ST_Centroid(NEW.geom). Note though that the planner might need to choose a slower seq scan then since it cannot determine that geometry ahead of execution (you might have aimed at this by using a CTE? Doesn't help, unfortunately...)