Skip site navigation (1) Skip section navigation (2)

Re: One-Shot Plans

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: One-Shot Plans
Date: 2011-08-01 17:56:11
Message-ID: CA+U5nM+bFBz73z-9C6k8+KSew4o4CDkZWfmF+0pLOBvCCZq8pQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2011-08-01 18:02:49
Subject: Re: WIP fix proposal for bug #6123
Previous:From: Tom LaneDate: 2011-08-01 17:55:21
Subject: Re: Compressing the AFTER TRIGGER queue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group