CODEWITHSUNDEEP
postgresqlpsqlpostgresql-9.3
Nils Pitapatting
Nils Pitapatting

Reputation: 75

Benchmarking many PostgreSQL queries

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

Upvotes: 4

Views: 2544

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

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.

Upvotes: 16

Related Questions