Query Execution Plans (EXPLAIN)
Using the database's query planner output to understand how a query is executed and identify performance bottlenecks.
Description
Every SQL query goes through a query planner that determines the most efficient execution strategy based on table statistics, available indexes, and cost estimates. The EXPLAIN command reveals this plan without executing the query, while EXPLAIN ANALYZE actually runs the query and reports real execution times alongside the estimates. In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) provides the richest output, including buffer hit/read statistics that reveal I/O behavior.
Key things to look for in execution plans: Sequential Scan on large tables (usually indicates a missing index), Nested Loop joins with high row estimates (may need a Hash Join or Merge Join instead), Sort operations that spill to disk (increase work_mem or add an index to avoid the sort), and large discrepancies between estimated and actual row counts (indicates stale statistics -- run ANALYZE on the table). The 'actual time' values show wall-clock time per node, and 'rows' shows actual row counts versus estimates.
Developing fluency in reading execution plans is one of the highest-leverage skills for backend engineers. Make it a practice to EXPLAIN every query before it reaches production. Tools like pganalyze, auto_explain (a PostgreSQL extension that logs plans for slow queries), and pg_stat_statements help surface problematic queries in running systems. Set log_min_duration_statement to capture queries exceeding your latency SLO.
Prompt Snippet
Enable the auto_explain extension with auto_explain.log_min_duration = 200ms and auto_explain.log_analyze = true to automatically capture execution plans for slow queries. Use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) during development and pipe output through pganalyze or explain.dalibo.com for visualization. Flag any plan node with actual rows differing from estimated rows by more than 10x -- this indicates stale statistics requiring ANALYZE or a histogram adjustment via ALTER TABLE SET STATISTICS.
Tags
Related Terms
Database Indexing Strategies
Creating and managing indexes to accelerate query performance while balancing write overhead and storage costs.
Composite Indexes
Indexes spanning multiple columns, ordered to match query filter and sort patterns for optimal lookup performance.
N+1 Query Problem
A performance anti-pattern where fetching a list of N records triggers N additional queries to load related data, instead of using a single JOIN or batch query.