Re: Avoiding bad prepared-statement plans.

On 02/09/2010 08:46 AM, Jeroen Vermeulen wrote:
>> This sounds like a really nice to have feature. Maybe it'd also be 
>> possible to skip replanning between executes if the current bound 
>> values are 'indexwise-equivalent' to the values used at previous 
>> planning, i.e. nothing in the statistics indicates that execution 
>> cost would be (much) different. Are there more ways to cut down on 
>> planning time? Obviously some plannedstatement/plannerinfo structures 
>> could be kept, but maybe it'd also be possible to plan only that part 
>> of the join tree where the params are used in a scan/join qual.
>
> I think we should be careful not to over-think this.  Planning isn't 
> *that* costly, so apply Amdahl's Law liberally.  I'm proposing some 
> easy things we could do without adding much overhead or maintenance 
> burden; I've been assuming that getting intimate with the planner 
> would risk those advantages.

In a current commercial app we have that uses JDBC and prepared plans 
for just about everything, it regularly ends up with execution times of 
30+ milliseconds when a complete plan + execute would take less than 1 
millisecond.

PostgreSQL planning is pretty fast. In terms of not over thinking things 
- I think I would even prefer an option that said "always re-plan 
prepared statements" as a starting point. If it happened to become 
smarter over time, such that it would have invalidation criteria that 
would trigger a re-plan, that would be awesome, but in terms of what 
would help me *today* - being able to convert prepared plans into just a 
means to use place holders would help me today on certain real 
applications in production use right now.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>