**Pattern Performance Guidelines:**

**CTE Usage:**

- Break complex patterns into logical steps

- Each CTE should have a clear purpose

- Avoid deeply nested CTEs (max 3-4 levels)

- Use meaningful CTE names that describe the step

**Indexing Strategy:**

- Index all PARTITION BY columns

- Index all ORDER BY columns in the same order

- Consider covering indexes for frequently used patterns

- Monitor index usage and maintenance overhead

**Pattern-Specific Optimizations:**

- Top-N: Use LIMIT in outer query when possible

- Gap Detection: Index date/sequence columns

- Running Calculations: Consider materialized views for expensive calculations

- Cohort Analysis: Pre-aggregate when dealing with large datasets

**Memory Management:**

- Large window frames require significant memory

- Monitor work_mem settings for complex patterns

- Consider breaking large partitions into smaller chunks

- Use ROWS instead of RANGE when possible for better performance

**Query Structure:**

- Use CTEs for readability and maintainability

- Filter early to reduce data processed

- Combine related calculations in the same CTE

- Document complex patterns for future maintenance

Create composite indexes on (partition_column, order_column) for each pattern

Use LIMIT in outer queries when you only need top results

Consider materialized views for frequently accessed complex patterns

Filter data early in CTEs to reduce processing overhead

Use ROWS instead of RANGE frames when exact row counts are needed

Monitor query execution plans to ensure indexes are being used effectively

Filtering window function results in WHERE clause instead of outer query

Not handling NULL values properly in ORDER BY clauses

Using overly complex nested window functions instead of clear CTEs

Forgetting to partition data when group-level analysis is needed

Not considering tie-breaking logic in ranking scenarios

Applying patterns without understanding the underlying business requirements