Re: default range partition and constraint exclusion

On Wed, Nov 22, 2017 at 4:21 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> 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.

OK, so I am still confused about whether the constraint is wrong or
the constraint exclusion logic is wrong. One of them, at least, has
to be wrong, and we have to fix whichever one is wrong. Fixing broken
constraint exclusion logic by hacking up the constraint, or conversely
fixing a broken constraint by hacking up the constraint exclusion
logic, wouldn't be right.

I think my last email was confused: I thought that the (2, null) tuple
was ending up in mc2p2, but it's really ending up in mc2p_default,
whose constraint currently looks like this:

NOT (
((a < 1) OR ((a = 1) AND (b < 1)))
OR
((a > 1) OR ((a = 1) AND (b >= 1)))
)

Now where exactly is constraint exclusion going wrong here? a = 2
refutes a < 1 and a = 1, which means that (a < 1) OR ((a = 1) AND (b <
1)) must be false and that (a = 1) AND (b >= 1) must also be false.
But (a > 1) could be either true or null, which means (a > 1) OR ((a =
1) AND (b >= 1)) can be true or null, which means the whole thing can
be false or null, which means that it is not refuted by a = 2. It
should be possible to dig down in there step by step and figure out
where the wheels are coming off -- have you tried to do that?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company