Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit005f583

Browse files
committed
Account better for planning cost when choosing whether to use custom plans.
The previous coding in plancache.c essentially used 10% of the estimatedruntime as its cost estimate for planning. This can be pretty bogus,especially when the estimated runtime is very small, such as in a simpleexpression plan created by plpgsql, or a simple INSERT ... VALUES.While we don't have a really good handle on how planning time comparesto runtime, it seems reasonable to use an estimate based on the number ofrelations referenced in the query, with a rather large multiplier. Thispatch uses 1000 * cpu_operator_cost * (nrelations + 1), so that even atrivial query will be charged 1000 * cpu_operator_cost for planning.This should address the problem reported by Marc Cousin and others that9.2 and up prefer custom plans in cases where the planning time greatlyexceeds what can be saved.
1 parent3979ff1 commit005f583

File tree

1 file changed

+43
-9
lines changed

1 file changed

+43
-9
lines changed

‎src/backend/utils/cache/plancache.c‎

Lines changed: 43 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,7 @@
5252
#include"executor/executor.h"
5353
#include"executor/spi.h"
5454
#include"nodes/nodeFuncs.h"
55+
#include"optimizer/cost.h"
5556
#include"optimizer/planmain.h"
5657
#include"optimizer/prep.h"
5758
#include"parser/analyze.h"
@@ -88,7 +89,7 @@ static CachedPlan *BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
8889
ParamListInfoboundParams);
8990
staticboolchoose_custom_plan(CachedPlanSource*plansource,
9091
ParamListInfoboundParams);
91-
staticdoublecached_plan_cost(CachedPlan*plan);
92+
staticdoublecached_plan_cost(CachedPlan*plan,boolinclude_planner);
9293
staticvoidAcquireExecutorLocks(List*stmt_list,boolacquire);
9394
staticvoidAcquirePlannerLocks(List*stmt_list,boolacquire);
9495
staticvoidScanQueryForLocks(Query*parsetree,boolacquire);
@@ -998,25 +999,30 @@ choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
998999
avg_custom_cost=plansource->total_custom_cost /plansource->num_custom_plans;
9991000

10001001
/*
1001-
* Prefer generic plan if it's less than 10% more expensive than average
1002-
* custom plan. This threshold is a bit arbitrary; it'd be better if we
1003-
* had some means of comparing planning time to the estimated runtime cost
1004-
* differential.
1002+
* Prefer generic plan if it's less expensive than the average custom
1003+
* plan. (Because we include a charge for cost of planning in the
1004+
* custom-plan costs, this means the generic plan only has to be less
1005+
* expensive than the execution cost plus replan cost of the custom
1006+
* plans.)
10051007
*
10061008
* Note that if generic_cost is -1 (indicating we've not yet determined
10071009
* the generic plan cost), we'll always prefer generic at this point.
10081010
*/
1009-
if (plansource->generic_cost<avg_custom_cost*1.1)
1011+
if (plansource->generic_cost<avg_custom_cost)
10101012
return false;
10111013

10121014
return true;
10131015
}
10141016

10151017
/*
10161018
* cached_plan_cost: calculate estimated cost of a plan
1019+
*
1020+
* If include_planner is true, also include the estimated cost of constructing
1021+
* the plan. (We must factor that into the cost of using a custom plan, but
1022+
* we don't count it for a generic plan.)
10171023
*/
10181024
staticdouble
1019-
cached_plan_cost(CachedPlan*plan)
1025+
cached_plan_cost(CachedPlan*plan,boolinclude_planner)
10201026
{
10211027
doubleresult=0;
10221028
ListCell*lc;
@@ -1029,6 +1035,34 @@ cached_plan_cost(CachedPlan *plan)
10291035
continue;/* Ignore utility statements */
10301036

10311037
result+=plannedstmt->planTree->total_cost;
1038+
1039+
if (include_planner)
1040+
{
1041+
/*
1042+
* Currently we use a very crude estimate of planning effort based
1043+
* on the number of relations in the finished plan's rangetable.
1044+
* Join planning effort actually scales much worse than linearly
1045+
* in the number of relations --- but only until the join collapse
1046+
* limits kick in.Also, while inheritance child relations surely
1047+
* add to planning effort, they don't make the join situation
1048+
* worse. So the actual shape of the planning cost curve versus
1049+
* number of relations isn't all that obvious. It will take
1050+
* considerable work to arrive at a less crude estimate, and for
1051+
* now it's not clear that's worth doing.
1052+
*
1053+
* The other big difficulty here is that we don't have any very
1054+
* good model of how planning cost compares to execution costs.
1055+
* The current multiplier of 1000 * cpu_operator_cost is probably
1056+
* on the low side, but we'll try this for awhile before making a
1057+
* more aggressive correction.
1058+
*
1059+
* If we ever do write a more complicated estimator, it should
1060+
* probably live in src/backend/optimizer/ not here.
1061+
*/
1062+
intnrelations=list_length(plannedstmt->rtable);
1063+
1064+
result+=1000.0*cpu_operator_cost* (nrelations+1);
1065+
}
10321066
}
10331067

10341068
returnresult;
@@ -1104,7 +1138,7 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
11041138
MemoryContextGetParent(plansource->context));
11051139
}
11061140
/* Update generic_cost whenever we make a new generic plan */
1107-
plansource->generic_cost=cached_plan_cost(plan);
1141+
plansource->generic_cost=cached_plan_cost(plan, false);
11081142

11091143
/*
11101144
* If, based on the now-known value of generic_cost, we'd not have
@@ -1133,7 +1167,7 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
11331167
/* Accumulate total costs of custom plans, but 'ware overflow */
11341168
if (plansource->num_custom_plans<INT_MAX)
11351169
{
1136-
plansource->total_custom_cost+=cached_plan_cost(plan);
1170+
plansource->total_custom_cost+=cached_plan_cost(plan, true);
11371171
plansource->num_custom_plans++;
11381172
}
11391173
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp