Last Updated: March 28, 2018
·
1.582K
· bennycwong

TIL: Postgres: Partial Index On Boolean Field Tp

I came across something interesting when working on a migration in a rails app:

When creating a partial index on a boolean field, IS and = in the where clause the the partial index are not the same thing.

For example:
Given a table products with a boolean column counterfeit
I created an index on counterfeit with the constraint counterfeit = true.

add_column :products, :counterfiet, :boolean
add_index :products, :id, algorithm: :concurrently, where: "(counterfiet = TRUE)"

It hits the index when I query using the below:
SELECT * FROM "products" WHERE "products"."counterfeit" = TRUE
Selects uses the index I just created.

However, if query using this next form, it does a sequential scan:
SELECT * FROM "products" WHERE "products"."counterfeit" IS TRUE

In order to make it work for either forms, I indexed using the following:

add_index :products, :id, algorithm: :concurrently, where: "(counterfiet = TRUE OR counterfiet IS TRUE)"

Now both forms use the index!