One-Shot Plans

Currently, the planner and executor are mostly independent of each
other: the planner doesn't really know when the plan will be executed,
and the executor doesn't know how recently the plan was made.

We can work out the various paths through the traffic cop to see when
a plan will be a "one-shot" - planned and then executed immediately,
then discarded.

In those cases we can take advantage of better optimisations. Most
interestingly, we can evaluate stable functions at plan time, to allow
us to handle partitioning and partial indexes better.

Patch attached. Works...

SET constraint_exclusion = on;
ALTER TABLE <table> ADD CHECK (dt < current_date - 5);
SELECT * FROM <table> WHERE datecolumn >= current_date - 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(2 rows)

WIP in the sense that we might want to change the special case
parameter handling as well.

Comments?

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

Simon Riggs wrote:
> Currently, the planner and executor are mostly independent of each
> other: the planner doesn't really know when the plan will be executed,
> and the executor doesn't know how recently the plan was made.
>
> We can work out the various paths through the traffic cop to see when
> a plan will be a "one-shot" - planned and then executed immediately,
> then discarded.

I was also hoping someday allow plans that are to be immediately
executed to probe the buffer cache to determine how expensive index
scans would be.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Tue, Jun 14, 2011 at 7:28 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Simon Riggs wrote:
>> Currently, the planner and executor are mostly independent of each
>> other: the planner doesn't really know when the plan will be executed,
>> and the executor doesn't know how recently the plan was made.
>>
>> We can work out the various paths through the traffic cop to see when
>> a plan will be a "one-shot" - planned and then executed immediately,
>> then discarded.
>
> I was also hoping someday allow plans that are to be immediately
> executed to probe the buffer cache to determine how expensive index
> scans would be.

Yes, it opens up many optimizations, both for cache sensitivity and
dynamic data access.

But those are later ideas based on the existence of this first step.

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

Simon Riggs wrote:
> On Tue, Jun 14, 2011 at 7:28 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Simon Riggs wrote:
> >> Currently, the planner and executor are mostly independent of each
> >> other: the planner doesn't really know when the plan will be executed,
> >> and the executor doesn't know how recently the plan was made.
> >>
> >> We can work out the various paths through the traffic cop to see when
> >> a plan will be a "one-shot" - planned and then executed immediately,
> >> then discarded.
> >
> > I was also hoping someday allow plans that are to be immediately
> > executed to probe the buffer cache to determine how expensive index
> > scans would be.
>
> Yes, it opens up many optimizations, both for cache sensitivity and
> dynamic data access.
>
> But those are later ideas based on the existence of this first step.

Agreed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> Currently, the planner and executor are mostly independent of each
> other: the planner doesn't really know when the plan will be executed,
> and the executor doesn't know how recently the plan was made.

> We can work out the various paths through the traffic cop to see when
> a plan will be a "one-shot" - planned and then executed immediately,
> then discarded.

I have already got plans for a significantly more sophisticated approach
to this.

> In those cases we can take advantage of better optimisations. Most
> interestingly, we can evaluate stable functions at plan time, to allow
> us to handle partitioning and partial indexes better.

I don't believe that's correct in detail.

regards, tom lane

On Tue, Jun 14, 2011 at 1:25 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> We can work out the various paths through the traffic cop to see when
> a plan will be a "one-shot" - planned and then executed immediately,
> then discarded.
>
> In those cases we can take advantage of better optimisations. Most
> interestingly, we can evaluate stable functions at plan time, to allow
> us to handle partitioning and partial indexes better.
>
> Patch attached. Works...
>

this breaks test guc.c for me... specifically the test at
src/test/regress/sql/guc.sql circa line 226:
"""
set work_mem = '3MB';

-- but SET isn't
create or replace function myfunc(int) returns text as $$
begin
set work_mem = '2MB';
return current_setting('work_mem');
end $$
language plpgsql
set work_mem = '1MB';

select myfunc(0), current_setting('work_mem');
"""

regressions.diff
"""
*** 656,662 ****
select myfunc(0), current_setting('work_mem');
myfunc | current_setting
--------+-----------------
! 2MB | 2MB
(1 row)

set work_mem = '3MB';
--- 656,662 ----
select myfunc(0), current_setting('work_mem');
myfunc | current_setting
--------+-----------------
! 2MB | 3MB
(1 row)

set work_mem = '3MB';
"""

it seems that the effect of SET is being discarded

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

On Tue, Jun 14, 2011 at 9:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> Currently, the planner and executor are mostly independent of each
>> other: the planner doesn't really know when the plan will be executed,
>> and the executor doesn't know how recently the plan was made.
>
>> We can work out the various paths through the traffic cop to see when
>> a plan will be a "one-shot" - planned and then executed immediately,
>> then discarded.
>
> I have already got plans for a significantly more sophisticated approach
> to this.

Hi Tom,

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.

>> In those cases we can take advantage of better optimisations. Most
>> interestingly, we can evaluate stable functions at plan time, to allow
>> us to handle partitioning and partial indexes better.
>
> 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

if (funcform->provolatile == PROVOLATILE_STABLE && (context->estimate
|| context->oneshot))

then we're OK to evaluate the function immediately.

Thanks

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

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. 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.

>> 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.

regards, tom lane

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

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.

regards, tom lane

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