PostgreSQL Index Performance

Performance Factors

-- High selectivity (good)
CREATE INDEX idx_email 
ON users(email);

-- Check index size
SELECT pg_size_pretty(pg_relation_size('idx_email'));

-- Rebuild index
REINDEX INDEX idx_email;

-- Check index usage
SELECT * FROM pg_stat_user_indexes 
WHERE indexrelname = 'idx_email';

🔹 Using EXPLAIN to Analyze Queries

EXPLAIN shows how PostgreSQL executes a query and whether it uses indexes. EXPLAIN ANALYZE actually runs the query and shows real timing data for accurate performance analysis.

-- See query plan without running
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Run query and see actual performance
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Look for these in output:
-- ✅ "Index Scan" - Good! Using an index
-- ❌ "Seq Scan" - Bad! Scanning entire table

🔹 Finding Unused Indexes

Unused indexes waste space and slow down writes without providing any benefit. Regularly check for and remove indexes that aren't being used by your queries.

-- Find indexes that are never used
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_toast%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Drop unused index
DROP INDEX idx_unused_column;

🔹 Checking Index Size

Large indexes consume memory and disk space. Monitor index sizes to ensure they're reasonable and consider partial indexes if full indexes are too large.

-- Check size of a specific index
SELECT pg_size_pretty(pg_relation_size('idx_users_email')) as index_size;

-- Check all indexes on a table
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE tablename = 'users'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Check total index size for database
SELECT pg_size_pretty(sum(pg_relation_size(indexrelid))) 
FROM pg_stat_user_indexes;

🔹 Index Maintenance

Over time, indexes can become bloated or fragmented. Regular maintenance keeps indexes efficient. REINDEX rebuilds an index, while VACUUM cleans up dead rows.

-- Rebuild a specific index
REINDEX INDEX idx_users_email;

-- Rebuild all indexes on a table
REINDEX TABLE users;

-- Rebuild all indexes in database (requires exclusive lock)
REINDEX DATABASE mydb;

-- Analyze table to update statistics
ANALYZE users;

-- Vacuum and analyze together
VACUUM ANALYZE users;

🔹 Monitoring Index Usage

Track how often indexes are used to identify which ones are valuable. This helps you decide which indexes to keep, optimize, or remove.

-- See index usage statistics
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as times_used,
    idx_tup_read as rows_read,
    idx_tup_fetch as rows_fetched
FROM pg_stat_user_indexes
WHERE tablename = 'users'
ORDER BY idx_scan DESC;

-- Find most used indexes
SELECT
    indexname,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 10;

🔹 Index Performance Best Practices

Follow these guidelines to maintain optimal index performance. Balance between read speed and write overhead, and regularly review your indexing strategy as data grows.

🔹 Quick Performance Tips

These quick tips can dramatically improve your database performance. Small changes in indexing strategy often yield significant speed improvements.

-- Tip 1: Index foreign keys
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Tip 2: Use covering indexes (include extra columns)
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name);

-- Tip 3: Use partial indexes for common filters
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Tip 4: Order matters in multi-column indexes
-- Put most selective column first
CREATE INDEX idx_user_date ON orders(user_id, created_at);

-- Tip 5: Use EXPLAIN to verify
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;