Query Performance & EXPLAIN

-- EXPLAIN shows the plan; ANALYZE actually runs it and reports real timings.
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.country_code = 'PT'
GROUP BY c.name;

-- What to look for in the output:
--
--   Seq Scan on orders          -> reading EVERY row. Fine on 500 rows,
--                                  a red flag on 50 million.
--   Index Scan using idx_...    -> using an index. Usually what you want.
--   Bitmap Heap Scan            -> index found many rows, then fetched them.
--   Nested Loop                 -> good for small inputs, terrible for large ones.
--   Hash Join / Merge Join      -> the usual choice for big joins.
--   rows=1000 ... actual rows=2000000
--                               -> the estimate is way off; run ANALYZE on the
--                                  table so the planner has fresh statistics.
--   Rows Removed by Filter      -> work done and thrown away: index the filter.

-- Index the columns you filter, join and sort on.
CREATE INDEX idx_orders_customer   ON orders(customer_id);
CREATE INDEX idx_orders_created    ON orders(created_at DESC);

-- Composite index: order matters. This serves
--   WHERE customer_id = ?
--   WHERE customer_id = ? AND status = ?
--   WHERE customer_id = ? AND status = ? ORDER BY created_at DESC
-- but NOT a query filtering only on status.
CREATE INDEX idx_orders_lookup ON orders(customer_id, status, created_at DESC);

-- Partial index: smaller and faster when you always filter the same way
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Covering index: the query is answered from the index alone (an "index-only scan")
CREATE INDEX idx_orders_covering ON orders(customer_id) INCLUDE (total, status);

-- Indexes are not free: every INSERT, UPDATE and DELETE has to maintain them,
-- and they take disk space. Find the ones nobody uses:
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

-- Patterns that silently disable an index:

-- BAD: a function on the indexed column
SELECT * FROM users WHERE LOWER(email) = 'ada@example.com';
-- FIX: index the expression instead
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- BAD: leading wildcard cannot use a normal B-tree index
SELECT * FROM products WHERE name LIKE '%phone%';
-- FIX: a trigram index or a full-text search column
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- BAD: arithmetic on the column
SELECT * FROM orders WHERE total * 1.23 > 100;
-- FIX: move the maths to the other side
SELECT * FROM orders WHERE total > 100 / 1.23;

-- BAD: SELECT * when you need two columns — more I/O, no index-only scan
SELECT * FROM orders WHERE customer_id = 42;
SELECT id, total FROM orders WHERE customer_id = 42;   -- better

-- BAD: OFFSET paging deep into a large table (it still reads every skipped row)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- FIX: keyset pagination
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;