Hi David,
Thanks for the explanation. Back when I developed this, I am pretty sure
I tried to find out the answer to this, but was left somewhat confused
as to the results of subqueries in relation to this aspect of avoiding
doing unnecessary work related to costly functions.
E.g. this very old StackOverflow post of Erwin Brandstetter, who's name
I have seen come up in that discussion forum a lot with what suggests
based on his answers a pretty thorough knowledge of PostgreSQL and
databases in general, at least suggested a subquery could work:
https://stackoverflow.com/questions/20718499/does-postgresql-cache-function-calls#comment31095072_20718499
And in relation to that post and thread, and the suggestion of WITH /
CTE clause, would that be a suitable substitute and avoid the recalling
of the functions? I assume with the MATERIALIZED option, it should, that
is what the MATERIALIZED option is for, isn't it?
Marco
Op 20-10-2025 om 21:09 schreef David Rowley:
> 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
