Postgresql 查询的基准测试/性能

Question

我想测量我编写postgresql代码的性能。在代码表中被创建，自写的函数被调用等。环顾四周，我发现EXPLAIN ANALYSE是要走的路。但是，据我了解，代码只执行一次。为了进行更真实的分析，我想多次执行代码，并将每次迭代的结果写在某处，最好写在表格中（用于以后的统计）。

有没有办法用原生的postgresql函数来做到这一点？如果没有本机postgresql函数，我会用一个简单的循环来完成吗？此外，我将如何写出每个EXPLAIN ANALYZE迭代的信息？

Answer 1

一种方法是编写一个运行解释的函数，然后将其输出假脱机到文件中（或将其插入到表中）。

例如：

create or replace function show_plan(to_explain text)
  returns table (line_nr integer, line text)
as
$$
declare
  l_plan_line record;
  l_line integer;
begin
   l_line := 1;
   for l_plan_line in execute 'explain (analyze, verbose)'||to_explain loop
      return query select l_line, l_plan_line."QUERY PLAN";
      l_line := l_line + 1;
   end loop;
end;
$$
language plpgsql;

然后，您可以使用 generate_series() 多次运行语句：

select g.i as run_nr, e.*
from show_plan('select * from foo') e
  cross join generate_series(1,10) as g(i)
order by g.i, e.line_nr;

这将使用传递的 SQL 语句运行函数 10 次。结果可以假脱机到文件（如何执行此操作取决于所使用的 SQL 客户端）或插入到表中。

---

对于自动分析，使用更"可解析"的解释格式（例如.XML或 JSON）可能更容易。这也更容易在输出中处理，因为计划是单个 XML（或 JSON）值，而不是多个文本行：

create or replace function show_plan_xml(to_explain text)
  returns xml
as
$$
begin
   return execut 'explain (analyze, verbose, format xml)'||to_explain;
end;
$$
language plpgsql;

然后使用：

select g.i as run_nr, show_plan_xml('select * from foo')
from join generate_series(1,10) as g(i)
order by g.i;