How to Use EXPLAIN ANALYZE to Optimize Slow SQL Queries
Inspect the query execution plan, index scans vs sequential table scans, execution time, and buffer cache hits.
EXPLAIN (ANALYZE, BUFFERS, COSTS)
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;Step-by-Step Query Breakdown
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders
WHERE customer_id = 1042 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 20;ANALYZE actually executes the query and compares estimated row counts against actual execution timing in milliseconds.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 1042
ORDER BY created_at DESC;Displays the iterator tree with cost, actual time per row, and loop counts.
Critical SQL Pitfalls & Precautions
- •WARNING: EXPLAIN ANALYZE actually executes the statement! If you run "EXPLAIN ANALYZE DELETE ...", the rows WILL be deleted from your database!
Optimize Slow Queries (EXPLAIN ANALYZE) - Frequently Asked Questions
Common questions about transactional safety, indexing, and engine compatibility.
"Seq Scan on table (cost=... rows=1000000)" indicates PostgreSQL is reading every single page on disk because no suitable index was found.
Related SQL Query Guides
Browse All SQL RecipesIdentify rows with duplicate column values using GROUP BY and HAVING count(*) > 1, and safely remove duplicates using ROW_NUMBER() CTEs.
Compare row-by-row logging (DELETE), fast table emptying with auto-increment reset (TRUNCATE), and complete table removal (DROP).
Atomically insert a new row or update an existing row if a unique constraint or primary key conflict is encountered.
Compute cumulative running totals over time or partitioned groups using SUM() OVER (ORDER BY ...).
Build a production database index on a table with millions of rows without locking out live INSERT, UPDATE, or DELETE operations.
Update values in one table based on matching columns or aggregations in a secondary table.