Thread: 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.php
>
> 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?

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.php
>>
>> 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?
>
> 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.php
>>
>> 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?
>
> 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

Does it mean, if it isn't broken, don't fix it?

Thanks and Regards
Yan Cheng CHEOK