40 karma · joined August 14, 2023
This is a place where I think tools like Rubocop help. They can be configured to point out method swaps like this (size over count) automatically which is a relatively low effort task to change the code.
With those rules/linting in place, you aren’t throwing out the benefits of AR (ORM), and hopefully leveraging their useful methods like these that help avoid unnecessary queries.
The purpose of the index often is to lower the “cost” (greater profit), by providing more values the query is accessing (filtering, ordering, selecting) within the index entries, for faster retrieval.
When users have the skills to generate query plans and review whether an index supports a query, verifying that the index is picked by the planner, and indeed lowers the cost, then they can answer this question for their own unique combination of hardware, data distribution, queries, and indexes.
As generic advice, I think more often than not the index won’t be used for Boolean columns. But it’s generic advice and it does depend.
As you suggested, users must check their own system.
My book also covers the 2 and 3 value variations (nulls allowed) for Boolean columns.
For readers wanting to build these skills themselves, here’s more info:
https://news.ycombinator.com/item?id=38407585
Good nudge that it depends!
For readers who want all of these and more in book form, with a sample Rails app and big data to test with (generated), please consider my book:
High Performance PostgreSQL for Rails https://news.ycombinator.com/item?id=38407585
The book helps readers build database skills with the overall purpose of improved performance and scalability.
Again, great, concise article. I’ll be recommending it to others and it will help a lot of developers!
Thanks!