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
