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

Commit4e97631

Browse files
committed
Fix join-removal logic for pseudoconstant and outerjoin-delayed quals.
In these cases a qual can get marked with the removable rel in itsrequired_relids, but this is just to schedule its evaluation correctly, notbecause it really depends on the rel. We were assuming that, in effect,we could throw away *all* quals so marked, which is nonsense. Tighten upthe logic to be a little more paranoid about which quals belong to theouter join being considered for removal, and arrange for all quals thatdon't belong to be updated so they will still get evaluated correctly.Also fix another problem that happened to be exposed by this test case,which was that make_join_rel() was failing to notice some cases wherea constant-false qual could be used to prove a join relation empty. If it'sa pushed-down constant false, then the relation is empty even if it's anouter join, because the qual applies after the outer join expansion.Per report from Nathan Grange. Back-patch into 9.0.
1 parent3522217 commit4e97631

File tree

6 files changed

+187
-31
lines changed

6 files changed

+187
-31
lines changed

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

Lines changed: 29 additions & 11 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.105 2010/02/26 02:00:45 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.106 2010/09/14 23:15:29 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -29,7 +29,8 @@ static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel);
2929
staticboolhas_legal_joinclause(PlannerInfo*root,RelOptInfo*rel);
3030
staticboolis_dummy_rel(RelOptInfo*rel);
3131
staticvoidmark_dummy_rel(RelOptInfo*rel);
32-
staticboolrestriction_is_constant_false(List*restrictlist);
32+
staticboolrestriction_is_constant_false(List*restrictlist,
33+
boolonly_pushed_down);
3334

3435

