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

Commit34c9e45

Browse files
committed
Improve performance of partition pruning remapping a little.
ExecFindInitialMatchingSubPlans has to update the PartitionPruneState'ssubplan mapping data to account for the removal of subplans it prunes.However, that's only necessary if run-time pruning will also occur,so we can skip it when that won't happen, which should result in notneeding to do the remapping in many cases. (We now need it only whensome partitions are potentially startup-time prunable and others arepotentially run-time prunable, which seems like an unusual case.)Also make some marginal performance improvements in the remappingitself. These will mainly win if most partitions got pruned bythe startup-time pruning, which is perhaps a debatable assumptionin this context.Also fix some bogus comments, and rearrange code to marginallyreduce space consumption in the executor's query-lifespan context.David Rowley, reviewed by Yoshikazu ImaiDiscussion:https://postgr.es/m/CAKJS1f9+m6-di-zyy4B4AGn0y1B9F8UKDRigtBbNviXOkuyOpw@mail.gmail.com
1 parent74514bd commit34c9e45

File tree

3 files changed

+138
-39
lines changed

3 files changed

+138
-39
lines changed

‎src/backend/executor/execPartition.c

Lines changed: 60 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -1596,10 +1596,13 @@ ExecCreatePartitionPruneState(PlanState *planstate,
15961596
/*
15971597
* ExecFindInitialMatchingSubPlans
15981598
*Identify the set of subplans that cannot be eliminated by initial
1599-
*pruning (disregarding any pruning constraints involving PARAM_EXEC
1600-
*Params). Also re-map the translation matrix which allows conversion
1601-
*of partition indexes into subplan indexes to account for the unneeded
1602-
*subplans having been removed.
1599+
*pruning, disregarding any pruning constraints involving PARAM_EXEC
1600+
*Params.
1601+
*
1602+
* If additional pruning passes will be required (because of PARAM_EXEC
1603+
* Params), we must also update the translation data that allows conversion
1604+
* of partition indexes into subplan indexes to account for the unneeded
1605+
* subplans having been removed.
16031606
*
16041607
* Must only be called once per 'prunestate', and only if initial pruning
16051608
* is required.
@@ -1613,17 +1616,18 @@ ExecFindInitialMatchingSubPlans(PartitionPruneState *prunestate, int nsubplans)
16131616
MemoryContextoldcontext;
16141617
inti;
16151618

1619+
/* Caller error if we get here without do_initial_prune */
16161620
Assert(prunestate->do_initial_prune);
16171621

16181622
/*
16191623
* Switch to a temp context to avoid leaking memory in the executor's
1620-
* memory context.
1624+
*query-lifespanmemory context.
16211625
*/
16221626
oldcontext=MemoryContextSwitchTo(prunestate->prune_context);
16231627

16241628
/*
1625-
* For each hierarchy, do the pruning tests, and adddeletable subplans'
1626-
* indexes to "result".
1629+
* For each hierarchy, do the pruning tests, and addnondeletable
1630+
*subplans'indexes to "result".
16271631
*/
16281632
for (i=0;i<prunestate->num_partprunedata;i++)
16291633
{
@@ -1640,22 +1644,27 @@ ExecFindInitialMatchingSubPlans(PartitionPruneState *prunestate, int nsubplans)
16401644
ResetExprContext(pprune->context.planstate->ps_ExprContext);
16411645
}
16421646

1647+
/* Add in any subplans that partition pruning didn't account for */
1648+
result=bms_add_members(result,prunestate->other_subplans);
1649+
16431650
MemoryContextSwitchTo(oldcontext);
16441651

16451652
/* Copy result out of the temp context before we reset it */
16461653
result=bms_copy(result);
16471654

1648-
/* Add in any subplans that partition pruning didn't account for */
1649-
result=bms_add_members(result,prunestate->other_subplans);
1650-
16511655
MemoryContextReset(prunestate->prune_context);
16521656

16531657
/*
1654-
* If any subplans were pruned, we must re-sequence the subplan indexes so
1655-
* that ExecFindMatchingSubPlans properly returns the indexes from the
1656-
* subplans which will remain after execution of this function.
1658+
* If exec-time pruning is required and we pruned subplans above, then we
1659+
* must re-sequence the subplan indexes so that ExecFindMatchingSubPlans
1660+
* properly returns the indexes from the subplans which will remain after
1661+
* execution of this function.
1662+
*
1663+
* We can safely skip this when !do_exec_prune, even though that leaves
1664+
* invalid data in prunestate, because that data won't be consulted again
1665+
* (cf initial Assert in ExecFindMatchingSubPlans).
16571666
*/
1658-
if (bms_num_members(result)<nsubplans)
1667+
if (prunestate->do_exec_prune&&bms_num_members(result)<nsubplans)
16591668
{
16601669
int*new_subplan_indexes;
16611670
Bitmapset*new_other_subplans;
@@ -1664,25 +1673,17 @@ ExecFindInitialMatchingSubPlans(PartitionPruneState *prunestate, int nsubplans)
16641673

16651674
/*
16661675
* First we must build a temporary array which maps old subplan
1667-
* indexes to new ones.While we're at it, also recompute the
1668-
*other_subplans set, sinceindexes init may change.
1676+
* indexes to new ones.For convenience of initialization, we use
1677+
*1-basedindexes inthis array and leave pruned items as 0.
16691678
*/
1670-
new_subplan_indexes= (int*)palloc(sizeof(int)*nsubplans);
1671-
new_other_subplans=NULL;
1672-
newidx=0;
1673-
for (i=0;i<nsubplans;i++)
1679+
new_subplan_indexes= (int*)palloc0(sizeof(int)*nsubplans);
1680+
newidx=1;
1681+
i=-1;
1682+
while ((i=bms_next_member(result,i)) >=0)
16741683
{
1675-
if (bms_is_member(i,result))
1676-
new_subplan_indexes[i]=newidx++;
1677-
else
1678-
new_subplan_indexes[i]=-1;/* Newly pruned */
1679-
1680-
if (bms_is_member(i,prunestate->other_subplans))
1681-
new_other_subplans=bms_add_member(new_other_subplans,
1682-
new_subplan_indexes[i]);
1684+
Assert(i<nsubplans);
1685+
new_subplan_indexes[i]=newidx++;
16831686
}
1684-
bms_free(prunestate->other_subplans);
1685-
prunestate->other_subplans=new_other_subplans;
16861687

16871688
/*
16881689
* Now we can update each PartitionedRelPruneInfo's subplan_map with
@@ -1699,7 +1700,7 @@ ExecFindInitialMatchingSubPlans(PartitionPruneState *prunestate, int nsubplans)
16991700
* order so that we determine present_parts for the lowest-level
17001701
* partitioned tables first. This way we can tell whether a
17011702
* sub-partitioned table's partitions were entirely pruned so we
1702-
* can excludethat from'present_parts'.
1703+
* can excludeit fromthe current level'spresent_parts.
17031704
*/
17041705
for (j=prunedata->num_partrelprunedata-1;j >=0;j--)
17051706
{
@@ -1728,9 +1729,9 @@ ExecFindInitialMatchingSubPlans(PartitionPruneState *prunestate, int nsubplans)
17281729
if (oldidx >=0)
17291730
{
17301731
Assert(oldidx<nsubplans);
1731-
pprune->subplan_map[k]=new_subplan_indexes[oldidx];
1732+
pprune->subplan_map[k]=new_subplan_indexes[oldidx]-1;
17321733

1733-
if (new_subplan_indexes[oldidx] >=0)
1734+
if (new_subplan_indexes[oldidx]>0)
17341735
pprune->present_parts=
17351736
bms_add_member(pprune->present_parts,k);
17361737
}
@@ -1748,6 +1749,19 @@ ExecFindInitialMatchingSubPlans(PartitionPruneState *prunestate, int nsubplans)
17481749
}
17491750
}
17501751

1752+
/*
1753+
* We must also recompute the other_subplans set, since indexes in it
1754+
* may change.
1755+
*/
1756+
new_other_subplans=NULL;
1757+
i=-1;
1758+
while ((i=bms_next_member(prunestate->other_subplans,i)) >=0)
1759+
new_other_subplans=bms_add_member(new_other_subplans,
1760+
new_subplan_indexes[i]-1);
1761+
1762+
bms_free(prunestate->other_subplans);
1763+
prunestate->other_subplans=new_other_subplans;
1764+
17511765
pfree(new_subplan_indexes);
17521766
}
17531767

