Benchmarking many PostgreSQL queries

Question

On a complex webpage more than hundred queries are executed. I search for a way to benchmark these queries. I tried to embed EXPLAIN ANALYZE into a query resp. into a psql function, to pick the execution time of every query and compare them. But EXPLAIN ANALYZE seems not to be embeddable in any way.

Is there another solution to compare the execution time of some queries?

Thanks a lot

Answer 1

There is no problem with embedding EXPLAIN in PL/pgSQL:

CREATE OR REPLACE FUNCTION profile(
      IN query text,
      OUT total_cost double precision,
      OUT runtime double precision
   ) RETURNS record
  LANGUAGE plpgsql STRICT AS
$$DECLARE
   j json;
BEGIN
   EXECUTE 'EXPLAIN (ANALYZE, FORMAT JSON) ' || query INTO j;
   total_cost := (j->0->'Plan'->>'Total Cost')::double precision;
   runtime := (j->0->'Plan'->>'Actual Total Time')::double precision;
   RETURN;
END;$$;

You can use it for example as follows:

test=> SELECT * FROM profile($$SELECT * FROM large WHERE val = 'mama'$$);
┌────────────┬─────────┐
│ total_cost │ runtime │
├────────────┼─────────┤
│   14542.43 │ 207.836 │
└────────────┴─────────┘
(1 row)

Don't use it with untrusted queries, as the function is vulnerable to SQL injection.