Re: Ordered Partitioned Table Scans

On Wed, Dec 19, 2018 at 3:01 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:
>
> On Thu, 20 Dec 2018 at 01:58, Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> I'm objecting to processing for all partitions, but processing for
> just non-pruned partitions seems fine to me. If there are 10k
> partitions and we pruned none of them, then planning will be slow
> anyway. I'm not too worried about slowing it down a further
> microsecond or two.  It'll be a drop in the ocean. When we have the
> live_parts flag in RelOptInfo then we can allow all of the cases
> you've mentioned above, we'll just need to look at the non-pruned
> partitions, and in partition order, determine if the lowest LIST
> partitioned value sorts earlier than some earlier partition's highest
> LIST value and disable the optimisation for such cases.

My concern is more for a more moderate number of partition (a few
hundreds?).  I don't know how expensive that'll be, but it just seem
sad to recompute their ordering each time and waste cycles if we can
do it only once in non corner cases.

> The flag you've mentioned will become redundant when support is added
> for the cases you've mentioned above.  I don't see any reason not to
> support all these cases, once the live_parts flag makes in into
> RelOptInfo.  I'm also a bit confused at why you think it's so
> important to make multi-valued LIST partitions work when no values are
> interleaved, but you suddenly don't care about the optimisation when
> the interleaved value partitions get pruned. Can you share your
> reasoning for that?

I never said that I don't care about interleaved partition being
pruned.  I do think it might not be a super frequent thing, but I
certainly wish we handle it.  I just agree with your argument that the
pruned partitions problem will be better handled with the live_parts
that should be added in another patch.

> If you're really so keen on this flag, can you share the design you
> have in mind?    If it's just a single bool flag like "parts_ordered",
> and that's set to false, then how would you know there is some natural
> order when the DEFAULT partition gets pruned? Or are you proposing
> multiple flags, maybe two flags, one for when the default is pruned
> and one when it's not?

I don't think that the design is a big problem here.  You can either
have a flag that say if the partitions are ordered whether there's a
default partition or not, so callers will have to check if the default
partition is still there, or just store an enum to distinguish the
different cases.

>  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.

> But in short, I just really don't like the flags idea and I'm not
> really willing to work on it or put my name on it. I'd much rather
> wait then build a proper solution that works in all cases.  I feel the
> current patch is worthwhile as it stands.

Ok, fine.

> > The multi-level partitioning case is another
> > thing that would need to be handled for instance (and that's the main
> > reason I couldn't submit a new patch when I was working on it), and
> > I'm definitely not arguing to cover it in this patch.
>
> As far as I'm aware, the multi-level partitioning should work just
> fine with the current patch. I added code for that a while ago. There
> are regression tests to exercise it. I'm not aware of any cases where
> it does not work.

Ok.