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

Commit336a649

Browse files
committed
Improve my initial, rather hacky implementation of joins to append
relations: fix the executor so that we can have an Append plan on theinside of a nestloop and still pass down outer index keys to index scanswithin the Append, then generate such plans as if they were regularinner indexscans. This avoids the need to evaluate the outer relationmultiple times.
1 parent354213c commit336a649

File tree

8 files changed

+114
-273
lines changed

8 files changed

+114
-273
lines changed

‎src/backend/commands/explain.c

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994-5, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.142 2005/11/29 01:25:49 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.143 2006/02/05 02:59:16 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -904,9 +904,15 @@ explain_outNode(StringInfo str,
904904
appendStringInfo(str," ");
905905
appendStringInfo(str," -> ");
906906

907+
/*
908+
* Ordinarily we don't pass down our own outer_plan value to our
909+
* child nodes, but in an Append we must, since we might be
910+
* looking at an appendrel indexscan with outer references
911+
* from the member scans.
912+
*/
907913
explain_outNode(str,subnode,
908914
appendstate->appendplans[j],
909-
NULL,
915+
outer_plan,
910916
indent+3,es);
911917
j++;
912918
}

‎src/backend/executor/nodeAppend.c

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/nodeAppend.c,v 1.65 2005/10/15 02:49:17 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/nodeAppend.c,v 1.66 2006/02/05 02:59:16 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -345,10 +345,12 @@ ExecReScanAppend(AppendState *node, ExprContext *exprCtxt)
345345
UpdateChangedParamSet(subnode,node->ps.chgParam);
346346

