Re: One-Shot Plans

On Mon, Aug 1, 2011 at 6:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> 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.
>
> I fail to detect the sanity in that.  You seem to be confusing "skewed"
> with "changing rapidly".  There's no reason to assume that a nonuniform
> distribution is less stable than one that is uniform, and in any case we
> already invalidate all plans related to a table after any update of the
> statistics by ANALYZE.

Slightly missing each other, I feel.

SELECT * FROM bigtable WHERE skewcol = :param1

could have selectivity anywhere from 1.0 to 0.000000000000001 or
lower, though you don't know until you see the parameter.

Deciding the plan on the basis of a default value will frequently give
a bad plan.

What I would like to give people is "plan stability" without the need
to freeze plans or use hints. I would like us to recognise when the
selectivity result is potentially skewed and to avoid over-reliance on
such plans. If we address the cause of plan instability we need not
supply mechanisms higher up to cope with this.

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