Shall I convert all my stored procedure to use "EXECUTE", to ensure I get index-scan
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
On Fri, Jan 22, 2010 at 01:49:50AM -0800, Yan Cheng Cheok wrote:
By refering to
http://archives.postgresql.org/pgsql-general/2010-01/msg00846.phpDoes 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?
I wouldn't bother, mainly because converting to EXECUTE does *not*
ensure it'll use an index scan. Just that the stats it's picking up
will be more appropriate to the query in question. That's normally
going to be a win, but for some queries PG will end up spending longer
planning the queries than it will running them.
Also, if you're only testing with made up datasets and not the whole
thing, PG will be behaving differently. You can only really see what's
going on when you're testing with the real data.
--
Sam http://samason.me.uk/
2010/1/22 Sam Mason <sam@samason.me.uk>:
On Fri, Jan 22, 2010 at 01:49:50AM -0800, Yan Cheng Cheok wrote:
By refering to
http://archives.postgresql.org/pgsql-general/2010-01/msg00846.phpDoes 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?I wouldn't bother, mainly because converting to EXECUTE does *not*
ensure it'll use an index scan. Just that the stats it's picking up
will be more appropriate to the query in question. That's normally
going to be a win, but for some queries PG will end up spending longer
planning the queries than it will running them.Also, if you're only testing with made up datasets and not the whole
thing, PG will be behaving differently. You can only really see what's
going on when you're testing with the real data.
Maybe the point is:
1. use the execute (to force a new query plan) and
2. ensure a "vacuum analyze" is executed sometimes (to have proper statistics)
--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS
On 22/01/2010 10:54 PM, Sam Mason wrote:
On Fri, Jan 22, 2010 at 01:49:50AM -0800, Yan Cheng Cheok wrote:
By refering to
http://archives.postgresql.org/pgsql-general/2010-01/msg00846.phpDoes 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?I wouldn't bother, mainly because converting to EXECUTE does *not*
ensure it'll use an index scan. Just that the stats it's picking up
will be more appropriate to the query in question.
I increasingly wish I had the brain-power to tackle implementing a
'REPLAN' option on PL/PgSQL functions and/or an 'EXECUTE REPLAN' for
prepared queries...
--
Craig Ringer