@@ -1768,15 +1782,22 @@ ExecFindMatchingSubPlans(PartitionPruneState *prunestate)
17681782
MemoryContextoldcontext;
17691783
inti;
17701784

1785+
/*
1786+
* If !do_exec_prune, we've got problems because
1787+
* ExecFindInitialMatchingSubPlans will not have bothered to update
1788+
* prunestate for whatever pruning it did.
1789+
*/
1790+
Assert(prunestate->do_exec_prune);
1791+
17711792
/*
17721793
* Switch to a temp context to avoid leaking memory in the executor's
1773-
* memory context.
1794+
*query-lifespanmemory context.
17741795
*/
17751796
oldcontext=MemoryContextSwitchTo(prunestate->prune_context);
17761797

17771798
/*
1778-
* For each hierarchy, do the pruning tests, and adddeletable subplans'
1779-
* indexes to "result".
1799+
* For each hierarchy, do the pruning tests, and addnondeletable
1800+
*subplans'indexes to "result".
17801801
*/
17811802
for (i=0;i<prunestate->num_partprunedata;i++)
17821803
{
@@ -1792,14 +1813,14 @@ ExecFindMatchingSubPlans(PartitionPruneState *prunestate)
17921813
ResetExprContext(pprune->context.planstate->ps_ExprContext);
17931814
}
17941815

