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

On vendredi 24 mars 2017 08:14:03 CEST Ashutosh Bapat wrote:
> On Mon, Mar 20, 2017 at 8:33 PM, Ronan Dunklau <ronan(dot)dunklau(at)dalibo(dot)com>
wrote:
> > On lundi 20 mars 2017 15:52:03 CET Robert Haas wrote:
> >> On Mon, Mar 20, 2017 at 6:31 AM, Ronan Dunklau <ronan(dot)dunklau(at)dalibo(dot)com>
> >
> > wrote:
> >> > With range partitioning, we guarantee that each partition contains non-
> >> > overlapping values. Since we know the range allowed for each partition,
> >> > it
> >> > is possible to sort them according to the partition key (as is done
> >> > already for looking up partitions).
> >> >
> >> > Thus, we ca generate sorted Append plans instead of MergeAppend when
> >> > sorting occurs on the partition key.
> >>
> >> Great idea. This is too late for v10 at this point, but please add it
> >> to the next CommitFest so we don't forget about it.
> >
> > I know it is too late, and thought that it was too early to add it to the
> > commitfest properly since so many design decisions should be discussed.
> > Thanks for the feedback, I added it.
>
> Thanks for working on this. I was also thinking about the same.
>
> I will try to get back to review/work with this for v11. Mean time, I
> am working on partition-wise joins [1]. In those patches, I have added
> a structure called PartitionScheme, which represents how a relation is
> partitioned. For base relations it's mostly copy of PartitionDesc and
> PartitionKey, but then it bubbles up across join, with each
> partitioned join getting relevant partitioning scheme. If you could
> base your design such that is uses PartitionScheme, it could be used
> for joins and probably when Jeevan's patch for partition-wise
> aggregate [2] comes along, it can be used with grouping.
>
> [1]
> https://www.postgresql.org/message-id/CAFjFpRcMWwepj-Do1otxQ-GApGPSZ1FmH7YQ
> vQTwzQOGczq_sw%40mail.gmail.com [2]
> http://www.mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg308861.html

Thank you. I should probably wait until your patch is finalised before
spending too much time on it, and I could probably also leverage the
incremental sort patch if there is progress on it.

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org