From: | Philip Semanchuk <philip(at)americanefficient(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Why is my function inlined only when STABLE? |
Date: | 2022-03-29 18:05:44 |
Message-ID: | 867DEE4A-D480-4009-9DE9-5082B09B357C@americanefficient.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I have a function that isn't being inlined, and I would appreciate help to understand why that's the case.
I'm using PG 11.15. I know that if I declare my function IMMUTABLE and it calls a non-IMMUTABLE function, Postgres won't inline my function. But even when my function calls only substring() (which I understand to be IMMUTABLE based on '\df+ substring'), I still can't get Postgres to inline it. If I re-declare my function as STABLE, then Postgres inlines it. According to the rules I understand (https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions#Inlining_conditions_for_scalar_functions) the IMMUTABLE version of my function should be inlined too. What am I missing?
Here's a log of a CLI session showing that the IMMUTABLE version is not inlined, but the STABLE one is.
show track_functions
+-------------------+
| track_functions |
|-------------------|
| all |
+-------------------+
SHOW
me@/tmp:wylan#
SELECT * FROM pg_stat_user_functions
+----------+--------------+------------+---------+--------------+-------------+
| funcid | schemaname | funcname | calls | total_time | self_time |
|----------+--------------+------------+---------+--------------+-------------|
+----------+--------------+------------+---------+--------------+-------------+
SELECT 0
Time: 0.021s
me@/tmp:wylan#
CREATE OR REPLACE FUNCTION f(foo text)
RETURNS text
AS $$
SELECT substring(foo FROM 1 FOR 2)
$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;
CREATE FUNCTION
Time: 0.003s
me@/tmp:wylan#
select f('4242')
+-----+
| f |
|-----|
| 42 |
+-----+
SELECT 1
Time: 0.008s
me@/tmp:wylan#
SELECT * FROM pg_stat_user_functions
+----------+--------------+------------+---------+--------------+-------------+
| funcid | schemaname | funcname | calls | total_time | self_time |
|----------+--------------+------------+---------+--------------+-------------|
| 14472085 | public | f | 1 | 0.05 | 0.05 |
+----------+--------------+------------+---------+--------------+-------------+
SELECT 1
Time: 0.022s
me@/tmp:wylan#
DROP FUNCTION f(text)
DROP FUNCTION
Time: 0.001s
me@/tmp:wylan#
CREATE OR REPLACE FUNCTION f(foo text)
RETURNS text
AS $$
SELECT substring(foo FROM 1 FOR 2)
$$ LANGUAGE sql STABLE PARALLEL SAFE;
CREATE FUNCTION
Time: 0.003s
me@/tmp:wylan#
select pg_stat_reset()
+-----------------+
| pg_stat_reset |
|-----------------|
| |
+-----------------+
SELECT 1
Time: 0.008s
me@/tmp:wylan#
SELECT * FROM pg_stat_user_functions
+----------+--------------+------------+---------+--------------+-------------+
| funcid | schemaname | funcname | calls | total_time | self_time |
|----------+--------------+------------+---------+--------------+-------------|
+----------+--------------+------------+---------+--------------+-------------+
SELECT 0
Time: 0.022s
me@/tmp:wylan#
select f('4242')
+-----+
| f |
|-----|
| 42 |
+-----+
SELECT 1
Time: 0.008s
me@/tmp:wylan#
SELECT * FROM pg_stat_user_functions
+----------+--------------+------------+---------+--------------+-------------+
| funcid | schemaname | funcname | calls | total_time | self_time |
|----------+--------------+------------+---------+--------------+-------------|
+----------+--------------+------------+---------+--------------+-------------+
SELECT 0
Time: 0.019s
me@/tmp:wylan#
Thanks
Philip
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-03-29 18:24:41 | Re: Why is my function inlined only when STABLE? |
Previous Message | Stephen Frost | 2022-03-29 15:17:29 | Re: PG12: Any drawback of increasing wal_keep_segments |