Re: Make Append Cost aware of some run time partition prune case

>
> I have implemented a new one, which only handles 1 level of partitioned
> table, and
> only 1 partition key. and only handle the eq operators like partkey = $1
> / partkey in ($1, $2)
> / parkey = $1 or partkey = $2; The patch works well in my user case. I
> can send
> one on the latest master shortly, and hope it is helpful for you as well.
>
>
Hi:

Here is the new patch for this topic, which has proved works in my limited
user
case (apply the similar logic on pg11), and it is incomplete since more
cases
should be covered but not. Uploading this patch now is just for discussing
and
testing.

Design principle:

1). the cost of AppendPath should be reduced for either init partition
prune or run
time partition prune. All of the startup_cost, total_cost, rows should be
adjusted. As for the startup_cost, if we should adjust it carefully, or
else we can
get the run_time_cost less than 0.

2). When we merge the subpath from sub-partitioned rel via
accumulate_append_subpath, currently we just merge the subpaths and discard
the
cost/rows in AppendPath. After this feature is involved, we may consider
to use
the AppendPath's cost as well during this stage.

3). When join is involved, AppendPath is not enough since the estimated
rows for
a join relation cares about rel1->rows, rel2->rows only, the Path.rows is
not
cared. so we need to adjust rel->rows as well. and only for the init
partition prune case. (appendrel->rows for planning time partition prune is
handled already).

The biggest problem of the above is I don't know how to adjust the cost for
Parallel Append Path. Currently I just ignore it, which would cause some
query
should use Parallel Append Path but not.

Something I don't want to handle really unless we can address its value.
1. Operators like >, <. Between and.
2. the uncompleted part key appeared in prunequals. Like we have partition
key (a,
b). But use just use A = 1 as restrictinfo.

The main reason I don't want to handle them are 1). it would be uncommon.
b). It introduces extra complexity. c). at last, we can't estimate it well
like partkey > $1,
what would be a prune ratio for ).

Something I don't handle so far are: 1). accumulate_append_subpath
stuff. 2). MergeAppend. 3). Multi Partition key.

--
Best Regards
Andy Fan (https://www.aliyun.com/)