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

On Tue, 21 Oct 2025 at 03:42, Marco Boeringa <marco@boeringa.demon.nl> wrote:
> 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.

So you basically have something like:

UPDATE t t1 SET col1 = t2.a1, col2 = t2.a2
FROM (SELECT unique_col, f1(col3) as a1, f2(col4) as a2 FROM t) AS t2
WHERE t1.unique_col = t2.unique_col
   AND <other filter clauses>

Assuming here that unique_col has a UNIQUE or PK constraint. The self
join basically amounts to wasted effort. There is no function result
caching anywhere. Looking at the EXPLAIN output, it seems those
functions are executed once per row that's output from the join and
just below the "Update" node and they're executed 8 times. That won't
change if you get rid of the self join.

David