Descrição

O comando PREPARE cria uma instrução preparada. Uma instrução preparada é um objeto do lado do servidor que pode ser usado para otimizar o desempenho. Quando o comando PREPARE é executado, a instrução especificada é lida, analisada e reescrita. Quando o comando EXECUTE é emitido posteriormente, a instrução preparada é planejada e executada. Esta divisão de trabalho evita a análise repetitiva, enquanto permite que o plano de execução dependa dos valores dos parâmetros fornecidos.

As instruções preparadas podem receber parâmetros: valores que são substituídos na instrução quando esta é executada. Ao criar a instrução preparada, os parâmetros são referenciados pela posição, usando $1, $2, etc. Opcionalmente, pode ser especificada a lista correspondente dos tipos de dados dos parâmetros. Quando o tipo de dados do parâmetro não é especificado, ou é declarado como unknown, o tipo de dados é inferido a partir do contexto no qual o parâmetro é referenciado pela primeira vez (se possível). Ao executar a instrução, são especificados os valores reais dos parâmetros no comando EXECUTE. Veja o comando EXECUTE para obter mais informações sobre a passagem de parâmetros.

As instruções preparadas permanecem apenas durante a sessão corrente do banco de dados. Quando a sessão termina, a instrução preparada é eliminada, devendo ser recriada antes de ser usada novamente. Isto também significa que a mesma instrução preparada não pode ser usada por vários clientes do banco de dados simultaneamente; entretanto, cada cliente pode criar sua própria instrução preparada para seu uso. As instruções preparadas podem ser eliminadas manualmente usando o comando DEALLOCATE.

As instruções preparadas têm potencialmente uma maior vantagem de desempenho quando uma única sessão está sendo usada para executar muitas instruções semelhantes. A diferença de desempenho é particularmente significativa quando as instruções são complexas para planejar ou reescrever, por exemplo, quando a consulta envolve a junção de muitas tabelas, ou exige a aplicação de várias regras. Se a instrução for relativamente simples de planejar e reescrever, mas relativamente cara de executar, a vantagem no desempenho das instruções preparadas será menos perceptível.

Parâmetros

Notas

Uma instrução preparada pode ser executada com um plano genérico ou um plano personalizado. Um plano genérico é o mesmo para todas as execuções, enquanto um plano personalizado é gerado para cada execução específica usando os valores dos parâmetros fornecidos nesta chamada. O uso de um plano genérico evita a sobrecarga de planejamento, mas em algumas situações um plano personalizado será muito mais eficiente de executar, porque o planejador pode fazer uso do conhecimento dos valores dos parâmetros. (Obviamente, se a instrução preparada não tiver parâmetros, isto é discutível, e será sempre usado um plano genérico.)

Por padrão (ou seja, quando plan_cache_mode estiver definido como auto), o servidor escolherá automaticamente se deseja usar um plano genérico ou personalizado para uma instrução preparada que possui parâmetros. A regra corrente para isto é que as cinco primeiras execuções são feitas com planos personalizados, e o custo médio estimado desses planos é calculado. Em seguida, um plano genérico é criado e seu custo estimado é comparado com o custo médio do plano personalizado. As execuções subsequentes irão usar o plano genérico, se seu custo não for muito maior do que o custo médio do plano personalizado, a ponto de fazer com que o replanejamento repetido pareça preferível.

Esta heurística pode ser alterada, forçando o servidor a usar planos genéricos ou personalizados, configurando plan_cache_mode como force_generic_plan ou force_custom_plan, respectivamente. Esta configuração é útil principalmente se a estimativa de custo do plano genérico estiver ruim por algum motivo, permitindo que este seja escolhido, mesmo que seu custo real seja muito maior do que o de um plano personalizado.

Para examinar o plano de consulta que o PostgreSQL está usando para uma instrução preparada, se usa o comando EXPLAIN. Por exemplo

EXPLAIN EXECUTE nome(valores_do_parâmetro);

Se estiver sendo usado um plano genérico, o plano conterá símbolos de parâmetro $n, enquanto um plano personalizado terá os valores dos parâmetros fornecidos substituídos nele.

Para obter mais informações sobre o planejamento de consultas e as estatísticas coletadas pelo PostgreSQL para este fim, deve ser consultada a documentação do comando ANALYZE.

Embora o objetivo principal de uma instrução preparada seja evitar análises e planejamentos repetidos da instrução, o PostgreSQL irá forçar a reanálise e replanejamento da instrução antes de usá-la, sempre que os objetos do banco de dados usados na instrução tiverem sofrido alterações de definição (DDL), ou as estatísticas do planejador tiverem sido atualizadas após o uso anterior da instrução preparada. Além disso, se o valor de search_path mudar de um uso para o próximo, a instrução será reanalisada usando o novo search_path. (Este último comportamento é novo, começando no PostgreSQL 9.3.) Estas regras usam uma instrução preparada semanticamente quase equivalente a reenviar o mesmo texto da consulta repetidamente, mas com benefício de desempenho se nenhuma definição de objeto for alterada, especialmente se o melhor plano permanecer o mesmo entre os usos. Um exemplo de caso onde a equivalência semântica não é perfeita, é quando a instrução se refere a uma tabela por um nome não qualificado e, em seguida, uma nova tabela com o mesmo nome é criada em um esquema que aparece antes no search_path, caso em que nenhuma nova análise automática ocorrerá, porque nenhum objeto usado na instrução foi alterado. No entanto, se alguma outra alteração forçar uma nova análise, a nova tabela será referenciada nos usos subsequentes.

Exemplos

Criar uma instrução preparada para um comando INSERT, e executá-la:

PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

Criar uma instrução preparada para um comando SELECT, e executá-la:

PREPARE usrrptplan (int) AS
    SELECT * FROM users u, logs l
    WHERE u.usrid=$1 AND u.usrid=l.usrid
      AND l.date = $2;
EXECUTE usrrptplan(1, current_date);

Neste exemplo, o tipo de dados do segundo parâmetro não é especificado, portanto é inferido do contexto em que $2 é usado.

Conformidade

O padrão SQL inclui a instrução PREPARE, mas é apenas para uso no SQL incorporado. Esta versão da instrução PREPARE também usa uma sintaxe um pouco diferente.

Consulta

As instruções preparadas disponíveis na sessão corrente podem ser consultadas através da visão do sistema pg_prepared_statements.

Veja também