Database Scan Cost Performance (Table Scan vs Index Seek)

-- EXPLAIN ANALYZE: see the actual execution plan
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Table Scan (bad for large tables):
-- Seq Scan on orders (cost=0.00..85432.21 rows=1000000)
-- Reads EVERY row even if only 5 match

-- Index Scan (good):
-- Index Scan using idx_orders_user_id on orders (cost=0.42..8.30 rows=5)
-- Jumps directly to matching rows

-- Index Only Scan (best — no table access at all!):
-- Reads from index without accessing the table heap
-- Requires a covering index

-- Bitmap Index Scan (medium — batch index lookup):
-- Used when many rows match — fetches matching pages in batches
-- Better than Seq Scan, worse than Index Scan for small result sets

-- Force index usage (usually not needed — trust the optimizer)
SELECT /*+ INDEX(orders idx_orders_user_id) */ * FROM orders;  -- MySQL hint
SELECT * FROM orders WHERE user_id = 42;  -- PostgreSQL auto-selects

-- Step 1: Identify slow query
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id), SUM(o.total_amount)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
  AND o.created_at >= '2025-01-01'
GROUP BY u.id, u.name;

-- Step 2: Read the output
-- Seq Scan on orders (cost=0..92000, actual time=0.023..3200ms)
--   Filter: (status = 'completed')
--   Rows Removed by Filter: 850000
-- → Problem: filtering 850K rows to find 150K completed orders

-- Step 3: Create targeted index
CREATE INDEX idx_orders_completed_2025
  ON orders(user_id, created_at)
  WHERE status = 'completed';  -- partial index!

-- Step 4: Re-run EXPLAIN ANALYZE
-- Index Scan using idx_orders_completed_2025 (cost=0.42..120, actual time=0.1..45ms)
-- → 70x speedup! 3200ms → 45ms

-- Common patterns that cause Seq Scan (avoid in WHERE):
-- WHERE LOWER(email) = ...    → function on indexed column
-- WHERE status != 'active'   → negation often not indexable
-- WHERE category IN (1,2,...200) → large IN list