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?
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.
where: 'active = false' - ie the small minority - does that only improve performance on queries that select this minority? - because I dont really care about those queries, - its the queries that ask for active=true - that I am worried about.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
false value? Something like add_index(:products, :featured, where_not: "featured")?
:where option accepts a query, so you can do: add_index(:products, :featured, where: "featured = false")
Filter: ((NOT playoff). Did anyone figure it out?