When developing server-side stored procedures performance measurement is an important issue. EXPLAIN ANALYZE is very usefull, but when it comes to simple queries like SELECT name FROM clients WHERE ... execution time varies from time to time which makes it difficult to compare two different approaches to the same relatively simple task.
The method I use to solve this problem is as follows:
--1st approach
CREATE OR REPLACE FUNCTION perf_test_q1()
RETURNS VOID AS
$BODY$
DECLARE
i bigint;
BEGIN
FOR i IN 1..1000000 LOOP
SELECT name FROM clients WHERE id<10 AND id>1;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
--2nd approach
CREATE OR REPLACE FUNCTION perf_test_q2()
RETURNS VOID AS
$BODY$
DECLARE
i bigint;
BEGIN
FOR i IN 1..1000000 LOOP
SELECT name FROM clients WHERE id IN (2,3,4,5,6,7,8,9);
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
And the explain analyze clause:
EXPLAIN ANALYZE select * from perf_test_q1(); --"Total runtime: 15188.418 ms"
EXPLAIN ANALYZE select * from perf_test_q2(); --"Total runtime: 17219.514 ms"
Is it valid (legit?) to measure performance of STABLE functions (which SELECT is in this case) by looping them? Or in other words, does PostgreSQL planner automaticly decide if the select query is stable or volatile?
pgbenchscript, or a client-side loop. – Craig Ringer Mar 18 '14 at 11:40pgbenchto measure query/stored procedure performance? – Nick Mar 18 '14 at 12:46stableorvolatileapplies to an entire function, not individual queries. What your example shows is a loop over a SELECT to accumulate execution time. It's unclear how it would relate to the stable or volatile qualifier and therefore what you're really asking. – Daniel Vérité Mar 18 '14 at 13:51