I came across something interesting when working on a migration in a rails app:
When creating a partial index on a boolean field,
= in the where clause the the partial index are not the same thing.
Given a table
products with a boolean column
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!