Skip site navigation (1) Skip section navigation (2)

Re: Avoiding bad prepared-statement plans.

From: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
To: Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
Cc: Yeb Havinga <yebhavinga(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-09 14:53:17
Message-ID: 4B7176DD.5060305@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
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>


In response to

Responses

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2010-02-09 15:32:26
Subject: Re: About psycopg2 (by its author)
Previous:From: Richard HuxtonDate: 2010-02-09 14:45:57
Subject: Re: Avoiding bad prepared-statement plans.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group