Introduction
Slow queries are usually an index problem. Before you add Redis or scale horizontally, check your query plans. This post documents the real performance wins I found while tuning the Slade360 healthcare database at Savannah Informatics.
Start With EXPLAIN ANALYZE
Never optimise blindly. Run this first:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.id, p.name, a.scheduled_at
FROM patients p
JOIN appointments a ON a.patient_id = p.id
WHERE p.facility_id = 42
AND a.scheduled_at BETWEEN NOW() AND NOW() + INTERVAL '7 days'
AND a.status = 'confirmed';
Look for:
- Seq Scan on large tables = missing index
- Hash Join vs Index Nested Loop — nested loop is faster when rows are selective
- Buffers: hit vs read — cache misses are expensive
Composite Indexes — Column Order Matters
-- ❌ Wrong order — facility_id has low cardinality here
CREATE INDEX idx_appt_wrong ON appointments(status, facility_id, scheduled_at);
-- ✓ Right — most selective column first, range column last
CREATE INDEX idx_appt_right ON appointments(facility_id, status, scheduled_at);
Rule: equality predicates first, range predicates last.
Partial Indexes — Index Only What You Query
If 95% of your queries filter on status = 'active', don't index the full table:
CREATE INDEX idx_active_patients
ON patients(last_visit_at)
WHERE status = 'active';
This index is ~5% the size of a full index and fits in memory — cache hit rates go through the roof.
GIN Indexes for JSONB
When you're storing semi-structured clinical data in JSONB:
-- Enable containment queries on JSONB
CREATE INDEX idx_patient_meta ON patients USING GIN (metadata);
-- Query that uses the index
SELECT * FROM patients
WHERE metadata @> '{"chronic_conditions": ["diabetes"]}';
Covering Indexes — Avoid Heap Fetches
-- PostgreSQL 11+ INCLUDE clause
-- Query only needs id, name, status — never touches the heap
CREATE INDEX idx_patient_covering
ON patients(facility_id)
INCLUDE (id, name, status);
Real Numbers
| Optimisation | Before | After |
|---|---|---|
| Added composite index on (facility_id, status, scheduled_at) | 4,200ms | 38ms |
| Replaced full index with partial index on active patients | 890ms | 45ms |
| Added covering index for patient list endpoint | 320ms | 12ms |
| GIN index on JSONB metadata | 2,100ms | 91ms |
Maintenance
Indexes aren't free — they slow writes and take disk space. Run this monthly to find bloated or unused indexes:
-- Unused indexes (candidates for removal)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- Index bloat
SELECT * FROM pgstattuple('idx_active_patients');
Key Rules
- EXPLAIN ANALYZE before and after every change
- Composite: equality columns first, range last
- Partial indexes for skewed distributions
- Covering indexes to eliminate heap fetches
- AUTOVACUUM must be healthy — dead tuples kill index performance