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

Commite8638d7

Browse files
committed
Fix planner error with multiple copies of an AlternativeSubPlan.
It's possible for us to copy an AlternativeSubPlan expression nodeinto multiple places, for example the scan quals of severalpartition children. Then it's possible that we choose a differentone of the alternatives as optimal in each place. Commit41efb83failed to consider this scenario, so its attempt to remove "unused"subplans could remove subplans that were still used elsewhere.Fix by delaying the removal logic until we've examined all theAlternativeSubPlans in a given query level. (This does assume thatAlternativeSubPlans couldn't get copied to other query levels, butfor the foreseeable future that's fine; cf qual_is_pushdown_safe.)Per report from Rajkumar Raghuwanshi. Back-patch to v14where the faulty logic came in.Discussion:https://postgr.es/m/CAKcux6==O3NNZC3bZ2prRYv3cjm3_Zw1GfzmOjEVqYN4jub2+Q@mail.gmail.com
1 parentbdeb2c4 commite8638d7

File tree

4 files changed

+103
-24
lines changed

4 files changed

+103
-24
lines changed

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

Lines changed: 47 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -249,6 +249,7 @@ static List *set_returning_clause_references(PlannerInfo *root,
249249
Plan*
250250
set_plan_references(PlannerInfo*root,Plan*plan)
251251
{
252+
Plan*result;
252253
PlannerGlobal*glob=root->glob;
253254
intrtoffset=list_length(glob->finalrtable);
254255
ListCell*lc;
@@ -301,8 +302,44 @@ set_plan_references(PlannerInfo *root, Plan *plan)
301302
glob->appendRelations=lappend(glob->appendRelations,appinfo);
302303
}
303304

305+
/* If needed, create workspace for processing AlternativeSubPlans */
306+
if (root->hasAlternativeSubPlans)
307+
{
308+
root->isAltSubplan= (bool*)
309+
palloc0(list_length(glob->subplans)*sizeof(bool));
310+
root->isUsedSubplan= (bool*)
311+
palloc0(list_length(glob->subplans)*sizeof(bool));
312+
}
313+
304314
/* Now fix the Plan tree */
305-
returnset_plan_refs(root,plan,rtoffset);
315+
result=set_plan_refs(root,plan,rtoffset);
316+
317+
/*
318+
* If we have AlternativeSubPlans, it is likely that we now have some
319+
* unreferenced subplans in glob->subplans. To avoid expending cycles on
320+
* those subplans later, get rid of them by setting those list entries to
321+
* NULL. (Note: we can't do this immediately upon processing an
322+
* AlternativeSubPlan, because there may be multiple copies of the
323+
* AlternativeSubPlan, and they can get resolved differently.)
324+
*/
325+
if (root->hasAlternativeSubPlans)
326+
{
327+
foreach(lc,glob->subplans)
328+
{
329+
intndx=foreach_current_index(lc);
330+
331+
/*
332+
* If it was used by some AlternativeSubPlan in this query level,
333+
* but wasn't selected as best by any AlternativeSubPlan, then we
334+
* don't need it. Do not touch subplans that aren't parts of
335+
* AlternativeSubPlans.
336+
*/
337+
if (root->isAltSubplan[ndx]&& !root->isUsedSubplan[ndx])
338+
lfirst(lc)=NULL;
339+
}
340+
}
341+
342+
returnresult;
306343
}
307344

