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

Error Reporting and Logging and pg_stat_statements PS: "On a complex webpage more than hundred queries are executed." - for now I know why the web becomes slower and slower :o( — Abelisto
– Abelisto, Commented Nov 19, 2016 at 15:50

Laurenz Albe · Accepted Answer · 2016-11-21 09:20:03Z

16

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.

answered Nov 21, 2016 at 9:20

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nils Pitapatting Over a year ago

Thanks, that is exactly what I need.

Bergi Over a year ago

Would using the Execution Time instead of Actual Total Time be more accurate?

Laurenz Albe Over a year ago

@Bergi I guess you are right. But the difference is usually marginal.

Collectives™ on Stack Overflow

Benchmarking many PostgreSQL queries

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related