# SQL Indexing Strategies

Vendor-neutral indexing fundamentals. For PostgreSQL-specific index types (GIN, GiST, BRIN, Hash, partial, expression indexes), see `postgres-ops/references/indexing.md`.

## B-Tree (Default)

The standard index type across all major databases. Best for: equality, range queries, ORDER BY, prefix LIKE.

```sql
-- Standard index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Works well for:
WHERE email = 'x@y.com'           -- equality
WHERE email LIKE 'john%'          -- prefix search
WHERE created_at > '2024-01-01'   -- range
ORDER BY created_at               -- sorting
```

## Composite Indexes

### Column Order Matters

```sql
-- Leftmost prefix rule
CREATE INDEX idx_orders ON orders(user_id, status, created_at);

-- This index supports:
WHERE user_id = 123                              -- yes
WHERE user_id = 123 AND status = 'pending'       -- yes
WHERE user_id = 123 AND status = 'pending'
  AND created_at > '2024-01-01'                  -- yes
WHERE user_id = 123
  AND created_at > '2024-01-01'                  -- partial (user_id only)
WHERE status = 'pending'                          -- no (user_id not present)
```

### Optimal Column Order

```sql
-- Rule: equality columns first, then range columns
-- Most selective equality column first when multiple equalities

-- If filtering by status (equality) and date range:
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- If user_id is more selective than status:
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);
```

## Covering Indexes

Include extra columns to avoid table lookup (index-only scan):

```sql
-- Query needs name but filters by email
SELECT name FROM users WHERE email = 'x@y.com';

-- Covering index (PostgreSQL INCLUDE, SQL Server INCLUDE)
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name);

-- Now the query uses index-only scan (no table access needed)
```

### When to Use

```sql
-- Frequently accessed columns in SELECT
CREATE INDEX idx_orders_status ON orders(status)
INCLUDE (total, created_at);

-- Supports without table access:
SELECT total, created_at FROM orders WHERE status = 'pending';
```

## Query Analysis with EXPLAIN

```sql
-- Basic plan
EXPLAIN SELECT * FROM users WHERE email = 'x@y.com';

-- Key scan types to look for:
-- Seq Scan       - Full table scan (bad for large tables)
-- Index Scan     - Using index, then fetching rows
-- Index Only Scan - Using covering index (best)
-- Bitmap Scan    - Multiple index conditions combined
```

```sql
-- With actual execution metrics
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

-- Shows:
-- Planning Time: 0.5 ms
-- Execution Time: 12.3 ms
-- actual rows vs estimated rows (mismatch = stale statistics)
```

## Anti-Patterns

| Mistake | Why | Fix |
|---------|-----|-----|
| Function on indexed column | Prevents index use | Expression index or rewrite query |
| `WHERE col LIKE '%text%'` | Leading wildcard, no B-tree match | Full-text search or trigram index |
| `OR` across different columns | May skip index | Rewrite as `UNION ALL` |
| Over-indexing | Slows writes, wastes space | Audit unused indexes regularly |
| Missing index on FK column | Slow cascading deletes, slow joins | Add B-tree on FK columns |

## Quick Reference

| Scenario | Index Strategy |
|----------|---------------|
| Equality lookup | B-tree on column |
| Range queries | B-tree on column |
| Multiple conditions | Composite (equality first, range last) |
| Avoid table access | Covering index with INCLUDE |
| Case-insensitive | Expression index on LOWER() |
| Full-text search | Database-specific (GIN in PostgreSQL) |

## See Also

- **PostgreSQL-specific**: `postgres-ops/references/indexing.md` - GIN, GiST, BRIN, Hash, partial, expression indexes
- **SQLite-specific**: `sqlite-ops` - SQLite indexing considerations

# SQL Window Functions

Complete reference for window functions and analytical queries.

## Core Window Functions

### ROW_NUMBER

Assigns unique sequential numbers within partition:

```sql
-- Rank employees by salary within department
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- Get top N per group
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;
```

### RANK and DENSE_RANK

Handle ties differently:

```sql
-- RANK: 1, 2, 2, 4 (skips after ties)
-- DENSE_RANK: 1, 2, 2, 3 (no skip)
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM contestants;

-- Result for scores [100, 95, 95, 90]:
-- name    score   rank   dense_rank
-- Alice   100     1      1
-- Bob     95      2      2
-- Carol   95      2      2
-- Dave    90      4      3
```

### NTILE

Divide into N equal groups:

```sql
-- Divide into quartiles
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

-- Percentile buckets
SELECT
    name,
    score,
    NTILE(100) OVER (ORDER BY score) as percentile
FROM students;
```

## Navigation Functions

### LAG and LEAD

Access previous/next rows:

