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(at)mielke(dot)cc>