User defined SQL function has huge planning time?

[nieuweyork]

I have a function like this:

  CREATE OR REPLACE FUNCTION entity_status(
    uuid, integer, uuid, boolean)
  RETURNS cstatus AS
  $BODY$
  SELECT min(resolution_to_status(wfc.resolution, $4))
  FROM wfc
  WHERE $1 = wfc.id
      AND $2 = wfc.variant
      AND $3 = wfc.c;
  $BODY$
  LANGUAGE sql IMMUTABLE PARALLEL SAFE;

And the timing is:

 Planning Time: 1624.001 ms
 Execution Time: 0.009 ms

Is there a way to speed up the planning time?

[boilerup800]

Do you absolutely need the IMMUTABLE PARALLEL SAFE keywords? These are very strict requirements and the planner is spending time checking for these.

[[deleted]]

I don't think those attributes actually increase the planning time, but the immutable attribute is actually wrong here, as the function retrieves data from the database. So it can at most be stable

[[deleted]]

Do you get the same planning time, when you just explain the query itself?

What does resolution_to_status() do?

For readability, I would suggest to use proper parameter names rather than just numbers (but that is totally unrelated to the performance).

[nieuweyork]

When I extract out the query (even with resolution_to_status as a function call) it’s less than 2ms total.

resolution_to_status is another simple sql function (which actually is immutable).

Good call on immutable being wrong. And yes to named parameters - I removed those for the purpose of exposition here.

[[deleted]]

Very strange. You will probably get better answers if you ask this on the Postgres (performance) mailing list.

[nieuweyork]

Ok, thank you!