PostgreSQL Partial Index: Planner Ignores Your Index

I still remember the query plan that missed a partial index. “I created a partial index specifically for this query, but PostgreSQL is doing a sequential scan.” A developer showed me their EXPLAIN output, frustrated after hours of optimization work. They had a table with 10 million rows, only 500,000 of which were active. The partial index should have been perfect—index only the active rows, make queries instant. Instead, PostgreSQL was scanning all 10 million rows.

The partial index was correct. The query was correct. The problem was subtle: the query used status != 'inactive' while the index predicate was status = 'active'. To a human, these are logically equivalent—there were only two statuses. To PostgreSQL’s query planner, they’re different expressions that it can’t prove equivalent.

This is one of the most frustrating PostgreSQL gotchas because everything looks right. The index exists. The query should use it. But PostgreSQL’s planner operates on syntactic matching, not semantic reasoning. If it can’t prove your query implies the index predicate, it won’t use the index.

Environment: PostgreSQL 13+, partial indexes on large tables, queries with varying WHERE clauses

The Problem

Index Exists But Not Used

Let me walk through the exact scenario that trips up most developers:

-- Create partial index for active users (5% of table)
CREATE INDEX idx_users_active_email ON users(email)
    WHERE status = 'active';

-- Table has 10 million rows, 500k active
EXPLAIN ANALYZE
SELECT * FROM users WHERE status = 'active' AND email = '[email protected]';

-- Expected: Index Scan using idx_users_active_email
-- Actual:   Seq Scan on users (cost=0.00..412583.00 rows=1 width=128)
--           Filter: ((status = 'active') AND (email = '[email protected]'))
--           Rows Removed by Filter: 9999999
--           Time: 4523ms

-- Why is it ignoring my perfect partial index?!

The query is slow—4.5 seconds to find one row. The partial index should make this instant, but PostgreSQL chooses to scan the entire table. The index exists, the statistics are current, yet the planner doesn’t use it.

The debugging process usually follows a predictable path: check if the index exists (it does), run ANALYZE (doesn’t help), check if the index is valid (it is), read Stack Overflow (conflicting advice). The real cause is something more fundamental about how PostgreSQL reasons about partial indexes.

Variations That Fail

The frustrating part is how many innocent-looking query variations break partial index usage:

-- Case 1: Different operator
SELECT * FROM users WHERE status != 'inactive' AND email = '[email protected]';
-- Planner: "status != 'inactive'" doesn't prove "status = 'active'"

-- Case 2: NULL handling
SELECT * FROM users WHERE status = 'active' AND email = '[email protected]'
    AND deleted_at IS NULL;
-- Planner: My index doesn't say anything about deleted_at

-- Case 3: OR condition
SELECT * FROM users
    WHERE (status = 'active' OR is_admin = true) AND email = '[email protected]';
-- Planner: Can't prove index predicate from OR

-- Case 4: Variable/function in query
SELECT * FROM users WHERE status = $1 AND email = '[email protected]';
-- Planner: At plan time, $1 is unknown - might not be 'active'

Each of these looks reasonable. Case 1 is logically equivalent (if there are only two statuses). Case 2 adds a filter that doesn’t change which rows match the predicate. Case 3 requests a superset that includes all active users. Case 4 uses a parameter that will almost always be ‘active’.

But PostgreSQL’s planner doesn’t do logical reasoning. It does syntactic pattern matching with a few simple transformations. If it can’t see that your query’s WHERE clause directly implies status = 'active', it won’t use the index.

Root Cause

Predicate Implication Rules

The rule for partial index usage is simple to state but has subtle implications: the query’s WHERE clause must logically imply the index’s WHERE clause. In practice, PostgreSQL uses a limited proof system that only recognizes certain patterns:

PostgreSQL partial index usage requirements:

Query WHERE clause must IMPLY index WHERE clause
(Not just overlap, not just be equal - must logically imply!)

┌─────────────────────────────────────────────────────────────┐
│ Index predicate: status = 'active'                          │
│                                                             │
│ ✓ Query: status = 'active'          → Direct match         │
│ ✓ Query: status = 'active' AND x    → Implies predicate    │
│                                                             │
│ ✗ Query: status IN ('active', 'x')  → Doesn't imply        │
│ ✗ Query: status != 'inactive'       → Logically same, but  │
│                                       planner can't prove   │
│ ✗ Query: status = $1                → Unknown at plan time │
│ ✗ Query: status = ANY(array)        → Can't prove          │
└─────────────────────────────────────────────────────────────┘

