EXPLAINANALYZESELECT u.first_name,COUNT(*)AS order_count
FROM users u
JOIN orders o ON o.user_id= u.idWHERE u.is_active= true
GROUPBY u.first_name;
Output
-- Shows execution plan with actual timing and row counts
-- Look for: Seq Scan (bad on big tables), Index Scan (good), Hash Join vs Nested Loop
Note EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and shows real timings. Use EXPLAIN (FORMAT JSON) in PostgreSQL for machine-parseable output. Never run EXPLAIN ANALYZE on destructive queries (DELETE/UPDATE) without wrapping in a transaction and rolling back.
-- Often slow (index may not be used):WHERE city ='Seattle'OR state ='WA'-- Faster alternative:SELECT...WHERE city ='Seattle'UNIONSELECT...WHERE state ='WA';
Example
-- May not use either index effectively:SELECT*FROM users
WHERE email ='[email protected]'OR phone ='2065551234';-- Better with UNION (each query uses its own index):SELECT*FROM users WHERE email ='[email protected]'UNIONSELECT*FROM users WHERE phone ='2065551234';
Output
-- UNION lets each branch use its optimal index
Note OR conditions on different columns often prevent the optimizer from using indexes efficiently. Rewriting as UNION (or UNION ALL if you know there are no duplicates) lets each branch use its own index. Check with EXPLAIN to verify.
Frequently asked questions
How does SQL handle slow query?
SQL covers this with 2 copy-ready snippets on this page. The "EXPLAIN / Query Plan" snippet in SQL uses `-- PostgreSQL`.
Which statement does the SQL example use?
The "EXPLAIN / Query Plan" snippet uses `-- PostgreSQL`, from the Indexes & Performance section of the SQL cheat sheet.
What other SQL snippets are shown for "slow query"?
Besides "EXPLAIN / Query Plan", this page also shows "OR Conditions and Index Usage".
Is there anything to watch out for?
Yes. For "EXPLAIN / Query Plan": EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and shows real timings. Use EXPLAIN (FORMAT JSON) in PostgreSQL for machine-parseable output. Never run EXPLAIN ANALYZE on destructive queries (DELETE/UPDATE) without wrapping in a transaction and rolling back.