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

2017-09-26 Thread Julien Rouhaud
On Tue, Sep 26, 2017 at 2:56 PM, Robert Haas wrote: > On Sat, Sep 23, 2017 at 6:29 AM, Julien Rouhaud wrote: >> That's true, but numCols, sortColdIdx etc are also used to display the >> sort key in an explain. If an append can return sorted data, it >>

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

2017-09-26 Thread Robert Haas
On Sat, Sep 23, 2017 at 6:29 AM, Julien Rouhaud wrote: > That's true, but numCols, sortColdIdx etc are also used to display the > sort key in an explain. If an append can return sorted data, it > should also display the sort information, so I think these fields are > still

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

2017-09-23 Thread Julien Rouhaud
On Fri, Sep 22, 2017 at 11:09 PM, Robert Haas wrote: > During planning, *every* node has a list of pathkeys associated with > it which represent the presumed output ordering. You can support > ordered append paths without changing any data structures at all; it's > just a

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

2017-09-22 Thread Robert Haas
On Fri, Sep 22, 2017 at 4:23 PM, Julien Rouhaud wrote: > That was one of the first question we had with the initial POC. The > reason is that this "sorted append" is not using a merge algorithm but > just appending partitions in the right order, so it looked like we > could

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

2017-09-22 Thread Julien Rouhaud
On Fri, Sep 22, 2017 at 9:58 PM, Robert Haas wrote: > On Fri, Sep 22, 2017 at 3:34 PM, Julien Rouhaud 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

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

2017-09-22 Thread Robert Haas
On Fri, Sep 22, 2017 at 3:34 PM, Julien Rouhaud 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

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

2017-09-22 Thread Julien Rouhaud
On Thu, Sep 21, 2017 at 11:13 AM, Julien Rouhaud wrote: > On Thu, Sep 21, 2017 at 10:52 AM, Ashutosh Bapat > wrote: >> With 9140cf8269b0c4ae002b2748d93979d535891311, we store the >> RelOptInfos of partitions in the RelOptInfo of partitioned

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

2017-09-21 Thread Julien Rouhaud
On Thu, Sep 21, 2017 at 10:52 AM, Ashutosh Bapat wrote: > With 9140cf8269b0c4ae002b2748d93979d535891311, we store the > RelOptInfos of partitions in the RelOptInfo of partitioned table. For > range partitioned table without default partition, they are arranged >

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

2017-09-21 Thread Ashutosh Bapat
On Thu, Sep 21, 2017 at 3:30 AM, Julien Rouhaud wrote: > On Thu, Aug 31, 2017 at 5:30 AM, Peter Eisentraut > wrote: >> On 3/20/17 11:03, Ronan Dunklau wrote: Great idea. This is too late for v10 at this point, but please add it to

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

2017-09-20 Thread Julien Rouhaud
On Thu, Aug 31, 2017 at 5:30 AM, Peter Eisentraut wrote: > On 3/20/17 11:03, Ronan Dunklau wrote: >>> 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

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

2017-08-30 Thread Peter Eisentraut
On 3/20/17 11:03, Ronan Dunklau wrote: >> 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

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

2017-04-05 Thread Ronan Dunklau
On vendredi 24 mars 2017 08:14:03 CEST Ashutosh Bapat wrote: > On Mon, Mar 20, 2017 at 8:33 PM, Ronan Dunklau wrote: > > On lundi 20 mars 2017 15:52:03 CET Robert Haas wrote: > >> On Mon, Mar 20, 2017 at 6:31 AM, Ronan Dunklau > > > > wrote:

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

2017-03-24 Thread Ashutosh Bapat
On Mon, Mar 20, 2017 at 8:33 PM, Ronan Dunklau wrote: > On lundi 20 mars 2017 15:52:03 CET Robert Haas wrote: >> On Mon, Mar 20, 2017 at 6:31 AM, Ronan Dunklau > wrote: >> > With range partitioning, we guarantee that each partition contains

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

2017-03-20 Thread Ronan Dunklau
On lundi 20 mars 2017 15:52:03 CET Robert Haas wrote: > On Mon, Mar 20, 2017 at 6:31 AM, Ronan Dunklau wrote: > > With range partitioning, we guarantee that each partition contains non- > > overlapping values. Since we know the range allowed for each partition, it > >

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

2017-03-20 Thread Robert Haas
On Mon, Mar 20, 2017 at 6:31 AM, Ronan Dunklau 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

[HACKERS] [Proposal] Make the optimiser aware of partitions ordering

2017-03-20 Thread Ronan Dunklau
Hello, With native partitioning landing in Postgres 10, we (Julien Rouhaud and myself) had the idea for the following simple optimisation. This simple optimisation comes from a real use case. = Proposal = With range partitioning, we guarantee that each partition contains non-