Re: Strange behavior for boolean predicates and partial indexes

Patrick Clery <etc(at)phpforhire(dot)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