Início / dba / Perguntas / 344274
Accepted
t316
Asked: 2024-12-18 22:12:35 +0800 CST

Uma função escalar simples se recusa a ser incorporada no postgresql 15.6, não importa o que eu tente

  • 772

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

2 respostas

  1. Uma função que diz respeito a uma subconsulta nunca será incorporada.

    Acho esta lista do PostgreSQL Wiki muito útil para descobrir se uma função pode ser incorporada ou não.

    • 4
  2. Best Answer

    Conforme mencionado por outros, uma função escalar com uma subconsulta não é inlining.

    Mas você pode usar uma função de tabela, que pode ser incorporada na maioria dos casos, desde que seja apenas uma SELECT, veja o 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;
$$;

    E EXPLAINmostra claramente que ele está embutido.

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

    Você pode encontrar diferentes reescritas e obter planos diferentes

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

    • 2

relate perguntas