Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
\nUnderstanding EXPLAIN output is fundamental to optimization.
\nPostgreSQL EXPLAIN:
\n-- Basic explain\nEXPLAIN SELECT * FROM users WHERE email = 'user@example.com';\n\n-- With actual execution stats\nEXPLAIN ANALYZE\nSELECT * FROM users WHERE email = 'user@example.com';\n\n-- Verbose output with more details\nEXPLAIN (ANALYZE, BUFFERS, VERBOSE)\nSELECT u.*, o.order_total\nFROM users u\nJOIN orders o ON u.id = o.user_id\nWHERE u.created_at > NOW() - INTERVAL '30 days';\nKey Metrics to Watch:
\nIndexes are the most powerful optimization tool.
\nIndex Types:
\n-- Standard B-Tree index\nCREATE INDEX idx_users_email ON users(email);\n\n-- Composite index (order matters!)\nCREATE INDEX idx_orders_user_status ON orders(user_id, status);\n\n-- Partial index (index subset of rows)\nCREATE INDEX idx_active_users ON users(email)\nWHERE status = 'active';\n\n-- Expression index\nCREATE INDEX idx_users_lower_email ON users(LOWER(email));\n\n-- Covering index (include additional columns)\nCREATE INDEX idx_users_email_covering ON users(email)\nINCLUDE (name, created_at);\n\n-- Full-text search index\nCREATE INDEX idx_posts_search ON posts\nUSING GIN(to_tsvector('english', title || ' ' || body));\n\n-- JSONB index\nCREATE INDEX idx_metadata ON events USING GIN(metadata);\nAvoid SELECT *:
\n-- Bad: Fetches unnecessary columns\nSELECT * FROM users WHERE id = 123;\n\n-- Good: Fetch only what you need\nSELECT id, email, name FROM users WHERE id = 123;\nUse WHERE Clause Efficiently:
\n-- Bad: Function prevents index usage\nSELECT * FROM users WHERE LOWER(email) = 'user@example.com';\n\n-- Good: Create functional index or use exact match\nCREATE INDEX idx_users_email_lower ON users(LOWER(email));\n-- Then:\nSELECT * FROM users WHERE LOWER(email) = 'user@example.com';\n\n-- Or store normalized data\nSELECT * FROM users WHERE email = 'user@example.com';\nOptimize JOINs:
\n-- Bad: Cartesian product then filter\nSELECT u.name, o.total\nFROM users u, orders o\nWHERE u.id = o.user_id AND u.created_at > '2024-01-01';\n\n-- Good: Filter before join\nSELECT u.name, o.total\nFROM users u\nJOIN orders o ON u.id = o.user_id\nWHERE u.created_at > '2024-01-01';\n\n-- Better: Filter both tables\nSELECT u.name, o.total\nFROM (SELECT * FROM users WHERE created_at > '2024-01-01') u\nJOIN orders o ON u.id = o.user_id;\nDetailed pattern documentation lives in references/details.md. Read that file when the navigation tier above is insufficient.
-- Update statistics\nANALYZE users;\nANALYZE VERBOSE orders;\n\n-- Vacuum (PostgreSQL)\nVACUUM ANALYZE users;\nVACUUM FULL users; -- Reclaim space (locks table)\n\n-- Reindex\nREINDEX INDEX idx_users_email;\nREINDEX TABLE users;\nLIKE '%abc' can't use index-- Find slow queries (PostgreSQL)\nSELECT query, calls, total_time, mean_time\nFROM pg_stat_statements\nORDER BY mean_time DESC\nLIMIT 10;\n\n-- Find missing indexes (PostgreSQL)\nSELECT\n schemaname,\n tablename,\n seq_scan,\n seq_tup_read,\n idx_scan,\n seq_tup_read / seq_scan AS avg_seq_tup_read\nFROM pg_stat_user_tables\nWHERE seq_scan > 0\nORDER BY seq_tup_read DESC\nLIMIT 10;\n\n-- Find unused indexes (PostgreSQL)\nSELECT\n schemaname,\n tablename,\n indexname,\n idx_scan,\n idx_tup_read,\n idx_tup_fetch\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\nORDER BY pg_relation_size(indexrelid) DESC;\n