Different queries result in big difference in performance

Question

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?

Answer 1

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.

Answer 2

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.