☰

PostgreSQL 原始查询与“函数返回表”——性能差异巨大。为什么？

tag-icon • tag-icon performance postgresql query
PostgreSQL 原始查询与“函数返回表”——性能差异巨大。为什么？

我使用 PostgreSQL 进行报告。其当前配置方式如下：

有一个复杂的查询返回报告数据，如下所示：

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

有一个函数利用了这个查询，其定义如下

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;

最后，当报告应用程序调用该函数时，它会发送以下 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);

我的问题是：

  • 当我只对数据库运行“查询”时，它运行得相当快。事实上，如果返回的数据相当小，几秒钟就可以完成
  • 当我运行从报告应用程序传递的 SQL 时，每次都要花费大量时间。事实上，查询在几秒钟内返回的相同数据需要超过 10 分钟的时间。
  • 事实上，我可以运行原始查询，需要几毫秒，运行函数 - 需要约 10 分钟，再次运行查询 - 几毫秒，运行函数 - 再次 10 分钟，所有这些都使用完全相同的参数。

那可能是什么原因呢？

答案1

好的，这很简单。事实证明，数据库必须在了解参数之前准备查询计划，这会导致糟糕的结果。解决方案是使用 plpgsql 并返回 QUERY EXECUTE。现在性能与预期相同。

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;

相关内容