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

Commit19e34b6

Browse files
committed
Improve sublink pullup code to handle ANY/EXISTS sublinks that are at top
level of a JOIN/ON clause, not only at top level of WHERE. (However, wecan't do this in an outer join's ON clause, unless the ANY/EXISTS refersonly to the nullable side of the outer join, so that it can effectivelybe pushed down into the nullable side.) Per request from Kevin Grittner.In passing, fix a bug in the initial implementation of EXISTS pullup:it would Assert if the EXIST's WHERE clause used a join alias variable.Since we haven't yet flattened join aliases when this transformationhappens, it's necessary to include join relids in the computed set ofRHS relids.
1 parent909346e commit19e34b6

File tree

6 files changed

+330
-108
lines changed

6 files changed

+330
-108
lines changed

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

Lines changed: 4 additions & 2 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.141 2008/08/14 18:47:59 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.142 2008/08/17 01:19:59 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -778,7 +778,9 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
778778
root->hasPseudoConstantQuals= true;
779779
/* if not below outer join, push it to top of tree */
780780
if (!below_outer_join)
781-
relids=get_relids_in_jointree((Node*)root->parse->jointree);
781+
relids=
782+
get_relids_in_jointree((Node*)root->parse->jointree,
783+
false);
782784
}
783785
}
784786
}

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

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.241 2008/08/14 18:47:59 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.242 2008/08/17 01:19:59 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -268,14 +268,13 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
268268
root->append_rel_list=NIL;
269269

270270
/*
271-
* Look for ANY and EXISTS SubLinksat the top level of WHERE, and try to
272-
* transform them into joins. Note that this steponly handles SubLinks
273-
*originally at top level of WHERE; if we pull up any subqueries below,
274-
*their SubLinks areprocessed just before pulling them up.
271+
* Look for ANY and EXISTS SubLinksin WHERE and JOIN/ON clauses, and try
272+
*totransform them into joins. Note that this stepdoes not descend
273+
*into subqueries; if we pull up any subqueries below, their SubLinks are
274+
* processed just before pulling them up.
275275
*/
276276
if (parse->hasSubLinks)
277-
parse->jointree->quals=pull_up_sublinks(root,
278-
parse->jointree->quals);
277+
pull_up_sublinks(root);
279278

280279
/*
281280
* Scan the rangetable for set-returning functions, and inline them

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

Lines changed: 74 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/subselect.c,v 1.133 2008/08/14 18:47:59 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/subselect.c,v 1.134 2008/08/17 01:20:00 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -725,18 +725,31 @@ hash_ok_operator(OpExpr *expr)
725725
/*
726726
* convert_ANY_sublink_to_join: can we convert an ANY SubLink to a join?
727727
*
728-
* The caller has found an ANY SubLink at the top level of WHERE, but has not
729-
* checked the properties of the SubLink further. Decide whether it is
730-
* appropriate to process this SubLink in join style. If not, return NULL.
731-
* If so, build the qual clause(s) to replace the SubLink, and return them.
732-
* The qual clauses are wrapped in a FlattenedSubLink node to help later
733-
* processing place them properly.
728+
* The caller has found an ANY SubLink at the top level of one of the query's
729+
* qual clauses, but has not checked the properties of the SubLink further.
730+
* Decide whether it is appropriate to process this SubLink in join style.
731+
* Return TRUE if so, FALSE if the SubLink cannot be converted.
732+
*
733+
* The only non-obvious input parameter is available_rels: this is the set
734+
* of query rels that can safely be referenced in the sublink expression.
735+
* (We must restrict this to avoid changing the semantics when a sublink
736+
* is present in an outer join's ON qual.) The conversion must fail if
737+
* the converted qual would reference any but these parent-query relids.
738+
*
739+
* On success, two output parameters are returned:
740+
**new_qual is set to the qual tree that should replace the SubLink in
741+
*the parent query's qual tree. The qual clauses are wrapped in a
742+
*FlattenedSubLink node to help later processing place them properly.
743+
**fromlist is set to a list of pulled-up jointree item(s) that must be
744+
*added at the proper spot in the parent query's jointree.
734745
*
735746
* Side effects of a successful conversion include adding the SubLink's
736747
* subselect to the query's rangetable.
737748
*/
738-
Node*
739-
convert_ANY_sublink_to_join(PlannerInfo*root,SubLink*sublink)
749+
bool
750+
convert_ANY_sublink_to_join(PlannerInfo*root,SubLink*sublink,
751+
Relidsavailable_rels,
752+
Node**new_qual,List**fromlist)
740753
{
741754
Query*parse=root->parse;
742755
Query*subselect= (Query*)sublink->subselect;
@@ -755,7 +768,7 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink)
755768
* higher levels should be okay, though.)
756769
*/
757770
if (contain_vars_of_level((Node*)subselect,1))
758-
returnNULL;
771+
returnfalse;
759772

