É possível ao usar o pgadmin
ou plsql
obter um plano de consulta para uma instrução sql executada dentro de uma função definida pelo usuário ( UDF ) usando EXPLAIN
. Então, como obtenho o plano de consulta para uma invocação específica de uma UDF? Vejo o UDF abstraído em uma única operação F()
no pgadmin.
Já procurei na documentação mas não encontrei nada.
Atualmente, estou retirando as instruções e executando-as manualmente. Mas isso não vai cortá-lo para grandes consultas.
Por exemplo, considere o UDF abaixo. Essa UDF, embora tenha a capacidade de imprimir sua string de consulta, não funcionará com um copiar e colar, pois possui uma tabela temporária criada localmente, que não existe quando você a cola e executa.
CREATE OR REPLACE FUNCTION get_paginated_search_results(
forum_id_ INTEGER,
query_ CHARACTER VARYING,
from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
in_categories_ INTEGER[] DEFAULT '{}')
RETURNS SETOF post_result_entry AS $$
DECLARE
join_string CHARACTER VARYING := ' ';
from_where_date CHARACTER VARYING := ' ';
to_where_date CHARACTER VARYING := ' ';
query_string_ CHARACTER VARYING := ' ';
BEGIN
IF NOT from_date_ IS NULL THEN
from_where_date := ' AND fp.posted_at > ''' || from_date_ || '''';
END IF;
IF NOT to_date_ IS NULL THEN
to_where_date := ' AND fp.posted_at < ''' || to_date_ || '''';
END IF;
CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ;
if in_categories_ != '{}' THEN
join_string := ' INNER JOIN forum_topics ft ON fp.topic_id = ft.id ' ||
' INNER JOIN un_cat uc ON uc.id = ft.category_id ' ;
END IF;
query_string_ := '
SELECT index,posted_at,post_text,name,join_date,quotes
FROM forum_posts fp
INNER JOIN forum_user fu ON
fu.forum_id = fp.forum_id AND fu.id = fp.user_id' ||
join_string
||
'WHERE fu.forum_id = ' || forum_id_ || ' AND
to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' ||
from_where_date ||
to_where_date
||';';
RAISE NOTICE '%', query_string_ ;
RETURN QUERY
EXECUTE query_string_;
END;
$$ LANGUAGE plpgsql;
Você deve ser capaz de usar a explicação automática . Ligue-o e
SET auto_explain.log_min_duration = 0;
e você deve obter os planos em seu log para todas as instruções executadas nessa sessão.
Você também pode querer definir
SET auto_explain.log_analyze = true;
mas você basicamente executará tudo em dobro - uma vez para 'real' e outra para EXPLICAR ANALISAR. Durante uma fase de teste de desempenho sem tempo, essa saída pode ser muito mais útil do que os planos EXPLAIN sozinhos, pois fornece qual plano realmente aconteceu.Além do conselho de @rfusca: as instruções SQL dentro das funções plpgsql são consideradas instruções aninhadas e você precisa definir o parâmetro adicional
auto_explain.log_nested_statements
.Ao contrário de algumas outras extensões, você não precisa se candidatar
CREATE EXTENSION
a esta. Basta carregá-lo dinamicamente em sua sessão comLOAD
. Você deve ser superusuário para isso.Sua sessão pode ficar assim:
Observe que o padrão para
auto_explain.log_min_duration
é-1
, que desativa o log de planos. Portanto, você precisa definir isso para algum outro valor para registrar todos os planos.Pode produzir muita saída de log.
As mensagens de log são gravadas em arquivos de log com configurações padrão. Para obtê-los diretamente no cliente (funciona pelo menos em psql), também:
Ver:
O manual atual sobre auto_explain.
Depesz escreveu um artigo de blog sobre isso quando foi introduzido com o PostgreSQL 8.4.