如何对一组PostgreSQL查询进行基准测试？

Question

我有一批SQL基准查询需要执行并计算它们的时间。我不想使用EXECUTE ANALYZE命令获取执行计划。

根据这里建议的方法 Get execution time of PostgreSQL query，我编写了一个包含以下代码的过程来执行每个查询：

-------------------------
Query = 'Q1';
StartTime := clock_timestamp();

PERFORM <** Query 1 goes here **>

EndTime := clock_timestamp();
Duration := 1000 * ( extract(epoch from EndTime) - extract(epoch from StartTime) );
INSERT INTO execution_tests VALUES (Query, StartTime, EndTime, Duration);
-------------------------

我使用PERFORM而不是SELECT来执行查询并丢弃结果。

这对于简单查询有效，但我有许多带有WITH子句的查询。根据手册

对于WITH查询，请使用PERFORM然后将查询放在括号中。 （在这种情况下，查询只能返回一行。）

但是，我需要测试性能的所有WITH查询都返回多行。

如何解决这个问题？

- Esteban Zimanyi

Answer 1

什么样的东西呢？

do $$
declare
  p json;
  your_query text := 'select pg_sleep(1.1)';
  t interval;
begin
  execute 'explain (analyse, format json) ' || your_query into p;
  raise info '%', p;
  t := make_interval(secs := ((p->0->>'Planning Time')::float + (p->0->>'Execution Time')::float)/1000);
  raise info '%', t;
end $$;

- Abelisto

---

太好了！谢谢！如何在 RAISE INFO 后移除 CONTEXT 消息？我已经尝试使用 ALTER DATABASE test3 SET log_error_verbosity = TERSE;，但没有效果。附注：我是在 pgAdmin 上执行的。 - Esteban Zimanyi

@EstebanZimanyi 不太确定你的意思。这只是一个例子。据我理解，您想将执行时间存储到表中... - Abelisto

确实，但也很好（不一定是在这个特定的例子中）每次在屏幕上执行长脚本（例如使用不同比例因子对长批量查询进行基准测试）时删除所有消息，例如“CONTEXT：PL/pgSQL函数test_queries()第262行RAISE”。有没有办法做到这一点？ - Esteban Zimanyi

@EstebanZimanyi 问题在于我这里没有这样的消息附加项。所以没有任何想法。 - Abelisto

@EstebanZimanyi 看起来这是 psql 的功能：https://www.depesz.com/2008/07/12/suppressing-context-lines-in-psql/ - Abelisto