Problem Statement
How do you use EXPLAIN (or EXPLAIN ANALYZE) to tune a slow query, and what are the first things you look for?
Explanation
Start by capturing the plan to see join order, access paths, and estimated versus actual row counts. Look for scans on very large tables where a selective index could be used, and for big mismatches between estimated and actual rows that signal bad statistics or non-SARGable predicates.
Then check operators with the highest cost or time. Add or adjust indexes to support the most selective predicates and common joins. Fix non-SARGable expressions, reduce columns (avoid select star), and ensure filters happen as early as possible. Re-run EXPLAIN ANALYZE to verify the change improved row counts and timing.
Code Solution
SolutionRead Only
EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';
Practice Sets
This question appears in the following practice sets:
