CODEWITHSUNDEEP
ruby-on-railspostgresql
bevanb
bevanb

Reputation: 8521

Adding an index on a boolean field

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?

Upvotes: 31

Views: 8797

Answers (2)

Mark Swardstrom
Mark Swardstrom

Reputation: 18080

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

Upvotes: 18

willglynn
willglynn

Reputation: 11520

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.

Upvotes: 45

Related Questions