The key insight is “logically imply” from PostgreSQL’s limited perspective. The planner isn’t a theorem prover. It recognizes direct equality, AND combinations, and a few other patterns. Anything more complex requires reasoning it doesn’t perform.

Planner’s Proof System

Understanding what the planner can and cannot prove helps you structure queries that will use your indexes:

-- PostgreSQL uses simple syntactic matching + some transforms
-- It does NOT do full logical reasoning

-- This works (syntactic match):
WHERE status = 'active' AND email = 'x'

-- This fails (would require semantic reasoning):
WHERE (CASE WHEN true THEN status ELSE 'x' END) = 'active'

-- This fails (different syntax, same meaning):
WHERE status = 'active'::text  -- Cast changes the expression tree!

-- Check what the planner sees
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM users WHERE status = 'active' AND email = 'x';

The type cast example is particularly sneaky. 'active' and 'active'::text have the same value, but they’re different expression nodes in the query tree. If your column is varchar and you cast to text, the planner sees different types and can’t match them.

This isn’t a PostgreSQL bug—implementing full logical reasoning in the planner would make query planning exponentially more expensive. The current approach handles common cases quickly. The cost is that uncommon cases require manual intervention.

Diagnosis

Check Index Predicate Matching

Start by verifying exactly what the index predicate is and testing if your query matches:

-- See the index definition
SELECT indexdef FROM pg_indexes
WHERE indexname = 'idx_users_active_email';
-- CREATE INDEX idx_users_active_email ON users(email) WHERE status = 'active'

-- Test if planner can match your query
EXPLAIN (COSTS OFF)
SELECT * FROM users WHERE status = 'active' AND email = 'test';
-- Should show: Index Scan using idx_users_active_email

-- If not, try exact predicate match
EXPLAIN (COSTS OFF)
SELECT * FROM users WHERE email = 'test' AND status = 'active';
-- Same logic, different order - still should work

The order of conditions in WHERE doesn’t matter—PostgreSQL normalizes the clause before matching. But the expressions themselves must be recognizable.

Check for Type Mismatches

Type mismatches are a common hidden cause of partial index failures:

-- Common gotcha: text vs varchar vs char
SELECT
    a.attname,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as type
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
WHERE c.relname = 'users' AND a.attname = 'status';
-- Returns: character varying(20)

-- If your query uses a different type:
EXPLAIN SELECT * FROM users
    WHERE status = 'active'::text AND email = 'x';
-- May not match if status is varchar!

PostgreSQL’s type system is stricter than you might expect. text and varchar are different types, even though they store the same data. When comparing expressions, the planner requires type equality.

Check Statistics

Even with correct predicate matching, stale statistics can prevent index usage if the planner thinks a sequential scan is faster:

-- Even with correct predicate, bad stats can prevent index use
SELECT
    n_distinct,
    null_frac,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE tablename = 'users' AND attname = 'status';

-- Refresh statistics
ANALYZE users;

-- Check index statistics
SELECT
    indexrelid::regclass AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_users_active_email';

If idx_scan is 0 for an index you expect to be used, something is preventing it. Either the predicate isn’t matching (most likely) or the statistics make sequential scan appear cheaper (less likely but possible).

The Fix

Option 1: Match Predicate Exactly

The simplest fix is ensuring your queries use exactly the same expression as the index predicate:

-- Use exact same expression as index predicate
-- DON'T:
SELECT * FROM users WHERE status != 'inactive' AND email = 'x';

-- DO:
SELECT * FROM users WHERE status = 'active' AND email = 'x';

-- For prepared statements, add explicit condition
PREPARE get_active_user(text) AS
    SELECT * FROM users WHERE status = 'active' AND email = $1;
-- Now planner knows status = 'active' at plan time

This might seem redundant if your application only ever queries active users, but making it explicit helps the planner. The prepared statement example is particularly important—without the explicit status = 'active', the planner can’t know at plan time that the parameter will match the index predicate.

Option 2: Create Expression Index

If you genuinely need different predicate expressions, create indexes that match them:

-- If you need status != 'inactive', create index for that
CREATE INDEX idx_users_not_inactive_email ON users(email)
    WHERE status != 'inactive';

-- Or use more complex predicates if needed
CREATE INDEX idx_users_active_or_pending ON users(email)
    WHERE status IN ('active', 'pending');

Multiple partial indexes with different predicates is a valid pattern. Each serves different query patterns. The storage cost is usually minimal since each index covers only a subset of rows.

Option 3: Use Immutable Function Wrapper

For complex predicates that you want to reuse across queries and indexes, wrap them in an immutable function:

