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

Commit7125687

Browse files
committed
Fix cost estimates for EXISTS subqueries that are evaluated as initPlans
(because they are uncorrelated with the immediate parent query). We werecharging the full run cost to the parent node, disregarding the fact thatonly one row need be fetched for EXISTS. While this would only be acosmetic issue in most cases, it might possibly affect planning outcomesif the parent query were itself a subquery to some upper query.Per recent discussion with Steve Crawford.
1 parent576b890 commit7125687

File tree

3 files changed

+49
-9
lines changed

3 files changed

+49
-9
lines changed

‎src/backend/optimizer/path/costsize.c

Lines changed: 42 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -54,7 +54,7 @@
5454
* Portions Copyright (c) 1994, Regents of the University of California
5555
*
5656
* IDENTIFICATION
57-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.185 2007/06/11 01:16:22 tgl Exp $
57+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.186 2007/09/22 21:36:40 tgl Exp $
5858
*
5959
*-------------------------------------------------------------------------
6060
*/
@@ -2049,9 +2049,10 @@ cost_qual_eval_walker(Node *node, cost_qual_eval_context *context)
20492049
{
20502050
/*
20512051
* Otherwise we will be rescanning the subplan output on each
2052-
* evaluation.We need to estimate how much of the output we will
2053-
* actually need to scan. NOTE: this logic should agree with the
2054-
* estimates used by make_subplan() in plan/subselect.c.
2052+
* evaluation. We need to estimate how much of the output we will
2053+
* actually need to scan. NOTE: this logic should agree with
2054+
* get_initplan_cost, below, and with the estimates used by
2055+
* make_subplan() in plan/subselect.c.
20552056
*/
20562057
Costplan_run_cost=plan->total_cost-plan->startup_cost;
20572058

@@ -2097,6 +2098,43 @@ cost_qual_eval_walker(Node *node, cost_qual_eval_context *context)
20972098
}
20982099

20992100

2101+
/*
2102+
* get_initplan_cost
2103+
*Get the expected cost of evaluating an initPlan.
2104+
*
2105+
* Keep this in sync with cost_qual_eval_walker's handling of subplans, above,
2106+
* and with the estimates used by make_subplan() in plan/subselect.c.
2107+
*/
2108+
Cost
2109+
get_initplan_cost(PlannerInfo*root,SubPlan*subplan)
2110+
{
2111+
Costresult;
2112+
Plan*plan=planner_subplan_get_plan(root,subplan);
2113+
2114+
/* initPlans never use hashtables */
2115+
Assert(!subplan->useHashTable);
2116+
/* they are never ALL or ANY, either */
2117+
Assert(!(subplan->subLinkType==ALL_SUBLINK||
2118+
subplan->subLinkType==ANY_SUBLINK));
2119+
2120+
if (subplan->subLinkType==EXISTS_SUBLINK)
2121+
{
2122+
/* we only need to fetch 1 tuple */
2123+
Costplan_run_cost=plan->total_cost-plan->startup_cost;
2124+
2125+
result=plan->startup_cost;
2126+
result+=plan_run_cost /plan->plan_rows;
2127+
}
2128+
else
2129+
{
2130+
/* assume we need all tuples */
2131+
result=plan->total_cost;
2132+
}
2133+
2134+
returnresult;
2135+
}
2136+
2137+
21002138
/*
21012139
* approx_selectivity
21022140
*Quick-and-dirty estimation of clause selectivities.

‎src/backend/optimizer/plan/subselect.c

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/subselect.c,v 1.124 2007/08/26 21:44:25 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/subselect.c,v 1.125 2007/09/22 21:36:40 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -18,6 +18,7 @@
1818
#include"miscadmin.h"
1919
#include"nodes/makefuncs.h"
2020
#include"optimizer/clauses.h"
21+
#include"optimizer/cost.h"
2122
#include"optimizer/planmain.h"
2223
#include"optimizer/planner.h"
2324
#include"optimizer/subselect.h"
@@ -266,7 +267,7 @@ make_subplan(PlannerInfo *root, SubLink *slink, Node *testexpr, bool isTopQual)
266267
* (we're only expecting one row out, anyway).
267268
*
268269
* NOTE: if you change these numbers, also change cost_qual_eval_walker()
269-
* in path/costsize.c.
270+
*and get_initplan_cost()in path/costsize.c.
270271
*
271272
* XXX If an ALL/ANY subplan is uncorrelated, we may decide to hash or
272273
* materialize its result below. In that case it would've been better to
@@ -1021,7 +1022,7 @@ SS_finalize_plan(PlannerInfo *root, Plan *plan)
10211022
* have extParams that are setParams of other initPlans, so we have to
10221023
* take care of this situation explicitly.)
10231024
*
1024-
* We also add thetotal_cost of each initPlan to the startup cost of the
1025+
* We also add theeval cost of each initPlan to the startup cost of the
10251026
* top node. This is a conservative overestimate, since in fact each
10261027
* initPlan might be executed later than plan startup, or even not at all.
10271028
*/
@@ -1041,7 +1042,7 @@ SS_finalize_plan(PlannerInfo *root, Plan *plan)
10411042
{
10421043
initSetParam=bms_add_member(initSetParam,lfirst_int(l2));
10431044
}
1044-
initplan_cost+=initplan->total_cost;
1045+
initplan_cost+=get_initplan_cost(root,initsubplan);
10451046
}
10461047
/* allParam must include all these params */
10471048
plan->allParam=bms_add_members(plan->allParam,initExtParam);

‎src/include/optimizer/cost.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/cost.h,v 1.88 2007/07/25 12:22:53 mha Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/cost.h,v 1.89 2007/09/22 21:36:40 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -92,6 +92,7 @@ extern void cost_mergejoin(MergePath *path, PlannerInfo *root);
9292
externvoidcost_hashjoin(HashPath*path,PlannerInfo*root);
9393
externvoidcost_qual_eval(QualCost*cost,List*quals,PlannerInfo*root);
9494
externvoidcost_qual_eval_node(QualCost*cost,Node*qual,PlannerInfo*root);
95+
externCostget_initplan_cost(PlannerInfo*root,SubPlan*subplan);
9596
externvoidset_baserel_size_estimates(PlannerInfo*root,RelOptInfo*rel);
9697
externvoidset_joinrel_size_estimates(PlannerInfo*root,RelOptInfo*rel,
9798
RelOptInfo*outer_rel,

0 commit comments

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