You have a table with millions of records.
Some are processed. Others are still waiting.
So you add an index to make your queries faster:
CREATE INDEX idx_jobs_processed
ON jobs (is_processed);
Looks reasonable, right?
But PostgreSQL might completely ignore your index.
And there's a good reason for that.
Why Your Boolean Index Isn't Helping
Imagine your jobs table has 1 million rows.
- 950,000 jobs are processed (
true). - 50,000 jobs are unprocessed (
false).
Now you run:
SELECT *
FROM jobs
WHERE is_processed = true;
You might expect PostgreSQL to use your index.
But think about it.
You're asking for 95% of the entire table.
Using the index could mean reading the index and then visiting a huge number of table pages to fetch the actual rows.
PostgreSQL may decide it's cheaper to just scan the table.
That's called a Sequential Scan.
And in this case, it can be the smarter choice.
The Real Problem: Low Selectivity
A Boolean column has only two possible values:
true or false.
This is called low cardinality.
But cardinality alone isn't the problem. What really matters is selectivity: how many rows match your query.
If almost every row matches, an index usually provides little benefit.
But what if you only want the 5% of jobs that haven't been processed?
That's where things get interesting.
The Better Solution: Partial Indexes
Instead of indexing every job, index only the ones you actually need.
CREATE INDEX idx_unprocessed_jobs
ON jobs (created_at)
WHERE is_processed = false;
That's a Partial Index.
It tells PostgreSQL:
"Only include rows where is_processed is false."
Now your index contains entries for roughly 50,000 rows instead of 1 million.
That's approximately 95% fewer index entries in our example, although the actual storage savings depend on the index structure.
And here's the useful part.
When you run:
SELECT *
FROM jobs
WHERE is_processed = false
ORDER BY created_at
LIMIT 100;
PostgreSQL can use the smaller index to find the oldest unprocessed jobs without scanning all the processed ones.
Notice that we indexed created_at, not is_processed.
That's intentional.
The WHERE condition decides which rows belong in the index.
The indexed column helps PostgreSQL find those rows in the requested order.
Simple, but powerful.
Does This Always Work?
Not necessarily.
PostgreSQL's query planner chooses an execution plan based on statistics, estimated costs, and the query itself.
A partial index is especially useful when:
- Only a small percentage of rows match the condition.
- Your application frequently queries those rows.
- The indexed columns support filtering or sorting.
It may help less when the table is small, most rows match, or your query doesn't satisfy the partial index condition.
Also, indexes aren't free. They consume storage and add work to inserts and updates.
So don't create one just because a column exists.
How to Check Whether PostgreSQL Uses It
Use EXPLAIN ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM jobs
WHERE is_processed = false
ORDER BY created_at
LIMIT 100;
Look for operations such as:
Index Scan using idx_unprocessed_jobs
or:
Seq Scan on jobs
An index scan means PostgreSQL used an index. A sequential scan means it scanned the table.
But remember: a sequential scan isn't automatically bad.
Compare actual execution times and buffer usage before deciding which approach performs better.
One More Thing Developers Often Miss
Partial indexes depend on PostgreSQL recognizing that your query satisfies the index condition.
For example, a prepared query like this:
SELECT *
FROM jobs
WHERE is_processed = $1;
may not use the partial index when PostgreSQL chooses a generic plan.
Why?
Because $1 could be either true or false, and the index only contains false rows.
If your query always targets unprocessed jobs, expressing that condition directly can make the partial index easier for the planner to use.
The Takeaway
Don't index a Boolean column just because you frequently filter by it.
Think about how many rows your query actually needs.
If you're repeatedly querying a small subset of a large table, a partial index can be a much better choice.
Smaller index. Less unnecessary work. Potentially faster queries.
And the best part?
You don't need another database, caching layer, or complicated optimization.
Just a better index.
Have you ever created an index that PostgreSQL decided not to use?
Top comments (1)
The point about prepared statements and generic plans is the one that bites in production. With a parameterised is_processed = $1 the planner can't prove the partial index applies, so hard-coding the literal false in the queue-polling query keeps the partial index usable. For job queues I'd also check that the partial index stays small by watching dead tuples, since rows leaving the predicate on update still leave bloat until vacuum catches up. Have you compared this against FOR UPDATE SKIP LOCKED workers on the same table?