**Execution Plan Analysis:**

```sql

-- PostgreSQL execution plan

EXPLAIN (ANALYZE, BUFFERS)

SELECT

employee_id,

department,

salary,

AVG(salary) OVER (

PARTITION BY department

ORDER BY salary

ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING

) as moving_avg

FROM employees;

```

**Key Metrics to Monitor:**

- Sort operations and memory usage

- Index usage in partition/order phases

- Buffer hits vs disk reads

- Execution time per partition

**Optimization Patterns:**

```sql

-- Pattern 1: Composite Index

CREATE INDEX idx_dept_salary ON employees(department, salary);

-- Pattern 2: Covering Index

CREATE INDEX idx_covering ON employees(department, salary)

INCLUDE (employee_id, hire_date);

-- Pattern 3: Partial Index for Hot Partitions

CREATE INDEX idx_active_dept ON employees(department, salary)

WHERE status = 'ACTIVE';

```

Index PARTITION BY columns first in composite indexes

Index ORDER BY columns second in composite indexes

Use ROWS frames instead of RANGE when possible

Bounded frames (BETWEEN) perform better than unbounded

Multiple window functions can share the same OVER clause

INCLUDE columns in covering indexes for better performance

EXPLAIN (ANALYZE, BUFFERS) shows detailed execution stats. work_mem controls window function memory.

EXPLAIN FORMAT=JSON provides execution details. Limited window function optimization features.

Execution plans show window spool operations. MAXDOP controls parallelism for window functions.