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

On Thu, Mar 4, 2021 at 9:51 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>
>
>>
>> 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.
>

The patch does not apply on Head anymore, could you rebase and post a
patch. I'm changing the status to "Waiting for Author".

Regards,
Vignesh