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

Hi.

While working on the patch for partition pruning for declarative
partitioned tables, I noticed that default range partition will fail to be
included in a plan in certain cases due to pruning by constraint exclusion.

Consider a multi-column range-partitioned table:

create table mc2p (a int, b int) partition by range (a, b);
create table mc2p_default partition of mc2p default;
create table mc2p0 partition of mc2p
    for values from (minvalue, minvalue) to (1, 1);
create table mc2p2 partition of mc2p
    for values from (1, 1) to (maxvalue, maxvalue);

-- add a row with null b and check that it enters the default partition
insert into mc2p values (2);
INSERT 0 1

select tableoid::regclass, * from mc2p;
   tableoid   | a | b
--------------+---+---
 mc2p_default | 2 |
(1 row)

-- but selecting like this doesn't work
select tableoid::regclass, * from mc2p where a = 2;
 tableoid | a | b
----------+---+---
(0 rows)

because:

explain (costs off) select tableoid::regclass, * from mc2p where a = 2;
              QUERY PLAN
--------------------------------------
 Result
   ->  Append
         ->  Seq Scan on mc2p2
               Filter: (a = 2)
(4 rows)


If you look at the default partition's constraint, which is as follows:

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

you'll notice that it doesn't explicitly say that the default partition
allows rows where a is null or b is null or both are null.  Given that,
constraint exclusion will end up concluding that the default partition's
constraint is refuted by a = 2.

The attached will make the constraint to look like:

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

Now since b IS NULL (which, btw, is NOT (b IS NOT NULL)) fails to be
refuted, as a whole, the whole constraint is not refuted.  So, we get the
correct result:

select tableoid::regclass, * from mc2p where a = 2;
   tableoid   | a | b
--------------+---+---
 mc2p_default | 2 |
(1 row)

explain (costs off) select tableoid::regclass, * from mc2p where a = 2;
              QUERY PLAN
--------------------------------------
 Result
   ->  Append
         ->  Seq Scan on mc2p2
               Filter: (a = 2)
         ->  Seq Scan on mc2p_default
               Filter: (a = 2)
(6 rows)


Attached patches.  Thoughts?

Thanks,
Amit