Re: BUG #1470: Boolean expression index not used when it could be

"Sergey Koshcheyev" <sergey(dot)p(dot)k(at)hotmail(dot)com> writes:
> I'm trying to optimize "is null" queries, since PgSQL doesn't index null
> values. I have found that creating an expression index on (column is null)
> could work, but it doesn't get used unless the index expression is part of a
> comparison. Could this be improved, so that (a boolean expression) is taken
> as equivalent to (a boolean expression = true)?

You would be better off to use a partial index:

create index tbl1_abc on tbl1 (somecol) where abc is null;

The advantage of this is that not only do you get the is-null filter,
but you may be able to filter on some other column(s) at the same time.
For instance if you commonly query

select ... from tbl1 where abc is null and def > 42

then making "somecol" be "def" would be a winner.

regards, tom lane