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

Commitbd3dadd

Browse files
committed
Arrange to convert EXISTS subqueries that are equivalent to hashable IN
subqueries into the same thing you'd have gotten from IN (except always withunknownEqFalse = true, so as to get the proper semantics for an EXISTS).I believe this fixes the last case within CVS HEAD in which an EXISTS couldgive worse performance than an equivalent IN subquery.The tricky part of this is that if the upper query probes the EXISTS for onlya few rows, the hashing implementation can actually be worse than the default,and therefore we need to make a cost-based decision about which way to use.But at the time when the planner generates plans for subqueries, it doesn'treally know how many times the subquery will be executed. The least invasivesolution seems to be to generate both plans and postpone the choice untilexecution. Therefore, in a query that has been optimized this way, EXPLAINwill show two subplans for the EXISTS, of which only one will actually getexecuted.There is a lot more that could be done based on this infrastructure: inparticular it's interesting to consider switching to the hash plan if we startout using the non-hashed plan but find a lot more upper rows going by than weexpected. I have therefore left some minor inefficiencies in place, such asinitializing both subplans even though we will currently only use one.
1 parent8875a16 commitbd3dadd

File tree

21 files changed

+854
-321
lines changed

21 files changed

+854
-321
lines changed

‎src/backend/catalog/dependency.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/dependency.c,v 1.78 2008/08/07 01:11:46 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/dependency.c,v 1.79 2008/08/22 00:16:03 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1464,7 +1464,7 @@ find_expr_references_walker(Node *node,
14641464
context->addrs);
14651465
/* fall through to examine arguments */
14661466
}
1467-
elseif (is_subplan(node))
1467+
elseif (IsA(node,SubPlan))
14681468
{
14691469
/* Extra work needed here if we ever need this case */
14701470
elog(ERROR,"already-planned subqueries not supported");

‎src/backend/executor/execQual.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.231 2008/05/15 00:17:39 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.232 2008/08/22 00:16:03 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3957,6 +3957,19 @@ ExecInitExpr(Expr *node, PlanState *parent)
39573957
state= (ExprState*)sstate;
39583958
}
39593959
break;
3960+
caseT_AlternativeSubPlan:
3961+
{
3962+
AlternativeSubPlan*asplan= (AlternativeSubPlan*)node;
3963+
AlternativeSubPlanState*asstate;
3964+
3965+
if (!parent)
3966+
elog(ERROR,"AlternativeSubPlan found with no parent plan");
3967+
3968+
asstate=ExecInitAlternativeSubPlan(asplan,parent);
3969+
3970+
state= (ExprState*)asstate;
3971+
}
3972+
break;
39603973
caseT_FieldSelect:
39613974
{
39623975
FieldSelect*fselect= (FieldSelect*)node;

‎src/backend/executor/nodeSubplan.c

Lines changed: 90 additions & 2 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/executor/nodeSubplan.c,v 1.93 2008/05/12 00:00:49 alvherre Exp $
10+
* $PostgreSQL: pgsql/src/backend/executor/nodeSubplan.c,v 1.94 2008/08/22 00:16:03 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -29,6 +29,14 @@
2929
#include"utils/memutils.h"
3030

3131

32+
staticDatumExecSubPlan(SubPlanState*node,
33+
ExprContext*econtext,
34+
bool*isNull,
35+
ExprDoneCond*isDone);
36+
staticDatumExecAlternativeSubPlan(AlternativeSubPlanState*node,
37+
ExprContext*econtext,
38+
bool*isNull,
39+
ExprDoneCond*isDone);
3240
staticDatumExecHashSubPlan(SubPlanState*node,
3341
ExprContext*econtext,
3442
bool*isNull);
@@ -45,7 +53,7 @@ static bool slotNoNulls(TupleTableSlot *slot);
4553
*ExecSubPlan
4654
* ----------------------------------------------------------------
4755
*/
48-
Datum
56+
staticDatum
4957
ExecSubPlan(SubPlanState*node,
5058
ExprContext*econtext,
5159
bool*isNull,
@@ -1066,3 +1074,83 @@ ExecReScanSetParamPlan(SubPlanState *node, PlanState *parent)
10661074
parent->chgParam=bms_add_member(parent->chgParam,paramid);
10671075
}
10681076
}
1077+
1078+
1079+
/*
1080+
* ExecInitAlternativeSubPlan
1081+
*
1082+
* Initialize for execution of one of a set of alternative subplans.
1083+
*/
1084+
AlternativeSubPlanState*
1085+
ExecInitAlternativeSubPlan(AlternativeSubPlan*asplan,PlanState*parent)
1086+
{
1087+
AlternativeSubPlanState*asstate=makeNode(AlternativeSubPlanState);
1088+
doublenum_calls;
1089+
SubPlan*subplan1;
1090+
SubPlan*subplan2;
1091+
Costcost1;
1092+
Costcost2;
1093+
1094+
asstate->xprstate.evalfunc= (ExprStateEvalFunc)ExecAlternativeSubPlan;
1095+
asstate->xprstate.expr= (Expr*)asplan;
1096+
1097+
/*
1098+
* Initialize subplans. (Can we get away with only initializing the
1099+
* one we're going to use?)
1100+
*/
1101+
asstate->subplans= (List*)ExecInitExpr((Expr*)asplan->subplans,
1102+
parent);
1103+
1104+
/*
1105+
* Select the one to be used. For this, we need an estimate of the
1106+
* number of executions of the subplan. We use the number of output
1107+
* rows expected from the parent plan node. This is a good estimate
1108+
* if we are in the parent's targetlist, and an underestimate (but
1109+
* probably not by more than a factor of 2) if we are in the qual.
1110+
*/
1111+
num_calls=parent->plan->plan_rows;
1112+
1113+
/*
1114+
* The planner saved enough info so that we don't have to work very hard
1115+
* to estimate the total cost, given the number-of-calls estimate.
1116+
*/
1117+
Assert(list_length(asplan->subplans)==2);
1118+
subplan1= (SubPlan*)linitial(asplan->subplans);
1119+
subplan2= (SubPlan*)lsecond(asplan->subplans);
1120+
1121+
cost1=subplan1->startup_cost+num_calls*subplan1->per_call_cost;
1122+
cost2=subplan2->startup_cost+num_calls*subplan2->per_call_cost;
1123+
1124+
if (cost1<cost2)
1125+
asstate->active=0;
1126+
else
1127+
asstate->active=1;
1128+
1129+
returnasstate;
1130+
}
1131+
1132+
/*
1133+
* ExecAlternativeSubPlan
1134+
*
1135+
* Execute one of a set of alternative subplans.
1136+
*
1137+
* Note: in future we might consider changing to different subplans on the
1138+
* fly, in case the original rowcount estimate turns out to be way off.
1139+
*/
1140+
staticDatum
1141+
ExecAlternativeSubPlan(AlternativeSubPlanState*node,
1142+
ExprContext*econtext,
1143+
bool*isNull,
1144+
ExprDoneCond*isDone)
1145+
{
1146+
/* Just pass control to the active subplan */
1147+
SubPlanState*activesp= (SubPlanState*)list_nth(node->subplans,
1148+
node->active);
1149+
1150+
Assert(IsA(activesp,SubPlanState));
1151+
1152+
returnExecSubPlan(activesp,
1153+
econtext,
1154+
isNull,
1155+
isDone);
1156+
}

