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

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