See how PostgreSQL runs a query with EXPLAIN, measure it for real with EXPLAIN ANALYZE, tell a slow sequential scan from a fast index scan, keep stats fresh with ANALYZE and VACUUM, and find slow queries with pg_stat_statements.
Why: EXPLAIN shows the plan PostgreSQL intends to use to run a query — without actually running it. You read it to find out whether it will use an index or scan the whole table, and the estimated cost of doing so.
EXPLAIN
SELECT * FROM orders WHERE user_id = 42;Why: adding ANALYZE actually runs the query and reports the real time taken and real row counts, so you can see where the time truly goes. Note: because it runs the query, do not use it on an UPDATE or DELETE unless you wrap it in a transaction you ROLLBACK.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;Why: "Seq Scan" in the plan means PostgreSQL is reading every row — fine for tiny tables, slow for big ones. "Index Scan" means it used an index to jump to the rows. Run these in order to watch the plan change. Note: we drop the index first so the "before" really shows a Seq Scan (you may have created it in an earlier topic).
-- start clean so the "before" plan is a Seq Scan
DROP INDEX IF EXISTS idx_orders_user_id;-- before an index: "Seq Scan on orders"
EXPLAIN SELECT * FROM orders WHERE user_id = 42;CREATE INDEX idx_orders_user_id ON orders (user_id);-- after: "Index Scan using idx_orders_user_id"
EXPLAIN SELECT * FROM orders WHERE user_id = 42;Why: PostgreSQL chooses plans using statistics about your data. ANALYZE refreshes those stats; VACUUM reclaims space from deleted/updated rows. Autovacuum does this for you in the background, but you can run them by hand after a big data change to keep plans sharp.
ANALYZE orders; -- refresh the stats the planner relies onVACUUM orders; -- reclaim dead space left by updates/deletesVACUUM ANALYZE orders; -- do both at onceWhy: pg_stat_statements is a built-in extension that records how long each query shape takes across the whole server, so you can find the real bottlenecks instead of guessing. Enable it once, then query its view sorted by total time.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;SELECT query, calls, round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;