SQL Index Strategy Patterns

-- Single column index
CREATE INDEX idx_users_email ON users (email);

-- Composite index (order matters for queries)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- Partial index (only index relevant rows)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- Covering index (includes columns for index-only scans)
CREATE INDEX idx_products_covering ON products (category_id)
INCLUDE (name, price);

-- Expression index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- GIN index for array/JSONB columns
CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_events_data ON events USING GIN (metadata jsonb_path_ops);

-- Unique index (enforces constraint)
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_logs_timestamp ON logs (created_at);

-- Check index usage
SELECT
    schemaname, tablename, indexname,
    idx_scan AS times_used,
    idx_tup_read AS rows_read,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find unused indexes
SELECT indexname, tablename,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;