‎src/backend/nodes/copyfuncs.c

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.400 2008/08/14 18:47:58 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.401 2008/08/22 00:16:03 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -969,6 +969,21 @@ _copySubPlan(SubPlan *from)
969969
COPY_NODE_FIELD(setParam);
970970
COPY_NODE_FIELD(parParam);
971971
COPY_NODE_FIELD(args);
972+
COPY_SCALAR_FIELD(startup_cost);
973+
COPY_SCALAR_FIELD(per_call_cost);
974+
975+
returnnewnode;
976+
}
977+
978+
/*
979+
* _copyAlternativeSubPlan
980+
*/
981+
staticAlternativeSubPlan*
982+
_copyAlternativeSubPlan(AlternativeSubPlan*from)
983+
{
984+
AlternativeSubPlan*newnode=makeNode(AlternativeSubPlan);
985+
986+
COPY_NODE_FIELD(subplans);
972987

973988
returnnewnode;
974989
}
@@ -3146,6 +3161,9 @@ copyObject(void *from)
31463161
caseT_SubPlan:
31473162
retval=_copySubPlan(from);
31483163
break;
3164+
caseT_AlternativeSubPlan:
3165+
retval=_copyAlternativeSubPlan(from);
3166+
break;
31493167
caseT_FieldSelect:
31503168
retval=_copyFieldSelect(from);
31513169
break;

