Consider parent's stats for set_append_rel_size.
Hi:
I would talk about the impact of init partition prune for
set_append_rel_size.
and create_append_path. Finally I just want to focus on set_append_rel_size
only in this thread.
Given the below example:
CREATE TABLE P (part_key int, v int) PARTITION BY RANGE (part_key);
CREATE TABLE p_1 PARTITION OF p FOR VALUES FROM (0) TO (10);
CREATE TABLE p_2 PARTITION OF p FOR VALUES FROM (10) TO (20);
CREATE TABLE p_3 PARTITION OF p FOR VALUES FROM (20) TO (30);
INSERT INTO p SELECT i % 30, i FROM generate_series(1, 300)i;
set plan_cache_mode to force_generic_plan ;
prepare s as select * from p where part_key = $1;
explain analyze execute s(2);
Then we will get estimated RelOptInfo.rows = 30, but actually it is 10 rows.
explain analyze execute s(2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Append (cost=0.00..6.90 rows=30 width=8) (actual time=0.019..0.042
rows=10 loops=1)
Subplans Removed: 2
-> Seq Scan on p_1 (cost=0.00..2.25 rows=10 width=8) (actual
time=0.017..0.038 rows=10 loops=1)
Filter: (part_key = $1)
Rows Removed by Filter: 90
Planning Time: 0.885 ms
Execution Time: 0.156 ms
(7 rows)
Actually there are 2 issues here. one is RelOptInfo->rows which is set by
set_append_rel_size, the other one appendPath->path.rows is set at
create_append_path. They are two independent data. (When we estimate
the rows of a joinrel, we only consider the RelOptInfo.rows rather than
Path.rows).
In set_append_rel_size, it pushes the quals to each child relation and does
a sum of
each child->rows. child's stats works better than parent stats if we know
exactly which
partitions we would access. But this strategy fails when init prune comes as
above.
So I think considering parent's stats for init prune case might be a good
solution (Ashutosh has mentioned global stats for this a long time
ago[1]/messages/by-id/CAExHW5t5Q7JuUW28QMRO7szuHcbsfx4M9=WL+up40h3PCd7dXw@mail.gmail.com). So I want
to refactor the code like this:
a). should_use_parent_stats(..); Decides which stats we should use for an
AppendRel.
b). set_append_rel_size_locally: Just do what we currently do.
c). set_append_rel_size_globally: We calculate the quals selectivity on
AppendRel level, and set the rows with AppendRel->tuples * sel.
More about should_use_parent_stats function:
1. If there are no quals for initial partition prune, we use child's stats.
2. If we have quals for initial partition prune, and the left op is not
used in
planning time prune, we use parent's stats. For example: (part_key = 2
and
part_key > $1);
However when I was coding it, I found out that finding "quals for initial
partition prune"
is not so easy. So I doubt if we need the troubles to decide which method
to use. Attached is just the PoC version which will use parent's stats
all the time.
Author: δΈζ <yizhi.fzh@alibaba-inc.com>
Date: Sun Apr 18 22:02:54 2021 +0800
Currently the set_append_rel_size doesn't consider the init partition
prune, so the estimated size may be wrong at a big scale sometimes.
In this patch I used the set the rows = parentrel->tuples *
clauseselecitivty. In this case we can loss some accuracy when the
initial
partition prune doesn't happen at all. but generally I think it would
be OK.
Another strategy is we should check if init partition prune can happen.
if we are sure about that, we adapt the above way. or else we can use
the local stats strategy still.
[1]: /messages/by-id/CAExHW5t5Q7JuUW28QMRO7szuHcbsfx4M9=WL+up40h3PCd7dXw@mail.gmail.com
/messages/by-id/CAExHW5t5Q7JuUW28QMRO7szuHcbsfx4M9=WL+up40h3PCd7dXw@mail.gmail.com
--
Best Regards
Andy Fan (https://www.aliyun.com/)
Attachments:
v1-0001-Currently-the-set_append_rel_size-doesn-t-conside.patchapplication/octet-stream; name=v1-0001-Currently-the-set_append_rel_size-doesn-t-conside.patchDownload
From 68f7b19a479319014a6ba39219fed524fc45ad34 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 18 Apr 2021 22:02:54 +0800
Subject: [PATCH v1] Currently the set_append_rel_size doesn't consider the
init partition
prune, so the estimated size may be wrong at a big scale sometimes.
In this patch I used the set the rows = parentrel->tuples *
clauseselecitivty. In this case we can loss some lose some accuracy when initial
partition prune doesn't happen at all. but generally I think it would be OK.
Another strategy is we should check if init partiton prune can happen.
if we are sure about that, we adapt the above way. or else we can use
the local stats strategy still.
---
src/backend/optimizer/path/allpaths.c | 13 ++-
src/backend/optimizer/util/plancat.c | 12 +--
src/test/regress/expected/partition_join.out | 14 +--
src/test/regress/expected/select_parallel.out | 92 +++++++++----------
4 files changed, 68 insertions(+), 63 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index edba5e49a8..31698370af 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1178,7 +1178,15 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
int i;
Assert(parent_rows > 0);
- rel->rows = parent_rows;
+ if (rel->tuples > 0)
+ rel->rows = clamp_row_est(rel->tuples * clauselist_selectivity(root,
+ rel->baserestrictinfo,
+ rel->relid,
+ JOIN_INNER,
+ NULL));
+ else
+ rel->rows = parent_rows;
+
rel->reltarget->width = rint(parent_size / parent_rows);
for (i = 0; i < nattrs; i++)
rel->attr_widths[i] = rint(parent_attrsizes[i] / parent_rows);
@@ -1187,7 +1195,8 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
* Set "raw tuples" count equal to "rows" for the appendrel; needed
* because some places assume rel->tuples is valid for any baserel.
*/
- rel->tuples = parent_rows;
+ if (rel->tuples == 0)
+ rel->tuples = parent_rows;
/*
* Note that we leave rel->pages as zero; this is important to avoid
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 345c7425f6..2aa2da0c66 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -143,13 +143,10 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
palloc0((rel->max_attr - rel->min_attr + 1) * sizeof(int32));
/*
- * Estimate relation size --- unless it's an inheritance parent, in which
- * case the size we want is not the rel's own size but the size of its
- * inheritance tree. That will be computed in set_append_rel_size().
+ * Estimate relation size.
*/
- if (!inhparent)
- estimate_rel_size(relation, rel->attr_widths - rel->min_attr,
- &rel->pages, &rel->tuples, &rel->allvisfrac);
+ estimate_rel_size(relation, rel->attr_widths - rel->min_attr,
+ &rel->pages, &rel->tuples, &rel->allvisfrac);
/* Retrieve the parallel_workers reloption, or -1 if not set. */
rel->rel_parallel_workers = RelationGetParallelWorkers(relation, -1);
@@ -1079,6 +1076,9 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
*tuples = rel->rd_rel->reltuples;
*allvisfrac = 0;
break;
+ case RELKIND_PARTITIONED_TABLE:
+ *tuples = rel->rd_rel->reltuples;
+ break;
default:
/* else it has no disk storage; probably shouldn't get here? */
*pages = 0;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 27f7525b3e..d8243ed6e6 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2120,17 +2120,17 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOI
QUERY PLAN
----------------------------------------------------------
Hash Join
- Hash Cond: (t2.c = (t1.c)::text)
+ Hash Cond: (t3.c = (t1.c)::text)
-> Append
- -> Seq Scan on prt2_n_p1 t2_1
- -> Seq Scan on prt2_n_p2 t2_2
+ -> Seq Scan on plt1_p1 t3_1
+ -> Seq Scan on plt1_p2 t3_2
+ -> Seq Scan on plt1_p3 t3_3
-> Hash
-> Hash Join
- Hash Cond: (t3.c = (t1.c)::text)
+ Hash Cond: (t2.c = (t1.c)::text)
-> Append
- -> Seq Scan on plt1_p1 t3_1
- -> Seq Scan on plt1_p2 t3_2
- -> Seq Scan on plt1_p3 t3_3
+ -> Seq Scan on prt2_n_p1 t2_1
+ -> Seq Scan on prt2_n_p2 t2_2
-> Hash
-> Append
-> Seq Scan on prt1_n_p1 t1_1
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 05ebcb284a..599e0399cc 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -12,20 +12,19 @@ set max_parallel_workers_per_gather=4;
-- Parallel Append with partial-subplans
explain (costs off)
select round(avg(aa)), sum(aa) from a_star;
- QUERY PLAN
---------------------------------------------------------------
- Finalize Aggregate
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
-> Gather
Workers Planned: 3
- -> Partial Aggregate
- -> Parallel Append
- -> Parallel Seq Scan on d_star a_star_4
- -> Parallel Seq Scan on f_star a_star_6
- -> Parallel Seq Scan on e_star a_star_5
- -> Parallel Seq Scan on b_star a_star_2
- -> Parallel Seq Scan on c_star a_star_3
- -> Parallel Seq Scan on a_star a_star_1
-(11 rows)
+ -> Parallel Append
+ -> Parallel Seq Scan on d_star a_star_4
+ -> Parallel Seq Scan on f_star a_star_6
+ -> Parallel Seq Scan on e_star a_star_5
+ -> Parallel Seq Scan on b_star a_star_2
+ -> Parallel Seq Scan on c_star a_star_3
+ -> Parallel Seq Scan on a_star a_star_1
+(10 rows)
select round(avg(aa)), sum(aa) from a_star a1;
round | sum
@@ -38,20 +37,19 @@ alter table c_star set (parallel_workers = 0);
alter table d_star set (parallel_workers = 0);
explain (costs off)
select round(avg(aa)), sum(aa) from a_star;
- QUERY PLAN
---------------------------------------------------------------
- Finalize Aggregate
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
-> Gather
Workers Planned: 3
- -> Partial Aggregate
- -> Parallel Append
- -> Seq Scan on d_star a_star_4
- -> Seq Scan on c_star a_star_3
- -> Parallel Seq Scan on f_star a_star_6
- -> Parallel Seq Scan on e_star a_star_5
- -> Parallel Seq Scan on b_star a_star_2
- -> Parallel Seq Scan on a_star a_star_1
-(11 rows)
+ -> Parallel Append
+ -> Seq Scan on d_star a_star_4
+ -> Seq Scan on c_star a_star_3
+ -> Parallel Seq Scan on f_star a_star_6
+ -> Parallel Seq Scan on e_star a_star_5
+ -> Parallel Seq Scan on b_star a_star_2
+ -> Parallel Seq Scan on a_star a_star_1
+(10 rows)
select round(avg(aa)), sum(aa) from a_star a2;
round | sum
@@ -66,20 +64,19 @@ alter table e_star set (parallel_workers = 0);
alter table f_star set (parallel_workers = 0);
explain (costs off)
select round(avg(aa)), sum(aa) from a_star;
- QUERY PLAN
------------------------------------------------------
- Finalize Aggregate
+ QUERY PLAN
+-----------------------------------------------
+ Aggregate
-> Gather
Workers Planned: 3
- -> Partial Aggregate
- -> Parallel Append
- -> Seq Scan on d_star a_star_4
- -> Seq Scan on f_star a_star_6
- -> Seq Scan on e_star a_star_5
- -> Seq Scan on b_star a_star_2
- -> Seq Scan on c_star a_star_3
- -> Seq Scan on a_star a_star_1
-(11 rows)
+ -> Parallel Append
+ -> Seq Scan on d_star a_star_4
+ -> Seq Scan on f_star a_star_6
+ -> Seq Scan on e_star a_star_5
+ -> Seq Scan on b_star a_star_2
+ -> Seq Scan on c_star a_star_3
+ -> Seq Scan on a_star a_star_1
+(10 rows)
select round(avg(aa)), sum(aa) from a_star a3;
round | sum
@@ -97,20 +94,19 @@ alter table f_star reset (parallel_workers);
set enable_parallel_append to off;
explain (costs off)
select round(avg(aa)), sum(aa) from a_star;
- QUERY PLAN
---------------------------------------------------------------
- Finalize Aggregate
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
-> Gather
Workers Planned: 1
- -> Partial Aggregate
- -> Append
- -> Parallel Seq Scan on a_star a_star_1
- -> Parallel Seq Scan on b_star a_star_2
- -> Parallel Seq Scan on c_star a_star_3
- -> Parallel Seq Scan on d_star a_star_4
- -> Parallel Seq Scan on e_star a_star_5
- -> Parallel Seq Scan on f_star a_star_6
-(11 rows)
+ -> Append
+ -> Parallel Seq Scan on a_star a_star_1
+ -> Parallel Seq Scan on b_star a_star_2
+ -> Parallel Seq Scan on c_star a_star_3
+ -> Parallel Seq Scan on d_star a_star_4
+ -> Parallel Seq Scan on e_star a_star_5
+ -> Parallel Seq Scan on f_star a_star_6
+(10 rows)
select round(avg(aa)), sum(aa) from a_star a4;
round | sum
--
2.21.0