Shall I convert all my stored procedure to use "EXECUTE", to ensure I get index-scan

[Yan Cheng Cheok <yccheok@xxxxxxxxx>]

By refering to http://archives.postgresql.org/pgsql-general/2010-01/msg00846.php

It solves speed problem in stored procedure, which use function parameter, during its SQL query.

Does this means, I shall convert *ALL* my stored procedure, which use function parameter during its SQL query, to use EXECUTE, to ensure I always get index-scan?

For example :

convert :

CREATE OR REPLACE FUNCTION update_lot_end_timestamp(bigint)
  RETURNS timestamp AS
$BODY$DECLARE
    _lotID ALIAS FOR $1;
    _timestamp timestamp;
BEGIN
    _timestamp = now();
    UPDATE lot SET timestamp = _timestamp WHERE lot_id = _lotID;
    return _timestamp;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_lot_end_timestamp(bigint) OWNER TO postgres;

to


CREATE OR REPLACE FUNCTION update_lot_end_timestamp(bigint)
  RETURNS timestamp AS
$BODY$DECLARE
    _lotID ALIAS FOR $1;
    _timestamp timestamp;
BEGIN
    _timestamp = now();
    EXECUTE 'UPDATE lot SET timestamp = $1 WHERE lot_id = $2'
    USING _timestamp, _lotID;
    return _timestamp;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_lot_end_timestamp(bigint) OWNER TO postgres;

Is there any rule of thumb to follow?

Thanks and Regards
Yan Cheng CHEOK


      


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general