Re: Ordered Partitioned Table Scans

On Fri, 29 Mar 2019 at 00:00, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
> On 2019/03/28 8:04, David Rowley wrote:
> > If it's *always* scanned last then it's fine for ORDER BY partkey
> > NULLS LAST. If they have ORDER BY partkey NULLS FIRST then we won't
> > match on the pathkeys.
>
> Sorry, I had meant to say that null values may or may not appear in the
> last partition depending on how the null-accepting partition is defined.
> I see that the code in partitions_are_ordered() correctly returns false if
> null partition is not the last one, for example, for:
>
> create table p (a int) partition by list (a);
> create table p1 partition of p for values in (1);
> create table p2_null partition of p for values in (2, null);
> create table p3 partition of p for values in (3);
>
> Maybe, a small comment regarding how that works correctly would be nice.

hmm, but there is a comment. It says:

/*
* LIST partitions can also guarantee ordering, but we'd need to
* ensure that partitions don't allow interleaved values. We
* could likely check for this looking at each partition, in
* order, and checking which Datums are accepted. If we find a
* Datum in a partition that's greater than one previously already
* seen, then values could become out of order and we'd have to
* disable the optimization. For now, let's just keep it simple
* and just accept LIST partitions without a DEFAULT partition
* which only accept a single Datum per partition. This is cheap
* as it does not require any per-partition processing. Maybe
* we'd like to handle more complex cases in the future.
*/

Your example above breaks the "don't allow interleaved values" and
"just accept LIST partitions without a DEFAULT partition which only
accept a single Datum per partition."

Do you think I need to add something like "and if there is a NULL
partition, that it only accepts NULL values"? I think that's implied
already, but if you think it's confusing then maybe it's worth adding
something along those lines.

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