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 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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073