Re: Delaying the planning of unnamed statements until Bind - Mailing list pgsql-hackers

Oliver Jowett <oliver@opencloud.com> writes:
> Tom Lane wrote:
>> I'm a bit concerned about driving such a change in behavior off nothing
>> more than whether the statement is named or not.

> Would it be better to bite the bullet and bump the protocol version, 
> adding a new field to Parse to control this behaviour?

No, I don't think so.  Protocol version changes are expensive.  Maybe in
a few years we'll want to do another one, when we've accumulated a list
of things we want to fix.  But not today and not for just one item.
I concur that making unnamed statements act this way is a reasonable
compromise for now.  However it seems we still have a bit of a
disagreement as to what the details of "this way" are.

>> I don't like that at all.  Do the planning using the given parameters,
>> but save the plan.  Otherwise you have substantially pessimized the
>> behavior for the case where an unnamed statement is reused.

> How often is the unnamed statement reused?

Who's to say?  If it's not reused then this argument is moot, so let's
assume there are people out there who want to reuse it.  What behavior
will they wish for?  The "plan with first set of actual parameters, then
keep using plan" behavior was discussed long ago, and it seems useful to
me to provide it.  People who think they are going to have statistically
different parameter values from time to time will of course not want to
use this, but people who expect the same plan to continue to be useful
won't want to pay the overhead of replanning.

The reason this is important is exactly the one you have already seen,
namely that when faced with unknown Params the planner will sometimes
fall back to very conservative assumptions.  An example that comes up
pretty often isselect * from mytable where entry_time >= $1;
The planner will take a seqscan when it sees this because it is worried
about the downside if a large fraction of the table is being selected.
However the people who do this generally know that they are always going
to provide cutoff times in the fairly recent past, which would make an
indexscan reasonable.  So planning on the basis of the first supplied
parameter value would cause the right decisions to be made, and redoing
that on every call would just make for useless overhead.

In my experience planning is significantly more expensive than parse
analysis, and so people who do not want this behavior could avoid it
at relatively little cost by just re-sending Parse each time.  But if
we don't provide the behavior then there is simply no way for people
who do want it to get it.


>> No, you can't replace Params with Consts.

> Transforming the tree seemed the most reliable way to get a result 
> that's consistent with Const being in the tree from the start (i.e. the 
> simple query case), and doing the replacement hasn't broken anything in 
> my (admittedly brief) testing yet.

This depends on the outcome of the above discussion.  If you're not
saving the plan then replacing params with constants is reasonable,
but if you are saving the plan then you can't do that.

> Would it be safe to save-and-clear the global parameter state only at 
> the topmost planner() level as is done for PlannerParamList and friends? 

Can't see why not.  Note that I do not see this state as "global" in the
sense of affecting anything outside the planner.
        regards, tom lane