PostgreSQL Index Optimization — Diagnosing Slow Queries and Index Strategy

-- Enable pg_stat_statements (postgresql.conf)
shared_preload_libraries = 'pg_stat_statements'

-- Install
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

-- Single column
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite index (highest selectivity first)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

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

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

-- Online creation (without locking)
CREATE INDEX CONCURRENTLY idx_posts_category ON posts (category);

-- B-tree: default, range queries (=, <, >, BETWEEN, LIKE 'prefix%')
CREATE INDEX idx_b ON events (created_at);

-- Hash: equality only, fast
CREATE INDEX idx_h ON sessions USING HASH (session_token);

-- GIN: arrays, JSONB, full-text search
CREATE INDEX idx_gin ON posts USING GIN (tags);
CREATE INDEX idx_jsonb ON products USING GIN (metadata jsonb_path_ops);

-- Index list and usage counts for a table
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS size,
  idx_scan AS scans
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

-- Unused indexes (consider dropping)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

-- Update statistics (so the planner can choose a good plan)
ANALYZE orders;

-- VACUUM + ANALYZE
VACUUM ANALYZE orders;

-- Check autovacuum status
SELECT * FROM pg_stat_user_tables WHERE relname = 'orders';