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

Commitb29f68f

Browse files
committed
Take OUTER JOIN semantics into account when estimating the size of join
relations. It's not very bright, but at least it now knows thatA LEFT JOIN B must produce at least as many rows as are in A ...
1 parentca71c66 commitb29f68f

File tree

5 files changed

+47
-11
lines changed

5 files changed

+47
-11
lines changed

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

Lines changed: 33 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,7 @@
4141
* Portions Copyright (c) 1994, Regents of the University of California
4242
*
4343
* IDENTIFICATION
44-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.67 2001/02/15 17:46:40 tgl Exp $
44+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.68 2001/02/16 00:03:07 tgl Exp $
4545
*
4646
*-------------------------------------------------------------------------
4747
*/
@@ -837,11 +837,12 @@ void
837837
set_joinrel_size_estimates(Query*root,RelOptInfo*rel,
838838
RelOptInfo*outer_rel,
839839
RelOptInfo*inner_rel,
840+
JoinTypejointype,
840841
List*restrictlist)
841842
{
842843
doubletemp;
843844

844-
/*cartesian product */
845+
/*Start with the Cartesian product */
845846
temp=outer_rel->rows*inner_rel->rows;
846847

847848
/*
@@ -854,13 +855,43 @@ set_joinrel_size_estimates(Query *root, RelOptInfo *rel,
854855
restrictlist,
855856
0);
856857

858+
/*
859+
* If we are doing an outer join, take that into account: the output
860+
* must be at least as large as the non-nullable input. (Is there any
861+
* chance of being even smarter?)
862+
*/
863+
switch (jointype)
864+
{
865+
caseJOIN_INNER:
866+
break;
867+
caseJOIN_LEFT:
868+
if (temp<outer_rel->rows)
869+
temp=outer_rel->rows;
870+
break;
871+
caseJOIN_RIGHT:
872+
if (temp<inner_rel->rows)
873+
temp=inner_rel->rows;
874+
break;
875+
caseJOIN_FULL:
876+
if (temp<outer_rel->rows)
877+
temp=outer_rel->rows;
878+
if (temp<inner_rel->rows)
879+
temp=inner_rel->rows;
880+
break;
881+
default:
882+
elog(ERROR,"set_joinrel_size_estimates: unsupported join type %d",
883+
(int)jointype);
884+
break;
885+
}
886+
857887
/*
858888
* Force estimate to be at least one row, to make explain output look
859889
* better and to avoid possible divide-by-zero when interpolating
860890
* cost.
861891
*/
862892
if (temp<1.0)
863893
temp=1.0;
894+
864895
rel->rows=temp;
865896

866897
/*

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/joinrels.c,v 1.50 2001/01/24 19:42:58 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/joinrels.c,v 1.51 2001/02/16 00:03:07 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -394,7 +394,7 @@ make_join_rel(Query *root, RelOptInfo *rel1, RelOptInfo *rel2,
394394
* Find or build the join RelOptInfo, and compute the restrictlist
395395
* that goes with this particular joining.
396396
*/
397-
joinrel=get_join_rel(root,rel1,rel2,&restrictlist);
397+
joinrel=get_join_rel(root,rel1,rel2,jointype,&restrictlist);
398398

399399
/*
400400
* Consider paths using each rel as both outer and inner.

‎src/backend/optimizer/util/relnode.c‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/util/relnode.c,v 1.31 2001/01/24 19:43:00 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/util/relnode.c,v 1.32 2001/02/16 00:03:08 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -152,6 +152,7 @@ find_join_rel(Query *root, Relids relids)
152152
*
153153
* 'outer_rel' and 'inner_rel' are relation nodes for the relations to be
154154
*joined
155+
* 'jointype': type of join (inner/outer)
155156
* 'restrictlist_ptr': result variable. If not NULL, *restrictlist_ptr
156157
*receives the list of RestrictInfo nodes that apply to this
157158
*particular pair of joinable relations.
@@ -163,6 +164,7 @@ RelOptInfo *
163164
get_join_rel(Query*root,
164165
RelOptInfo*outer_rel,
165166
RelOptInfo*inner_rel,
167+
JoinTypejointype,
166168
List**restrictlist_ptr)
167169
{
168170
List*joinrelids;
@@ -252,7 +254,7 @@ get_join_rel(Query *root,
252254
* Set estimates of the joinrel's size.
253255
*/
254256
set_joinrel_size_estimates(root,joinrel,outer_rel,inner_rel,
255-
restrictlist);
257+
jointype,restrictlist);
256258

257259
/*
258260
* Add the joinrel to the query's joinrel list.

‎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-2001, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: cost.h,v 1.37 2001/01/24 19:43:26 momjian Exp $
10+
* $Id: cost.h,v 1.38 2001/02/16 00:03:05 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -70,6 +70,7 @@ extern void set_baserel_size_estimates(Query *root, RelOptInfo *rel);
7070
externvoidset_joinrel_size_estimates(Query*root,RelOptInfo*rel,
7171
RelOptInfo*outer_rel,
7272
RelOptInfo*inner_rel,
73+
JoinTypejointype,
7374
List*restrictlist);
7475

7576
/*

‎src/include/optimizer/pathnode.h‎

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: pathnode.h,v 1.33 2001/01/24 19:43:26 momjian Exp $
10+
* $Id: pathnode.h,v 1.34 2001/02/16 00:03:06 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -66,8 +66,10 @@ extern HashPath *create_hashjoin_path(RelOptInfo *joinrel,
6666
*/
6767
externRelOptInfo*get_base_rel(Query*root,intrelid);
6868
externRelOptInfo*make_base_rel(Query*root,intrelid);
69-
externRelOptInfo*get_join_rel(Query*root,RelOptInfo*outer_rel,
70-
RelOptInfo*inner_rel,
71-
List**restrictlist_ptr);
69+
externRelOptInfo*get_join_rel(Query*root,
70+
RelOptInfo*outer_rel,
71+
RelOptInfo*inner_rel,
72+
JoinTypejointype,
73+
List**restrictlist_ptr);
7274

7375
#endif/* PATHNODE_H */

0 commit comments

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