使用Postgres中的explain analytics创建一个函数来检查执行时间 [英] Create a function to check execution time using explain analyze in Postgres

问题描述

我运行了一些查询以获取执行时间

I ran some queries to get the execution time

输入查询

explain analyze SELECT * FROM employee emp where emp.empid = ' 00010   ';

输出结果

"Seq Scan on employee emp  (cost=0.00..279.00 rows=1 width=90) (actual time=0.014..3.341 rows=1 loops=1)"
"  Filter: (empid = ' 00010   '::bpchar)"
"  Rows Removed by Filter: 9999"
"Planning time: 0.066 ms"
"Execution time: 3.356 ms"

然后我编写了如下函数

CREATE OR REPLACE FUNCTION test19() RETURNS TEXT AS $$
DECLARE total TEXT;
BEGIN
    EXPLAIN ANALYZE into total SELECT * FROM employee emp, payroll pr where emp.empid = ' 00010   ';
    RETURN total;
END;

但是当我运行函数时

输入查询

select * from test19()

输出结果

Nested Loop  (cost=0.00..543.00 rows=10000 width=108) (actual time=0.022..9.311 rows=10000 loops=1)

函数的输出不包含执行时间。我在功能上面犯了什么错误？

So the output from function doesn't contain the execution time. What is the mistake I have done above function? 

推荐答案

您可以将 EXPLAIN ANALYZE 的输出作为XML / JSON和解析它：

You could return output from EXPLAIN ANALYZE as XML/JSON and parse it:

CREATE OR REPLACE FUNCTION test19() RETURNS TEXT
AS $$
DECLARE total TEXT;
BEGIN
    EXPLAIN (ANALYZE, TIMING, FORMAT JSON) into total SELECT 1 AS c;
    RETURN ((total::jsonb)-> 0 -> 'Execution Time');
END;
$$  LANGUAGE plpgsql;

db< fiddle演示

db<>fiddle demo

这篇关于使用Postgres中的explain analytics创建一个函数来检查执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！