3536
/*
@@ -603,7 +604,10 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
603604
*
604605
* Also, a provably constant-false join restriction typically means that
605606
* we can skip evaluating one or both sides of the join. We do this by
606-
* marking the appropriate rel as dummy.
607+
* marking the appropriate rel as dummy. For outer joins, a constant-false
608+
* restriction that is pushed down still means the whole join is dummy,
609+
* while a non-pushed-down one means that no inner rows will join so we
610+
* can treat the inner rel as dummy.
607611
*
608612
* We need only consider the jointypes that appear in join_info_list, plus
609613
* JOIN_INNER.
@@ -612,7 +616,7 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
612616
{
613617
caseJOIN_INNER:
614618
if (is_dummy_rel(rel1)||is_dummy_rel(rel2)||
615-
restriction_is_constant_false(restrictlist))
619+
restriction_is_constant_false(restrictlist, false))
616620
{
617621
mark_dummy_rel(joinrel);
618622
break;
@@ -625,12 +629,13 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
625629
restrictlist);
626630
break;
627631
caseJOIN_LEFT:
628-
if (is_dummy_rel(rel1))
632+
if (is_dummy_rel(rel1)||
633+
restriction_is_constant_false(restrictlist, true))
629634
{
630635
mark_dummy_rel(joinrel);
631636
break;
632637
}
633-
if (restriction_is_constant_false(restrictlist)&&
638+
if (restriction_is_constant_false(restrictlist, false)&&
634639
bms_is_subset(rel2->relids,sjinfo->syn_righthand))
635640
mark_dummy_rel(rel2);
636641
add_paths_to_joinrel(root,joinrel,rel1,rel2,
@@ -641,7 +646,8 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
641646
restrictlist);
642647
break;
643648
caseJOIN_FULL:
644-
if (is_dummy_rel(rel1)&&is_dummy_rel(rel2))
649+
if ((is_dummy_rel(rel1)&&is_dummy_rel(rel2))||
650+
restriction_is_constant_false(restrictlist, true))
645651
{
646652
mark_dummy_rel(joinrel);
647653
break;
@@ -665,7 +671,7 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
665671
bms_is_subset(sjinfo->min_righthand,rel2->relids))
666672
{
667673
if (is_dummy_rel(rel1)||is_dummy_rel(rel2)||
668-
restriction_is_constant_false(restrictlist))
674+
restriction_is_constant_false(restrictlist, false))
669675
{
670676
mark_dummy_rel(joinrel);
671677
break;
@@ -687,6 +693,12 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
687693
create_unique_path(root,rel2,rel2->cheapest_total_path,
688694
sjinfo)!=NULL)
689695
{
696+
if (is_dummy_rel(rel1)||is_dummy_rel(rel2)||
697+
restriction_is_constant_false(restrictlist, false))
698+
{
699+
mark_dummy_rel(joinrel);
700+
break;
701+
}
690702
add_paths_to_joinrel(root,joinrel,rel1,rel2,
691703
JOIN_UNIQUE_INNER,sjinfo,
692704
restrictlist);
@@ -696,12 +708,13 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
696708
}
697709
break;
698710
caseJOIN_ANTI:
699-
if (is_dummy_rel(rel1))
711+
if (is_dummy_rel(rel1)||
712+
restriction_is_constant_false(restrictlist, true))
700713
{
701714
mark_dummy_rel(joinrel);
702715
break;
703716
}
704-
if (restriction_is_constant_false(restrictlist)&&
717+
if (restriction_is_constant_false(restrictlist, false)&&
705718
bms_is_subset(rel2->relids,sjinfo->syn_righthand))
706719
mark_dummy_rel(rel2);
707720
add_paths_to_joinrel(root,joinrel,rel1,rel2,
@@ -947,9 +960,11 @@ mark_dummy_rel(RelOptInfo *rel)
947960
* join situations this will leave us computing cartesian products only to
948961
* decide there's no match for an outer row, which is pretty stupid. So,
949962
* we need to detect the case.
963+
*
964+
* If only_pushed_down is TRUE, then consider only pushed-down quals.
950965
*/
951966
staticbool
952-
restriction_is_constant_false(List*restrictlist)
967+
restriction_is_constant_false(List*restrictlist,boolonly_pushed_down)
953968
{
954969
ListCell*lc;
955970

@@ -964,6 +979,9 @@ restriction_is_constant_false(List *restrictlist)
964979
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
965980

966981
Assert(IsA(rinfo,RestrictInfo));
982+
if (only_pushed_down&& !rinfo->is_pushed_down)
983+
continue;
984+
967985
if (rinfo->clause&&IsA(rinfo->clause,Const))
968986
{
969987
Const*con= (Const*)rinfo->clause;

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

Lines changed: 65 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -16,19 +16,21 @@
1616
*
1717
*
1818
* IDENTIFICATION
19-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/analyzejoins.c,v 1.3 2010/07/06 19:18:56 momjian Exp $
19+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/analyzejoins.c,v 1.4 2010/09/14 23:15:29 tgl Exp $
2020
*
2121
*-------------------------------------------------------------------------
2222
*/
2323
#include"postgres.h"
2424

25+
#include"optimizer/joininfo.h"
2526
#include"optimizer/pathnode.h"
2627
#include"optimizer/paths.h"
2728
#include"optimizer/planmain.h"
2829

2930
/* local functions */
3031
staticbooljoin_is_removable(PlannerInfo*root,SpecialJoinInfo*sjinfo);
31-
staticvoidremove_rel_from_query(PlannerInfo*root,intrelid);
32+
staticvoidremove_rel_from_query(PlannerInfo*root,intrelid,
33+
Relidsjoinrelids);
3234
staticList*remove_rel_from_joinlist(List*joinlist,intrelid,int*nremoved);
3335

3436

@@ -67,7 +69,9 @@ remove_useless_joins(PlannerInfo *root, List *joinlist)
6769
*/
6870
innerrelid=bms_singleton_member(sjinfo->min_righthand);
6971

70-
remove_rel_from_query(root,innerrelid);
72+
remove_rel_from_query(root,innerrelid,
73+
bms_union(sjinfo->min_lefthand,
74+
sjinfo->min_righthand));
7175

7276
/* We verify that exactly one reference gets removed from joinlist */
7377
nremoved=0;
@@ -216,19 +220,25 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
216220
{
217221
RestrictInfo*restrictinfo= (RestrictInfo*)lfirst(l);
218222

219-
/* Ignore clauses not pertinent to this join */
220-
if (!bms_is_subset(restrictinfo->required_relids,joinrelids))
221-
continue;
222-
223223
/*
224-
* If we find a pushed-down clause, it must have come from above the
225-
* outer join and it must contain references to the inner rel.(If it
226-
* had only outer-rel variables, it'd have been pushed down into the
227-
* outer rel.)Therefore, we can conclude that join removal is unsafe
228-
* without any examination of the clause contents.
224+
* If it's not a join clause for this outer join, we can't use it.
225+
* Note that if the clause is pushed-down, then it is logically from
226+
* above the outer join, even if it references no other rels (it might
227+
* be from WHERE, for example).
229228
*/
230-
if (restrictinfo->is_pushed_down)
231-
return false;
229+
if (restrictinfo->is_pushed_down||
230+
!bms_equal(restrictinfo->required_relids,joinrelids))
231+
{
232+
/*
233+
* If such a clause actually references the inner rel then
234+
* join removal has to be disallowed. We have to check this
235+
* despite the previous attr_needed checks because of the
236+
* possibility of pushed-down clauses referencing the rel.
237+
*/
238+
if (bms_is_member(innerrelid,restrictinfo->clause_relids))
239+
return false;
240+
continue;/* else, ignore; not useful here */
241+
}
232242

233243
/* Ignore if it's not a mergejoinable clause */
234244
if (!restrictinfo->can_join||
@@ -299,14 +309,14 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
299309
* We are not terribly thorough here. We must make sure that the rel is
300310
* no longer treated as a baserel, and that attributes of other baserels
301311
* are no longer marked as being needed at joins involving this rel.
302-
* In particular, we don't bother removing join quals involving the rel from
303-
* the joininfo lists; they'll just get ignored, since we will never form a
304-
* join relation at which they could be evaluated.
312+
* Also, join quals involving the rel have to be removed from the joininfo
313+
* lists, but only if they belong to the outer join identified by joinrelids.
305314
*/
306315
staticvoid
307-
remove_rel_from_query(PlannerInfo*root,intrelid)
316+
remove_rel_from_query(PlannerInfo*root,intrelid,Relidsjoinrelids)
308317
{
309318
RelOptInfo*rel=find_base_rel(root,relid);
319+
List*joininfos;
310320
Indexrti;
311321
ListCell*l;
312322

@@ -379,6 +389,43 @@ remove_rel_from_query(PlannerInfo *root, int relid)
379389

380390
phinfo->ph_needed=bms_del_member(phinfo->ph_needed,relid);
381391
}
392+
393+
/*
394+
* Remove any joinquals referencing the rel from the joininfo lists.
395+
*
396+
* In some cases, a joinqual has to be put back after deleting its
397+
* reference to the target rel. This can occur for pseudoconstant and
398+
* outerjoin-delayed quals, which can get marked as requiring the rel in
399+
* order to force them to be evaluated at or above the join. We can't
400+
* just discard them, though. Only quals that logically belonged to the
401+
* outer join being discarded should be removed from the query.
402+
*
403+
* We must make a copy of the rel's old joininfo list before starting the
404+
* loop, because otherwise remove_join_clause_from_rels would destroy the
405+
* list while we're scanning it.
406+
*/
407+
joininfos=list_copy(rel->joininfo);
408+
foreach(l,joininfos)
409+
{
410+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(l);
411+
412+
remove_join_clause_from_rels(root,rinfo,rinfo->required_relids);
413+
414+
if (rinfo->is_pushed_down||
415+
!bms_equal(rinfo->required_relids,joinrelids))
416+
{
417+
/* Recheck that qual doesn't actually reference the target rel */
418+
Assert(!bms_is_member(relid,rinfo->clause_relids));
419+
/*
420+
* The required_relids probably aren't shared with anything else,
421+
* but let's copy them just to be sure.
422+
*/
423+
rinfo->required_relids=bms_copy(rinfo->required_relids);
424+
rinfo->required_relids=bms_del_member(rinfo->required_relids,
425+
relid);
426+
distribute_restrictinfo_to_rels(root,rinfo);
427+
}
428+
}
382429
}
383430

384431
/*

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

Lines changed: 35 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/util/joininfo.c,v 1.52 2010/01/02 16:57:48 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/util/joininfo.c,v 1.53 2010/09/14 23:15:29 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -98,3 +98,37 @@ add_join_clause_to_rels(PlannerInfo *root,
9898
}
9999
bms_free(tmprelids);
100100
}
101+
102+
/*
103+
* remove_join_clause_from_rels
104+
* Delete 'restrictinfo' from all the joininfo lists it is in
105+
*
106+
* This reverses the effect of add_join_clause_to_rels. It's used when we
107+
* discover that a relation need not be joined at all.
108+
*
109+
* 'restrictinfo' describes the join clause
110+
* 'join_relids' is the list of relations participating in the join clause
111+
* (there must be more than one)
112+
*/
113+
void
114+
remove_join_clause_from_rels(PlannerInfo*root,
115+
RestrictInfo*restrictinfo,
116+
Relidsjoin_relids)
117+
{
118+
Relidstmprelids;
119+
intcur_relid;
120+
121+
tmprelids=bms_copy(join_relids);
122+
while ((cur_relid=bms_first_member(tmprelids)) >=0)
123+
{
124+
RelOptInfo*rel=find_base_rel(root,cur_relid);
125+
126+
/*
127+
* Remove the restrictinfo from the list. Pointer comparison is
128+
* sufficient.
129+
*/
130+
Assert(list_member_ptr(rel->joininfo,restrictinfo));
131+
rel->joininfo=list_delete_ptr(rel->joininfo,restrictinfo);
132+
}
133+
bms_free(tmprelids);
134+
}

‎src/include/optimizer/joininfo.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/joininfo.h,v 1.38 2010/01/02 16:58:07 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/joininfo.h,v 1.39 2010/09/14 23:15:29 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -23,5 +23,8 @@ extern bool have_relevant_joinclause(PlannerInfo *root,
2323
externvoidadd_join_clause_to_rels(PlannerInfo*root,
2424
RestrictInfo*restrictinfo,
2525
Relidsjoin_relids);
26+
externvoidremove_join_clause_from_rels(PlannerInfo*root,
27+
RestrictInfo*restrictinfo,
28+
Relidsjoin_relids);
2629

2730
#endif/* JOININFO_H */

‎src/test/regress/expected/join.out

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2586,6 +2586,43 @@ explain (costs off)
25862586
-> Seq Scan on child c
25872587
(5 rows)
25882588

2589+
-- check for a 9.0rc1 bug: join removal breaks pseudoconstant qual handling
2590+
select p.* from
2591+
parent p left join child c on (p.k = c.k)
2592+
where p.k = 1 and p.k = 2;
2593+
k | pd
2594+
---+----
2595+
(0 rows)
2596+
2597+
explain (costs off)
2598+
select p.* from
2599+
parent p left join child c on (p.k = c.k)
2600+
where p.k = 1 and p.k = 2;
2601+
QUERY PLAN
2602+
------------------------------------------------
2603+
Result
2604+
One-Time Filter: false
2605+
-> Index Scan using parent_pkey on parent p
2606+
Index Cond: (k = 1)
2607+
(4 rows)
2608+
2609+
select p.* from
2610+
(parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k
2611+
where p.k = 1 and p.k = 2;
2612+
k | pd
2613+
---+----
2614+
(0 rows)
2615+
2616+
explain (costs off)
2617+
select p.* from
2618+
(parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k
2619+
where p.k = 1 and p.k = 2;
2620+
QUERY PLAN
2621+
--------------------------
2622+
Result
2623+
One-Time Filter: false
2624+
(2 rows)
2625+
25892626
-- bug 5255: this is not optimizable by join removal
25902627
begin;
25912628
CREATE TEMP TABLE a (id int PRIMARY KEY);

‎src/test/regress/sql/join.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -615,6 +615,23 @@ explain (costs off)
615615
left join (select c.*, trueas linkedfrom child c)as ss
616616
on (p.k=ss.k);
617617

618+
-- check for a 9.0rc1 bug: join removal breaks pseudoconstant qual handling
619+
select p.*from
620+
parent pleft join child con (p.k=c.k)
621+
wherep.k=1andp.k=2;
622+
explain (costs off)
623+
select p.*from
624+
parent pleft join child con (p.k=c.k)
625+
wherep.k=1andp.k=2;
626+
627+
select p.*from
628+
(parent pleft join child con (p.k=c.k))join parent xonp.k=x.k
629+
wherep.k=1andp.k=2;
630+
explain (costs off)
631+
select p.*from
632+
(parent pleft join child con (p.k=c.k))join parent xonp.k=x.k
633+
wherep.k=1andp.k=2;
634+
618635
-- bug 5255: this is not optimizable by join removal
619636
begin;
620637

0 commit comments

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