Re: Ordered Partitioned Table Scans

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.
With my idea for using live_parts, we'll process the partitions
looking for interleaved values on each query, after pruning takes
place. In this case, we'll see the partitions are naturally ordered. I
don't really foresee any issues with that additional processing since
it will only be a big effort when there are a large number of
partitions, and in those cases the planner already has lots of work to
do. Such processing is just a drop in the ocean when compared to path
generation for all those partitions.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services