## Re: One-Shot Plans

From: Simon Riggs Tom Lane PostgreSQL-development Re: One-Shot Plans 2011-08-01 17:56:11 CA+U5nM+bFBz73z-9C6k8+KSew4o4CDkZWfmF+0pLOBvCCZq8pQ@mail.gmail.com (view raw or flat) 2011-06-14 18:25:13 from Simon Riggs  2011-06-14 18:28:03 from Bruce Momjian   2011-06-14 18:36:23 from Simon Riggs    2011-06-14 18:38:14 from Bruce Momjian  2011-06-14 20:36:06 from Tom Lane   2011-08-01 09:24:43 from Simon Riggs    2011-08-01 15:55:00 from Tom Lane     2011-08-01 17:08:23 from Simon Riggs      2011-08-01 17:39:32 from Tom Lane       2011-08-01 17:56:11 from Simon Riggs  2011-06-21 06:51:11 from Jaime Casanova pgsql-hackers
```On Mon, Aug 1, 2011 at 6:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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

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.

--