Postgresql – How to find out if a SQL Function can be inlined

execution-planfunctionsoptimizationperformancepostgresql

In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will actually be slower.

That's interesting though because I didn't even know to_char() wasn't IMMUTABLE. Moreover, I didn't know that IMMUTABLE would ever make anything slower. My question is now, how do I find out if a function marked as IMMUTABLE is being inlined, or not?

Best Answer

Since it changes the actual execution plan, you can tell from the output of EXPLAIN (ANALYZE, VERBOSE) (among others): Example:

EXPLAIN (ANALYZE, VERBOSE) SELECT my_func(col) FROM tbl;

I simplified and adapted the fiddle from my answer you are referring to:

db<>fiddle here

The first function is inlined, hence you see the inlined expression in the output:

Output: (to_char((d)::timestamp with time zone, 'MMDD'::text))::integer

The second function is not inlined, hence you see the function call in the output:

Output: f_mmdd_tc_i(d)

To be precise: this shows whether the function has been inlined in the tested query. If it has not, that does not necessarily prove it can't be (under different circumstances).
And the test is not bound to just IMMUTABLE functions in any way.

Related Question