Consider parent's stats for set_append_rel_size. - Mailing list pgsql-hackers
| From | Andy Fan |
|---|---|
| Subject | Consider parent's stats for set_append_rel_size. |
| Date | |
| Msg-id | CAKU4AWrayQ-7FSNv7dkWBFfnEmMv34erpa6oOVUvge1C+WyYWw@mail.gmail.com Whole thread Raw |
| List | pgsql-hackers |
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_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]). 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);
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]). 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.
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.
Attachment
pgsql-hackers by date: