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

2010/1/22 Sam Mason <sam(at)samason(dot)me(dot)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