Thread: Strange behavior for boolean predicates and partial indexes

I have a partial index that contains a predicate to check for whether the
field deleted is false or not:

CREATE INDEX people_essays_any_essaytype_idx
    ON people_essays (person_id)
 WHERE NOT deleted;

The following query does NOT use the index:

EXPLAIN ANALYZE
SELECT *
  FROM people_essays
 WHERE person_id = 1
   AND deleted IS FALSE;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on people_essays  (cost=0.00..10225.85 rows=4 width=67) (actual
time=110.205..417.113 rows=4 loops=1)
   Filter: ((person_id = 1) AND (deleted IS FALSE))
 Total runtime: 417.203 ms
(3 rows)

EXPLAIN ANALYZE
SELECT *
  FROM people_essays
 WHERE person_id = 1
   AND deleted = FALSE;
                                                              QUERY PLAN
              

--------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using people_essays_uniq on people_essays  (cost=0.00..18.06
rows=4 width=67) (actual time=35.094..35.971 rows=4 loops=1)
   Index Cond: (person_id = 1)
   Filter: (deleted = false)
 Total runtime: 36.070 ms
(4 rows)

EXPLAIN ANALYZE
SELECT *
  FROM people_essays
 WHERE person_id = 1
   AND NOT deleted;
                                                                   QUERY PLAN
                         

-------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using people_essays_any_essaytype_idx on people_essays
(cost=0.00..18.05 rows=4 width=67) (actual time=0.034..0.047 rows=4 loops=1)
   Index Cond: (person_id = 1)
   Filter: (NOT deleted)
 Total runtime: 0.136 ms
(4 rows)


Though the index was created with "NOT deleted", shouldn't the planner
evaluate "IS FALSE" as the same if "= FALSE" works?

Patrick Clery <etc@phpforhire.com> writes:
> I have a partial index that contains a predicate to check for whether the
> field deleted is false or not:

> CREATE INDEX people_essays_any_essaytype_idx
>     ON people_essays (person_id)
>  WHERE NOT deleted;

> The following query does NOT use the index:

> EXPLAIN ANALYZE
> SELECT *
>   FROM people_essays
>  WHERE person_id = 1
>    AND deleted IS FALSE;

The planner does not consider "NOT x" and "x IS FALSE" to be equivalent.
They are not in general (they give different answers for NULL).  In this
particular case it would be safe to use the index anyway, because NULL
is treated the same as FALSE at top level of WHERE ... but I'm not sure
how the implication-prover could be made to handle that without risk of
introducing subtle bugs.

> Though the index was created with "NOT deleted", shouldn't the planner
> evaluate "IS FALSE" as the same if "= FALSE" works?

deleted = FALSE wouldn't use that index either, though perhaps with less
justification since that is mathematically equivalent to NOT deleted.

Basically you should spell the WHERE condition the same way you spelled
the index condition.  Whether the planner is able to recognize the
logical equivalence of different conditions is not guaranteed.

            regards, tom lane