PostgreSQL consulta bruta vs “Função retorna TABELA” - diferença insana no desempenho. Por quê?

Question

Eu trabalho com o PostgreSQL e ele é usado para relatórios. A maneira como está configurada atualmente é a seguinte:

Existe uma consulta complexa que retorna os dados do relatório, assim:

select Column1 as Name1, Column2 as Name2
from sometable tbl
inner join ...
where ...
and ...
and $1 <= somedate
and $2 >= somedate
group by ...
order by ...;

Existe uma função que utiliza essa consulta e é definida como essa

CREATE OR REPLACE FUNCTION GetMyReport(IN fromdate timestamp without time zone, IN todate timestamp without time zone)
  RETURNS TABLE(Name1 character varying, Name2 character varying) AS
$BODY$

--query start
select Column1 as Name1, Column2 as Name2
from sometable tbl
inner join ...
where ...
and ...
and $1 <= somedate
and $2 >= somedate
group by ...
order by ...;
--query end

$BODY$
  LANGUAGE sql VOLATILE
  COST 10
  ROWS 1000;

Finalmente, quando o aplicativo de relatório chama a função, ele envia o seguinte SQL:

select null::text as Name1, Name2 from GetMyReport ('2012-05-28T12:19:39.0000000+11:00'::timestamp, '2012-05-28T12:19:44.0000000+11:00'::timestamp);

E meu problema é:

• Quando executo apenas a "consulta" no banco de dados, ele é executado rapidamente. De fato, em questão de segundos, se os dados retornados forem razoavelmente pequenos
• Quando executo o sql que é passado do aplicativo de relatório, leva tempo insano para ser executado - a cada vez. De fato, mais de 10 minutos para os mesmos dados que são retornados pela consulta em segundos.
• Na verdade, posso executar a consulta bruta, tomar milissegundos, executar as funções - demora ~ 10 minutos, executar consulta novamente - milissegundos, executar a função - novamente 10 min, todos com exatamente os mesmos parâmetros.

Qual poderia ser o motivo disso?

    

Answer 1

OK, isso foi fácil. Acontece que o banco de dados tem que preparar o plano de consulta antes de conhecer os parâmetros, o que leva a resultados ruins. A solução foi usar plpgsql e retornar QUERY EXECUTE. Agora o desempenho é o mesmo, como esperado.

CREATE OR REPLACE FUNCTION GetMyReport(IN fromdate timestamp without time zone, IN todate timestamp without time zone)
  RETURNS TABLE(Name1 character varying, Name2 character varying) AS
$BODY$

BEGIN
RETURN QUERY EXECUTE'
select Column1 as Name1, Column2 as Name2
from sometable tbl
inner join ...
where ...
and ...
and $1 <= somedate
and $2 >= somedate
group by ...
order by ...;' USING $1, $2
END

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 10
  ROWS 1000;