Re: [HACKERS] One-Shot Plans
On Tue, Jun 14, 2011 at 9:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
Simon Riggs si...@2ndquadrant.com writes: On Tue, Jun 14, 2011 at 9:36 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
On Mon, Aug 1, 2011 at 4:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Tue, Jun 14, 2011 at 9:36 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
Simon Riggs si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
On Mon, Aug 1, 2011 at 6:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.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.001 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
On Tue, Jun 14, 2011 at 1:25 PM, Simon Riggs si...@2ndquadrant.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 oneshot_plans.v2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
On Tue, Jun 14, 2011 at 7:28 PM, Bruce Momjian br...@momjian.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
Simon Riggs wrote: On Tue, Jun 14, 2011 at 7:28 PM, Bruce Momjian br...@momjian.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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One-Shot Plans
Simon Riggs si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
