3

I have a batch of SQL benchmark queries that I would like to execute and compute their timing. I do not want to obtain the execution plan with the command EXECUTE ANALYZE.

Based on the approach suggested here Get execution time of PostgreSQL query I have written a procedure that contains for each query the following code

-------------------------
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);
-------------------------

I use PERFORM instead of SELECT to execute the query and discard the result.

That works for simple queries, but I have many queries that have a WITH clause. According to the manual

For WITH queries, use PERFORM and then place the query in parentheses. (In this case, the query can only return one row.)

However, all the WITH queries I need to benchmark return multiple rows.

How to solve this problem ?

Esteban Zimanyi
  • 201
  • 3
  • 6

1 Answers1

1

What about something like

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
  • 14,826
  • 2
  • 33
  • 41
  • Great ! Thanks ! How to remove the CONTEXT messages after RAISE INFO ? I have tried ALTER DATABASE test3 SET log_error_verbosity = TERSE; but didn't work. P.S. I am executing on pgAdmin – Esteban Zimanyi Jun 13 '17 at 07:39
  • @EstebanZimanyi Not sure what you mean. It is just an example. As I understood, you want to store execution time into the table... – Abelisto Jun 13 '17 at 08:11
  • Indeed, but it would also be nice (not necessarily in this particular example) to remove all messages such as "CONTEXT: PL/pgSQL function test_queries() line 262 at RAISE" each time a message is displayed on the screen when executing long scripts such as benchmarking a long batch of queries with different scale factors. Is there a way to do that ? – Esteban Zimanyi Jun 13 '17 at 08:38
  • @EstebanZimanyi The problem is that I have no such additions to messages here. So no any ideas. – Abelisto Jun 13 '17 at 08:49
  • @EstebanZimanyi It seems that it is `psql` feature: https://www.depesz.com/2008/07/12/suppressing-context-lines-in-psql/ – Abelisto Jun 13 '17 at 08:56