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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jeroen Vermeulen <jtv(at)xs4all(dot)nl>, Alex Hunsaker <badalex(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, Bart Samwel <bart(at)samwel(dot)tk>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-26 19:22:37
Message-ID: 4B881F7D.2020506@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
On 02/26/2010 01:59 PM, Tom Lane wrote:
>> ... It's walking around the problem
>> that the idea of a generic plan is just wrong. The only time a generic
>> plan is right, is when the specific plan would result in the same.
>>      
> I think that's a significant overstatement.  There are a large number
> of cases where a custom plan isn't worth it, even if it doesn't generate
> exactly the same plan.
>    


There must be some way to lift the cost of planning out of the plan 
enumeration and selection phase, such that only plan enumeration and 
selection is run at execute time. In most cases, plan enumeration and 
selection, provided that all data required to make these decisions is 
all cached in data structures ready to go, should be very fast? Right? 
Wrong? If right, my original post suggested that prepare should do the 
parts of planning which are fixed, and not change based on the input 
parameters, while execute should do the dynamic parts that would change 
based on the input parameters.

By "not worth it", do you mean development effort or run time?

For development effort, it would definitely be worth it in the grand 
scheme of things, but perhaps not worth it to specific individuals.

For run time, I've having trouble seeing the situation where it would 
not be worth it. In the case that the resulting plan is the same (custom 
vs generic) there should be no cost. In the case that the plan is 
different, I think the difference proves that it is worth it. The case 
where it wouldn't be worth it would be if a prepared statement was 
called many times with many different parameters, and each set of 
parameters required a re-plan - but my experience in this regard tells 
me that the current model is to choose a sub-optimal plan, and the 
entire query will run much slower than the planning time, on every 
execute. We wouldn't be having this discussion if generic plans were 
considered adequate. So, I feel that it is worth it in this case as well.

It's the development effort that is the problem. I can't do it, and I 
can't make you do it. If you say "too hard", there isn't anything I can 
do about it. :-)

Cheers,
mark


In response to

Responses

pgsql-hackers by date

Next:From: Mark MielkeDate: 2010-02-26 19:23:22
Subject: Re: Avoiding bad prepared-statement plans.
Previous:From: Tom LaneDate: 2010-02-26 19:16:40
Subject: Re: Re: Hot Standby query cancellation and Streaming Replication integration

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