Defining session level estimations for function

Question

In a function definition I can specify estimations for planner like ROWS 1000 and COST 100. But the function can return one row or a million rows depending on condition. Is there any "conditional way" to define estimations? Of course, this is not changing the function definition each time.

Answer 1

No, there is no conditional way.

The only exception I can think of are very simple SQL functions which can be "inlined" in the outer query (not possible for plpgsql functions). Then Postgres applies default estimates instead of the estimates declared for the black boxes that functions otherwise are to the query planner. (Inlining basically discards the function wrapper and incorporates the SQL in the body into the outer query.)

The settings for COST and ROWS don't matter much as long as the function isn't nested into a (complex) bigger query, and that's the case where you should rather avoid user-defined set-returning functions that cannot be inlined. You can do it, but it may lead to sub-optimal query plans - where COST and ROWS settings are not the most important problem, either. The query planner cannot optimize the plan for the whole query, but has to do it for function and the rest of the query separately.
Related:

• PostgreSQL Stored Procedure Performance
• Difference between language sql and language plpgsql in PostgreSQL functions
• Function executes faster without STRICT modifier?
• Why is the planner coming up with different results for functions with different volatilities?
• SQL function very slow compared to query without function wrapper