Merlin Moncure <mmonc...@gmail.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
