Hacker News Reader

Also good to remember that booleans can have 3 values: true, false, or null. Creating a partial index on `WHERE NOT NULL` can be helpful too.

View on HN

Postgres uses distinct nulls. I've not checked, but I'd assume postgres simply does not index nulls, as it can't find them again anyway (unless you use the new "NULLS NOT DISTINCT" anyway). I think you need a separate index on the boolean IS NULL (which should probably be a partial index on whichever of IS NULL and IS NOT NULL is better).

You're correct, thanks for noting this – I had it backwards.

Postgres absolutely adds nulls to its indexes. You can even control how they are ordered, and on the last few versions if nulls are equal or not.

A complete index over a column will have entries for all records, and can be used on "x is null" and "x is not null" filters.

Even when the column is made with NOT NULL?

No.

Reply on news.ycombinator.com