PostgreSQL composite indexes for multi-column query patterns

-- BAD: Three single-column indexes — planner may not combine them efficiently
CREATE INDEX idx_traces_status ON traces(status);
CREATE INDEX idx_traces_trust_score ON traces(trust_score);
CREATE INDEX idx_traces_created_at ON traces(created_at);

-- GOOD: Composite index ordered: equality filter → range filter → sort
CREATE INDEX idx_traces_status_trust_created ON traces(status, trust_score DESC, created_at DESC);

-- Partial index — only indexes rows that match the WHERE (smaller, faster)
CREATE INDEX idx_validated_traces ON traces(trust_score DESC, created_at DESC)
WHERE status = 'validated';

-- Partial index for non-null embeddings (used by embedding worker polling)
CREATE INDEX idx_traces_needs_embedding ON traces(id)
WHERE embedding IS NULL;

-- Verify index is used
EXPLAIN ANALYZE
SELECT id, title, trust_score
FROM traces
WHERE status = 'validated'
  AND trust_score > 0.5
ORDER BY created_at DESC
LIMIT 20;
-- Should show: Index Scan using idx_traces_status_trust_created
-- NOT: Seq Scan

-- Check index sizes and usage
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS scans,
    idx_tup_read AS rows_read
FROM pg_stat_user_indexes
WHERE tablename = 'traces'
ORDER BY idx_scan DESC;