Introduction

Usually, not by itself. A standalone index on a boolean column often has low value because there are only two possible values, so each index entry still points to a large fraction of the table. Databases tend to benefit more from indexes on selective columns, or from composite or filtered indexes where the boolean participates in a more targeted access path.

Why Boolean Indexes Often Disappoint

Indexes help when they let the optimizer skip most rows. A boolean column such as is_active or deleted usually does not do that.

If your table is split roughly 50/50, an index lookup for WHERE is_active = true still leads to reading about half the table. At that point, many database engines will prefer a sequential scan because jumping through the index and then fetching many table rows is not cheaper.

That is the core rule:

• low-cardinality columns are usually poor standalone index candidates

A boolean column has the lowest practical cardinality possible.

When a Boolean Index Can Help

There are still cases where it is useful.

Highly Skewed Data

If only a tiny percentage of rows have one value, the index can help queries that target the minority set.

Example:

• '99.8% of rows have is_deleted = false'
• '0.2% have is_deleted = true'

A query looking for deleted rows may benefit because the index can find a very small subset quickly.

Composite Indexes

A boolean can be useful as the leading or supporting column in a composite index when the overall predicate is selective.

CREATE INDEX idx_orders_paid_created_at
    ON orders (is_paid, created_at);

This can help if your common query pattern is:

1SELECT *
2FROM orders
3WHERE is_paid = false
4  AND created_at >= '2025-01-01';

The boolean alone is weak, but the combination with a date or tenant id can be useful.

Partial or Filtered Indexes

Some databases support indexing only the subset you care about. This is often much better than indexing the raw boolean column.

PostgreSQL example:

CREATE INDEX idx_users_inactive_only
    ON users (created_at)
    WHERE is_active = false;

Now the index stores only inactive users. That is often a much stronger design than CREATE INDEX ON users (is_active).

What the Optimizer Actually Considers

Database engines do not choose indexes mechanically. They compare estimated costs. A boolean index may exist, but the optimizer may still ignore it because:

• too many rows match
• the query needs columns not covered by the index
• reading the table sequentially is cheaper
• statistics show the distribution is not selective enough

That is why the right question is not "can I create the index," but "does the plan use it and does runtime improve?"

Use EXPLAIN or your engine's equivalent before and after indexing.

EXPLAIN SELECT *
FROM users
WHERE is_active = false;

If the plan still shows a full scan, the engine is telling you the standalone boolean index is not worth using.

A Better Modeling Habit

Many real applications filter on a boolean plus another condition such as recency, account, or status. That usually suggests one of these designs:

• a composite index built around the full query pattern
• a filtered or partial index for the minority case
• no boolean index at all if the table is small or the predicate is broad

For example, an admin screen showing recently inactive users may benefit more from this:

CREATE INDEX idx_users_inactive_recent
    ON users (updated_at)
    WHERE is_active = false;

than from indexing is_active directly.

Common Pitfalls

The most common mistake is creating an index on every true or false column automatically. That adds write overhead without meaningful read gains.

Another mistake is evaluating the idea without checking data distribution. A boolean field with extreme skew behaves very differently from one split evenly.

A third pitfall is ignoring composite or filtered indexes, which are often the real solution when a boolean predicate appears in important queries.

Summary

• A standalone boolean index usually provides little benefit because the column has low cardinality.
• It can help when one value is rare and the query targets that minority set.
• Boolean columns are often more useful inside composite indexes than as standalone indexes.
• Partial or filtered indexes are frequently better than indexing the raw boolean column.
• Always verify with EXPLAIN and real query timings instead of guessing.