Thanks. I am able to solve my problem using the following (EXECUTE) :
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 EXECUTE '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 $2'
USING _lotID, _type;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION get_measurements(bigint) OWNER TO postgres;
Thanks and Regards
Yan Cheng CHEOK
--- On Wed, 1/20/10, Yan Cheng Cheok <yccheok@xxxxxxxxx> wrote:
> From: Yan Cheng Cheok <yccheok@xxxxxxxxx>
> Subject: Why Stored Procedure is Slower In The Following Case?
> To: pgsql-general@xxxxxxxxxxxxxx
> 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);
>
>
>
>
>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
