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

Commit7371ab7

Browse files
committed
Further mucking with PlaceHolderVar-related restrictions on join order.
Commit85e5e22 turns out not to have takencare of all cases of the partially-evaluatable-PlaceHolderVar problem foundby Andreas Seltenreich's fuzz testing. I had set it up to check for riskyPHVs only in the event that we were making a star-schema-based exception tothe param_source_rels join ordering heuristic. However, it turns out thatthe problem can occur even in joins that satisfy the param_source_relsheuristic, in which case allow_star_schema_join() isn't consulted.Refactor so that we check for risky PHVs whenever the proposed join hasany remaining parameterization.Back-patch to 9.2, like the previous patch (except for the regression testcase, which only works back to 9.3 because it uses LATERAL).Note that this discovery implies that problems of this sort could'veoccurred in 9.2 and up even before the star-schema patch; though I've nottried to prove that experimentally.
1 parentd4ad167 commit7371ab7

File tree

3 files changed

+79
-28
lines changed

3 files changed

+79
-28
lines changed

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

Lines changed: 41 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -274,44 +274,55 @@ add_paths_to_joinrel(PlannerInfo *root,
274274
* across joins unless there's a join-order-constraint-based reason to do so.
275275
* So we ignore the param_source_rels restriction when this case applies.
276276
*
277-
* However, there's a pitfall: suppose the inner rel (call it A) has a
278-
* parameter that is a PlaceHolderVar, and that PHV's minimum eval_at set
279-
* includes the outer rel (B) and some third rel (C). If we treat this as a
280-
* star-schema case and create a B/A nestloop join that's parameterized by C,
281-
* we would end up with a plan in which the PHV's expression has to be
282-
* evaluated as a nestloop parameter at the B/A join; and the executor is only
283-
* set up to handle simple Vars as NestLoopParams. Rather than add complexity
284-
* and overhead to the executor for such corner cases, it seems better to
285-
* forbid the join. (Note that existence of such a PHV probably means there
286-
* is a join order constraint that will cause us to consider joining B and C
287-
* directly; so we can still make use of A's parameterized path, and there is
288-
* no need for the star-schema exception.)To implement this exception to the
289-
* exception, we check whether any PHVs used in the query could pose such a
290-
* hazard. We don't have any simple way of checking whether a risky PHV would
291-
* actually be used in the inner plan, and the case is so unusual that it
292-
* doesn't seem worth working very hard on it.
293-
*
294277
* allow_star_schema_join() returns TRUE if the param_source_rels restriction
295278
* should be overridden, ie, it's okay to perform this join.
296279
*/
297-
staticbool
280+
staticinlinebool
298281
allow_star_schema_join(PlannerInfo*root,
299282
Path*outer_path,
300283
Path*inner_path)
301284
{
302285
Relidsinnerparams=PATH_REQ_OUTER(inner_path);
303286
Relidsouterrelids=outer_path->parent->relids;
304-
ListCell*lc;
305287

306288
/*
307-
* It'snota star-schema caseunless the outer rel provides some but not
308-
*all ofthe inner rel's parameterization.
289+
* It's a star-schema caseif the outer rel provides some but not all of
290+
* the inner rel's parameterization.
309291
*/
310-
if (!(bms_overlap(innerparams,outerrelids)&&
311-
bms_nonempty_difference(innerparams,outerrelids)))
312-
return false;
292+
return (bms_overlap(innerparams,outerrelids)&&
293+
bms_nonempty_difference(innerparams,outerrelids));
294+
}
295+
296+
/*
297+
* There's a pitfall for creating parameterized nestloops: suppose the inner
298+
* rel (call it A) has a parameter that is a PlaceHolderVar, and that PHV's
299+
* minimum eval_at set includes the outer rel (B) and some third rel (C).
300+
* We might think we could create a B/A nestloop join that's parameterized by
301+
* C. But we would end up with a plan in which the PHV's expression has to be
302+
* evaluated as a nestloop parameter at the B/A join; and the executor is only
303+
* set up to handle simple Vars as NestLoopParams. Rather than add complexity
304+
* and overhead to the executor for such corner cases, it seems better to
305+
* forbid the join. (Note that existence of such a PHV probably means there
306+
* is a join order constraint that will cause us to consider joining B and C
307+
* directly; so we can still make use of A's parameterized path with B+C.)
308+
* So we check whether any PHVs used in the query could pose such a hazard.
309+
* We don't have any simple way of checking whether a risky PHV would actually
310+
* be used in the inner plan, and the case is so unusual that it doesn't seem
311+
* worth working very hard on it.
312+
*
313+
* This case can occur whether or not the join's remaining parameterization
314+
* overlaps param_source_rels, so we have to check for it separately from
315+
* allow_star_schema_join, even though it looks much like a star-schema case.
316+
*/
317+
staticinlinebool
318+
check_hazardous_phv(PlannerInfo*root,
319+
Path*outer_path,
320+
Path*inner_path)
321+
{
322+
Relidsinnerparams=PATH_REQ_OUTER(inner_path);
323+
Relidsouterrelids=outer_path->parent->relids;
324+
ListCell*lc;
313325

314-
/* Check for hazardous PHVs */
315326
foreach(lc,root->placeholder_list)
316327
{
317328
PlaceHolderInfo*phinfo= (PlaceHolderInfo*)lfirst(lc);
@@ -354,13 +365,15 @@ try_nestloop_path(PlannerInfo *root,
354365
/*
355366
* Check to see if proposed path is still parameterized, and reject if the
356367
* parameterization wouldn't be sensible --- unless allow_star_schema_join
357-
* says to allow it anyway.
368+
* says to allow it anyway. Also, we must reject if check_hazardous_phv
369+
* doesn't like the look of it.
358370
*/
359371
required_outer=calc_nestloop_required_outer(outer_path,
360372
inner_path);
361373
if (required_outer&&
362-
!bms_overlap(required_outer,param_source_rels)&&
363-
!allow_star_schema_join(root,outer_path,inner_path))
374+
((!bms_overlap(required_outer,param_source_rels)&&
375+
!allow_star_schema_join(root,outer_path,inner_path))||
376+
!check_hazardous_phv(root,outer_path,inner_path)))
364377
{
365378
/* Waste no memory when we reject a path here */
366379
bms_free(required_outer);

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2960,6 +2960,26 @@ where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
29602960
11 | WFAAAA | 3 | LKIAAA
29612961
(1 row)
29622962

2963+
-- variant that isn't quite a star-schema case
2964+
select ss1.d1 from
2965+
tenk1 as t1
2966+
inner join tenk1 as t2
2967+
on t1.tenthous = t2.ten
2968+
inner join
2969+
int8_tbl as i8
2970+
left join int4_tbl as i4
2971+
inner join (select 64::information_schema.cardinal_number as d1
2972+
from tenk1 t3,
2973+
lateral (select abs(t3.unique1) + random()) ss0(x)
2974+
where t3.fivethous < 0) as ss1
2975+
on i4.f1 = ss1.d1
2976+
on i8.q1 = i4.f1
2977+
on t1.tenthous = ss1.d1
2978+
where t1.unique1 < i4.f1;
2979+
d1
2980+
----
2981+
(0 rows)
2982+
29632983
--
29642984
-- test extraction of restriction OR clauses from join OR clause
29652985
-- (we used to only do this for indexable clauses)

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -858,6 +858,24 @@ select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
858858
on (subq1.y1=t2.unique1)
859859
wheret1.unique2<42andt1.stringu1>t2.stringu2;
860860

861+
-- variant that isn't quite a star-schema case
862+
863+
selectss1.d1from
864+
tenk1as t1
865+
inner join tenk1as t2
866+
ont1.tenthous=t2.ten
867+
inner join
868+
int8_tblas i8
869+
left join int4_tblas i4
870+
inner join (select64::information_schema.cardinal_numberas d1
871+
from tenk1 t3,
872+
lateral (select abs(t3.unique1)+ random()) ss0(x)
873+
wheret3.fivethous<0)as ss1
874+
oni4.f1=ss1.d1
875+
oni8.q1=i4.f1
876+
ont1.tenthous=ss1.d1
877+
wheret1.unique1<i4.f1;
878+
861879
--
862880
-- test extraction of restriction OR clauses from join OR clause
863881
-- (we used to only do this for indexable clauses)

0 commit comments

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