Is it a good practice to add indexing on a boolean field in a Django model

Question

Suppose we have model with boolean field:

class AModel(models.Model):
    flag = models.BoleanField()

Is there any reason to add index on this field?

I think it hasn't reason because there will be small profit in search(this only split two) but large overhead with recording. But my colleague thinks different.

Is there any rule of thumb for this?

Answer 1

It depends.

If you had data that has predominantly one or other of the boolean values (ie, almost everything is FALSE), but you want to commonly query only values that match the other (ie, query only for TRUE values), then an index on the boolean field could make a very big difference to performance: especially if you force the index to store the TRUE values first.

The trick there is that the field (and index) is selective, because you can discard most of the rows, and so the index can be used to speed this up, by storing the ones that are useful.

Alternatively, you could have an index on a different field (that you are also querying on, perhaps) that uses a WHERE <boolean-field> clause to only store the true values (or vice-versa, depending upon your needs). I haven't tried it, but I'd wager WHOLE DOLLARS that Postgres is able to use this index correctly...