Re: Potential "AIO / io workers" inter-worker locking issue in PG18? - Mailing list pgsql-bugs

Op 20-10-2025 om 11:34 schreef Marco Boeringa:
> Hi David,
>
> To be honest I am not a SQL wizard like some of you here on the list, 
> but part of the reason I setup the query as it currently is, is that 
> the PostGIS function calls like ST_Area and ST_Perimeter can be very 
> expensive depending on the complexity and size of the geometry, and I 
> thus want to avoid at all cost to have to unnecessarily recalculate 
> them multiple times in the same query. Maybe I am misunderstanding how 
> PostgreSQL processes such queries, but I need the values multiple 
> times to calculate some other parameters. So unless PostgreSQL is 
> smart enough to cache the result and not execute ST_Area multiple 
> times if it is used multiple times in the same query, I thought it 
> wise to separate out the calculation and use the SELECT's results as 
> input for the calculation of the other parameters. Maybe that isn't 
> actually needed, but I think I remember seeing performance gains from 
> the current setup when I initially wrote it this way, but I am not 
> entirely sure at this point in time, it is a while ago. I know far to 
> little about the internals of PostgreSQL and its exact query 
> processing to say anything really sensible about this.
>
Hi David,

Looking through the 'auto_explain' output of the bad query plan, I 
noticed the below included clause as generated by the planner. In the 
context of what I actually wrote above about the desire to not run 
expensive function calls like ST_Area multiple times, do I understand it 
correctly from the 'auto_explain' output excerpt that PostgreSQL, by 
removing the self join, actually *does* run the ST_Area and ST_Perimeter 
multiple times? Is this how I need to interpret this part of the 
'auto_explain' output? If there is no caching of the function result, 
this could be expensive as well.

Marco


*** 'auto_explain' output excerpt ***:

st_area((landcover_grassy_small_scale_2_ply.way)::geography, TRUE),
st_perimeter((landcover_grassy_small_scale_2_ply.way)::geography, TRUE),
CASE
     WHEN (st_area((landcover_grassy_small_scale_2_ply.way)::geography, 
TRUE) > '0'::double PRECISION) THEN 
((power(st_perimeter((landcover_grassy_small_scale_2_ply.way)::geography, 
TRUE), '2'::double PRECISION) / 
st_area((landcover_grassy_small_scale_2_ply.way)::geography, TRUE)) / 
'12.566370614359172'::double PRECISION)
     ELSE '0'::double PRECISION
END,
st_npoints(landcover_grassy_small_scale_2_ply.way),
CASE
     WHEN (st_npoints(landcover_grassy_small_scale_2_ply.way) > 0) THEN 
(CASE
         WHEN 
(st_area((landcover_grassy_small_scale_2_ply.way)::geography, TRUE) > 
'0'::double PRECISION) THEN 
((power(st_perimeter((landcover_grassy_small_scale_2_ply.way)::geography, 
TRUE), '2'::double PRECISION) / 
st_area((landcover_grassy_small_scale_2_ply.way)::geography, TRUE)) / 
'12.566370614359172'::double PRECISION)
         ELSE '0'::double PRECISION
     END / (st_npoints(landcover_grassy_small_scale_2_ply.way))::double 
PRECISION)
     ELSE '0'::double PRECISION
END,
CASE
     WHEN (st_area((st_convexhull(t1.way))::geography, TRUE) > 
'0'::double PRECISION) THEN 
(st_area((landcover_grassy_small_scale_2_ply.way)::geography, TRUE) / 
st_area((st_convexhull(t1.way))::geography, TRUE))
     ELSE '1'::double PRECISION
END,
t1.ctid,
landcover_grassy_small_scale_2_ply.ctid,
landcover_grassy_small_scale_2_ply_pg.ctid