Adding an index on a boolean field

Question

I have a Rails model with a boolean field that I search on (I use a scope that finds all instances where the field is set to true). I'm using Postgres.

My instinct is to add an index on the boolean field. Is that good practice, or is there something in Postgres that makes an index on a boolean field unnecessary?

Answer 1

No, you can index a boolean field if you'll be filtering by it. That's a perfectly reasonable thing to do, although as with all indexes, PostgreSQL may choose to ignore it if it won't exclude enough of the table -- an index scan plus a ton of row fetches may be more expensive than a sequential scan -- which may or may not affect you depending on the values in that column.

You should also be aware that PostgreSQL lets you put conditions on indexes, which I often find useful with boolean fields. (See Partial Indexes for details.) If you'll be commonly filtering or sorting within that scope, you might be best served by something like CREATE INDEX ... ON table (some_field) WHERE boolean_field.

Answer 2

To create the partial index in a Rails migration, you would do this. In this example, the model is Product and the column is featured.

class AddFeaturedOnProducts < ActiveRecord::Migration   def change     add_index(:products, :featured, where: "featured")   end end