I am using Postgresql. I want to test how much time a function takes to execute. Since the function takes only a few milliseconds, I would like to call it in a loop 1000s of times to get an accurate figure.
MySQL has a BENCHMARK() function to do this. Is there an equivalent or do I have to write a procedure with a loop to do this?
In PostgreSQL you typically do this with generate_series:
SELECT my_function()
FROM generate_series(1,5000);
or
SELECT (SELECT my_query ....)
FROM generate_series(1,5000);
In the latter case you can add OFFSET 0 to the subquery or wrap it in a STRICT SQL function to prevent the query planner from pulling out common conditions and subclauses and otherwise being clever.
Timing can be obtained with psql's \timing command, with SET log_duration = on, or with EXPLAIN (ANALYZE, BUFFERS), all of which time subtly different things - see the documentation. In brief, \timing measures time including round-trips and value transfer to the client. log_duration measures server-side execution time. EXPLAIN (ANALYZE, BUFFERS) measures details of the statement's execution but adds timing overhead that can slow it down a bit.
Write a procedure with a loop - shouldn't take more than 5 minutes.
