Skip to content

Commit b29f68f

Browse files
tglsfdctglsfdc
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 that A LEFT JOIN B must produce at least as many rows as are in A ...
1 parent ca71c66 commit b29f68f

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+
JoinType jointype,
840841
List *restrictlist)
841842
{
842843
double temp;
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+
case JOIN_INNER:
866+
break;
867+
case JOIN_LEFT:
868+
if (temp < outer_rel->rows)
869+
temp = outer_rel->rows;
870+
break;
871+
case JOIN_RIGHT:
872+
if (temp < inner_rel->rows)
873+
temp = inner_rel->rows;
874+
break;
875+
case JOIN_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+
JoinType jointype,
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
extern void set_joinrel_size_estimates(Query *root, RelOptInfo *rel,
7171
RelOptInfo *outer_rel,
7272
RelOptInfo *inner_rel,
73+
JoinType jointype,
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
extern RelOptInfo *get_base_rel(Query *root, int relid);
6868
extern RelOptInfo *make_base_rel(Query *root, int relid);
69-
extern RelOptInfo *get_join_rel(Query *root, RelOptInfo *outer_rel,
70-
RelOptInfo *inner_rel,
71-
List **restrictlist_ptr);
69+
extern RelOptInfo *get_join_rel(Query *root,
70+
RelOptInfo *outer_rel,
71+
RelOptInfo *inner_rel,
72+
JoinType jointype,
73+
List **restrictlist_ptr);
7274

7375
#endif /* PATHNODE_H */

0 commit comments

Comments
 (0)