Re: One-Shot Plans

On Mon, Aug 1, 2011 at 4:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Tue, Jun 14, 2011 at 9:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I have already got plans for a significantly more sophisticated approach
>>> to this.
>
>> I'd like to move forwards on this capability in this release cycle. I
>> want to be able to tell whether a plan is a one-shot plan, or not.
>
>> If you've got something planned here, please say what it is or
>> implement directly, so we can avoid me being late on later patches
>> that depend upon this.
>
> Yes, I'm planning to do something about this for 9.2, hopefully before
> the next commitfest starts.

OK, I will work on the assumption that a "one shot plan" will be
visible in the output of the planner for 9.2.

> See prior discussions --- what I have in
> mind is to generate one-shot plans and test whether they're predicted to
> be significantly cheaper than a generic plan.  After a certain number of
> failures to be better than generic, we'd give up and just use the
> generic plan every time.  Another heuristic that might be worth thinking
> about is to not even bother with a generic plan until the N'th execution
> of a prepared statement, for some N that's small but more than 1.  We
> already have that behavior for certain cases associated with particular
> FE protocol usages, but not for plpgsql statements as an example.

One of the things I was looking at doing was allowing the operator
estimation functions mark the plan as "one-shot" if they used
non-uniform data to predict the estimate. That would require most
functions to observe the rule that if a plan is marked unsafe then
nobody marks it safe again later. More of a guideline, really.

For example, if we a doing a PK retrieval it will have a uniform
distribution and so we can always use the final plan, whereas a plan
that relates to a highly skewed distribution would be dangerous and so
would be marked one-shot.

This would almost eliminate the problem of parameters selected from a
skewed population or against a skewed distribution.

I'll leave that area to you if your looking to work there.

>>> I don't believe that's correct in detail.
>
>> If you can explain why you think this is wrong, I'm happy to remove
>> the line in evaluate_function() that says
>
> I'm concerned about which snapshot the function is executed against.

OK, I'll leave that for now and return to this thought later.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services