Início / user-214868

t316's questions

Martin Hope
t316
Asked: 2024-12-18 22:12:35 +0800 CST
  • 8

Tenho a seguinte função escalar de retorno booleano que não está sendo incorporada, não importa o que eu tenha tentado (IMMUTABLE PARALLEL SAFE COST 1 é apenas a mais recente permutação de muitas tentativas malsucedidas):

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);
$$;

O seguinte faz uma varredura de função (n chamadas da função por linha na tabela de alimentos):

select count(1) from food f
where has_artificial_colors_in_food(f.id);

Então faça:

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;

Eu tentei todos os conselhos que pude encontrar sobre como fazer a função ser inline. A versão a seguir da consulta leva 3s vs 15+ min versão com a função:

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)

Sei que posso simplesmente reescrever minha consulta sem a função, mas é uma meta de design importante ter esses tipos de abstrações disponíveis como blocos de construção. Estou disposto a aceitar um pequeno golpe de desempenho por isso, mas não um golpe gigante porque a função não pode ser embutida.

Alguém pode me dar algumas dicas sobre o que posso estar perdendo?

Estamos usando o postgresql 15.6. A tabela food tem aprox. 750K registros. Há índices individuais em todas as colunas de junção referenciadas na função.

ATUALIZAÇÃO: Tentando evitar uma subconsulta no corpo da função, também reescrevi a função como:

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;
$$;

Ainda não está sendo alinhado...

postgresql