760773
/*
761774
* The test expression must contain some Vars of the current query,
@@ -764,16 +777,22 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink)
764777
*/
765778
left_varnos=pull_varnos(sublink->testexpr);
766779
if (bms_is_empty(left_varnos))
767-
returnNULL;
780+
return false;
781+
782+
/*
783+
* However, it can't refer to anything outside available_rels.
784+
*/
785+
if (!bms_is_subset(left_varnos,available_rels))
786+
return false;
768787

769788
/*
770789
* The combining operators and left-hand expressions mustn't be volatile.
771790
*/
772791
if (contain_volatile_functions(sublink->testexpr))
773-
returnNULL;
792+
returnfalse;
774793

775794
/*
776-
* Okay, pull up the sub-select intotop range table and jointree.
795+
* Okay, pull up the sub-select intoupper range table.
777796
*
778797
* We rely here on the assumption that the outer query has no references
779798
* to the inner (necessarily true, other than the Vars that we build
@@ -786,16 +805,15 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink)
786805
false);
787806
parse->rtable=lappend(parse->rtable,rte);
788807
rtindex=list_length(parse->rtable);
789-
rtr=makeNode(RangeTblRef);
790-
rtr->rtindex=rtindex;
791808

792809
/*
793-
* We assume it's okay to add the pulled-up subquery to the topmost FROM
794-
* list. This should be all right for ANY clauses appearing in WHERE
795-
* or in upper-level plain JOIN/ON clauses. ANYs appearing below any
796-
* outer joins couldn't be placed there, however.
810+
* Form a RangeTblRef for the pulled-up sub-select. This must be added
811+
* to the upper jointree, but it is caller's responsibility to figure
812+
* out where.
797813
*/
798-
parse->jointree->fromlist=lappend(parse->jointree->fromlist,rtr);
814+
rtr=makeNode(RangeTblRef);
815+
rtr->rtindex=rtindex;
816+
*fromlist=list_make1(rtr);
799817

800818
/*
801819
* Build a list of Vars representing the subselect outputs.
@@ -805,22 +823,24 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink)
805823
rtindex);
806824

807825
/*
808-
* Build theresult qual expression, replacing Params with these Vars.
826+
* Build thereplacement qual expression, replacing Params with these Vars.
809827
*/
810828
quals= (Expr*)convert_testexpr(root,
811829
sublink->testexpr,
812830
subquery_vars);
813831

814832
/*
815-
*Now build the FlattenedSubLink node.
833+
*And finally, build the FlattenedSubLink node.
816834
*/
817835
fslink=makeNode(FlattenedSubLink);
818836
fslink->jointype=JOIN_SEMI;
819837
fslink->lefthand=left_varnos;
820838
fslink->righthand=bms_make_singleton(rtindex);
821839
fslink->quals=quals;
822840

823-
return (Node*)fslink;
841+
*new_qual= (Node*)fslink;
842+
843+
return true;
824844
}
825845

