Re: [Proposal] Make the optimiser aware of partitions ordering

On Fri, Sep 22, 2017 at 3:34 PM, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> PFA v3 of the patch, once again rebased and that now use all the newly
> available infrastructure.
>
> I also added a check that a sorted append can't be generated when a
> default partition has been declared.

I just took a quick look at this and was kind of surprised to see that
it didn't look much like what I would expect.

What I would expect is:

1. The PartitionDescData grows a new flag indicating whether
partitions can be regarded as being in bound order. This is true for
range partitions without a default partition, list partitions without
a default partition and without interleaved bounds, and maybe other
cases we want to worry about later. We set this flag correctly when
we build the PartitionDesc and propagate it into the RelOptInfo for
the partitioned table when we set up its other partitioning details.

2. generate_mergeappend_paths() gets renamed to
generate_sorted_append_paths(). At the top of the function, it checks
whether the above flag is set; if not, give up on this optimization.
Otherwise, figure out what the set of pathkeys that correspond to the
partition key would look like. Call these the
partition_order_pathkeys.

3. For each set of possible pathkeys, it checks whether the proposed
pathkeys are equal to (or an initial prefix of) the
partition_order_pathkeys.

4. If so, instead of calling create_merge_append_path(), it calls
create_append_path().

5. create_append_path() gets the proposed pathkeys via a new List
*pathkeys argument and sticks them on the path.

And that's it. The extensive patch does a lot of other stuff, like
whacking around the structure of AppendPath vs. MergeAppendPath, and
I'm not sure why we need or want any of that. I might be missing
something, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company