-- Wrap complex logic in immutable function
CREATE OR REPLACE FUNCTION is_active_user(status text)
RETURNS boolean AS $$
    SELECT status = 'active' OR status = 'pending'
$$ LANGUAGE sql IMMUTABLE;

-- Create index using function
CREATE INDEX idx_users_active_fn ON users(email)
    WHERE is_active_user(status);

-- Query using same function
SELECT * FROM users WHERE is_active_user(status) AND email = 'x';

The function must be IMMUTABLE for this to work—PostgreSQL needs to know the function returns the same result for the same inputs. SQL functions are inlined by the planner, so there’s no performance penalty.

This pattern also helps with code maintainability. If “active user” logic changes, update the function and recreate the index. All queries automatically use the new logic.

Option 4: Constraint Exclusion Setup

For partitioned tables, PostgreSQL has additional machinery that can help:

-- For partitioned tables or inheritance, enable constraint_exclusion
SET constraint_exclusion = on;  -- Default is 'partition'

-- This helps planner use partition constraints
-- Similar logic applies to partial indexes

Constraint exclusion is mostly relevant for partitioning, but enabling it fully (on instead of partition) can help the planner recognize more implication patterns.

Option 5: Force Index with CTE

As a last resort, you can force the planner’s hand by restructuring the query:

-- Last resort: Force the planner's hand
WITH active_users AS MATERIALIZED (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE email = 'x';

-- This forces evaluation of status = 'active' first
-- Not ideal, but works when planner won't cooperate

The MATERIALIZED keyword (PostgreSQL 12+) prevents the CTE from being inlined, forcing a two-step execution. This is a workaround, not a solution—it can actually make performance worse in some cases. Use it only when other options fail.

Monitoring

Set up alerts to catch partial indexes that aren’t being used:

groups:
  - name: postgresql-indexes
    rules:
      - alert: PartialIndexNotUsed
        expr: |
          increase(pg_stat_user_indexes_idx_scan{indexrelname=~".*partial.*"}[1d]) == 0
          and pg_stat_user_indexes_idx_tup_read{indexrelname=~".*partial.*"} > 0
        for: 1d
        labels:
          severity: warning
        annotations:
          summary: "Partial index {{ $labels.indexrelname }} not scanned in 24h"

      - alert: SequentialScanOnLargeTable
        expr: |
          rate(pg_stat_user_tables_seq_scan{relname="users"}[5m]) > 10
          and pg_stat_user_tables_n_live_tup{relname="users"} > 1000000
        for: 30m
        labels:
          severity: warning
        annotations:
          summary: "High sequential scan rate on large table users"

The first alert specifically catches partial indexes that exist but aren’t being used—usually a sign of predicate mismatch. The second alert catches general sequential scan problems on large tables, which often indicate missing or unused indexes.

Checklist

## PostgreSQL Partial Index Not Used

### Diagnosis
- [ ] Check EXPLAIN output for index usage
- [ ] Verify query WHERE exactly implies index predicate
- [ ] Check for type mismatches (text vs varchar)
- [ ] Verify statistics are current (ANALYZE)
- [ ] Test with literal values vs parameters

### Common Causes
- [ ] Using != instead of = in query
- [ ] Using IN/ANY instead of exact value
- [ ] Prepared statement with unknown parameter
- [ ] Type cast changing expression tree
- [ ] OR conditions breaking implication

### Fixes
- [ ] Rewrite query to match predicate exactly
- [ ] Create additional partial index for other predicates
- [ ] Use immutable function wrapper for complex logic
- [ ] Add explicit predicate to prepared statements
- [ ] Refresh statistics with ANALYZE

Conclusion

PostgreSQL partial index usage requires syntactic proof that the query implies the index predicate. “Logically equivalent” isn’t enough—the planner needs to see the exact same expression or something it can trivially transform.

This isn’t a limitation to work around; it’s a design trade-off. Full logical reasoning in the planner would make query planning slow and unpredictable. The current system is fast and deterministic, at the cost of requiring developers to match index predicates explicitly.

Key principles to remember:

1. Query must imply predicate - not just overlap, not just be semantically equivalent
2. Syntactic matching, not semantic - the planner can’t prove complex logical equivalences
3. Parameters unknown at plan time - add explicit predicates for prepared statements
4. Type matters - 'active' vs 'active'::text are different expressions

When in doubt, test with EXPLAIN. If your partial index isn’t being used, the cause is almost always a predicate mismatch that’s subtle enough to miss on first inspection.

Related Articles

• PostgreSQL HOT Updates Index Trap - Index behavior surprises
• GIN Index Pending List Overflow - Index maintenance issues