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

Commit75c85bd

Browse files
committed
Tighten up join ordering rules to account for recent more-careful analysis
of the associativity of antijoins. Also improve optimizer/README discussionof outer join ordering rules.
1 parent5c4ca3b commit75c85bd

File tree

3 files changed

+38
-19
lines changed

3 files changed

+38
-19
lines changed

‎src/backend/optimizer/README

Lines changed: 18 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
$PostgreSQL: pgsql/src/backend/optimizer/README,v 1.48 2008/08/14 18:47:59 tgl Exp $
1+
$PostgreSQL: pgsql/src/backend/optimizer/README,v 1.49 2009/02/27 22:41:37 tgl Exp $
22

33
Optimizer
44
=========
@@ -205,28 +205,40 @@ strict, the first form might produce some rows with nonnull C columns
205205
where the second form would make those entries null.
206206

207207
RIGHT JOIN is equivalent to LEFT JOIN after switching the two input
208-
tables, so the same identities work for right joins. Only FULL JOIN
209-
cannot be re-ordered at all.
208+
tables, so the same identities work for right joins.
210209

211210
An example of a case that does *not* work is moving an innerjoin into or
212211
out of the nullable side of an outer join:
213212

214213
A leftjoin (B join C on (Pbc)) on (Pab)
215214
!= (A leftjoin B on (Pab)) join C on (Pbc)
216215

216+
SEMI joins work a little bit differently. A semijoin can be reassociated
217+
into or out of the lefthand side of another semijoin, but not into or out
218+
of the righthand side. Likewise, an inner join, left join, or antijoin
219+
can be reassociated into or out of the lefthand side of a semijoin, but
220+
not into or out of the righthand side.
221+
222+
ANTI joins work approximately like LEFT joins, except that identity 3
223+
fails if the join to C is an antijoin (even if Pbc is strict, and in
224+
both the cases where the other join is a leftjoin and where it is an
225+
antijoin). So we can't reorder antijoins into or out of the RHS of a
226+
leftjoin or antijoin, even if the relevant clause is strict.
227+
228+
The current code does not attempt to re-order FULL JOINs at all.
217229
FULL JOIN ordering is enforced by not collapsing FULL JOIN nodes when
218-
translating the jointree to "joinlist" representation.LEFT and RIGHT
230+
translating the jointree to "joinlist" representation.Other types of
219231
JOIN nodes are normally collapsed so that they participate fully in the
220232
join order search. To avoid generating illegal join orders, the planner
221-
creates a SpecialJoinInfo node for eachouter join, and join_is_legal
233+
creates a SpecialJoinInfo node for eachnon-inner join, and join_is_legal
222234
checks this list to decide if a proposed join is legal.
223235

224236
What we store in SpecialJoinInfo nodes are the minimum sets of Relids
225237
required on each side of the join to form the outer join. Note that
226238
these are minimums; there's no explicit maximum, since joining other
227239
rels to the OJ's syntactic rels may be legal. Per identities 1 and 2,
228240
non-FULL joins can be freely associated into the lefthand side of an
229-
OJ, but ingeneral they can't be associated into the righthand side.
241+
OJ, but insome cases they can't be associated into the righthand side.
230242
So the restriction enforced by join_is_legal is that a proposed join
231243
can't join a rel within or partly within an RHS boundary to one outside
232244
the boundary, unless the join validly implements some outer join.

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.98 2009/02/19 20:32:45 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.99 2009/02/27 22:41:37 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -185,7 +185,7 @@ join_search_one_level(PlannerInfo *root, int level, List **joinrels)
185185
* Last-ditch effort: if we failed to find any usable joins so far, force
186186
* a set of cartesian-product joins to be generated. This handles the
187187
* special case where all the available rels have join clauses but we
188-
* cannot use any ofthe joins yet. An example is
188+
* cannot use any ofthose clauses yet. An example is
189189
*
190190
* SELECT * FROM a,b,c WHERE (a.f1 + b.f2 + c.f3) = 0;
191191
*
@@ -737,7 +737,7 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
737737
*
738738
* In practice this is always used with have_relevant_joinclause(), and so
739739
* could be merged with that function, but it seems clearer to separate the
740-
* two concerns. We needthese tests because there are degenerate cases where
740+
* two concerns. We needthis test because there are degenerate cases where
741741
* a clauseless join must be performed to satisfy join-order restrictions.
742742
*
743743
* Note: this is only a problem if one side of a degenerate outer join

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

Lines changed: 17 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.148 2009/02/25 03:30:37 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.149 2009/02/27 22:41:38 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -628,26 +628,32 @@ make_outerjoininfo(PlannerInfo *root,
628628
* min_lefthand. (We must use its full syntactic relset, not just its
629629
* min_lefthand + min_righthand. This is because there might be other
630630
* OJs below this one that this one can commute with, but we cannot
631-
* commute with them if we don't with this one.)
631+
* commute with them if we don't with this one.) Also, if the
632+
* current join is an antijoin, we must preserve ordering regardless
633+
* of strictness.
632634
*
633635
* Note: I believe we have to insist on being strict for at least one
634636
* rel in the lower OJ's min_righthand, not its whole syn_righthand.
635637
*/
636-
if (bms_overlap(left_rels,otherinfo->syn_righthand)&&
637-
bms_overlap(clause_relids,otherinfo->syn_righthand)&&
638-
!bms_overlap(strict_relids,otherinfo->min_righthand))
638+
if (bms_overlap(left_rels,otherinfo->syn_righthand))
639639
{
640-
min_lefthand=bms_add_members(min_lefthand,
641-
otherinfo->syn_lefthand);
642-
min_lefthand=bms_add_members(min_lefthand,
643-
otherinfo->syn_righthand);
640+
if (bms_overlap(clause_relids,otherinfo->syn_righthand)&&
641+
(jointype==JOIN_ANTI||
642+
!bms_overlap(strict_relids,otherinfo->min_righthand)))
643+
{
644+
min_lefthand=bms_add_members(min_lefthand,
645+
otherinfo->syn_lefthand);
646+
min_lefthand=bms_add_members(min_lefthand,
647+
otherinfo->syn_righthand);
648+
}
644649
}
645650

646651
/*
647652
* For a lower OJ in our RHS, if our join condition does not use the
648653
* lower join's RHS and the lower OJ's join condition is strict, we
649654
* can interchange the ordering of the two OJs; otherwise we must add
650-
* lower OJ's full syntactic relset to min_righthand.
655+
* lower OJ's full syntactic relset to min_righthand. Here, we must
656+
* preserve ordering anyway if the lower OJ is an antijoin.
651657
*
652658
* Here, we have to consider that "our join condition" includes any
653659
* clauses that syntactically appeared above the lower OJ and below
@@ -663,6 +669,7 @@ make_outerjoininfo(PlannerInfo *root,
663669
if (bms_overlap(right_rels,otherinfo->syn_righthand))
664670
{
665671
if (bms_overlap(clause_relids,otherinfo->syn_righthand)||
672+
otherinfo->jointype==JOIN_ANTI||
666673
!otherinfo->lhs_strict||otherinfo->delay_upper_joins)
667674
{
668675
min_righthand=bms_add_members(min_righthand,

0 commit comments

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