Menu
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?
805
The Boolean values like True & false and 1&0 can be used as indexes in panda dataframe. They can help us filter out the required records. In the below exampels we will see different methods that can be used to carry out the Boolean indexing operations.
Since MySQL always use TINYINT as Boolean, we can also insert any integer values into the Boolean column. Execute the following statement: Mysql> INSERT INTO student(name, pass) VALUES('Miller',2);
You can use tinyint(1) or bool or boolean. All are synonym. If you use bool or boolean datatype, then it nternally change into tinyint(1). In PHP, the value 0 represents false and 1 represents true.
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.
72
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
