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
