Query planner and EXPLAIN

In short: The query planner chooses the fastest execution plan for an SQL query. With EXPLAIN you see which plan it picks and find slow spots.

In more detail: SQL describes what you want, not how. The planner estimates the costs of different plans from statistics (row counts, value distribution), for example index or full scan, join order and join method, and takes the cheapest.

In Depth

• EXPLAIN / EXPLAIN ANALYZE: Show the plan, or actually run it and measure real times.
• Scan types: Seq scan (read everything), index scan, index only scan, bitmap scan.
• Join methods: Nested loop (small), hash join (large, unordered), merge join (sorted).
• Statistics: Outdated statistics lead to bad plans. ANALYZE updates them.
• Common causes of slow queries: Missing index, function on the column in WHERE, SELECT *, N+1 queries.
• Selectivity: An index mainly pays off when few rows are hit.

See also: SQL index, B-tree and B+ tree