‎src/backend/nodes/equalfuncs.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.327 2008/08/14 18:47:58 tgl Exp $
21+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.328 2008/08/22 00:16:03 tgl Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -314,6 +314,16 @@ _equalSubPlan(SubPlan *a, SubPlan *b)
314314
COMPARE_NODE_FIELD(setParam);
315315
COMPARE_NODE_FIELD(parParam);
316316
COMPARE_NODE_FIELD(args);
317+
COMPARE_SCALAR_FIELD(startup_cost);
318+
COMPARE_SCALAR_FIELD(per_call_cost);
319+
320+
return true;
321+
}
322+
323+
staticbool
324+
_equalAlternativeSubPlan(AlternativeSubPlan*a,AlternativeSubPlan*b)
325+
{
326+
COMPARE_NODE_FIELD(subplans);
317327

318328
return true;
319329
}
@@ -2098,6 +2108,9 @@ equal(void *a, void *b)
20982108
caseT_SubPlan:
20992109
retval=_equalSubPlan(a,b);
21002110
break;
2111+
caseT_AlternativeSubPlan:
2112+
retval=_equalAlternativeSubPlan(a,b);
2113+
break;
21012114
caseT_FieldSelect:
21022115
retval=_equalFieldSelect(a,b);
21032116
break;

‎src/backend/nodes/outfuncs.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.334 2008/08/14 18:47:58 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.335 2008/08/22 00:16:03 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -846,6 +846,16 @@ _outSubPlan(StringInfo str, SubPlan *node)
846846
WRITE_NODE_FIELD(setParam);
847847
WRITE_NODE_FIELD(parParam);
848848
WRITE_NODE_FIELD(args);
849+
WRITE_FLOAT_FIELD(startup_cost,"%.2f");
850+
WRITE_FLOAT_FIELD(per_call_cost,"%.2f");
851+
}
852+
853+
staticvoid
854+
_outAlternativeSubPlan(StringInfostr,AlternativeSubPlan*node)
855+
{
856+
WRITE_NODE_TYPE("ALTERNATIVESUBPLAN");
857+
858+
WRITE_NODE_FIELD(subplans);
849859
}
850860

851861
staticvoid
@@ -2208,6 +2218,9 @@ _outNode(StringInfo str, void *obj)
22082218
caseT_SubPlan:
22092219
_outSubPlan(str,obj);
22102220
break;
2221+
caseT_AlternativeSubPlan:
2222+
_outAlternativeSubPlan(str,obj);
2223+
break;
22112224
caseT_FieldSelect:
22122225
_outFieldSelect(str,obj);
22132226
break;

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/clausesel.c,v 1.92 2008/08/16 00:01:36 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/clausesel.c,v 1.93 2008/08/22 00:16:03 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -680,7 +680,8 @@ clause_selectivity(PlannerInfo *root,
680680
s1= (Selectivity)0.3333333;
681681
}
682682
#ifdefNOT_USED
683-
elseif (is_subplan(clause))
683+
elseif (IsA(clause,SubPlan)||
684+
IsA(clause,AlternativeSubPlan))
684685
{
685686
/*
686687
* Just for the moment! FIX ME! - vadim 02/04/98

0 commit comments

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