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
