Обсуждение: Partitioning on IS NULL / IS NOT NULL not supported?

Hi,

I'm trying to split a table into 2 partitions based on whether a field's value is NULL or NOT NULL, but when I run
"EXPLAIN SELECT * FROM tab WHERE version IS NULL" it shows that both partitions are being scanned, not just the one
which contains rows where version is null.

Is this not supported?

Here are the table definitions I'm using:

CREATE TABLE tab (id SERIAL, name TEXT, version INTEGER, PRIMARY KEY (id));
CREATE TABLE tab_null (CHECK (version IS NULL), PRIMARY KEY (id)) INHERITS (tab);
CREATE TABLE tab_not_null (CHECK (version IS NOT NULL), PRIMARY KEY (id)) INHERITS (tab);


SHOW constraint_exclusion ;
  constraint_exclusion
----------------------
  on


This works as expected (only one partition is scanned):
EXPLAIN SELECT * FROM tab WHERE version = 5;
                                   QUERY PLAN
------------------------------------------------------------------------------
  Result  (cost=0.00..47.50 rows=12 width=40)
    ->  Append  (cost=0.00..47.50 rows=12 width=40)
          ->  Seq Scan on tab  (cost=0.00..23.75 rows=6 width=40)
                Filter: (version = 5)
          ->  Seq Scan on tab_not_null tab  (cost=0.00..23.75 rows=6 width=40)
                Filter: (version = 5)
(6 rows)


On the other hand, this doesn't (both partitions are scanned):
EXPLAIN SELECT * FROM tab WHERE version IS NULL;
                                   QUERY PLAN
------------------------------------------------------------------------------
  Result  (cost=0.00..63.00 rows=18 width=40)
    ->  Append  (cost=0.00..63.00 rows=18 width=40)
          ->  Seq Scan on tab  (cost=0.00..21.00 rows=6 width=40)
                Filter: (version IS NULL)
          ->  Seq Scan on tab_null tab  (cost=0.00..21.00 rows=6 width=40)
                Filter: (version IS NULL)
          ->  Seq Scan on tab_not_null tab  (cost=0.00..21.00 rows=6 width=40)
                Filter: (version IS NULL)
(8 rows)


Regards,
Aleksander

Aleksander Kmetec <aleksander.kmetec@intera.si> writes:
> I'm trying to split a table into 2 partitions based on whether a field's value is NULL or NOT NULL, but when I run
> "EXPLAIN SELECT * FROM tab WHERE version IS NULL" it shows that both partitions are being scanned, not just the one
> which contains rows where version is null.

> Is this not supported?

Nope, predicate_refuted_by() doesn't cover that particular combination
at the moment.  The case it does cover was motivated by the thought of
having a regular partitioning into different value classes plus a
partition for NULLs, hence it knows that a test like "version = X"
refutes "version IS NULL".

            regards, tom lane