308345
/*
@@ -1765,8 +1802,7 @@ fix_param_node(PlannerInfo *root, Param *p)
17651802
* Note: caller must still recurse into the result!
17661803
*
17671804
* We don't make any attempt to fix up cost estimates in the parent plan
1768-
* node or higher-level nodes. However, we do remove the rejected subplan(s)
1769-
* from root->glob->subplans, to minimize cycles expended on them later.
1805+
* node or higher-level nodes.
17701806
*/
17711807
staticNode*
17721808
fix_alternative_subplan(PlannerInfo*root,AlternativeSubPlan*asplan,
@@ -1778,9 +1814,8 @@ fix_alternative_subplan(PlannerInfo *root, AlternativeSubPlan *asplan,
17781814

17791815
/*
17801816
* Compute the estimated cost of each subplan assuming num_exec
1781-
* executions, and keep the cheapest one. Replace discarded subplans with
1782-
* NULL pointers in the global subplans list. In event of exact equality
1783-
* of estimates, we prefer the later plan; this is a bit arbitrary, but in
1817+
* executions, and keep the cheapest one. In event of exact equality of
1818+
* estimates, we prefer the later plan; this is a bit arbitrary, but in
17841819
* current usage it biases us to break ties against fast-start subplans.
17851820
*/
17861821
Assert(asplan->subplans!=NIL);
@@ -1791,31 +1826,19 @@ fix_alternative_subplan(PlannerInfo *root, AlternativeSubPlan *asplan,
17911826
Costcurcost;
17921827

17931828
curcost=curplan->startup_cost+num_exec*curplan->per_call_cost;
1794-
if (bestplan==NULL)
1795-
{
1796-
bestplan=curplan;
1797-
bestcost=curcost;
1798-
}
1799-
elseif (curcost <=bestcost)
1829+
if (bestplan==NULL||curcost <=bestcost)
18001830
{
1801-
/* drop old bestplan */
1802-
ListCell*lc2=list_nth_cell(root->glob->subplans,
1803-
bestplan->plan_id-1);
1804-
1805-
lfirst(lc2)=NULL;
18061831
bestplan=curplan;
18071832
bestcost=curcost;
18081833
}
1809-
else
1810-
{
1811-
/* drop curplan */
1812-
ListCell*lc2=list_nth_cell(root->glob->subplans,
1813-
curplan->plan_id-1);
18141834

1815-
lfirst(lc2)=NULL;
1816-
}
1835+
/* Also mark all subplans that are in AlternativeSubPlans */
1836+
root->isAltSubplan[curplan->plan_id-1]= true;
18171837
}
18181838

1839+
/* Mark the subplan we selected */
1840+
root->isUsedSubplan[bestplan->plan_id-1]= true;
1841+
18191842
return (Node*)bestplan;
18201843
}
18211844

‎src/include/nodes/pathnodes.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -368,6 +368,10 @@ struct PlannerInfo
368368
RelidscurOuterRels;/* outer rels above current node */
369369
List*curOuterParams;/* not-yet-assigned NestLoopParams */
370370

371+
/* These fields are workspace for setrefs.c */
372+
bool*isAltSubplan;/* array corresponding to glob->subplans */
373+
bool*isUsedSubplan;/* array corresponding to glob->subplans */
374+
371375
/* optional private data for join_search_hook, e.g., GEQO */
372376
void*join_search_private;
373377

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

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -921,6 +921,46 @@ where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
921921
10
922922
(1 row)
923923

924+
-- It's possible for the same EXISTS to get resolved both ways
925+
create temp table exists_tbl (c1 int, c2 int, c3 int) partition by list (c1);
926+
create temp table exists_tbl_null partition of exists_tbl for values in (null);
927+
create temp table exists_tbl_def partition of exists_tbl default;
928+
insert into exists_tbl select x, x/2, x+1 from generate_series(0,10) x;
929+
analyze exists_tbl;
930+
explain (costs off)
931+
select * from exists_tbl t1
932+
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
933+
QUERY PLAN
934+
------------------------------------------------------
935+
Append
936+
-> Seq Scan on exists_tbl_null t1_1
937+
Filter: ((SubPlan 1) OR (c3 < 0))
938+
SubPlan 1
939+
-> Append
940+
-> Seq Scan on exists_tbl_null t2_1
941+
Filter: (t1_1.c1 = c2)
942+
-> Seq Scan on exists_tbl_def t2_2
943+
Filter: (t1_1.c1 = c2)
944+
-> Seq Scan on exists_tbl_def t1_2
945+
Filter: ((hashed SubPlan 2) OR (c3 < 0))
946+
SubPlan 2
947+
-> Append
948+
-> Seq Scan on exists_tbl_null t2_4
949+
-> Seq Scan on exists_tbl_def t2_5
950+
(15 rows)
951+
952+
select * from exists_tbl t1
953+
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
954+
c1 | c2 | c3
955+
----+----+----
956+
0 | 0 | 1
957+
1 | 0 | 2
958+
2 | 1 | 3
959+
3 | 1 | 4
960+
4 | 2 | 5
961+
5 | 2 | 6
962+
(6 rows)
963+
924964
--
925965
-- Test case for planner bug with nested EXISTS handling
926966
--

‎src/test/regress/sql/subselect.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -526,6 +526,18 @@ select count(*) from tenk1 t
526526
where (exists(select1from tenk1 kwherek.unique1=t.unique2)or ten<0)
527527
and thousand=1;
528528

529+
-- It's possible for the same EXISTS to get resolved both ways
530+
create temp table exists_tbl (c1int, c2int, c3int) partition by list (c1);
531+
create temp table exists_tbl_null partition of exists_tbl forvaluesin (null);
532+
create temp table exists_tbl_def partition of exists_tbl default;
533+
insert into exists_tblselect x, x/2, x+1from generate_series(0,10) x;
534+
analyze exists_tbl;
535+
explain (costs off)
536+
select*from exists_tbl t1
537+
where (exists(select1from exists_tbl t2wheret1.c1=t2.c2)or c3<0);
538+
select*from exists_tbl t1
539+
where (exists(select1from exists_tbl t2wheret1.c1=t2.c2)or c3<0);
540+
529541
--
530542
-- Test case for planner bug with nested EXISTS handling
531543
--

0 commit comments

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