Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 18 at 12:22

This question came from our site for professional and enthusiast programmers.

    
This might get better answers on dba.SE, where it would fit. I suggest to migrate it there. (Just flag, asking for migration if you agree.) –  Erwin Brandstetter Mar 18 at 10:49
    
You're better off with a custom pgbench script, or a client-side loop. –  Craig Ringer Mar 18 at 11:40
    
Well if Postgres marks select query as STABLE it doesn't matter if the loop is client side or server-side, however network stability will affect the result greatly. How can I use pgbench to measure query/stored procedure performance? –  Nick Mar 18 at 12:46
    
stable or volatile applies 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 at 13:51
    
I'm asking if its legit to use this approach for single query performance measurement. How can I test if the query is executed every time or planner skips execution of select query and just gives me cached value from previous query execution? –  Nick Mar 18 at 20:20

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.