1816+
/* Add in any subplans that partition pruning didn't account for */
1817+
result=bms_add_members(result,prunestate->other_subplans);
1818+
17951819
MemoryContextSwitchTo(oldcontext);
17961820

17971821
/* Copy result out of the temp context before we reset it */
17981822
result=bms_copy(result);
17991823

1800-
/* Add in any subplans that partition pruning didn't account for */
1801-
result=bms_add_members(result,prunestate->other_subplans);
1802-
18031824
MemoryContextReset(prunestate->prune_context);
18041825

18051826
returnresult;

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2472,11 +2472,61 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s
24722472
Filter: (b = $0)
24732473
(39 rows)
24742474

2475+
-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
2476+
create table xy_1 (x int, y int);
2477+
insert into xy_1 values(100,-10);
2478+
set enable_bitmapscan = 0;
2479+
set enable_indexscan = 0;
2480+
set plan_cache_mode = 'force_generic_plan';
2481+
prepare ab_q6 as
2482+
select * from (
2483+
select tableoid::regclass,a,b from ab
2484+
union all
2485+
select tableoid::regclass,x,y from xy_1
2486+
union all
2487+
select tableoid::regclass,a,b from ab
2488+
) ab where a = $1 and b = (select -10);
2489+
-- Ensure the xy_1 subplan is not pruned.
2490+
explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
2491+
QUERY PLAN
2492+
--------------------------------------------------------
2493+
Append (actual rows=0 loops=1)
2494+
InitPlan 1 (returns $0)
2495+
-> Result (actual rows=1 loops=1)
2496+
Subplans Removed: 12
2497+
-> Seq Scan on ab_a1_b1 (never executed)
2498+
Filter: ((a = $1) AND (b = $0))
2499+
-> Seq Scan on ab_a1_b2 (never executed)
2500+
Filter: ((a = $1) AND (b = $0))
2501+
-> Seq Scan on ab_a1_b3 (never executed)
2502+
Filter: ((a = $1) AND (b = $0))
2503+
-> Seq Scan on xy_1 (actual rows=0 loops=1)
2504+
Filter: ((x = $1) AND (y = $0))
2505+
Rows Removed by Filter: 1
2506+
-> Seq Scan on ab_a1_b1 ab_a1_b1_1 (never executed)
2507+
Filter: ((a = $1) AND (b = $0))
2508+
-> Seq Scan on ab_a1_b2 ab_a1_b2_1 (never executed)
2509+
Filter: ((a = $1) AND (b = $0))
2510+
-> Seq Scan on ab_a1_b3 ab_a1_b3_1 (never executed)
2511+
Filter: ((a = $1) AND (b = $0))
2512+
(19 rows)
2513+
2514+
-- Ensure we see just the xy_1 row.
2515+
execute ab_q6(100);
2516+
tableoid | a | b
2517+
----------+-----+-----
2518+
xy_1 | 100 | -10
2519+
(1 row)
2520+
2521+
reset enable_bitmapscan;
2522+
reset enable_indexscan;
2523+
reset plan_cache_mode;
24752524
deallocate ab_q1;
24762525
deallocate ab_q2;
24772526
deallocate ab_q3;
24782527
deallocate ab_q4;
24792528
deallocate ab_q5;
2529+
deallocate ab_q6;
24802530
-- UPDATE on a partition subtree has been seen to have problems.
24812531
insert into ab values (1,2);
24822532
explain (analyze, costs off, summary off, timing off)

‎src/test/regress/sql/partition_prune.sql

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -548,11 +548,39 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
548548
explain (analyze, costs off, summary off, timing off)
549549
select*from (select*from abwhere a=1union all (values(10,5))union allselect*from ab) abwhere b= (select1);
550550

551+
-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
552+
createtablexy_1 (xint, yint);
553+
insert into xy_1values(100,-10);
554+
555+
set enable_bitmapscan=0;
556+
set enable_indexscan=0;
557+
set plan_cache_mode='force_generic_plan';
558+
559+
prepare ab_q6as
560+
select*from (
561+
select tableoid::regclass,a,bfrom ab
562+
union all
563+
select tableoid::regclass,x,yfrom xy_1
564+
union all
565+
select tableoid::regclass,a,bfrom ab
566+
) abwhere a= $1and b= (select-10);
567+
568+
-- Ensure the xy_1 subplan is not pruned.
569+
explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
570+
571+
-- Ensure we see just the xy_1 row.
572+
execute ab_q6(100);
573+
574+
reset enable_bitmapscan;
575+
reset enable_indexscan;
576+
reset plan_cache_mode;
577+
551578
deallocate ab_q1;
552579
deallocate ab_q2;
553580
deallocate ab_q3;
554581
deallocate ab_q4;
555582
deallocate ab_q5;
583+
deallocate ab_q6;
556584

557585
-- UPDATE on a partition subtree has been seen to have problems.
558586
insert into abvalues (1,2);

0 commit comments

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