> > 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/)
v1-0001-adjust-cost-model-for-partition-prune-case.patch
Description: Binary data
