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 PREPARE to do the same. Also, allow control over replanning prepared queries either manually or automatically when statistics for 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
