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

Commitfbc0fe9

Browse files
author
Richard Guo
committed
Adjust tuples estimate for appendrels
In set_append_rel_size(), we currently set rel->tuples to rel->rowsfor an appendrel. Generally, rel->tuples is the raw number of tuplesin the relation and rel->rows is the estimated number of tuples afterthe relation's restriction clauses have been applied. Although anappendrel itself doesn't directly enforce any quals today, its childrelations may. Therefore, setting rel->tuples equal to rel->rows foran appendrel isn't always appropriate.Doing so can lead to issues in cost estimates in some cases. Forinstance, when estimating the number of distinct values from anappendrel, we would not be able to adjust the estimate based on therestriction selectivity.This patch addresses this by setting an appendrel's tuples to thetotal number of tuples accumulated from each live child, which betteraligns with reality.This is arguably a bug, but nobody has complained about that untilnow, so no back-patch.Author: Richard Guo <guofenglinux@gmail.com>Reviewed-by: Tender Wang <tndrwang@gmail.com>Reviewed-by: Alena Rybakina <a.rybakina@postgrespro.ru>Discussion:https://postgr.es/m/CAMbWs4_TG_+kVn6fjG-5GYzzukrNK57=g9eUo4gsrUG26OFawg@mail.gmail.com
1 parenta7f9585 commitfbc0fe9

File tree

3 files changed

+64
-6
lines changed

3 files changed

+64
-6
lines changed

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

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -958,6 +958,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
958958
{
959959
intparentRTindex=rti;
960960
boolhas_live_children;
961+
doubleparent_tuples;
961962
doubleparent_rows;
962963
doubleparent_size;
963964
double*parent_attrsizes;
@@ -983,6 +984,15 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
983984
/*
984985
* Initialize to compute size estimates for whole append relation.
985986
*
987+
* We handle tuples estimates by setting "tuples" to the total number of
988+
* tuples accumulated from each live child, rather than using "rows".
989+
* Although an appendrel itself doesn't directly enforce any quals, its
990+
* child relations may. Therefore, setting "tuples" equal to "rows" for
991+
* an appendrel isn't always appropriate, and can lead to inaccurate cost
992+
* estimates. For example, when estimating the number of distinct values
993+
* from an appendrel, we would be unable to adjust the estimate based on
994+
* the restriction selectivity (see estimate_num_groups).
995+
*
986996
* We handle width estimates by weighting the widths of different child
987997
* rels proportionally to their number of rows. This is sensible because
988998
* the use of width estimates is mainly to compute the total relation
@@ -995,6 +1005,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
9951005
* have zero rows and/or width, if they were excluded by constraints.
9961006
*/
9971007
has_live_children= false;
1008+
parent_tuples=0;
9981009
parent_rows=0;
9991010
parent_size=0;
10001011
nattrs=rel->max_attr-rel->min_attr+1;
@@ -1161,6 +1172,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
11611172
*/
11621173
Assert(childrel->rows>0);
11631174

1175+
parent_tuples+=childrel->tuples;
11641176
parent_rows+=childrel->rows;
11651177
parent_size+=childrel->reltarget->width*childrel->rows;
11661178

@@ -1207,17 +1219,12 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
12071219
inti;
12081220

12091221
Assert(parent_rows>0);
1222+
rel->tuples=parent_tuples;
12101223
rel->rows=parent_rows;
12111224
rel->reltarget->width=rint(parent_size /parent_rows);
12121225
for (i=0;i<nattrs;i++)
12131226
rel->attr_widths[i]=rint(parent_attrsizes[i] /parent_rows);
12141227

1215-
/*
1216-
* Set "raw tuples" count equal to "rows" for the appendrel; needed
1217-
* because some places assume rel->tuples is valid for any baserel.
1218-
*/
1219-
rel->tuples=parent_rows;
1220-
12211228
/*
12221229
* Note that we leave rel->pages as zero; this is important to avoid
12231230
* double-counting the appendrel tree in total_table_pages.

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

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3666,3 +3666,33 @@ UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20;
36663666
ERROR: no partition of relation "errtst_parent" found for row
36673667
DETAIL: Partition key of the failing row contains (partid) = (30).
36683668
DROP TABLE errtst_parent;
3669+
-- Check that we have the correct tuples estimate for an appendrel
3670+
create table tuplesest_parted (a int, b int, c float) partition by range(a);
3671+
create table tuplesest_parted1 partition of tuplesest_parted for values from (0) to (100);
3672+
create table tuplesest_parted2 partition of tuplesest_parted for values from (100) to (200);
3673+
create table tuplesest_tab (a int, b int);
3674+
insert into tuplesest_parted select i%200, i%300, i%400 from generate_series(1, 1000)i;
3675+
insert into tuplesest_tab select i, i from generate_series(1, 100)i;
3676+
analyze tuplesest_parted;
3677+
analyze tuplesest_tab;
3678+
explain (costs off)
3679+
select * from tuplesest_tab join
3680+
(select b from tuplesest_parted where c < 100 group by b) sub
3681+
on tuplesest_tab.a = sub.b;
3682+
QUERY PLAN
3683+
--------------------------------------------------------------------
3684+
Hash Join
3685+
Hash Cond: (tuplesest_parted.b = tuplesest_tab.a)
3686+
-> HashAggregate
3687+
Group Key: tuplesest_parted.b
3688+
-> Append
3689+
-> Seq Scan on tuplesest_parted1 tuplesest_parted_1
3690+
Filter: (c < '100'::double precision)
3691+
-> Seq Scan on tuplesest_parted2 tuplesest_parted_2
3692+
Filter: (c < '100'::double precision)
3693+
-> Hash
3694+
-> Seq Scan on tuplesest_tab
3695+
(11 rows)
3696+
3697+
drop table tuplesest_parted;
3698+
drop table tuplesest_tab;

‎src/test/regress/sql/inherit.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1483,3 +1483,24 @@ UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20;
14831483
UPDATE errtst_parentSET partid=30, data= data+10WHERE partid=20;
14841484

14851485
DROPTABLE errtst_parent;
1486+
1487+
-- Check that we have the correct tuples estimate for an appendrel
1488+
createtabletuplesest_parted (aint, bint, c float) partition by range(a);
1489+
createtabletuplesest_parted1 partition of tuplesest_parted forvaluesfrom (0) to (100);
1490+
createtabletuplesest_parted2 partition of tuplesest_parted forvaluesfrom (100) to (200);
1491+
1492+
createtabletuplesest_tab (aint, bint);
1493+
1494+
insert into tuplesest_partedselect i%200, i%300, i%400from generate_series(1,1000)i;
1495+
insert into tuplesest_tabselect i, ifrom generate_series(1,100)i;
1496+
1497+
analyze tuplesest_parted;
1498+
analyze tuplesest_tab;
1499+
1500+
explain (costs off)
1501+
select*from tuplesest_tabjoin
1502+
(select bfrom tuplesest_partedwhere c<100group by b) sub
1503+
ontuplesest_tab.a=sub.b;
1504+
1505+
droptable tuplesest_parted;
1506+
droptable tuplesest_tab;

0 commit comments

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