Создайте функцию для проверки времени выполнения с помощью объяснения анализа в Postgres

Я выполнил несколько запросов, чтобы получить время выполнения

Входной запрос

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)

Таким образом, вывод функции не содержит времени выполнения. В чем ошибка, которую я сделал выше функции?

🤔 А знаете ли вы, что...
PostgreSQL поддерживает многозадачность и многопоточность для обработки одновременных запросов.


1 352
3

Ответы:

Вы можете вернуть вывод EXPLAIN ANALYZE в виде XML/JSON и проанализировать его:

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<>демонстрация скрипки


Я создал приведенную ниже функцию, я не могу поверить, что она работает...

CREATE OR REPLACE FUNCTION test19(query TEXT) RETURNS JSON language 'plpgsql' AS $$
DECLARE total JSON;
BEGIN
    EXECUTE 'EXPLAIN (ANALYZE, FORMAT JSON) ' || query into total;
    RETURN total;
END;
$$;

Вот пример того, как его использовать (с запросами, относящимися к моей базе данных, замените своими):

WITH Queries(q) AS (
    VALUES ('SELECT * FROM Price'),
    ('SELECT * FROM Price WHERE PriceTypeIK=2 AND PriceDate > current_date - interval ''50 days''')
)
SELECT q, (test19(q)->0->'Plan'->>'Actual Total Time')::FLOAT from queries;

Убедитесь, что вы всегда используете его в транзакции, которую вы откатываете, или точку сохранения, откатываете до точки сохранения, а затем фиксируете.

Обновлено: последний комментарий, который я понял, я не писал: вы не обязаны извлекать только фактическое общее время, так как я закодировал функцию для возврата всего плана. (попробуйте SELECT q, test19(q) from queries с указанным выше).


SELECT INTO в PL/pgSQL обрабатывает только одну строку.

EXPLAIN возвращает несколько строк, поэтому ваша функция возвращает только первую.

Если вы хотите извлечь только время выполнения, вы можете либо проанализировать вывод JSON, как показано Лукашем, либо вам нужен цикл:

create or replace function show_time(to_explain text)
  returns text,
as
$$
declare
  l_plan_line record;
  l_line text;
begin
   l_line := 1;
   for l_plan_line in execute 'explain (analyze, verbose, format text)'||to_explain    
   loop
      l_line := l_plan_line."QUERY PLAN"::text;
      if l_line like 'Execution Time%' then 
         return l_line;
      end if;
   end loop;
   return null;
end;
$$
language plpgsql;