4 ms·
> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. Why? Is it because an index on the bool alone, with symme
by moring 3y ago
> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing.
Why? Is it because an index on the bool alone, with symmetric distribution, will still leave you with half the table to scan? In other words, does that statement apply to biased distribution (as mentioned by another response) or indices on multiple fields of which one is a boolean?
- jeltz 3y agoYes, it is because it leaves you with half the table the scan while adding the overhead of doing an index scan. And of you have a biased distribution you probably want a partial index since those are smaller.
- xwdv 3y agoNo, it’s rarely half the table, most bool columns are biased to one value.
- code_biologist 3y agoHalf the rows to scan in 99% of cases means you’ll still hit every page and incur exactly the same amount of IO (the expensive part) as a full table scan.
- webstrand 3y agoWould periodically clustering the table on the boolean index help here? Since then the true rows would be in different pages than the false rows. Unless I misunderstand what clustering does.
- valenterry 3y agoYes, that would indeed help.
- marcosdumay 3y agoThe thing is that, since you can only cluster around a single ordering, a boolean column is very rarely the most useful one to use. But then, given the number of things that very rarely happen in a database, you are prone to have 1 or 2 of them happening every time you do something. Just not that specific thing; but if you keep all of those rules in mind, you will always be surprised.