I want to benchmark a query containing a user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark?

I know that timing can be turned on with \timing at the psql prompt, but ideally I'd like to have a script that takes care of everything automatically: running the query a few times, clearing PostgreSQL cache after each run (probably by restarting the PostgreSQL's service), and outputting the average running time (and memory used is a plus).

  • 3
    Check out pgbench; you can run it with custom scripts to do some of what you want. With a wrapper shell script to stop and restart Pg and to drop the OS disk cache you have most of what you need. May 13, 2013 at 5:11
  • Not quite a duplicate of dba.stackexchange.com/questions/3148/…. Feb 27, 2018 at 17:46

1 Answer 1


The widely used tool is the SQL command EXPLAIN ANALYZE, possibly with more options for more details in the answer. That outputs the query plan with the planner estimates plus actual execution times.

Why would you want to clear the cache? The generally more likely use case is that the cache is populated. If you still want to go that route, here is a related answer on SO.

Not resetting the cache, here are two simple ways to test with many iterations:

Simple UDF

SELECT f_myfunc(g) FROM generate_series (1,1000) AS t(g);

Or with random input - random numbers between 0 and 5000 in the example:

SELECT f_myfunc((random()*5000)::int) FROM generate_series (1,1000) AS t(g);

Or with a real life table:

SELECT f_myfunc(my_column) FROM my_tbl;   -- LIMIT n

More complex functions / queries

CREATE FUNCTION f_test(ct int, sql text)
  RETURNS void
  LANGUAGE plpgsql AS
   i int;
   FOR i IN 1 .. $1 LOOP
      EXECUTE sql;  -- not safe against SQLi!


SELECT f_test(100, $q$SELECT * from MADLIB.gp('mock3', '{x1, x2, x3}', '{y1}', 100,20, 3)$q$

Careful: The query is actually executed!
Careful: Not fit for public use. Possible SQL injection.

Again, you can use random parameters if needed. Possibly with the USING clause of EXECUTE.

More options

  • psql \timing
  • the GUC log_duration
  • manual server-side timing with clock_timestamp()

For details, see:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.