Slow database queries — EXPLAIN ANALYZE reading guide

Database queries are slow but it's unclear why. Adding indexes doesn't help or makes other queries slower. Need to understand the query plan to optimize effectively.

Use EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL) to see the actual query plan. Key things to look for: (1) Seq Scan on large tables — add an index on the filtered column. (2) Nested Loop with large outer set — consider a Hash Join or add an index to enable it. (3) Sort with large rows — add an index that covers the ORDER BY columns. (4) Bitmap Heap Scan — index is being used but many rows matched, fetching from heap. (5) Rows estimate vs actual: if estimates are way off, run ANALYZE table to update statistics. (6) Index-only scan is the best case — add INCLUDE columns to cover the query. (7) For complex queries: break into CTEs and analyze each part.

The query planner makes decisions based on table statistics, available indexes, and cost estimates. EXPLAIN ANALYZE shows what it actually did versus what it estimated, revealing where optimization is needed.