**Problem: Slow GROUP BY Without Index**

Slow (full table scan + sort):

SELECT department, COUNT(*), AVG(salary)

FROM employees

GROUP BY department;

-- 10M rows, no index

-- Full scan + sort: 12 seconds

Fast (indexed GROUP BY):

CREATE INDEX idx_dept ON employees(department);

SELECT department, COUNT(*), AVG(salary)

FROM employees

GROUP BY department;

-- Index scan, no sort needed: 0.8 seconds (15x faster)

**Problem: Table Lookups in Aggregates**

Slow (index + table lookups):

CREATE INDEX idx_dept ON employees(department);

SELECT department, AVG(salary), MAX(salary)

FROM employees

GROUP BY department;

-- Index for grouping, but must lookup salary from table

-- 10M table lookups: 8 seconds

Fast (covering index, no table access):

CREATE INDEX idx_dept_salary ON employees(department, salary);

SELECT department, AVG(salary), MAX(salary)

FROM employees

GROUP BY department;

-- All data in index, no table access

-- Index-only scan: 0.5 seconds (16x faster)

**Problem: Aggregating Before Filtering**

Slow (aggregate all rows):

SELECT category, COUNT(*), AVG(price)

FROM products

GROUP BY category;

-- Aggregates all 5M products (including inactive)

-- Time: 6 seconds

Fast (filter first with WHERE):

SELECT category, COUNT(*), AVG(price)

FROM products

WHERE active = true -- Reduces to 1M rows

GROUP BY category;