I have the following boolean returning scalar function that is not getting inlined no matter what I have tried (IMMUTABLE PARALLEL SAFE COST 1 is just the latest permutation of many unsuccessful trials):
CREATE OR REPLACE FUNCTION has_artificial_colors_in_food(fid uuid) RETURNS boolean
LANGUAGE sql
IMMUTABLE PARALLEL SAFE COST 1 AS
$$
SELECT EXISTS (SELECT 1
FROM food_ingredient fi
INNER JOIN canon_ingredient ci
ON fi.canon_ingredient_id = ci.id
WHERE fi.food_id = fid
AND ci.artificial_color = TRUE);
$$;
The following does a function scan (n calls of the function per row in food table):
select count(1) from food f
where has_artificial_colors_in_food(f.id);
So do:
select has_artificial_colors_in_food(f.id)
from food f;
SELECT count(1)
FROM food f
JOIN LATERAL has_artificial_colors_in_food(f.id) AS has_artificial_color ON true
WHERE has_artificial_color;
I have tried all advice I can find on how to make the function be inlined. The following version of the query takes 3s vs 15+ min version with the function:
select count(1) from food f
where exists(
SELECT 1
FROM food_ingredient fi inner join canon_ingredient ci on fi.canon_ingredient_id = ci.id
WHERE fi.food_id = f.id
AND ci.artificial_color = true)
I know I can just rewrite my query without the function but it is an important design goal to have these types of abstractions be available as building blocks. I am willing to take a small perf hit for that but not a giant one because the function cannot be inlined.
Can someone please give me some pointers as to what I might be missing?
We're using postgresql 15.6. The food table has approx. 750K records. There are individual indexes on all the join columns referenced in the function.
UPDATE: Trying to avoid a subquery in the function body, I have also rewritten the function as:
CREATE OR REPLACE FUNCTION has_artificial_colors_in_food(fid uuid) RETURNS boolean
LANGUAGE sql
IMMUTABLE PARALLEL SAFE COST 1 AS
$$
SELECT (count(1) > 0)
FROM food_ingredient fi
INNER JOIN canon_ingredient ci
ON fi.canon_ingredient_id = ci.id
WHERE fi.food_id = fid
AND ci.artificial_color = TRUE;
$$;
Still not being inlined...