A simple scalar function refuses to be inlined on postgresql 15.6 no matter what I try

Question

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...

Answer 1

A function that concerns a subquery will never be inlined.

I find this list from the PostgreSQL Wiki most helpful when figuring out if a function can be inlined or not.

Answer 2

As mentioned by others, a scalar function with a subquery is not inlinable.

But you can use a Table Function instead, which can be inlined in most cases, as long as it's just a single SELECT, see the Wiki

CREATE OR REPLACE FUNCTION colors_in_food(food_id uuid, is_artificial bool)
    RETURNS TABLE (id uuid)
    LANGUAGE sql
    IMMUTABLE PARALLEL SAFE AS
$$
    SELECT
      ci.id
    FROM food_ingredient fi
    INNER JOIN canon_ingredient ci
      ON fi.canon_ingredient_id = ci.id
    WHERE fi.food_id = food_id
      AND ci.artificial_color = is_artificial;
$$;

And the EXPLAIN clearly shows it being inlined.

EXPLAIN (ANALYZE, VERBOSE)
SELECT count(1)
FROM food f
WHERE EXISTS (SELECT FROM colors_in_food(f.id, TRUE));

You may find different rewrites get different plans

EXPLAIN (ANALYZE, VERBOSE)
SELECT count(1)
FROM food f
CROSS JOIN LATERAL (SELECT
    FROM colors_in_food(f.id, TRUE)
    GROUP BY ()
    HAVING COUNT(*) > 0
) v;

db<>fiddle