Re: Interesting case of IMMUTABLE significantly hurting performance

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> I guess the real problems here are lack of feedback on a number of fronts:
> *) the server knows the function is not immutable but lets you create it
> anyway, even though it can have negative downstream consequences

That's debatable I think. If you know what you're doing, you're going
to be annoyed by warnings telling you that you don't.

> *) there is no way to discern inline vs non-inlined execution in explain

That's simply false. Using the examples in this thread:

regression=# explain (verbose,analyze)
select formatted_num_immutable(i) from generate_series(1,1000000) i;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Function Scan on pg_catalog.generate_series i (cost=0.00..262500.00 rows=1000000 width=32) (actual time=65.535..2444.956 rows=1000000.00 loops=1)
Output: formatted_num_immutable((i)::bigint)
Function Call: generate_series(1, 1000000)
Buffers: temp read=1709 written=1709
Planning Time: 0.086 ms
Execution Time: 2481.218 ms
(6 rows)

regression=# explain (verbose,analyze)
select formatted_num_stable(i) from generate_series(1,1000000) i;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Function Scan on pg_catalog.generate_series i (cost=0.00..17500.00 rows=1000000 width=32) (actual time=65.615..478.780 rows=1000000.00 loops=1)
Output: ltrim(to_char((i)::bigint, '999 999 999 999 999 999 999 999'::text))
Function Call: generate_series(1, 1000000)
Buffers: temp read=1709 written=1709
Planning Time: 0.091 ms
Execution Time: 501.412 ms
(6 rows)

You can easily see that the second case was inlined, because you don't
see the SQL function anymore, rather its body.

> *) the planner is clearly not modelling function scan overhead give the
> relative costing discrepancies

That's also false; note the 15x difference in estimated cost above,
which is actually more than the real difference in runtime. (I hasten
to add that I don't have a lot of faith in our function cost
estimates. But the planner is quite well aware that a non-inlined SQL
function is likely to be expensive.)

regards, tom lane