4 ms·
> 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
by masklinn 4y ago
> 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).
- Waterluvian 4y agoYeah. Finding ”open” tickets, for example. There’s actually some really good cases to index on a Boolean.
- j45 4y agoNice example.
- WirelessGigabit 4y agoWe 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.
- h1fra 4y agoIf 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.
- ellisv 4y agoAlso 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.
- WirelessGigabit 4y agoEven when the column is made with NOT NULL?
- exac 4y agoNo.
- masklinn 4y agoPostgres 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).
- ellisv 4y agoYou're correct, thanks for noting this – I had it backwards.
- marcosdumay 4y agoPostgres 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.