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.

I have 2 similar functions, different way result in big diff in performance.

The PostgreSQL version: 9.2.1.

Function 1

create or replace function func_1()  returns text  as
$$
declare
   v_i integer;  
   v_md5 varchar; 
begin
   for v_i in 1..1000000 loop
       v_md5:='a'|| v_i::character varying;
   end loop;  
   return 'ok';
end;
$$
language plpgsql;

Function 2

create or replace function func_select()  returns text  as
$$
declare
   v_i integer;  
   v_md5 varchar; 
begin
   for v_i in 1..1000000 loop
      select 'a'|| v_i::character varying into  v_md5;
   end loop;
   return 'ok';
end;
$$
language plpgsql;

Function Timings

francs=> \timing
Timing is on.
francs=> select func_1();
 func_1 
--------
 ok
(1 row)

Time: 1467.231 ms
francs=> select func_1();
 func_1 
--------
 ok
(1 row)

Time: 1383.424 ms
francs=> select func_select();
 func_select 
-------------
 ok
(1 row)

Time: 22176.600 ms
francs=> select func_select();
 func_select 
-------------
 ok
(1 row)

Time: 23265.755 ms

From the above, function func_1() only takes about 1383 ms, but function func_select() takes about 23265 ms, anybody can explain this?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

The use of the pure SQL statement in the second loop makes the database engine compile the whole select 'a'|| v_i::character varying into v_md5; on each loop iteration before evaluating it whereas the variable assignment is parsed easily into low level instructions once.

share|improve this answer
    
thanks,can you explain more detailedly? –  francs Apr 19 '13 at 8:05
    
you mean second funciton need grammer check , plan , and execution which takes more time? –  francs Apr 19 '13 at 8:10
    
when a database is presented with SQL e.g. "select * from sometable where x = 'some_value'; it has to determine what the best way to execute the SQL is. It looks at the analysis statistics and determines the best execution plan before compiling into more low level instructions. Now I know that the select statement in your example is a very simple but the database does not so it has to determine the execution plan and compile the statement every time it sees it. –  Paddy Carroll Apr 19 '13 at 12:04
    
so you mean hard parse rather than soft parse? –  francs Apr 20 '13 at 3:13

For best performance, you may probably use generate_series as in

create or replace function func_1()  returns text  as
$$
declare
  v_i integer;  
  v_md5 varchar; 
  begin
     select 'a'||i     into  v_md5
     from generate_series(1, 1000000) i;
     return 'ok';
  end;
$$
language plpgsql;

In my test, it takes 1/6 of the time of your first implementation.

Addendum: btw, the main difference among these three implementations are how much work you delegate in one instruction to the backend. In my solution you do not loop, so it is 1 instruction, while in your second implementation you have a loop that include a select (that is a complex function), and in your first example you have a loop that include an assignment (that is a simple function). That explain the difference.

share|improve this answer

Your Answer

 
discard

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.