Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to benchmark a query containing an 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).

share|improve this question
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. – Craig Ringer May 13 '13 at 5:11
Very useful tool indeed, thanks! – Franck Dernoncourt May 22 '13 at 19:42
up vote 16 down vote accepted

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 AS
   i int;

FOR i IN 1 .. $1 LOOP
    EXECUTE sql;  -- not safe against SQLi!

$func$ LANGUAGE plpgsql


SELECT f_test(100, $x$SELECT * from'mock3', '{x1, x2, x3}', '{y1}', 100,20, 3)$x$

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.

share|improve this answer
Thanks! I'm actually looking for a way to run the query several times, as when I call the same query twice, the time it takes seems to vary a bit (like 5% difference, very approximately). The query, which I should have probably added in the question's detail on second thought, is very simple: SELECT * from'mock3', '{x1, x2, x3}', '{y1}', 100,20, 3); so in this situation I don't really need ANALYZE, as I simply call a user-defined function with a couple of parameters. – Franck Dernoncourt May 12 '13 at 19:32
I would like to clear the cache as I want to see how the parameters I give to impact the runtime. The first run after starting postgresql might be have some disadvantage as it has no cache (all the queries use the same relation). Also, more generally, I wanted not to worry about cache affecting run times, so I thought getting rid of it each time might be (maybe not?) a good idea from benchmark. – Franck Dernoncourt May 12 '13 at 19:33
Another reason why I would like to run the query a bunch of times is that the UDF isn't deterministic (if you're curious gp stands for genetic programming, which is used to perform and symbolic regression between attributes in a relation), so runtime can change quite a lot. (The approximation of 5% of run time difference I gave in my previous comment was in the case of a deterministic UDF.) – Franck Dernoncourt May 12 '13 at 19:35
@FranckDernoncourt: I added two simple methods to test many iterations. Neither pre-empties the cache, though. – Erwin Brandstetter May 12 '13 at 20:03
Thanks a lot for the additional details! – Franck Dernoncourt May 22 '13 at 19:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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