Partitioning on IS NULL / IS NOT NULL not supported?

Поиск
Список
Период
Сортировка
От Aleksander Kmetec
Тема Partitioning on IS NULL / IS NOT NULL not supported?
Дата
Msg-id 4645E15C.1030905@intera.si
обсуждение исходный текст
Ответы Re: Partitioning on IS NULL / IS NOT NULL not supported?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Jasbinder Singh Bali"
Дата:
Сообщение: Re: Database transaction related
Следующее
От: Tom Allison
Дата:
Сообщение: Re: stuck on values in 8.2