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!
Written by bennycwong
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Rails
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#