Re: Ordered Partitioned Table Scans

Hi,

On 2019/03/29 7:59, David Rowley wrote:
> 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.

How about extending the sentence about when the optimization is disabled
as follows:

"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. We'd also need to disable the optimization if
NULL values are interleaved with other Datum values, because the calling
code expect them to be present in the last partition."

Further, extend the "For now..." sentence as:

"For now, let's just keep it simple and just accept LIST partitioned table
without a DEFAULT partition where each partition only accepts a single
Datum or NULL. It's OK to always accept NULL partition in that case,
because PartitionBoundInfo lists it as the last partition."

Thanks,
Amit