Partitioning on IS NULL / IS NOT NULL not supported?

From: Aleksander Kmetec <aleksander(dot)kmetec(at)intera(dot)si>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Partitioning on IS NULL / IS NOT NULL not supported?
Date: 2007-05-12 15:46:36
Message-ID: 4645E15C.1030905@intera.si
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Broersma Jr 2007-05-12 16:06:57 Re: stuck on values in 8.2
Previous Message Tom Allison 2007-05-12 15:30:10 Re: stuck on values in 8.2