2 ms·

Some good advice here, but the “don’t index boolean columns” needs an “it depends” caveat, since Postgres will sometime use multiple boolean indexes to perform

Some good advice here, but the “don’t index boolean columns” needs an “it depends” caveat, since Postgres will sometime use multiple boolean indexes to perform a bitmap index scan, which can be advantageous.

andatki 3y ago
I like how the PostgreSQL docs use the term “profitable” for whether an index helps. 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 https://news.ycombinator.com/item?id=38407585 Good nudge that it depends!
silvestrov 3y ago
If the boolean values are not 50%/50% but skewed like 1%/99% then there can be a big advantage in using a partial index CREATE INDEX index_accounts_balance ON accounts (id) WHERE boolean_flag;
- andatki 3y ago
  Correct, when the query conditions match the index conditions, and they both select a low proportion of the rows. For PostgreSQL and those unfamiliar with partial indexes, worth a read: https://www.postgresql.org/docs/current/indexes-partial.html https://www.postgresql.org/docs/current/indexes-partial.html

andatki 3y ago

I like how the PostgreSQL docs use the term “profitable” for whether an index helps. 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 https://news.ycombinator.com/item?id=38407585 Good nudge that it depends!

silvestrov 3y ago

If the boolean values are not 50%/50% but skewed like 1%/99% then there can be a big advantage in using a partial index CREATE INDEX index_accounts_balance ON accounts (id) WHERE boolean_flag;

andatki 3y ago
Correct, when the query conditions match the index conditions, and they both select a low proportion of the rows. For PostgreSQL and those unfamiliar with partial indexes, worth a read: https://www.postgresql.org/docs/current/indexes-partial.html https://www.postgresql.org/docs/current/indexes-partial.html