0

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)?

3
  • Without showing us the code of the function and the query you are using this is impossible to answer. Jun 16 at 8:49
  • @a_horse_with_no_name ... and the tables, with statistics. The query has 5 tables, which create 3 input columns that execute the function with nested functions that involve ~10 tables, and has many window functions. I think your answer to my exact question would be "No, there is no way to force it to do what you want. You need to convince it using a myriad of techniques that depend on 'the code of the function and the query you are using'".
    – Stephen
    Jun 20 at 10:12
  • (For the answerers, this is the smallest fire I've now got this week - I hope to verify in July :()
    – Stephen
    Jun 20 at 10:13

2 Answers 2

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

2
  • Intriguing - does f1 need to be stable as well? I'm skeptical this will work, but I will give it a try...
    – Stephen
    Jun 17 at 9:09
  • I think you need to put all the SQL view code inside this PL/pgSQL function to get it work.
    – ElevenZ
    Jun 17 at 10:16
0

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);
1
  • I presume the group by is what eliminates the extra calls there (your wording isn't clear when you say "this will work"; what's before and after?) I wonder if select f, first_value(foo(a)) over (partition by a) from t will also perform correctly...
    – Stephen
    Jun 17 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.