Re: Ordered Partitioned Table Scans

On Wed, Dec 19, 2018 at 11:08 PM David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>
> On Thu, 20 Dec 2018 at 09:48, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> > On Wed, Dec 19, 2018 at 3:01 PM David Rowley
> > <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> > > If so, I'd question why the default partition
> > > is so special? Pruning of any of the other partitions could turn a
> > > naturally unordered LIST partitioned table into a naturally ordered
> > > partitioned table if the pruned partition happened to be the only one
> > > with interleaved values. Handling only the DEFAULT partition in a
> > > special way seems to violate the principle of least astonishment.
> >
> > I'm not sure I'm following you, the default partition is by nature a
> > special partition, and its simple presence prevent this optimisation.
> > We can't possibly store all the sets of subsets of partitions that
> > would make the partitioned table naturally ordered if they were
> > pruned, so it seems like a different problem.
>
> For example:
>
> create table listp (a int) partition by list (a);
> create table listp12 partition of listp for values in(1,2);
> create table listp03 partition of listp for vlaues in(0,3);
> create table listp45 partition of listp for values in(4,5);
> create table listpd partition of listp default;
>
> select * from listp where a in(1,2,4,5);
>
> Here we prune all but listp12 and listp45. Since the default is pruned
> and listp03 is pruned then there are no interleaved values. By your
> proposed design the natural ordering is not detected since we're
> storing a flag that says the partitions are unordered due to listp03.

No, what I'm proposing is to store if the partitions are naturally
ordered or not, *and* recheck after pruning if that property could
have changed (so if some partitions have been pruned). So we avoid
extra processing if we already knew that the partitions were ordered
(possibly with the default partition pruning information), or if we
know that the partitions are not ordered and no partition have been
pruned.