Query slow but don’t know why? EXPLAIN shows exactly how database executes it.
Basic Usage:
EXPLAIN SELECT * FROM Orders WHERE CustomerId = 123; -- Output shows: -- - Table scan or index usage -- - Number of rows examined -- - Join order -- - Estimated cost
PostgreSQL Analyze:
EXPLAIN ANALYZE SELECT * FROM Orders WHERE CustomerId = 123; -- Shows actual execution time and row counts
Red Flags in Output:
– “Table Scan” on large tables → Need index
– “Rows examined: 1000000” → Too many rows
– High cost numbers → Inefficient query
Fix Example:
-- EXPLAIN shows table scan EXPLAIN SELECT * FROM Orders WHERE CustomerId = 123; -- Add index CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId); -- Now EXPLAIN shows index seek (fast!) EXPLAIN SELECT * FROM Orders WHERE CustomerId = 123;
Essential tool for query optimization!
