public inbox for [email protected]help / color / mirror / Atom feed
Re: Interesting case of IMMUTABLE significantly hurting performance 4+ messages / 4 participants [nested] [flat]
* Re: Interesting case of IMMUTABLE significantly hurting performance @ 2025-04-11 03:51 Tom Lane <[email protected]> 2025-04-11 04:13 ` Re: Interesting case of IMMUTABLE significantly hurting performance David G. Johnston <[email protected]> 2025-04-11 07:59 ` Re: Interesting case of IMMUTABLE significantly hurting performance Dominique Devienne <[email protected]> 2025-04-11 23:31 ` Re: Interesting case of IMMUTABLE significantly hurting performance Merlin Moncure <[email protected]> 0 siblings, 3 replies; 4+ messages in thread From: Tom Lane @ 2025-04-11 03:51 UTC (permalink / raw) To: Merlin Moncure <[email protected]>; +Cc: David G. Johnston <[email protected]>; Nico Williams <[email protected]>; Adrian Klaver <[email protected]>; Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Merlin Moncure <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance 2025-04-11 03:51 Re: Interesting case of IMMUTABLE significantly hurting performance Tom Lane <[email protected]> @ 2025-04-11 04:13 ` David G. Johnston <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: David G. Johnston @ 2025-04-11 04:13 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Merlin Moncure <[email protected]>; Nico Williams <[email protected]>; Adrian Klaver <[email protected]>; Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Thursday, April 10, 2025, Tom Lane <[email protected]> wrote: > Merlin Moncure <[email protected]> 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. > So long as you use atomic SQL functions I suspect it is possible to use the dependency data to get the volatility of the used functions and compare them to the volatility of the UDF. David J. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance 2025-04-11 03:51 Re: Interesting case of IMMUTABLE significantly hurting performance Tom Lane <[email protected]> @ 2025-04-11 07:59 ` Dominique Devienne <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: Dominique Devienne @ 2025-04-11 07:59 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Merlin Moncure <[email protected]>; David G. Johnston <[email protected]>; Nico Williams <[email protected]>; Adrian Klaver <[email protected]>; Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Fri, Apr 11, 2025 at 5:52 AM Tom Lane <[email protected]> wrote: > Merlin Moncure <[email protected]> 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. True, but that's typically what pragmas in the code are for then, to explicitly suppress warnings. Or a new option in the CREATE FUNCTION command. Better safe than sorry, and I prefer useful "on-by-default" warnings for the non-experts, which can be disabled, than silence. PS: And FWIW, I removed all attributes from the functions I'm writting, based on your aerlier advice Tom. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance 2025-04-11 03:51 Re: Interesting case of IMMUTABLE significantly hurting performance Tom Lane <[email protected]> @ 2025-04-11 23:31 ` Merlin Moncure <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: Merlin Moncure @ 2025-04-11 23:31 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Nico Williams <[email protected]>; Adrian Klaver <[email protected]>; Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Thu, Apr 10, 2025 at 10:51 PM Tom Lane <[email protected]> wrote: > Merlin Moncure <[email protected]> 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: > ah -- gotcha. misread the original email -- should have known better :) thanks merlin ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-04-11 23:31 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-11 03:51 Re: Interesting case of IMMUTABLE significantly hurting performance Tom Lane <[email protected]> 2025-04-11 04:13 ` David G. Johnston <[email protected]> 2025-04-11 07:59 ` Dominique Devienne <[email protected]> 2025-04-11 23:31 ` Merlin Moncure <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox