Force stable postgres function to be executed once

Question

I've created a naming function for multiple table id combinations for human understanding. Essentially it denormalises tables combining the important data into a varchar.

It takes 3 parameters, and is a stable function but I can't get my view to use it as such. When my view returns 10k results, with the same 3 input values for every row... it executes the function 10000 times, making the query take 100x longer.

My function is not meant to be fast, but presumably with the query plan the database considers that it's going to be faster that way (I have cross joins, and window aggregates etc)

I've tried putting the rest of the query into an inner query, my function into a lateral join, and have increased the function cost to millions, all in vain.

Is there a way to hint it's (cached) execution (other than what I've already tried), or some factor that would make postgres ignore the stable declaration (too many arguments)?

Answer 1

If you think the query optimizer get things wrong, then using PL/pgSQL language to do queries step by step may help. And you can cache function results with variables.

create function f1(<...>) returns <type> as $body$
declare
  A int;
begin
  select stable_func() into A;
  <...>
end $body$ language 'plpgsql';

The PostgreSQL Documentation provides a good material for anyone to learn this language:

https://www.postgresql.org/docs/current/plpgsql.html

Answer 2

Drastically increasing the function cost will generally make the planner pick the plan which it thinks will evaluate the function least, from the universe of all plans it considers. But it does not cause the planner to consider plans it would not otherwise consider. There is no secret setting you can make to change this, it would require new code in the planner.

I've tried putting the rest of the query into an inner query, my function into a lateral join, and have increased the function cost to millions, all in vain

Inner queries can work in general, but without knowing what specifically you did...

For example, this will work to minimize the number of calls to foo(4) from 15 to 1.

create table t as select f,4 a from generate_series(1,15) f(f);

select f, a2 from (select a, foo(a) as a2 from t group by a) distincta join t using (a);