SQL Query Optimization: EXPLAIN and Execution Plans

Explain how to use EXPLAIN to optimize SQL queries.

EXPLAIN Basics

EXPLAIN SELECT ... shows the query execution plan without running it. EXPLAIN ANALYZE SELECT ... executes the query and shows actual timing (PostgreSQL).

Key Indicators

• Seq Scan: Full table scan — a warning on large tables.
• Index Scan: Uses an index; generally faster.
• Index Only Scan: Covering index; fastest.
• Rows: Estimated row count; if far from actual, run ANALYZE to refresh statistics.
• Cost: Relative cost units; lower is better.

Common Optimizations

• Add indexes on WHERE and JOIN ON columns.
• Avoid SELECT *; fetch only needed columns.
• Avoid function calls on indexed columns; use range queries instead.
• Run ANALYZE table_name to update table statistics.

Reading the Plan Tree

Execution plans are trees. Execution flows from leaf nodes (data reads) to the root (final output). Deeper indentation means earlier execution.