EXPLAIN ANALYZE

-- ===== Basic EXPLAIN (no execution) =====
EXPLAIN SELECT * FROM users WHERE email = 'a@x.io';
--                               QUERY PLAN
-- Index Scan using users_email_key on users  (cost=0.28..8.30 rows=1 width=...)
--   Index Cond: (email = 'a@x.io'::text)

-- ===== EXPLAIN ANALYZE (actually runs) =====
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@x.io';
-- Adds actual time + rows for each node.

-- ===== Useful flags =====
EXPLAIN (ANALYZE, BUFFERS) ...
-- Adds Buffers: shared hit=N read=M

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ...
-- Machine-readable; great for tools like pev2 (visualiser).

EXPLAIN (ANALYZE, VERBOSE, SETTINGS) ...
-- Verbose adds column types; SETTINGS shows non-default planner settings.

-- ===== What the columns mean =====
-- cost=startup..total      planner estimates (lower is better)
-- rows                     ESTIMATED rows returned
-- width                    estimated row width in bytes
-- actual time=startup..total  real time (only with ANALYZE)
-- loops                    number of times this node executed

-- ===== Reading bottom-up =====
EXPLAIN ANALYZE
SELECT u.name, COUNT(*) AS orders
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.country = 'AU'
GROUP BY u.name
ORDER BY orders DESC;

-- Plan reads bottom-up:
-- Sort
--   ->  HashAggregate
--         ->  Hash Join
--               ->  Seq Scan on orders
--               ->  Hash
--                     ->  Index Scan on users_country_idx

-- ===== Common bad smells =====
-- Seq Scan on a big table when you expected an index -> missing or unused index
-- Rows estimate WILDLY off actual -> stale statistics; ANALYZE the table
-- Nested Loop with high rows on inner side -> JOIN explosion
-- Sort with 'Sort Method: external merge' -> spill to disk; increase work_mem
-- Hash Join 'Memory: ...' shows the hash table size

-- ===== Force planner choices for diagnostics (not for prod!) =====
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;

-- Other knobs: enable_indexscan, enable_hashjoin, enable_mergejoin, enable_nestloop

-- ===== Statistics =====
ANALYZE users;                          -- refresh stats on this table
SELECT relname, n_live_tup, last_analyze
FROM pg_stat_user_tables
WHERE relname = 'users';

-- ===== Auto-vacuum / auto-analyze status =====
SELECT relname, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autoanalyze DESC NULLS LAST
LIMIT 10;

-- ===== Index hints (Postgres has none; use schema design) =====
-- Postgres does not support HINT comments like Oracle.
-- Influence the planner by:
--   - creating the right index
--   - keeping stats fresh
--   - adjusting work_mem / random_page_cost / cpu_tuple_cost

-- ===== Online tools =====
-- explain.depesz.com    paste plan; get colour-coded breakdown
-- pev2 / explain.dalibo.com  visual plan tree

-- ===== Patterns to internalise =====
-- - EXPLAIN ANALYZE on any slow query before guessing
-- - Use BUFFERS + JSON output for tooling
-- - Compare actual vs estimated rows for stats freshness
-- - Address the BOTTOM node first; high cost roots usually trace there

-- ===== Pitfalls =====
-- - EXPLAIN ANALYZE on destructive queries (UPDATE / DELETE) — IT RUNS
-- - Reading plans top-down; they read bottom-up
-- - Tuning random_page_cost without measuring (default 4 assumes spinning disk; SSD is closer to 1)
-- - Ignoring 'rows=1' on a node that fans out (multiply by loops)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- Look for Seq Scan vs Index Scan / Index Only Scan.