Hacker News Reader

Comment by masklinn | Hacker News Reader

> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing.

However if the field is highly biased (e.g. 90 or 99% one value) it can be useful to create a partial index on the rarer value. Though even better is to create a partial index on the other stuff filtered by that value, especially if the smaller set is the commonly queried one (e.g. soft-deletes).

View on HN

Yeah. Finding ”open” tickets, for example. There’s actually some really good cases to index on a Boolean.

Nice example.

We work in different places. Here the index in closed tickets would be smaller. But you know, some sales guy called and they want this little feature NOW.

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.

Even when the column is made with NOT NULL?

No.

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.

If it's highly biased indeed, in combinaison of a condition it's useful.

I was referring of indexing the column without distinction, the last time I checked (years ago) Postgres didn't do any statistical distribution so the query planner was always discarding the index anyway.

Reply on news.ycombinator.com