PostgreSQL Index Recommendations: Find Missing Indexes Without Guesswork

The Index Problem Every DBA Faces

Missing indexes cause slow queries. Unused indexes waste space and slow writes. But finding the right indexes is part art, part science - and most tools get it wrong.

Traditional Approach: pg_stat_statements + pgBadger

The standard workflow:

1. Run pgBadger on slow query logs
2. Find sequential scans taking > 1 second
3. Guess which columns need indexes
4. Create index, hope it helps
5. Realize 6 months later it's never used

Problems with this approach:

• Based on samples, not actual workload
• No quantified impact prediction
• No consideration of write overhead
• No covering index suggestions
• No partial index analysis

pgHero: A Step Forward, But Limited

pgHero added "suggested indexes" which improved things:

• Analyzes query patterns
• Shows scan vs index usage ratios
• Identifies unused indexes

But pgHero still misses:

• Quantified time savings ("this will save 150ms/query")
• Covering indexes with INCLUDE columns
• Partial indexes for filtered queries
• Composite index column ordering optimization
• Production-safe DDL with rollback

PG Monitoring's AI-Powered Index Advisor

1. Real Impact Quantification

Instead of "this index might help," PG Monitoring calculates:

Sequential scans: 1,247/hour
Average scan time: 245ms
Query frequency: 8,000 calls/day
Estimated index benefit: 192ms/query
Daily time saved: 26 minutes of CPU
Index maintenance cost: 12ms per INSERT
Recommendation: CREATE (net positive)

2. Index Type Intelligence

PG Monitoring suggests the right index type for the workload:

• Covering Index: When queries select columns not in WHERE clause
• Partial Index: When queries always filter on status='active'
• Composite Index: When column order matters for range queries
• Expression Index: When filtering on UPPER(email) or date_trunc

3. Production-Safe DDL

Every recommendation includes:

-- Apply (no table lock, index usable immediately)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_created 
ON orders(user_id, created_at DESC) 
INCLUDE (total, status);

-- Rollback (if performance degrades)
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_created;

-- Build time estimate: ~4 minutes (based on 2.3M rows)

Case Study: E-commerce Query Optimization

A customer's "My Orders" page was loading in 2.3 seconds.

pgHero suggested: "Add index on orders(user_id)"

PG Monitoring recommended:

CREATE INDEX CONCURRENTLY idx_orders_user_created_covering 
ON orders(user_id, created_at DESC) 
INCLUDE (total, status, tracking_number)
WHERE deleted_at IS NULL;

Result:

• Query time: 2.3s → 45ms (98% reduction)
• Index covers 94% of order queries
• Partial index saves 40% space vs full index
• No write performance impact (CONCURRENTLY)

Feature Comparison

The Difference

Traditional tools: "Add an index on user_id."

PG Monitoring: "Create a covering composite index on (user_id, created_at DESC) with INCLUDE columns. This will save 187ms per query based on 1,247 daily sequential scans. Estimated build time: 4 minutes using CONCURRENTLY. Rollback command ready if needed."