Query Optimization

-- ANALYZE collects statistics on a table (run automatically by autovacuum)
ANALYZE orders;

-- View statistics: most common values for a column
SELECT
  attname,
  n_distinct,
  correlation    -- 1.0 = perfectly sorted, -1.0 = reverse sorted, ~0 = random
FROM  pg_stats
WHERE tablename = 'orders';

-- Increase statistics target for columns with skewed data (default = 100)
ALTER TABLE orders
  ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;
-- More statistics = better estimates = better plans for skewed distributions

-- Shows the query PLAN (does NOT run the query)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- Shows plan + actually RUNS the query and reports real timing
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Full detail: plan + run + memory + I/O buffers (most useful)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT * FROM orders WHERE user_id = 42;

-- JSON format for programmatic tools (explain.depesz.com, pganalyze)
EXPLAIN (ANALYZE, FORMAT JSON)
  SELECT * FROM orders WHERE user_id = 42;

-- Which scan type will be chosen?

-- Table: 1M rows, 1% match → Index Scan
SELECT * FROM orders WHERE user_id = 42;

-- Table: 1M rows, 60% match → Sequential Scan (most rows anyway)
SELECT * FROM orders WHERE status != 'completed';

-- Two indexes combined → Bitmap Index Scan + BitmapAnd
SELECT * FROM orders
WHERE  user_id = 42 AND status = 'pending';
-- PostgreSQL may use idx_user_id + idx_status, intersect bitmaps

-- Covering index → Index Only Scan
SELECT order_id, status FROM orders WHERE user_id = 42;
-- If index on (user_id) INCLUDE (order_id, status) exists

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

-- Output includes:
--   Buffers: shared hit=5  read=120
--   ↑ "hit" = came from memory cache (fast)
--   ↑ "read" = had to read from disk (slow — 120 random disk reads!)
--
-- Goal: maximize cache hits, minimize disk reads
-- If read is high → either the table/index needs more RAM (shared_buffers)
-- or the query is doing too much disk I/O (missing index / bad plan)

-- Query: join users and orders
EXPLAIN ANALYZE
SELECT u.name, o.total
FROM   users u
JOIN   orders o ON o.user_id = u.id
WHERE  u.id = 42;

-- Plan: user.id=42 matches 1 user → few orders → Nested Loop
--
-- Hash Join (analytics query, many rows):
-- EXPLAIN ANALYZE
-- SELECT u.name, COUNT(*) FROM users u JOIN orders o ON o.user_id = u.id
-- GROUP BY u.name;
-- → Hash Join: builds hash table on users, probes with orders

-- Check current work_mem
SHOW work_mem;   -- default: 4MB (often too low)

-- Set it for your current session (safe)
SET work_mem = '64MB';

-- In EXPLAIN ANALYZE, disk spill shows as:
--   Sort Method: external merge  Disk: 1456kB  ← spilling to disk
--   Sort Method: quicksort  Memory: 25kB       ← fits in memory ✓

-- Tip: a single query can use work_mem × number_of_sort_operations
-- So with 100 connections × 64MB = 6.4GB potential RAM usage
-- Set it per-session for heavy queries rather than globally

-- Other common expression indexes
-- Index on extracted JSONB field:
CREATE INDEX idx_meta_brand ON products ((metadata ->> 'brand'));

-- Index on date part (group by month):
CREATE INDEX idx_orders_month ON orders (DATE_TRUNC('month', created_at));

-- Index on COALESCE:
CREATE INDEX idx_name_coalesce ON users (COALESCE(display_name, username));

-- Column type: user_id BIGINT
-- Index: CREATE INDEX idx ON orders (user_id);

-- ✗ Bad: passing a string — implicit cast, index may not be used
SELECT * FROM orders WHERE user_id = '42';

-- ✓ Good: correct type
SELECT * FROM orders WHERE user_id = 42;

-- ✗ Bad: comparing varchar id to integer
SELECT * FROM users WHERE phone = 9876543210;   -- phone is TEXT

-- ✓ Always pass parameters in the correct type in your ORM/driver