Re: default range partition and constraint exclusion - Mailing list pgsql-hackers

On 2017/11/22 6:31, Robert Haas wrote:
> On Tue, Nov 21, 2017 at 4:36 AM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>>> The attached will make the constraint to look like:
>>>
>>> Uh, if the constraint exclusion logic we're using is drawing false
>>> conclusions, we need to fix it so it doesn't, not change the
>>> constraint so that the wrong logic gives the right answer.
>>
>> I did actually consider it, but ended up concluding that constraint
>> exclusion is doing all it can using the provided list partition constraint
>> clauses.
>>
>> If all predicate_refuted_by() receives is the expression tree (AND/OR)
>> with individual nodes being strict clauses involving partition keys (and
>> nothing about the nullness of the keys), the downstream code is just
>> playing by the rules as explained in the header comment of
>> predicate_refuted_by_recurse() in concluding that query's restriction
>> clause a = 2 refutes it.
> 
> Oh, wait a minute.  Actually, I think predicate_refuted_by() is doing
> the right thing here.  Isn't the problem that mc2p2 shouldn't be
> accepting a (2, null) tuple at all?

It doesn't.  But, for a query, it does contain (2, <unknown>) tuples,
where <unknown> would always be non-null.  So, it should be scanned in the
plan for the query that has only a = 2 as restriction and no restriction
on b.  That seems to work.

\d+ mc2p2
...
Partition of: mc2p FOR VALUES FROM (1, 1) TO (MAXVALUE, MAXVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 1) OR
((a = 1) AND (b >= 1))))

insert into mc2p2 values (2, null);
ERROR:  new row for relation "mc2p2" violates partition constraint
DETAIL:  Failing row contains (2, null).

explain select * from mc2p where a = 2;                        QUERY PLAN
-------------------------------------------------------------Append  (cost=0.00..38.25 rows=11 width=8)  ->  Seq Scan
onmc2p2  (cost=0.00..38.25 rows=11 width=8)        Filter: (a = 2)
 
(3 rows)


My complaint is about m2p_default not being included in the plan for a
query with only a = 2 restriction.  The aforementioned <unknown> includes
null and it's only m2p_default that has such tuples, so it should be in
the plan for queries that don't explicitly prevent null values for b by
including b is not null in the query.

With the patch:

explain (costs off) select * from mc2p where a = 2;                           QUERY PLAN
-------------------------------------------------------------------Append  ->  Seq Scan on mc2p_default        Filter:
((bIS NULL) AND (a = 2))
 
(3 rows)

explain (costs off) select * from mc2p where a = 2 and b is null;                           QUERY PLAN
-------------------------------------------------------------------Append  ->  Seq Scan on mc2p_default        Filter:
((bIS NULL) AND (a = 2))
 
(3 rows)

explain (costs off) select * from mc2p where a = 2 and b is not null;                        QUERY PLAN
-------------------------------------------------------------Append  ->  Seq Scan on mc2p2        Filter: ((b IS NOT
NULL)AND (a = 2))
 
(3 rows)

Thanks,
Amit