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

Commit4200a92

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 parente7293e3 commit4200a92

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
@@ -282,44 +282,55 @@ add_paths_to_joinrel(PlannerInfo *root,
282282
* across joins unless there's a join-order-constraint-based reason to do so.
283283
* So we ignore the param_source_rels restriction when this case applies.
284284
*
285-
* However, there's a pitfall: suppose the inner rel (call it A) has a
286-
* parameter that is a PlaceHolderVar, and that PHV's minimum eval_at set
287-
* includes the outer rel (B) and some third rel (C). If we treat this as a
288-
* star-schema case and create a B/A nestloop join that's parameterized by C,
289-
* we would end up with a plan in which the PHV's expression has to be
290-
* evaluated as a nestloop parameter at the B/A join; and the executor is only
291-
* set up to handle simple Vars as NestLoopParams. Rather than add complexity
292-
* and overhead to the executor for such corner cases, it seems better to
293-
* forbid the join. (Note that existence of such a PHV probably means there
294-
* is a join order constraint that will cause us to consider joining B and C
295-
* directly; so we can still make use of A's parameterized path, and there is
296-
* no need for the star-schema exception.)To implement this exception to the
297-
* exception, we check whether any PHVs used in the query could pose such a
298-
* hazard. We don't have any simple way of checking whether a risky PHV would
299-
* actually be used in the inner plan, and the case is so unusual that it
300-
* doesn't seem worth working very hard on it.
301-
*
302285
* allow_star_schema_join() returns TRUE if the param_source_rels restriction
303286
* should be overridden, ie, it's okay to perform this join.
304287
*/
305-
staticbool
288+
staticinlinebool
306289
allow_star_schema_join(PlannerInfo*root,
307290
Path*outer_path,
308291
Path*inner_path)
309292
{
310293
Relidsinnerparams=PATH_REQ_OUTER(inner_path);
311294
Relidsouterrelids=outer_path->parent->relids;
312-
ListCell*lc;
313295

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

322-
/* Check for hazardous PHVs */
323334
foreach(lc,root->placeholder_list)
324335
{
325336
PlaceHolderInfo*phinfo= (PlaceHolderInfo*)lfirst(lc);
@@ -358,13 +369,15 @@ try_nestloop_path(PlannerInfo *root,
358369
/*
359370
* Check to see if proposed path is still parameterized, and reject if the
360371
* parameterization wouldn't be sensible --- unless allow_star_schema_join
361-
* says to allow it anyway.
372+
* says to allow it anyway. Also, we must reject if check_hazardous_phv
373+
* doesn't like the look of it.
362374
*/
363375
required_outer=calc_nestloop_required_outer(outer_path,
364376
inner_path);
365377
if (required_outer&&
366-
!bms_overlap(required_outer,extra->param_source_rels)&&
367-
!allow_star_schema_join(root,outer_path,inner_path))
378+
((!bms_overlap(required_outer,extra->param_source_rels)&&
379+
!allow_star_schema_join(root,outer_path,inner_path))||
380+
!check_hazardous_phv(root,outer_path,inner_path)))
368381
{
369382
/* Waste no memory when we reject a path here */
370383
bms_free(required_outer);

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

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

3003+
-- variant that isn't quite a star-schema case
3004+
select ss1.d1 from
3005+
tenk1 as t1
3006+
inner join tenk1 as t2
3007+
on t1.tenthous = t2.ten
3008+
inner join
3009+
int8_tbl as i8
3010+
left join int4_tbl as i4
3011+
inner join (select 64::information_schema.cardinal_number as d1
3012+
from tenk1 t3,
3013+
lateral (select abs(t3.unique1) + random()) ss0(x)
3014+
where t3.fivethous < 0) as ss1
3015+
on i4.f1 = ss1.d1
3016+
on i8.q1 = i4.f1
3017+
on t1.tenthous = ss1.d1
3018+
where t1.unique1 < i4.f1;
3019+
d1
3020+
----
3021+
(0 rows)
3022+
30033023
--
30043024
-- test extraction of restriction OR clauses from join OR clause
30053025
-- (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
@@ -874,6 +874,24 @@ select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
874874
on (subq1.y1=t2.unique1)
875875
wheret1.unique2<42andt1.stringu1>t2.stringu2;
876876

877+
-- variant that isn't quite a star-schema case
878+
879+
selectss1.d1from
880+
tenk1as t1
881+
inner join tenk1as t2
882+
ont1.tenthous=t2.ten
883+
inner join
884+
int8_tblas i8
885+
left join int4_tblas i4
886+
inner join (select64::information_schema.cardinal_numberas d1
887+
from tenk1 t3,
888+
lateral (select abs(t3.unique1)+ random()) ss0(x)
889+
wheret3.fivethous<0)as ss1
890+
oni4.f1=ss1.d1
891+
oni8.q1=i4.f1
892+
ont1.tenthous=ss1.d1
893+
wheret1.unique1<i4.f1;
894+
877895
--
878896
-- test extraction of restriction OR clauses from join OR clause
879897
-- (we used to only do this for indexable clauses)

0 commit comments

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