Re: parameter hints to the optimizer

OK, I added this TODO:


* Allow finer control over the caching of prepared query plans
 Currently, queries prepared via the libpq API are planned on first execute using the supplied parameters --- allow SQL
PREPAREto do the same.  Also, allow control over replanning prepared queries either manually or automatically when
statisticsfor execute parameters differ dramatically from those used during planning.
 

---------------------------------------------------------------------------

Oliver Jowett wrote:
> Bruce Momjian wrote:
> > Oliver Jowett wrote:
> > 
> >>Bruce Momjian wrote:
> >>
> >>>Oliver Jowett wrote:
> >>>
> >>>
> >>>>Merlin Moncure wrote:
> >>>>
> >>>>
> >>>>
> >>>>>Another way to deal with the problem is to defer plan generation until
> >>>>>the first plan execution and use the parameters from that execution.
> >>>>
> >>>>When talking the V3 protocol, 7.5 defers plan generation for the unnamed 
> >>>>statement until parameters are received in the Bind message (which is 
> >>>>essentially the same as what you describe). There was some discussion at 
> >>>>the time about making it more flexible so you could apply it to arbitary 
> >>>>statements, but that needed a protocol change so it didn't happen.
> >>>
> >>>
> >>>What do you mean about arbitrary statements?  Non-prepared ones, or
> >>>non-unnamed ones?
> >>
> >>Non-unnamed ones. Adding flag on the Parse message that says when to 
> >>plan the statement (or maybe on each Bind message even).
> > 
> > 
> > OK, what are unnamed prepared statements?  When are they used currently?
> > Only via the wire protocol?  Who uses them now?
> 
> The unnamed prepared statement is like any other prepared statement 
> except it doesn't have a name :)  It can be accessed via:
> 
> 1) V3 protocol Parse/Bind with an empty statement name uses the unnamed 
> statement.
> 2) V2 or V3 "simple query" implicitly closes the unnamed statement.
> 
> CVS HEAD defers planning in case (1) until the Bind is received so it 
> can do planning cost estimation using concrete parameter values and 
> produce a better plan. It only does this for the unnamed statement, not 
> for named statements. If you Parse into a named statement, planning 
> happens immediately when the Parse is done.
> 
> This behaviour gives the client some flexibility without changing the 
> protocol. It means that using Parse/Bind on the unnamed statement with 
> parameters is essentially equivalent planning-wise to substituting the 
> parameter values into the actual query and submitting that instead.
> 
> What we talked about briefly was providing some way to control when 
> planning was done on a per-statement basis -- so you could say "don't 
> defer planning for this unnamed query because I'm going to reuse the 
> unnamed statement multiple times and the first set of parameters might 
> not generate an efficient plan" or "do defer planning of this named 
> query because I know I will be executing it with many similar parameter 
> values and estimating using the first set of parameters gives a good plan".
> 
> Or an alternative is to have a way to control query replanning on each 
> Bind individually -- so a client can get the benefit of skipping the 
> parse step on subsequent executions and is able to pass parameters via 
> Bind, but the query is replanned for the concrete parameter values on 
> each execution. The JDBC driver wants this -- currently the use of named 
> statements has to be explicitly turned on as with the current behaviour 
> you may take a performance hit due to less-than-ideal plans as soon as 
> you start using named statements.
> 
> So maybe the TODO should be something like "allow finer-grained client 
> control of query estimation and (re-)planning when using Parse/Bind".
> 
> -O
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073