```sql
-- Previous and next day revenue
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    LEAD(revenue, 1) OVER (ORDER BY date) as next_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) as day_change
FROM daily_sales;

-- With default value for first/last
SELECT
    date,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY date) as prev_or_zero
FROM daily_sales;

-- Multiple periods back
SELECT
    date,
    revenue,
    LAG(revenue, 7) OVER (ORDER BY date) as same_day_last_week
FROM daily_sales;
```

### FIRST_VALUE and LAST_VALUE

Get first/last value in window:

```sql
-- Compare to first sale of month
SELECT
    date,
    revenue,
    FIRST_VALUE(revenue) OVER (
        PARTITION BY DATE_TRUNC('month', date)
        ORDER BY date
    ) as first_day_revenue,
    revenue - FIRST_VALUE(revenue) OVER (
        PARTITION BY DATE_TRUNC('month', date)
        ORDER BY date
    ) as diff_from_first
FROM daily_sales;

-- Note: LAST_VALUE needs explicit frame
SELECT
    date,
    revenue,
    LAST_VALUE(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last_revenue
FROM daily_sales;
```

### NTH_VALUE

Get Nth value in window:

```sql
-- Get 2nd highest salary per department
SELECT
    department,
    name,
    salary,
    NTH_VALUE(salary, 2) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as second_highest
FROM employees;
```

## Aggregate Window Functions

### Running Totals

```sql
-- Running total
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- Running total by category
SELECT
    date,
    category,
    amount,
    SUM(amount) OVER (
        PARTITION BY category
        ORDER BY date
    ) as category_running_total
FROM transactions;
```

### Moving Averages

```sql
-- 7-day moving average
SELECT
    date,
    value,
    AVG(value) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7day
FROM metrics;

-- Centered moving average
SELECT
    date,
    value,
    AVG(value) OVER (
        ORDER BY date
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) as centered_avg
FROM metrics;
```

### Cumulative Statistics

```sql
-- Running count, sum, avg, min, max
SELECT
    date,
    revenue,
    COUNT(*) OVER (ORDER BY date) as cumulative_count,
    SUM(revenue) OVER (ORDER BY date) as cumulative_sum,
    AVG(revenue) OVER (ORDER BY date) as cumulative_avg,
    MIN(revenue) OVER (ORDER BY date) as cumulative_min,
    MAX(revenue) OVER (ORDER BY date) as cumulative_max
FROM daily_sales;
```

## Window Frame Specification

### ROWS vs RANGE

```sql
-- ROWS: Physical row count
SELECT
    date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as sum_3_rows
FROM sales;

-- RANGE: Logical value range (careful with duplicates)
SELECT
    date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) as sum_7_days
FROM sales;
```

### Frame Boundaries

```sql
-- All frames available
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- Entire partition
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW          -- From start to here
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING          -- From here to end
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING                  -- 7 rows centered
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW                  -- 7 rows trailing
```

## Practical Examples

### Year-over-Year Comparison

```sql
SELECT
    date,
    revenue,
    LAG(revenue, 365) OVER (ORDER BY date) as revenue_year_ago,
    revenue - LAG(revenue, 365) OVER (ORDER BY date) as yoy_change,
    ROUND(100.0 * (revenue - LAG(revenue, 365) OVER (ORDER BY date))
        / NULLIF(LAG(revenue, 365) OVER (ORDER BY date), 0), 2) as yoy_pct
FROM daily_sales;
```

### Running Percentage of Total

```sql
SELECT
    category,
    sales,
    SUM(sales) OVER () as total,
    ROUND(100.0 * sales / SUM(sales) OVER (), 2) as pct_of_total,
    ROUND(100.0 * SUM(sales) OVER (ORDER BY sales DESC)
        / SUM(sales) OVER (), 2) as cumulative_pct
FROM category_sales;
```

### Session/Gap Detection

```sql
-- Find sessions (gaps > 30 minutes = new session)
WITH events_with_gaps AS (
    SELECT
        *,
        EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (
            PARTITION BY user_id ORDER BY timestamp
        ))) / 60 as minutes_since_last
    FROM user_events
)
SELECT
    *,
    SUM(CASE WHEN minutes_since_last > 30 OR minutes_since_last IS NULL
        THEN 1 ELSE 0 END) OVER (
        PARTITION BY user_id ORDER BY timestamp
    ) as session_id
FROM events_with_gaps;
```

### Deduplication with Row Number

```sql
-- Keep only the latest record per user
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY updated_at DESC
        ) as rn
    FROM users
)
SELECT * FROM ranked WHERE rn = 1;
```

## Performance Tips

1. **Index the ORDER BY column** - Window functions sort data
2. **Limit partitions** - Large partitions = more memory
3. **Named windows** - Reuse window definitions
4. **Avoid nested windows** - Use CTEs instead

### Named Windows

```sql
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER dept_salary as rank,
    AVG(salary) OVER dept_salary as dept_avg,
    salary - AVG(salary) OVER dept_salary as diff_from_avg
FROM employees
WINDOW dept_salary AS (PARTITION BY department ORDER BY salary DESC);
```