Обсуждение: Boolean partition constraint behaving strangely

Hi,

I have an issue with a table partitioned by one boolean column. The
query planner only seems to skip the non matching table if expired
(the column I use for the partition) is true.

Here is a simple example:

CREATE TABLE mos (type_id   INTEGER UNIQUE, expired boolean);
CREATE TABLE mos_expired_1 ( CHECK ( expired = true  ) ) INHERITS
(mos);
CREATE TABLE mos_active_1 ( CHECK ( expired = false ) ) INHERITS
(mos);
INSERT INTO mos_expired_1 (type_id,expired) VALUES(1, true);
INSERT INTO mos_active_1 (type_id,expired) VALUES(2, false);

EXPLAIN SELECT * from mos where expired = true;

Result  (cost=0.00..66.60 rows=2330 width=5)
  ->  Append  (cost=0.00..66.60 rows=2330 width=5)
        ->  Seq Scan on mos  (cost=0.00..33.30 rows=1165 width=5)
              Filter: expired
        ->  Seq Scan on mos_expired_1 mos  (cost=0.00..33.30 rows=1165
width=5)
              Filter: expired



EXPLAIN SELECT * from mos where expired = false;

Result  (cost=0.00..99.90 rows=3495 width=5)
  ->  Append  (cost=0.00..99.90 rows=3495 width=5)
        ->  Seq Scan on mos  (cost=0.00..33.30 rows=1165 width=5)
              Filter: (NOT expired)
        ->  Seq Scan on mos_expired_1 mos  (cost=0.00..33.30 rows=1165
width=5)
              Filter: (NOT expired)
        ->  Seq Scan on mos_active_1 mos  (cost=0.00..33.30 rows=1165
width=5)
              Filter: (NOT expired)

I would really like to know if I am missing something or it's a query
planner issue.

--
Dominik Sander

Dominik Sander <depairet@gmail.com> writes:
> I have an issue with a table partitioned by one boolean column. The
> query planner only seems to skip the non matching table if expired
> (the column I use for the partition) is true.

Hm, interesting case.  The reason it's behaving asymmetrically is the
fix for this bug:
http://archives.postgresql.org/pgsql-sql/2008-01/msg00084.php

The planner forces expressions like "bool_var = true" into the
simpler forms "bool_var" or "NOT bool_var" so as to recognize
that these forms are equivalent.  However, that means that your
"expired = false" case looks like the case that was removed as
incorrect, ie

+  * Unfortunately we *cannot* use
+  *    NOT A R=> B if:                    B => A
+  * because this type of reasoning fails to prove that B doesn't yield NULL.

It strikes me though that we could make the more limited deduction
that NOT A refutes A itself.  That would fix this case, and I think
it would cover all the cases that we would have recognized if we'd
left the clauses in boolean-comparison form.

I'll see about fixing this for the next updates.

            regards, tom lane