Why is select faster than a function call?

Question

I have a STABLE function get_value_by_id(id) which just gets the value from table indexed by id. I run EXPLAIN ANALYZE on function and also on the same SELECT statement which is inside function.

SELECT * FROM table where id = $id.

Total runtime for SELECT is always better. Why is that ? Should avoid using functions for this kind of situations ?

EDIT:

Adding result of the explain analyze :

"Function Scan on get_value_by_id  (cost=0.25..0.26 rows=1 width=1640) (actual time=0.187..0.189 rows=1 loops=1)" 
"Total runtime: 0.231 ms"

"Index Scan using id_index on table  (cost=0.29..8.31 rows=1 width=255) (actual time=0.023..0.026 rows=1 loops=1)" 
"  Index Cond: (id = 10000)" 
"Total runtime: 0.073 ms"

EDIT2 Adding the function

CREATE OR REPLACE FUNCTION get_value_by_id(my_id integer)
  RETURNS table1 AS
$BODY$
DECLARE 
  result table1;
BEGIN
    SELECT val1, val2, val3 INTO result
        FROM table1
        WHERE id  = my_id;

RETURN result;
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100;
ALTER FUNCTION get_value_by_id(integer)
  OWNER TO my_user;

Results with SQL function :

"Function Scan on get_value_by_id_sql  (cost=0.25..0.26 rows=1 width=1640) (actual time=0.324..0.326 rows=1 loops=1)" 
"Total runtime: 0.421 ms"

Please show us the execution plans (or upload them to explain.depesz.com) — a_horse_with_no_name
@juergend I thought it would be faster since it caches the execution plan. — Tamerlane

Bazinga Bazinga · Accepted Answer · 2014-04-17T16:43:08

"User defined functions in any language are going to be slower than using the equivalent inline logic; it adds more processing instructions and overhead to the overall algorithm and more processing = more slower." source

Why is select faster than a function call?

1 Answers