Explain SQL query execution with index analysis and optimization hints. Part of the DevTools Surf developer suite. Browse more tools in the Developer Utilities collection.
Use Cases
Identify why a query that runs fast in development is slow in production with real data volumes.
Determine which columns need indexing based on filter and join conditions in the explain plan.
Compare two query rewrites (e.g., JOIN vs. subquery) by analyzing their respective execution plans.
Explain complex query plans to junior developers as a learning tool during code review.
Tips
Run EXPLAIN ANALYZE (not just EXPLAIN) to see actual row counts vs. estimated — large discrepancies indicate stale statistics that need VACUUM ANALYZE.
Look for sequential scans on large tables first — they are almost always the highest-priority index candidates.
Sort time and rows in the explain output: the most expensive nodes (highest actual time) are where optimization effort yields the biggest gains.
Fun Facts
PostgreSQL's EXPLAIN was introduced in version 6.0 (1996) and has been continuously extended — the JSON output format was added in 2011 in version 9.0.
The EXPLAIN ANALYZE command actually executes the query; for DELETE or UPDATE queries, wrap in a transaction and ROLLBACK to avoid unintended data changes.
MySQL's query optimizer was completely rewritten in MySQL 8.0 (2018) with a cost-based optimizer that uses histogram statistics, dramatically improving execution plan quality for complex queries.
FAQ
What's the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the estimated execution plan without running the query. EXPLAIN ANALYZE runs the query and shows actual row counts and timing alongside estimates. Use ANALYZE to catch plan estimation errors.
What is a Seq Scan and is it always bad?
A sequential scan reads every row in a table. It is efficient for small tables or queries returning more than 10–20% of rows. On large tables with selective filters, it almost always indicates a missing index.
