Why Stored Procedure is Slower In The Following Case?

From: Yan Cheng Cheok <yccheok@yahoo.com>
Subject: Why Stored Procedure is Slower In The Following Case?
To: pgsql-general@postgresql.org
Date: Wednesday, January 20, 2010, 3:10 PM
I have a stored procedure, which
perform single RETURN QUERY SELECT..., by taking in 2
function parameters.

It takes around 7 seconds to complete the operation.

When I perform outside stored procedure, with exact same
SELECT statement, it only takes 0.5 seconds, with same
result being returned.

Testing using Stored Procedure
==============================
SELECT * FROM get_measurements(1, 'Pad%');
6949.593 ms

EXPLAIN SELECT * FROM get_measurements(1, 'Pad%');
               
               
QUERY PLAN
---------------------------------------------------------------------------
Function Scan on get_measurements  (cost=0.00..260.00
rows=1000 width=72)
(1 row)

Testing using SQL statement
===========================
SemiconductorInspection=# SELECT measurement_type.value,
measurement.value, measurement_unit.value FROM unit, lot,
measurement,
measurement_unit, measurement_type WHERE lot_id =
fk_lot_id AND fk_unit_id = unit_id AND
fk_measurement_unit_id = measurement_
unit_id AND fk_measurement_type_id = measurement_type_id
AND lot_id = 1 AND measurement_type.value LIKE 'Pad%';
552.234 ms

SemiconductorInspection=# EXPLAIN SELECT
measurement_type.value, measurement.value,
measurement_unit.value FROM unit, lot, meas
urement, measurement_unit, measurement_type WHERE lot_id =
fk_lot_id AND fk_unit_id = unit_id AND
fk_measurement_unit_id = meas
urement_unit_id AND fk_measurement_type_id =
measurement_type_id AND lot_id = 1 AND
measurement_type.value LIKE 'Pad%';
               
               
             
   QUERY PLAN
------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=51.33..44328.65 rows=3629
width=53)
   ->  Index Scan using pk_lot_id on
lot  (cost=0.00..8.27 rows=1 width=8)
         Index Cond: (lot_id
= 1)
   ->  Hash Join 
(cost=51.33..44284.10 rows=3629 width=61)
         Hash Cond:
(measurement.fk_measurement_unit_id =
measurement_unit.measurement_unit_id)
         ->  Hash
Join  (cost=13.65..44196.52 rows=3629 width=33)
           
   Hash Cond:
(measurement.fk_measurement_type_id =
measurement_type.measurement_type_id)
           
   ->  Hash Join 
(cost=11.38..44138.71 rows=5134 width=24)
               
     Hash Cond: (measurement.fk_unit_id
= unit.unit_id)
               
     ->  Seq Scan on
measurement  (cost=0.00..36261.81 rows=2083781
width=24)
               
     ->  Hash 
(cost=10.08..10.08 rows=104 width=16)
               
           -> 
Index Scan using idx_fk_lot_id on unit 
(cost=0.00..10.08 rows=104 width=16)
               
             
   Index Cond: (fk_lot_id = 1)
           
   ->  Hash  (cost=1.76..1.76
rows=41 width=17)
               
     ->  Seq Scan on
measurement_type  (cost=0.00..1.76 rows=41 width=17)
               
           Filter: (value
~~ 'Pad%'::text)
         -> 
Hash  (cost=22.30..22.30 rows=1230 width=36)
           
   ->  Seq Scan on
measurement_unit  (cost=0.00..22.30 rows=1230
width=36)
(18 rows)

The content for stored procedure is as follow :

CREATE OR REPLACE FUNCTION get_measurements(IN bigint, IN
text)
  RETURNS TABLE(_type text, _value double precision,
_unit text) AS
$BODY$DECLARE
    _lotID ALIAS FOR $1;
    _type ALIAS FOR $2;
BEGIN
    RETURN QUERY SELECT measurement_type.value,
measurement.value, measurement_unit.value
    FROM unit, lot, measurement,
measurement_unit, measurement_type
    WHERE lot_id = fk_lot_id AND fk_unit_id =
unit_id AND fk_measurement_unit_id = measurement_unit_id AND

          fk_measurement_type_id =
measurement_type_id AND
          lot_id = _lotID AND
measurement_type.value LIKE _type;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION get_measurements(bigint, text) OWNER TO
postgres;

As you can see, their select statement is the same. Except
stored procedure is having additional 'QUERY'. Does that
make the speed difference?

Thanks and Regards
Yan Cheng CHEOK

p/s I have index on measurement_type table using :

CREATE INDEX idx_measurement_type_value
  ON measurement_type
  USING btree
  (value);