Re: parameter hints to the optimizer

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