⚡ Advancedadvanced

How to analyze query performance with EXPLAIN

Understand how the database executes your query and find performance bottlenecks.

EXPLAIN — see the query planPostgreSQL

EXPLAIN
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Shows the query execution plan without running it. Look for Seq Scan (no index) vs Index Scan.

EXPLAIN ANALYZE — run and measurePostgreSQL

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Actually executes the query and shows real timing. Look at "actual time" vs "estimated rows".

When to use: Diagnosing slow queries. Shows actual execution time per step.

EXPLAIN ANALYZE actually runs the query — avoid on heavy DELETE/UPDATE without wrapping in a transaction.

EXPLAIN FORMAT JSON (machine readable)PostgreSQL

EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'alice@example.com';

Returns the execution plan as JSON. Useful for tools like explain.dalibo.com.

MySQL EXPLAINMySQL

EXPLAIN SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Shows the execution plan in MySQL. Check the "type" column — "ALL" means full table scan.

Common Mistakes

→Seq Scan is not always bad — for small tables a sequential scan is faster than an index scan
→EXPLAIN ANALYZE runs the query — wrap in a transaction and ROLLBACK if running on a write query