Em 05-02-2014 11:55, Matheus de Oliveira escreveu:
...
', uma vez que a view vai gerar todos os dados e só depois é que o
planejador realizará a restrição através do cláusula WHERE e
ordenações necessárias.
humm... Sua afirmação não está correta. Dada a view "teste" a seguinte
consulta:
SELECT * FROM teste WHERE nome = 'matheus';
Passa por algumas transformações, primeiro:
SELECT * FROM (SELECT id_nome, nome FROM nomes) teste WHERE nome =
'matheus';
EXECUTE nunca é minha primeira opção
Em seguida, o PostgreSQL "percebe" que não precisa da subquery e
transforma a query acima em:
SELECT id_nome, nome FROM nomes AS teste WHERE teste.nome = 'matheus';
Resumindo, a view não vai gerar todos os dados, a consulta (acima) é
que será executada no final (claro ainda tem o plano de execução,
métodos de acesso, etc.).
Eu até diria que nesse exemplo específico a view me parece até mais
adequada e mais fácil de manter do que a função.
Como eu falei no email que respondi para Euler: My bad :-(
Minha sugestão é criar uma função que realiza a consulta já
adicionando as restrições necessárias para a triagem inicial dos
dados desejados.
Ex (descartando índices, segurança da função, etc)*1*.
/CREATE TABLE lancamentos (id int, conta text, campo1 text,
campo2 text);//
//
//INSERT INTO lancamentos
VALUES(1,'Produtivo','valor1','valor2');//
//INSERT INTO lancamentos
VALUES(2,'Produtivo','valor1','valor2');//
//INSERT INTO lancamentos
VALUES(3,'Mecanica','valor1','valor2');//
//INSERT INTO lancamentos
VALUES(4,'Mecanica','valor1','valor2');//
....
//
//CREATE OR REPLACE FUNCTION sp_lancamentos(IN CODCONTA
varchar) RETURNS TABLE(i int, c1 text,c2 text, c text)//
//AS $$//
//
// BEGIN//
// RETURN QUERY SELECT id,conta,campo1,campo2 FROM
lancamentos WHERE conta = CODCONTA;//
// END;//
//$$//
//language 'plpgsql';/
OK. Realmente, para aceitar parâmetros da forma que o OP espera, é
necessário sim uma função.
Eu só recomendaria, nesse caso especificamente, a usar uma função SQL
ao invés de PL/pgSQL:
CREATE OR REPLACE FUNCTION sp_lancamentos(IN CODCONTA varchar)
RETURNS TABLE(i int, c1 text,c2 text, c text)
AS $$
SELECT id,conta,campo1,campo2 FROM lancamentos WHERE conta =
CODCONTA;
$$
LANGUAGE SQL;
Para chamá-la:
SELECT * FROM sp_lancamentos('Produtivo');
(a chamada em si seria igual à PL/pgSQL, só quis exemplificar)]
Claro, usei o plpgsql por me basear em algo que já tinha, e o mesmo
depende dos recursos além da sql.
Para uma function muito grande e com muitas variáveis para
substituição, vale a pena utilizar a cláusula EXECUTE no RETURN
QUERY (isso já me salvou de vários problemas em functions com mais
mais de 1500 linhas e código e dezenas de variáveis)*2*:
/
....
RETURN QUERY EXECUTE 'SELECT id,conta,campo1,campo2 FROM
lancamentos WHERE conta = $1' USING CODCONTA;//
/ ....
Ah? Por quê? Eu particularmente tento evitar o EXECUTE ao menos que
estritamente necessário. Lembre-se que ele não pode ser otimizado pelo
PL/pgSQL para armazenar o plano de execução.
Eu também não. Mas neste meu caso, após o crescimento de dados em alguns
clientes (alguns milhões de registros em dezenas de tabelas), o
QueryPlan dentro da procedure passou a ser tratado de maneira totalmente
diferente se comparado à execução da mesma query já com os valores
prefixados nas condicionais e subqueryes fora da procedure.
Aparentemente, como o prepare acontece antes do conhecimento dos
parâmetros na procedure, o planejador gerava um plano de execução mais
custoso. (novamente, esta foi a minha impressão sobre o caso).
Estou falando da mesma consulta rodando em 5 horas dentro da procedure e
7 segundos rodando fora. Inicialmente pensei que fosse a adoção do
RETURN QUERY. Ao modificar a procedure fazendo o retorno com o EXECUTE,
o tempo foi normalizado e o custo de operação dentro e fora da procedure
ficou, de fato, irrelevante.
Lembrando que estas ações podem dificultar a análise de um
problema futuro, pois o EXPLAIN não vai detalhar o conteúdo
interno executado na função e nem nos logs.
+1. Otimizar funções é uma tarefa bem árdua. Só uma dica, a extensão
auto_explain pode ajudar nessa tarefa. ;-)
Obrigado pela dica! Vou estudar a extensão para verificar sua adoção em
nosso ambiente.
Atenciosamente
--
+--------------------------------------------------------------------------+
| Daniel Cordeiro de Morais Neto
| Diretor de TI - Portal de Cotações e-Compras
| Sócio-diretor ADM Soluções em Informática LTDA
| daniel.cordeiro(at)cotacoesecompras.com.br
| dmoraisn(at)gmail.com
| www.cotacoesecompras.com.br
| Fone: (083)8724-4440
| Gentoo User
| http://twitter.com/dmoraisn
+--------------------------------------------------------------------------+
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