826846
/*
@@ -883,20 +903,15 @@ simplify_EXISTS_query(Query *query)
883903
/*
884904
* convert_EXISTS_sublink_to_join: can we convert an EXISTS SubLink to a join?
885905
*
886-
* The caller has found an EXISTS SubLink at the top level of WHERE, or just
887-
* underneath a NOT, but has not checked the properties of the SubLink
888-
* further. Decide whether it is appropriate to process this SubLink in join
889-
* style. If not, return NULL. If so, build the qual clause(s) to replace
890-
* the SubLink, and return them. (In the NOT case, the returned clauses are
891-
* intended to replace the NOT as well.) The qual clauses are wrapped in a
892-
* FlattenedSubLink node to help later processing place them properly.
893-
*
894-
* Side effects of a successful conversion include adding the SubLink's
895-
* subselect to the query's rangetable.
906+
* The API of this function is identical to convert_ANY_sublink_to_join's,
907+
* except that we also support the case where the caller has found NOT EXISTS,
908+
* so we need an additional input parameter "under_not".
896909
*/
897-
Node*
910+
bool
898911
convert_EXISTS_sublink_to_join(PlannerInfo*root,SubLink*sublink,
899-
boolunder_not)
912+
boolunder_not,
913+
Relidsavailable_rels,
914+
Node**new_qual,List**fromlist)
900915
{
901916
Query*parse=root->parse;
902917
Query*subselect= (Query*)sublink->subselect;
@@ -924,7 +939,7 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
924939
* us with noplace to evaluate the targetlist.
925940
*/
926941
if (!simplify_EXISTS_query(subselect))
927-
returnNULL;
942+
returnfalse;
928943

929944
/*
930945
* Separate out the WHERE clause. (We could theoretically also remove
@@ -939,31 +954,31 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
939954
* query. (Vars of higher levels should be okay, though.)
940955
*/
941956
if (contain_vars_of_level((Node*)subselect,1))
942-
returnNULL;
957+
returnfalse;
943958

944959
/*
945960
* On the other hand, the WHERE clause must contain some Vars of the
946961
* parent query, else it's not gonna be a join.
947962
*/
948963
if (!contain_vars_of_level(whereClause,1))
949-
returnNULL;
964+
returnfalse;
950965

951966
/*
952967
* We don't risk optimizing if the WHERE clause is volatile, either.
953968
*/
954969
if (contain_volatile_functions(whereClause))
955-
returnNULL;
970+
returnfalse;
956971

957972
/*
958973
* Also disallow SubLinks within the WHERE clause. (XXX this could
959974
* probably be supported, but it would complicate the transformation
960975
* below, and it doesn't seem worth worrying about in a first pass.)
961976
*/
962977
if (contain_subplans(whereClause))
963-
returnNULL;
978+
returnfalse;
964979

965980
/*
966-
*Okay,pull up the sub-select into top range table and jointree.
981+
*Prepare topull up the sub-select into top range table.
967982
*
968983
* We rely here on the assumption that the outer query has no references
969984
* to the inner (necessarily true). Therefore this is a lot easier than
@@ -973,7 +988,7 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
973988
* to do. The machinations of simplify_EXISTS_query ensured that there
974989
* is nothing interesting in the subquery except an rtable and jointree,
975990
* and even the jointree FromExpr no longer has quals. So we can just
976-
* append the rtable to our own andappend the fromlist to our own.
991+
* append the rtable to our own andattach the fromlist to our own.
977992
* But first, adjust all level-zero varnos in the subquery to account
978993
* for the rtable merger.
979994
*/
@@ -1007,32 +1022,39 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
10071022
bms_free(clause_varnos);
10081023
Assert(!bms_is_empty(left_varnos));
10091024

1010-
/* Also identify all the rels syntactically within the subselect */
1011-
subselect_varnos=get_relids_in_jointree((Node*)subselect->jointree);
1025+
/*
1026+
* Now that we've got the set of upper-level varnos, we can make the
1027+
* last check: only available_rels can be referenced.
1028+
*/
1029+
if (!bms_is_subset(left_varnos,available_rels))
1030+
return false;
1031+
1032+
/* Identify all the rels syntactically within the subselect */
1033+
subselect_varnos=get_relids_in_jointree((Node*)subselect->jointree,
1034+
true);
10121035
Assert(bms_is_subset(right_varnos,subselect_varnos));
10131036

10141037
/* Now we can attach the modified subquery rtable to the parent */
10151038
parse->rtable=list_concat(parse->rtable,subselect->rtable);
10161039

10171040
/*
1018-
* We assume it's okay to add the pulled-up subquery to the topmost FROM
1019-
* list. This should be all right for EXISTS clauses appearing in WHERE
1020-
* or in upper-level plain JOIN/ON clauses. EXISTS appearing below any
1021-
* outer joins couldn't be placed there, however.
1041+
* Pass back the subquery fromlist to be attached to upper jointree
1042+
* in a suitable place.
10221043
*/
1023-
parse->jointree->fromlist=list_concat(parse->jointree->fromlist,
1024-
subselect->jointree->fromlist);
1044+
*fromlist=subselect->jointree->fromlist;
10251045

10261046
/*
1027-
*Now build the FlattenedSubLink node.
1047+
*And finally, build the FlattenedSubLink node.
10281048
*/
10291049
fslink=makeNode(FlattenedSubLink);
10301050
fslink->jointype=under_not ?JOIN_ANTI :JOIN_SEMI;
10311051
fslink->lefthand=left_varnos;
10321052
fslink->righthand=subselect_varnos;
10331053
fslink->quals= (Expr*)whereClause;
10341054

1035-
return (Node*)fslink;
1055+
*new_qual= (Node*)fslink;
1056+
1057+
return true;
10361058
}
10371059

10381060
/*

0 commit comments

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