Hi David,
PostGIS functions can be very expensive, especially in the context of
the fact that Polygon and Line geometries can vary vastly in size in
terms of the number of vertices that constitute them, which has a
profound impact on some PostGIS function calls, merely due to the
enormous complexity of some shapes.
But of course you're right that any change will need some thorough
testing before assuming it will actually benefit the queries.
Marco
Op 20-10-2025 om 23:06 schreef David Rowley:
> On Tue, 21 Oct 2025 at 09:02, Marco Boeringa <marco@boeringa.demon.nl> wrote:
>> 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?
> That article states "this function is invoked multiple times with the
> same parameter", so doesn't sound very applicable for your case since
> your function parameter changes with every row.
>
> I don't see how WITH MATERIALIZED could help you here as that's not a
> parameterized cache. I suppose we could adjust the planner to consider
> something similar to Memoize for caching results for expensive stable
> functions. We'd have to put a lot of trust into n_distinct estimates
> and the function(s) COST setting, however.
>
> I suspect you're trying to optimise for something that's not an actual problem.
>
> David
