Composite Index & Column Order

-- Index: (department_id, salary)
CREATE INDEX idx_emp_dept_salary ON employees (department_id, salary);

-- Query 1: uses index (leading column present)
EXPLAIN SELECT name, salary FROM employees
WHERE department_id = 3;
-- ✓ Index Scan using idx_emp_dept_salary

-- Query 2: uses index (both columns)
EXPLAIN SELECT name FROM employees
WHERE department_id = 3 AND salary > 70000;
-- ✓ Index Scan — range on second column is fine after equality on first

-- Query 3: CANNOT use index (leading column absent)
EXPLAIN SELECT name, salary FROM employees
WHERE salary > 70000;
-- ✗ Seq Scan — salary is not the leading column
-- Fix: create a separate index on (salary) if this query is common

-- Wrong column order for this query pattern:
-- WHERE status = 'pending' AND created_at > '2024-01-01'
CREATE INDEX idx_wrong ON orders (created_at, status);
-- Optimizer can use this only for range on created_at,
-- then must filter status in memory.

-- Correct order: equality column first
CREATE INDEX idx_correct ON orders (status, created_at DESC);
-- Optimizer binary-searches to status = 'pending' leaf,
-- then scans forward on the sorted created_at range.

-- Proof: compare EXPLAIN output
EXPLAIN SELECT id, amount FROM orders
WHERE status = 'pending' AND created_at > '2024-01-01'
ORDER BY created_at DESC;

-- Bad: is_active has only 2 values — poor leading column
CREATE INDEX idx_bad_leading ON users (status, name);
-- If status has values: 'active', 'inactive', 'banned' (3 values),
-- 33% of rows match 'active' — planner may prefer seq scan.

-- Better option 1: partial index (no low-cardinality column needed)
CREATE INDEX idx_active_users_name ON users (name) WHERE status = 'active';

-- Better option 2: put high-cardinality column first
-- If searching by city + status:
CREATE INDEX idx_city_status ON users (city, status);
-- city has high cardinality → narrows result set quickly