Re: Transient plans versus the SPI API

On Fri, Aug 19, 2011 at 6:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> [ getting back to the planner finally ]
>
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>>>> I think its possible to tell automatically whether we need to replan
>>>> always or not based upon the path we take through selectivity
>>>> functions.
>
>>> I don't really believe that, or at least I think it would only detect a
>>> few cases.
>
>> The problem there is which executions we build custom plans for. That
>> turns the problem into a sampling issue and you'll only fix the
>> problems that occur with a frequency to match your sampling pattern
>> and rate. Examples of situations where it won't help.
>
> Sure, this is not going to solve every problem we have with the
> planner.  What it is intended to solve is cases where someone is trying
> to use the prepared-plan mechanisms but he would be a lot better off
> with parameter-value-specific plans.  In particular:

I just realised this is exactly the same strategy as the
no-longer-used JDBC parameter prepareThreshold.

If we treat this in a similar way. prepare_threshold currently = 0 and
you are suggesting we move the value to 5. OK.

Will this be an actual parameter? If so, it removes my objection
because I can turn it off. What would be even better would be some
other controls, like a plugin that allows us to control the mechanism
or at least experiment with it.

Maybe we can assemble enough evidence to remove it before release.

I've been arguing it won't solve all problems. It won't. But if it
solves some, so its worth having.

On another point, I'd still like a "one-shot plan" flag, so that we
can act on that knowledge and have various pieces of code take
decisions that override the plan cache. i.e. if the plan screws up
during execution we can mark the plan as a one shot so it isn't
reused.

>> * plans that vary by table size will be about the same in the first 5
>> executions. After large number of executions, things go bad.
>
> This is a red herring.  The plancache code already arranges to replan
> every time the relevant table stats are updated by autovacuum, which
> should certainly happen from time to time if the table's contents are
> changing materially.  If you're thinking in terms of plans being "stale"
> then you're worrying about a long-since-solved problem.

Fair enough.

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