public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Merlin Moncure <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Nico Williams <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Olleg Samoylov <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
Date: Thu, 10 Apr 2025 23:51:53 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHyXU0ypfKJ6LmEWJHKF63m5UEcZwB5gny3DWGQygvVeePM8mw@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<Z/bk5E6pqIBWTF3j@ubby>
<[email protected]>
<Z/foWNmbdDOWH6/M@ubby>
<CAKFQuwa-H6ZzST_dFNhviqQ8b4fPfLT+o3btfygrVOxQJhgWNQ@mail.gmail.com>
<CAHyXU0ypfKJ6LmEWJHKF63m5UEcZwB5gny3DWGQygvVeePM8mw@mail.gmail.com>
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
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox