Index Selectivity and Database Performance

Indexes are useless if they point to everything. If you index a column with only two values like a boolean for ‘active’ or ‘inactive’, the database engine usually ignores the index entirely. It’s just too expensive to traverse the index tree.

Why high selectivity wins

High selectivity means a column has many unique values. Think of an ID or an email address. When you query for a specific ID, the database jumps straight to the record. That is efficient. Low selectivity on the other hand is like looking for a person in a city by just knowing they live in the city. It doesn’t narrow the search down enough to be useful.

When indexes backfire

I’ve seen developers try to index every column that appears in a WHERE clause. This is a trap. Every index adds a write penalty to your inserts and updates. If your column has poor selectivity, you’re paying the write tax without getting any read benefits.

Consider a user table with 1 million rows:

1
-- Good selectivity: Index this
2
CREATE INDEX idx_user_email ON users(email);
3

4
-- Bad selectivity: Don't index this
5
CREATE INDEX idx_user_active ON users(is_active);

If 90 percent of your users are ‘active’, the query planner knows it’s faster to do a sequential scan. It’s just simple math. Scanning the whole table is faster than jumping around the index to grab almost every row anyway (I’ll admit, this changes if your query is filtered heavily by other columns too).

The “but my tool said it was fine” objection

“But my database GUI shows the index is being used sometimes!” Sure, that’s fair. Sometimes a covering index or a small table size can make low-selectivity indexes appear performant. However, you’re building tech debt for when that table grows to 100 million rows. I’d rather avoid indexes on columns that don’t actually narrow the search space down significantly.

My take

I prefer using partial indexes in PostgreSQL for low-selectivity columns if I really need them. If you only care about finding the ‘inactive’ users and that is a small percentage of your total data, do this instead:

1
CREATE INDEX idx_users_inactive
2
ON users(id)
3
WHERE is_active = false;

This keeps your index small and your search fast. Only index what matters. Don’t waste space on noise. Are you really checking your query plans after adding indexes, or just hoping for the best?