347347
/*
348-
* if chgParam of subnode is not null then plan will be re-scanned by
349-
* first ExecProcNode.
348+
* If chgParam of subnode is not null then plan will be re-scanned by
349+
* first ExecProcNode. However, if caller is passing us an exprCtxt
350+
* then forcibly rescan all the subnodes now, so that we can pass
351+
* the exprCtxt down to the subnodes (needed for appendrel indexscan).
350352
*/
351-
if (subnode->chgParam==NULL)
353+
if (subnode->chgParam==NULL||exprCtxt!=NULL)
352354
{
353355
/* make sure estate is correct for this subnode (needed??) */
354356
node->as_whichplan=i;

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

Lines changed: 34 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@
4949
* Portions Copyright (c) 1994, Regents of the University of California
5050
*
5151
* IDENTIFICATION
52-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.152 2005/12/28 01:29:59 tgl Exp $
52+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.153 2006/02/05 02:59:16 tgl Exp $
5353
*
5454
*-------------------------------------------------------------------------
5555
*/
@@ -991,6 +991,38 @@ cost_group(Path *path, PlannerInfo *root,
991991
path->total_cost=total_cost;
992992
}
993993

994+
/*
995+
* If a nestloop's inner path is an indexscan, be sure to use its estimated
996+
* output row count, which may be lower than the restriction-clause-only row
997+
* count of its parent. (We don't include this case in the PATH_ROWS macro
998+
* because it applies *only* to a nestloop's inner relation.) We have to
999+
* be prepared to recurse through Append nodes in case of an appendrel.
1000+
*/
1001+
staticdouble
1002+
nestloop_inner_path_rows(Path*path)
1003+
{
1004+
doubleresult;
1005+
1006+
if (IsA(path,IndexPath))
1007+
result= ((IndexPath*)path)->rows;
1008+
elseif (IsA(path,BitmapHeapPath))
1009+
result= ((BitmapHeapPath*)path)->rows;
1010+
elseif (IsA(path,AppendPath))
1011+
{
1012+
ListCell*l;
1013+
1014+
result=0;
1015+
foreach(l, ((AppendPath*)path)->subpaths)
1016+
{
1017+
result+=nestloop_inner_path_rows((Path*)lfirst(l));
1018+
}
1019+
}
1020+
else
1021+
result=PATH_ROWS(path);
1022+
1023+
returnresult;
1024+
}
1025+
9941026
/*
9951027
* cost_nestloop
9961028
* Determines and returns the cost of joining two relations using the
@@ -1008,21 +1040,10 @@ cost_nestloop(NestPath *path, PlannerInfo *root)
10081040
Costcpu_per_tuple;
10091041
QualCostrestrict_qual_cost;
10101042
doubleouter_path_rows=PATH_ROWS(outer_path);
1011-
doubleinner_path_rows=PATH_ROWS(inner_path);
1043+
doubleinner_path_rows=nestloop_inner_path_rows(inner_path);
10121044
doublentuples;
10131045
Selectivityjoininfactor;
10141046

1015-
/*
1016-
* If inner path is an indexscan, be sure to use its estimated output row
1017-
* count, which may be lower than the restriction-clause-only row count of
1018-
* its parent.(We don't include this case in the PATH_ROWS macro because
1019-
* it applies *only* to a nestloop's inner relation.)
1020-
*/
1021-
if (IsA(inner_path,IndexPath))
1022-
inner_path_rows= ((IndexPath*)inner_path)->rows;
1023-
elseif (IsA(inner_path,BitmapHeapPath))
1024-
inner_path_rows= ((BitmapHeapPath*)inner_path)->rows;
1025-
10261047
if (!enable_nestloop)
10271048
startup_cost+=disable_cost;
10281049

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

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.200 2006/01/29 17:40:00 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.201 2006/02/05 02:59:16 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -129,7 +129,7 @@ static Const *string_to_const(const char *str, Oid datatype);
129129
*
130130
* 'rel' is the relation for which we want to generate index paths
131131
*
132-
* Note: check_partial_indexes() must have been run previously.
132+
* Note: check_partial_indexes() must have been run previously for this rel.
133133
*/
134134
void
135135
create_index_paths(PlannerInfo*root,RelOptInfo*rel)
@@ -1290,6 +1290,9 @@ matches_any_index(RestrictInfo *rinfo, RelOptInfo *rel, Relids outer_relids)
12901290
* negligible startup cost. (True today, but someday we might have to think
12911291
* harder.) Therefore, there is only one dimension of comparison and so it's
12921292
* sufficient to return a single "best" path.
1293+
*
1294+
* Note: create_index_paths() must have been run previously for this rel,
1295+
* else the result will always be NULL.
12931296
*/
12941297
Path*
12951298
best_inner_indexscan(PlannerInfo*root,RelOptInfo*rel,

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

Lines changed: 39 additions & 98 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.101 2006/02/04 23:03:20 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.102 2006/02/05 02:59:16 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -35,9 +35,8 @@ static void match_unsorted_outer(PlannerInfo *root, RelOptInfo *joinrel,
3535
staticvoidhash_inner_and_outer(PlannerInfo*root,RelOptInfo*joinrel,
3636
RelOptInfo*outerrel,RelOptInfo*innerrel,
3737
List*restrictlist,JoinTypejointype);
38-
staticvoidjoin_before_append(PlannerInfo*root,RelOptInfo*joinrel,
39-
RelOptInfo*outerrel,RelOptInfo*innerrel,
40-
JoinTypejointype);
38+
staticPath*best_appendrel_indexscan(PlannerInfo*root,RelOptInfo*rel,
39+
Relidsouter_relids,JoinTypejointype);
4140
staticList*select_mergejoin_clauses(RelOptInfo*joinrel,
4241
RelOptInfo*outerrel,
4342
RelOptInfo*innerrel,
@@ -118,13 +117,6 @@ add_paths_to_joinrel(PlannerInfo *root,
118117
if (enable_hashjoin)
119118
hash_inner_and_outer(root,joinrel,outerrel,innerrel,
120119
restrictlist,jointype);
121-
122-
/*
123-
* 5. If the inner relation is an append relation, consider joining
124-
* the outer rel to each append member and then appending the results.
125-
*/
126-
if (innerrel->cheapest_total_path->pathtype==T_Append)
127-
join_before_append(root,joinrel,outerrel,innerrel,jointype);
128120
}
129121

130122
/*
@@ -405,8 +397,17 @@ match_unsorted_outer(PlannerInfo *root,
405397
* Get the best innerjoin indexpath (if any) for this outer rel. It's
406398
* the same for all outer paths.
407399
*/
408-
bestinnerjoin=best_inner_indexscan(root,innerrel,
409-
outerrel->relids,jointype);
400+
if (innerrel->reloptkind!=RELOPT_JOINREL)
401+
{
402+
if (IsA(inner_cheapest_total,AppendPath))
403+
bestinnerjoin=best_appendrel_indexscan(root,innerrel,
404+
outerrel->relids,
405+
jointype);
406+
elseif (innerrel->rtekind==RTE_RELATION)
407+
bestinnerjoin=best_inner_indexscan(root,innerrel,
408+
outerrel->relids,
409+
jointype);
410+
}
410411
}
411412

412413
foreach(l,outerrel->pathlist)
@@ -788,75 +789,27 @@ hash_inner_and_outer(PlannerInfo *root,
788789
}
789790

790791
/*
791-
* join_before_append
792-
* Creates possible join paths for processing a single join relation
793-
* 'joinrel' when the inner input is an append relation.
794-
*
795-
* The idea here is to swap the order of the APPEND and JOIN operators.
796-
* This is only really helpful if it allows us to reduce the cost of
797-
* scanning the members of the append relation, and so we only consider
798-
* plans involving nestloops with inner indexscans. Also, since the APPEND
799-
* will certainly yield an unsorted result, there's no point in considering
800-
* any but the cheapest-total outer path.
801-
*
802-
* XXX this is a bit of a kluge, because the resulting plan has to evaluate
803-
* the outer relation multiple times. Would be better to allow
804-
* best_inner_indexscan to generate an AppendPath and not have this routine
805-
* at all. But we can't do that without some executor changes (need a way
806-
* to pass outer keys down through Append). FIXME later.
807-
*
808-
* 'joinrel' is the join relation
809-
* 'outerrel' is the outer join relation
810-
* 'innerrel' is the inner join relation
811-
* 'jointype' is the type of join to do
792+
* best_appendrel_indexscan
793+
* Finds the best available set of inner indexscans for a nestloop join
794+
* with the given append relation on the inside and the given outer_relids
795+
* outside. Returns an AppendPath comprising the best inner scans, or
796+
* NULL if there are no possible inner indexscans.
812797
*/
813-
staticvoid
814-
join_before_append(PlannerInfo*root,
815-
RelOptInfo*joinrel,
816-
RelOptInfo*outerrel,
817-
RelOptInfo*innerrel,
818-
JoinTypejointype)
798+
staticPath*
799+
best_appendrel_indexscan(PlannerInfo*root,RelOptInfo*rel,
800+
Relidsouter_relids,JoinTypejointype)
819801
{
820-
Path*outer_cheapest_total=outerrel->cheapest_total_path;
821-
intparentRTindex=innerrel->relid;
802+
intparentRTindex=rel->relid;
822803
List*append_paths=NIL;
804+
boolfound_indexscan= false;
823805
ListCell*l;
824806

825-
/*
826-
* Swapping JOIN with APPEND only works for inner joins, not outer joins.
827-
* However, we can also handle a unique-ified outer path.
828-
*/
829-
switch (jointype)
830-
{
831-
caseJOIN_INNER:
832-
break;
833-
caseJOIN_UNIQUE_OUTER:
834-
outer_cheapest_total= (Path*)
835-
create_unique_path(root,outerrel,outer_cheapest_total);
836-
break;
837-
caseJOIN_LEFT:
838-
caseJOIN_RIGHT:
839-
caseJOIN_FULL:
840-
caseJOIN_IN:
841-
caseJOIN_UNIQUE_INNER:
842-
return;/* can't join this way */
843-
default:
844-
elog(ERROR,"unrecognized join type: %d",
845-
(int)jointype);
846-
break;
847-
}
848-
849-
/*
850-
* Generate suitable access paths for each member relation.
851-
*/
852807
foreach(l,root->append_rel_list)
853808
{
854809
AppendRelInfo*appinfo= (AppendRelInfo*)lfirst(l);
855810
intchildRTindex;
856811
RelOptInfo*childrel;
857812
Path*bestinnerjoin;
858-
RelOptInfo*this_joinrel;
859-
List*this_restrictlist;
860813

861814
/* append_rel_list contains all append rels; ignore others */
862815
if (appinfo->parent_relid!=parentRTindex)
@@ -876,42 +829,30 @@ join_before_append(PlannerInfo *root,
876829
continue;/* OK, we can ignore it */
877830

878831
/*
879-
* Get the best innerjoin indexpath (if any) for thisouter rel.
832+
* Get the best innerjoin indexpath (if any) for thischild rel.
880833
*/
881834
bestinnerjoin=best_inner_indexscan(root,childrel,
882-
outerrel->relids,JOIN_INNER);
835+
outer_relids,jointype);
836+
883837
/*
884838
* If no luck on an indexpath for this rel, we'll still consider
885-
* an Append substituting the cheapest-total inner path.This
886-
*is only likely to win ifthere'sat least one member rel for
887-
*which an indexscanpathdoes exist.
839+
* an Append substituting the cheapest-total inner path.However
840+
*we must find at least one indexpath, elsethere'snot going to
841+
*be any improvement over the basepathfor the appendrel.
888842
*/
889-
if (!bestinnerjoin)
843+
if (bestinnerjoin)
844+
found_indexscan= true;
845+
else
890846
bestinnerjoin=childrel->cheapest_total_path;
891847

892-
/*
893-
* We need a joinrel that describes this join accurately. Although
894-
* the joinrel won't ever be used by the join path search algorithm
895-
* in joinrels.c, it provides necessary context for the Path,
896-
* such as properly-translated target and quals lists.
897-
*/
898-
this_joinrel=translate_join_rel(root,joinrel,appinfo,
899-
outerrel,childrel,jointype,
900-
&this_restrictlist);
901-
902-
/* Build Path for join and add to result list */
903-
append_paths=lappend(append_paths,
904-
create_nestloop_path(root,
905-
this_joinrel,
906-
JOIN_INNER,
907-
outer_cheapest_total,
908-
bestinnerjoin,
909-
this_restrictlist,
910-
NIL));
848+
append_paths=lappend(append_paths,bestinnerjoin);
911849
}
912850

913-
/* Form the completed Append path and add it to the join relation. */
914-
add_path(joinrel, (Path*)create_append_path(joinrel,append_paths));
851+
if (!found_indexscan)
852+
returnNULL;
853+
854+
/* Form and return the completed Append path. */
855+
return (Path*)create_append_path(rel,append_paths);
915856
}
916857

917858
/*

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

Lines changed: 20 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/setrefs.c,v 1.119 2005/11/26 22:14:57 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/setrefs.c,v 1.120 2006/02/05 02:59:16 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -769,8 +769,9 @@ set_join_references(Join *join, List *rtable)
769769
*Handle join references appearing in an inner indexscan's quals
770770
*
771771
* To handle bitmap-scan plan trees, we have to be able to recurse down
772-
* to the bottom BitmapIndexScan nodes, so this is split out as a separate
773-
* function.
772+
* to the bottom BitmapIndexScan nodes; likewise, appendrel indexscans
773+
* require recursing through Append nodes. This is split out as a separate
774+
* function so that it can recurse.
774775
*/
775776
staticvoid
776777
set_inner_join_references(Plan*inner_plan,
@@ -910,6 +911,22 @@ set_inner_join_references(Plan *inner_plan,
910911
outer_itlist);
911912
}
912913
}
914+
elseif (IsA(inner_plan,Append))
915+
{
916+
/*
917+
* The inner side is an append plan. Recurse to see if it contains
918+
* indexscans that need to be fixed.
919+
*/
920+
Append*appendplan= (Append*)inner_plan;
921+
ListCell*l;
922+
923+
foreach(l,appendplan->appendplans)
924+
{
925+
set_inner_join_references((Plan*)lfirst(l),
926+
rtable,
927+
outer_itlist);
928+
}
929+
}
913930
elseif (IsA(inner_plan,TidScan))
914931
{
915932
TidScan*innerscan= (TidScan*)inner_plan;

0 commit comments

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