JOIN Performance

-- EXPLAIN ANALYZE: the authoritative source for join algorithm and cost
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.email, o.total_amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'completed';

/* Sample EXPLAIN output (annotated):
Hash Join  (cost=1200.00..3400.00 rows=50000 width=48)  ← planner estimated cost
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (cost=0.00..1800.00 rows=50000)
        Filter: (status = 'completed')
        Rows Removed by Filter: 150000
  ->  Hash  (cost=800.00..800.00 rows=20000)
        ->  Seq Scan on users u (cost=0.00..800.00 rows=20000)

Analysis:
- Hash Join chosen: no usable index on o.user_id (missing FK index!)
- After adding: CREATE INDEX idx_orders_user_id ON orders(user_id);
  → planner switches to Index Nested Loop
*/

-- Force planner to use specific strategy (PostgreSQL — for testing only)
SET enable_hashjoin = OFF;
SET enable_mergejoin = OFF;
-- Now only nested loop is available; useful to compare actual runtimes

-- Refresh statistics (run after large data loads)
ANALYZE orders;
ANALYZE users;
ANALYZE VERBOSE orders;   -- shows rows sampled and estimated

-- Increase work_mem for a specific session (hash join uses in-memory hash table)
SET work_mem = '256MB';   -- default is 4MB — insufficient for large hash joins
SELECT o.id, u.email FROM orders o JOIN users u ON u.id = o.user_id;
RESET work_mem;

-- Check if hash join spilled to disk (bad: very slow)
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... FROM large_table a JOIN another_large b ON b.id = a.fk;
-- Look for: "Batches: 8" in Hash node — spilled to disk 8 times

-- Hint join order in PostgreSQL (disable reordering for debugging)
SET join_collapse_limit = 1;   -- respect textual join order
-- MySQL hint:
SELECT /*+ STRAIGHT_JOIN */ o.id, u.email
FROM orders o JOIN users u ON u.id = o.user_id;

-- Ensure FK indexes exist (biggest single win for join performance)
SELECT
    tc.table_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = tc.constraint_name
LEFT JOIN pg_indexes pi ON pi.tablename = tc.table_name AND pi.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY' AND pi.indexname IS NULL;