patternsqlpostgresqlTip
Partial indexes: index only the rows you query
Viewed 0 times
partial indexfiltered indexindex where clausestatus column indexselective index
Problem
A full B-tree index on a status column where 99% of rows are 'completed' makes the index nearly useless for queries filtering on status = 'pending', and wastes disk space and write amplification.
Solution
Create a partial index with a WHERE clause to index only the relevant subset:
-- Index only unprocessed jobs (tiny index, high selectivity):
CREATE INDEX idx_jobs_pending
ON jobs (created_at)
WHERE status = 'pending';
-- This query uses the partial index:
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100;
-- Partial unique index (allow multiple NULLs, enforce unique non-null):
CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE deleted_at IS NULL;
-- Index only unprocessed jobs (tiny index, high selectivity):
CREATE INDEX idx_jobs_pending
ON jobs (created_at)
WHERE status = 'pending';
-- This query uses the partial index:
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100;
-- Partial unique index (allow multiple NULLs, enforce unique non-null):
CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE deleted_at IS NULL;
Why
A partial index stores only the rows matching its WHERE clause. Fewer pages means faster scans, lower memory pressure in the buffer cache, and faster writes because fewer index tuples need updating.
Gotchas
- The query's WHERE clause must be implied by the index predicate for the planner to use it
- Parameterized queries where the value is not a constant may not use partial indexes
- Partial indexes do not appear in pg_indexes.indexdef but are visible via \d tablename in psql
Code Snippets
Confirm partial index usage with EXPLAIN ANALYZE
-- Verify partial index is used:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 10;
-- Look for: Index Scan using idx_jobs_pendingContext
Tables with a status or soft-delete column where most queries target a small subset of rows
Revisions (0)
No revisions yet.