Data Sql Optimization

SQL Optimization — Comprehensive Reference

This skill provides actionable checklists, patterns, and templates for transactional (OLTP) SQL optimization: measurement-first triage, EXPLAIN/plan interpretation, balanced indexing (avoiding over-indexing), performance monitoring, schema evolution, migrations, backup/recovery, high availability, and security.

Supported Platforms: PostgreSQL, MySQL, SQL Server, Oracle, SQLite

For OLAP/Analytics: See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)

---

Quick Reference

---

Decision Tree: Choosing the Right Approach

Query performance issue?
    ├─ Identify slow queries first?
    │   ├─ PostgreSQL -> pg_stat_statements (top queries by total_exec_time)
    │   └─ MySQL -> Performance Schema / slow query log
    │
    ├─ Analyze execution plan?
    │   ├─ PostgreSQL -> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
    │   ├─ MySQL -> EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE
    │   └─ SQL Server -> SET STATISTICS IO ON; SET STATISTICS TIME ON;
    │
    ├─ Need indexing strategy?
    │   ├─ PostgreSQL -> B-tree (default), GIN (JSONB), GiST (spatial), partial indexes
    │   ├─ MySQL -> BTREE (default), FULLTEXT (text search), SPATIAL
    │   └─ Check: Table >10k rows AND selectivity <10% AND 10x+ speedup verified
    │
    ├─ Schema changes needed?
    │   ├─ New database -> template-schema-design.md
    │   ├─ Modify schema -> template-migration.md (Flyway/Liquibase)
    │   └─ Large tables (MySQL) -> gh-ost / pt-online-schema-change (avoid locks)
    │
    ├─ High availability setup?
    │   ├─ PostgreSQL -> Streaming replication (template-replication-ha.md)
    │   └─ MySQL -> GTID-based replication (template-replication-ha.md)
    │
    ├─ Backup/disaster recovery?
    │   └─ template-backup-restore.md (pg_dump, mysqldump, PITR)
    │
    └─ Analytics on large datasets (OLAP)?
        └─ See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)

---

When to Use This Skill

Codex should invoke this skill when users ask for:

Query Optimization (Modern Approaches)

• SQL query performance review and tuning
• EXPLAIN/plan interpretation with optimization suggestions
• Index creation strategies with balanced approach (avoiding over-indexing)
• Troubleshooting slow queries using pg_stat_statements or Performance Schema
• Identifying and remediating SQL anti-patterns with operational fixes
• Query rewrite suggestions or migration from slow to fast patterns
• Statistics maintenance and auto-analyze configuration

Database Operations

• Schema design with normalization and performance trade-offs
• Database migrations with version control (Liquibase, Flyway)
• Backup and recovery strategies (point-in-time recovery, automated testing)
• High availability and replication setup (streaming, GTID-based)
• Database security auditing (access controls, encryption, SQL injection prevention)
• Lock analysis and deadlock troubleshooting
• Connection pooling (pgBouncer, Pgpool-II, ProxySQL)

Performance Tuning (Modern Standards)

• Memory configuration (work_mem, shared_buffers, effective_cache_size)
• Automated monitoring with pg_stat_statements and query pattern analysis
• Index health monitoring (unused index detection, index bloat analysis)
• Vacuum strategy and autovacuum tuning (PostgreSQL)
• InnoDB buffer pool optimization (MySQL)
• Partition pruning improvements (PostgreSQL 18+)

---

Resources (Best Practices Guides)

Find detailed operational patterns and quick references in:

• SQL Best Practices: references/sql-best-practices.md
• Query Tuning Patterns: references/query-tuning-patterns.md
• Indexing Strategies: references/index-patterns.md
• EXPLAIN/Analysis: references/explain-analysis.md
• SQL Anti-Patterns: references/sql-antipatterns.md
• External Sources: data/sources.json — vendor docs and reference links
• Operational Standards: references/operational-patterns.md — Deep operational checklists, database-specific guidance, and template selection trees
• Connection Pooling: references/connection-pooling-patterns.md — PgBouncer, RDS Proxy, pool sizing, connection leak troubleshooting
• Partition Strategies: references/partition-strategies.md — Range/list/hash partitioning, pruning, maintenance, migration patterns
• Monitoring & Alerting: references/monitoring-alerting-patterns.md — pg_stat_statements dashboards, alert thresholds, slow query pipelines

Each file includes:

• Copy-paste ready checklists (e.g., "query review", "index design", "explain review")
• Anti-patterns with operational fixes and alternatives
• Query rewrite and indexing strategies with examples
• Troubleshooting guides (step-by-step)

---

Templates (Copy-Paste Ready)

Templates are organized by database technology for precision and clarity:

Cross-Platform Templates (All Databases)

• assets/cross-platform/template-query-tuning.md - Universal query optimization
• assets/cross-platform/template-explain-analysis.md - Execution plan analysis
• assets/cross-platform/template-performance-tuning-worksheet.md - NEW 4-step tuning workflow (Measure -> Explain -> Change -> Verify)
• assets/cross-platform/template-index.md - Index design patterns
• assets/cross-platform/template-slow-query.md - Slow query triage
• assets/cross-platform/template-schema-design.md - Schema modeling
• assets/cross-platform/template-migration.md - Database migrations
• assets/cross-platform/template-backup-restore.md - Backup/DR planning
• assets/cross-platform/template-security-audit.md - Security review
• assets/cross-platform/template-diagnostics.md - Performance diagnostics
• assets/cross-platform/template-lock-analysis.md - Lock troubleshooting

PostgreSQL Templates

• assets/postgres/template-pg-explain.md - PostgreSQL EXPLAIN analysis
• assets/postgres/template-pg-index.md - PostgreSQL indexing (B-tree, GIN, GiST)
• assets/postgres/template-replication-ha.md - Streaming replication & HA

MySQL Templates

• assets/mysql/template-mysql-explain.md - MySQL EXPLAIN analysis
• assets/mysql/template-mysql-index.md - MySQL/InnoDB indexing
• assets/mysql/template-replication-ha.md - MySQL replication & HA

Microsoft SQL Server Templates

• assets/mssql/template-mssql-explain.md - SQL Server EXPLAIN/SHOWPLAN analysis
• assets/mssql/template-mssql-index.md - SQL Server indexing and tuning

Oracle Templates

• assets/oracle/template-oracle-explain.md - Oracle EXPLAIN plan review and tuning

SQLite Templates

• assets/sqlite/template-sqlite-optimization.md - SQLite optimization and pragma guidance

---

Related Skills

Infrastructure & Operations:

• ../ops-devops-platform/SKILL.md — Infrastructure, backups, monitoring, and incident response
• ../qa-observability/SKILL.md — Performance monitoring, profiling, and metrics
• ../qa-debugging/SKILL.md — Production debugging patterns

Application Integration:

• ../software-backend/SKILL.md — API/database integration and application patterns
• ../software-architecture-design/SKILL.md — System design and data architecture
• ../dev-api-design/SKILL.md — REST API and database interaction patterns

Quality & Security:

• ../qa-resilience/SKILL.md — Resilience, circuit breakers, and failure handling
• ../software-security-appsec/SKILL.md — Database security, auth, SQL injection prevention
• ../qa-testing-strategy/SKILL.md — Database testing strategies

Data Engineering:

• ../ai-ml-data-science/SKILL.md — SQLMesh, dbt, data transformations
• ../ai-mlops/SKILL.md — Data pipelines, ETL, and warehouse loading (dlt)
• ../ai-ml-timeseries/SKILL.md — Time-series databases and forecasting

---

Navigation

Resources

• references/explain-analysis.md
• references/query-tuning-patterns.md
• references/operational-patterns.md
• references/sql-antipatterns.md
• references/index-patterns.md
• references/sql-best-practices.md
• references/connection-pooling-patterns.md
• references/partition-strategies.md
• references/monitoring-alerting-patterns.md

Templates

• assets/cross-platform/template-slow-query.md
• assets/cross-platform/template-backup-restore.md
• assets/cross-platform/template-schema-design.md
• assets/cross-platform/template-explain-analysis.md
• assets/cross-platform/template-performance-tuning-worksheet.md
• assets/cross-platform/template-security-audit.md
• assets/cross-platform/template-diagnostics.md
• assets/cross-platform/template-index.md
• assets/cross-platform/template-migration.md
• assets/cross-platform/template-lock-analysis.md
• assets/cross-platform/template-query-tuning.md
• assets/oracle/template-oracle-explain.md
• assets/sqlite/template-sqlite-optimization.md
• assets/postgres/template-pg-index.md
• assets/postgres/template-replication-ha.md
• assets/postgres/template-pg-explain.md
• assets/mysql/template-mysql-explain.md
• assets/mysql/template-mysql-index.md
• assets/mysql/template-replication-ha.md
• assets/mssql/template-mssql-index.md
• assets/mssql/template-mssql-explain.md

Data

• data/sources.json — Curated external references

---

Operational Deep Dives

See references/operational-patterns.md for:

• End-to-end optimization checklists and anti-pattern fixes
• Database-specific quick references (PostgreSQL, MySQL, SQL Server, Oracle, SQLite)
• Slow query troubleshooting workflow and reliability drills
• Template selection decision tree and platform migration notes

---

Do / Avoid

GOOD: Do

• Measure baseline before any optimization
• Change one variable at a time
• Verify results match after query changes
• Update statistics before concluding "needs index"
• Test with production-like data volumes
• Document all optimization decisions
• Include performance tests in CI/CD

BAD: Avoid

• Adding indexes without checking if they'll be used
• Using SELECT * in production queries
• Optimizing for test data (use representative volumes)
• Ignoring write performance impact of indexes
• Skipping EXPLAIN analysis before changes
• Multiple simultaneous changes (can't attribute improvement)
• N+1 query patterns in application code

---

Anti-Patterns Quick Reference

See references/sql-antipatterns.md for detailed fixes.

---

OLTP vs OLAP Decision Tree

Is your query for...?
├─ Point lookups (by ID/key)?
│   └─ OLTP database (this skill)
│       - Ensure proper indexes
│       - Use connection pooling
│       - Optimize for low latency
│
├─ Aggregations over recent data (dashboard)?
│   └─ OLTP database (this skill)
│       - Consider materialized views
│       - Index common filter columns
│       - Watch for lock contention
│
├─ Full table scans or historical analysis?
│   └─ OLAP database (data-lake-platform)
│       - ClickHouse, DuckDB, Doris
│       - Columnar storage
│       - Partitioning by date
│
└─ Mixed workload (both)?
    └─ Separate OLTP and OLAP
        - OLTP for transactions
        - Replicate to OLAP for analytics
        - Avoid running analytics on primary

---

Optional: AI/Automation

Note: AI tools assist but require human validation of correctness.

• EXPLAIN summarization — Identify bottlenecks from complex plans
• Query rewrite suggestions — Must verify result equivalence
• Index recommendations — Check selectivity and write impact first

Bounded Claims

• AI cannot determine correct query results
• Automated index suggestions may miss workload context
• Human review required for production changes

---

Analytical Databases (OLAP)

For OLAP databases and data lake infrastructure, see [data-lake-platform](../data-lake-platform/SKILL.md):

• Query engines: ClickHouse, DuckDB, Apache Doris, StarRocks
• Table formats: Apache Iceberg, Delta Lake, Apache Hudi
• Transformation: SQLMesh, dbt (staging/marts layers)
• Ingestion: dlt, Airbyte (connectors)
• Streaming: Apache Kafka patterns

This skill focuses on transactional database optimization (PostgreSQL, MySQL, SQL Server, Oracle, SQLite). Use data-lake-platform for analytical workloads.

---

Related Skills

This skill focuses on query optimization within a single database. For related workflows:

SQL Transformation & Analytics Engineering: -> [ai-ml-data-science](../ai-ml-data-science/SKILL.md) skill

• SQLMesh templates for building staging/intermediate/marts layers
• Incremental models (FULL, INCREMENTAL_BY_TIME_RANGE, INCREMENTAL_BY_UNIQUE_KEY)
• DAG management and model dependencies
• Unit tests and audits for SQL transformations

Data Ingestion (Loading into Warehouses): -> [ai-mlops](../ai-mlops/SKILL.md) skill

• dlt templates for extracting from REST APIs, databases
• Loading to Snowflake, BigQuery, Redshift, Postgres, DuckDB
• Incremental loading patterns (timestamp, ID-based, merge/upsert)
• Database replication (Postgres, MySQL, MongoDB -> warehouse)

Data Lake Infrastructure: -> [data-lake-platform](../data-lake-platform/SKILL.md) skill

• ClickHouse, DuckDB, Doris, StarRocks query engines
• Iceberg, Delta Lake, Hudi table formats
• Kafka streaming, Dagster/Airflow orchestration

Use Case Decision:

• Query is slow in production -> Use this skill (data-sql-optimization)
• Building feature pipelines in SQL -> Use ai-ml-data-science (SQLMesh)
• Loading data from APIs/DBs to warehouse -> Use ai-mlops (dlt)
• Analytics on large datasets (OLAP) -> Use data-lake-platform

---

External Resources

See data/sources.json for 62+ curated resources including:

Core Documentation:

• RDBMS Documentation: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, DuckDB official docs
• Query Optimization: Use The Index, Luke, SQL Performance Explained, vendor optimization guides
• Schema Design: Database Refactoring (Fowler), normalization guides, data type selection

Modern Optimization (Current):

• PostgreSQL: official release notes and "current" docs for planner/optimizer changes
• MySQL: official reference manual sections for EXPLAIN, optimizer, and Performance Schema
• SQL Server / Oracle: official docs for execution plans, indexing, and concurrency controls

Operations & Infrastructure:

• HA & Replication: Streaming replication, GTID-based replication, failover automation
• Migrations: Liquibase, Flyway version control and deployment patterns
• Backup/Recovery: pgBackRest, Percona XtraBackup, point-in-time recovery
• Monitoring: pg_stat_statements, Performance Schema, EXPLAIN visualizers (Dalibo, depesz)
• Security: OWASP SQL Injection Prevention, Postgres hardening, encryption standards
• Analytical Databases: DuckDB extensions, Parquet specification, columnar storage patterns

---

Use references/operational-patterns.md and the templates directory for detailed workflows, migration notes, and ready-to-run commands.

Fact-Checking

• Use web search/web fetch to verify current external facts, versions, pricing, deadlines, regulations, or platform behavior before final answers.
• Prefer primary sources; report source links and dates for volatile information.
• If web access is unavailable, state the limitation and mark guidance as unverified.

# SQL Backup & Restore Template (DR-Ready)

*Purpose: A complete template for planning, executing, validating, and documenting database backups, restores, retention policies, DR scenarios, and integrity verification.*

---

## 1. Backup Overview

**Database:**  
- [ ] Postgres  
- [ ] MySQL  
- [ ] Other: ____________

**Environment:**  
- [ ] Production  
- [ ] Staging  
- [ ] Development  

**Reason for Backup:**  
- [ ] Scheduled backup  
- [ ] Pre-deployment snapshot  
- [ ] Pre-migration backup  
- [ ] Pre-index rebuild  
- [ ] Manual request  
- [ ] DR simulation  

**Requested By:**  
[Name]

**Date:**  
[YYYY-MM-DD]

---

## 2. Backup Configuration Summary

| Item | Value |
|------|--------|
| Backup Type | Full / Incremental / Differential / PITR |
| Target Storage | S3 / GCS / Local / NFS / Blob |
| Encryption | Yes/No |
| Compression | Yes/No |
| Retention Period | days/weeks/months |
| Expected Backup Size | |
| Expected Time Window | |
| Performance Impact | Low / Medium / High |

---

## 3. Backup Command(s)

### 3.1 Postgres

**Full Backup (pg_dump):**

pg_dump -Fc -Z9 -f backup_$(date +%F).dump <database_name>

**Physical Backup (pg_basebackup):**

pg_basebackup -D /backups/base -Ft -z -P -X stream

**WAL Archiving (PITR):**

archive_command = 'cp %p /wal-archive/%f'

---

### 3.2 MySQL

**Logical Backup (mysqldump):**

mysqldump --single-transaction --routines --events --quick <database> \ | gzip > backup_$(date +%F).sql.gz

**Physical Backup (XtraBackup):**

xtrabackup --backup --target-dir=/backups/base

**Binlog Backup:**

mysqlbinlog --read-from-remote-server --raw \ --result-file=/binlogs <host-binlog-index>

---

## 4. Backup Verification Steps

### 4.1 Structural Verification
- [ ] File exists  
- [ ] File size reasonable  
- [ ] Not truncated  
- [ ] Checksums verified  
- [ ] Backup metadata stored  

### 4.2 Logical Verification (recommended weekly)

pg_restore --list backup.dump

or

mysql --execute="SHOW TABLES;"

Checklist:
- [ ] Table count matches  
- [ ] Schema versions match  
- [ ] No corrupted dump entries  

---

## 5. Restore Plan (Dry Run Recommended)

### 5.1 Restore Summary

**Restore Type:**  
- [ ] Full  
- [ ] PITR  
- [ ] Table-level restore  
- [ ] Point snapshot recovery  
- [ ] Replica rebuild  

**Destination:**  
- [ ] Local instance  
- [ ] Staging environment  
- [ ] New production node  
- [ ] On-demand restore environment  

---

## 6. Restore Commands

### 6.1 Postgres

**Full Restore:**

createdb restored_db pg_restore -Fc -j 4 -d restored_db backup.dump

**PITR Restore:**  
(Requires WAL archive)

restore_command = 'cp /wal-archive/%f %p' recovery_target_time = '2023-05-10 18:00:00'

---

### 6.2 MySQL

**Logical Restore:**

gunzip < backup.sql.gz | mysql restored_db

**XtraBackup Restore:**

xtrabackup --prepare --target-dir=/backups/base xtrabackup --copy-back --target-dir=/backups/base

---

## 7. Recovery Validation

### 7.1 Logical Validation
- [ ] Row count diff < 0.1%  
- [ ] Index structures valid  
- [ ] Constraints validated  
- [ ] Application queries tested  

### 7.2 Functional Validation
- [ ] Key API endpoints work  
- [ ] No missing reference data  
- [ ] Views and functions compile  
- [ ] Time-based lookups validated  

### 7.3 Performance Validation
- [ ] Slow queries not regressed  
- [ ] Indexes used correctly  
- [ ] No unexpected locks  

---

## 8. Retention & Rotation Plan

| Backup Type | Frequency | Retention | Location |
|-------------|-----------|-----------|----------|
| Full | Daily | 30 days | S3 |
| WAL/Binlog | Every 5 min | 7 days | S3 |
| Snapshots | Weekly | 12 weeks | Cloud provider |

Checklist:
- [ ] Automated cleanup enabled  
- [ ] Archive lifecycle rules configured  
- [ ] Offsite/region redundancy verified  

---

## 9. DR (Disaster Recovery) Capability

### 9.1 RPO (Recovery Point Objective)
Target: [e.g., ≤ 5 minutes]

Actual Achieved:  
[Value collected from binlog/WAL frequency]

### 9.2 RTO (Recovery Time Objective)
Target: [e.g., ≤ 30 minutes]

Actual Achieved:  
[Test restoration speed]

### 9.3 DR Test Summary
- [ ] Annual full DR drill  
- [ ] Quarterly restore validation  
- [ ] Replica rebuild tested  
- [ ] Restore from oldest backup tested  

---

## 10. Failure Scenarios & Procedures

### 10.1 Corruption
- Recover from last known good backup  
- Validate via row count & checksum  

### 10.2 Accidental Deletes/Drops
- Use PITR logs to restore  
- Apply filtered restore
  
### 10.3 Bad Deployment
- Roll forward via restore into shadow DB  
- Compare diffs  

### 10.4 Replica Desync
- Rebuild replica from backup  
- Apply logs until consistent  

---

## 11. Final Approval

| Role | Approved? | Name | Date |
|------|-----------|-------|-------|
| SQL Engineer | [ ] | | |
| DBA | [ ] | | |
| SRE / Platform | [ ] | | |
| Security | [ ] | | |

---

## 12. Completed Example

**Scenario:**  
PITR restore required due to accidental mass deletion.

**Backup Used:**  
`pg_basebackup` + WAL archive

**Restore Commands:**

createdb recovered_db pg_restore -d recovered_db backup.dump

Replay WAL to target time:

recovery_target_time = '2023-05-10 18:33:01'

**Verification:**  
[check] Records restored  
[check] No index corruption  
[check] RTO: 18 minutes  
[check] RPO: < 60 seconds  

---

# END

# SQL Diagnostics Template

*Purpose: A specialized template for diagnosing slow queries, lock contention, deadlocks, storage bloat, CPU/I/O pressure, or unexplained database performance regressions.*

---

## 1. Incident Overview

**Issue Type:**  
- [ ] Slow query  
- [ ] Locking/blocking  
- [ ] Deadlock  
- [ ] High CPU  
- [ ] High I/O  
- [ ] Memory pressure  
- [ ] Table/index bloat  
- [ ] Replication lag  
- [ ] Connection saturation  

**Impact:**  
[Describe user/system impact]

**Start Time:**  
[Timestamp]

**Severity:**  
- [ ] P0  
- [ ] P1  
- [ ] P2  

---

## 2. Reproduction Details

**Query or Operation:**

-- Paste query or operation

**Example Parameters:**
| Parameter | Example | Notes |
|----------|----------|-------|
| user_id | 123 | high-frequency |
| date_to | 2023-01-01 | optional |

**Frequency:**  
- [ ] Consistent  
- [ ] Intermittent  
- [ ] Parameter-dependent  
- [ ] Spike under load  

---

## 3. Environment Snapshot

### 3.1 Postgres Metrics (if applicable)
- `pg_stat_activity` sample  
- `pg_locks` snapshot  
- Check blocked/blocked_by columns  
- autovacuum activity  
- replication lag  
- checkpoints & WAL stats  

### 3.2 MySQL Metrics (if applicable)
- `INNODB_TRX`, `INNODB_LOCKS`, `INNODB_LOCK_WAITS`  
- slow query log entries  
- buffer pool hit rate  
- temp table usage  
- row lock waits  

**Paste relevant metric samples here**

---

## 4. Execution Plan Capture

Use real parameters.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>

### Quick Checks:
- [ ] Seq Scan (unexpected)  
- [ ] Slow nested loop  
- [ ] Hash join spilling to disk  
- [ ] Sort spilling to disk  
- [ ] Rows out of estimation  
- [ ] Low filter ratio  
- [ ] High memory usage  

---

## 5. Diagnostics Workflows

### 5.1 Slow Query Root Cause Analysis

**Checklist:**
- [ ] Inefficient filter (no index)  
- [ ] Poor join ordering  
- [ ] Misestimated row counts  
- [ ] Large table scan  
- [ ] Function on indexed column  
- [ ] OR conditions causing seq scan  
- [ ] Too many nested loops  
- [ ] Distinct / Group By causing heavy sort  
- [ ] Missing LIMIT  
- [ ] Inefficient pagination (OFFSET)  

---

### 5.2 Lock Contention Analysis

**Queries:**

SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity;

SELECT * FROM pg_locks;

**Identify:**
- [ ] Blocking PID  
- [ ] Type of lock (RowExclusive, AccessExclusive, etc.)  
- [ ] Query causing blockage  
- [ ] Long-running transaction (> 60s)  
- [ ] Uncommitted write holding locks  

**Fix Patterns:**
- Kill blocker (only if safe)  
- Rewrite transaction to commit sooner  
- Convert update to batch mode  
- Avoid `ALTER TABLE` in peak hours  

---

### 5.3 Deadlock Analysis

**Postgres Example Log:**

ERROR: deadlock detected DETAIL: Process X waits for ShareLock on transaction Y...

**Checklist:**
- [ ] Identify conflicting statements  
- [ ] Normalize lock ordering  
- [ ] Reduce transaction scope  
- [ ] Avoid selecting rows “FOR UPDATE” unnecessarily  
- [ ] Review foreign key cascades  

---

### 5.4 Memory / Sort Spill Analysis

**Indicators:**
- Hash Join using temp files  
- Sort using disk (external sort)  
- EXPLAIN shows: `Disk: 120MB`  

**Fix Patterns:**
- Add index aligning with ORDER BY  
- Rewrite GROUP BY  
- Reduce result set size  
- Tune `work_mem` (Postgres) per query via SET LOCAL  

---

### 5.5 I/O Pressure Diagnosis

Check:
- Index fragmentation  
- Bloat percentage  
- Buffer cache miss rate  
- Vacuum lag  
- Large sequential scans on frequently accessed tables  

**Fix Patterns:**
- Add targeted indexes  
- Rebuild index if > 30% bloat  
- Adjust autovacuum scale factors  
- Analyze table  

---

### 5.6 Replication Lag

Check:
- WAL generation spikes  
- Large batch updates  
- Long-running vacuum  
- Write-amplifying indexes  

**Fix Patterns:**
- Break writes into batches  
- Increase replica resources  
- Move large maintenance operations off-peak  

---

## 6. Change Experiments

List small reversible experiments:

- [ ] Add temporary index  
- [ ] Rewrite join order  
- [ ] Add LIMIT during debugging  
- [ ] Sample parameters differently  
- [ ] Tune memory parameters (session-based)  
- [ ] Toggle `enable_seqscan` or `enable_indexscan` (debug only)  

---

## 7. Fix Summary

**Primary Root Cause:**  
[e.g., Missing composite index]

**Fix Applied:**  
[Describe changes]

**Expected Improvement:**  
[ms -> ms]

**Risk Level:**  
- [ ] Low  
- [ ] Medium  
- [ ] High  

---

## 8. Verification Checklist

**After Fix:**
- [ ] Latency meets SLO  
- [ ] Execution plan stable  
- [ ] No new regressions  
- [ ] No increased lock contention  
- [ ] No disk spills  
- [ ] CPU/I/O stable  
- [ ] Stats updated (`ANALYZE`)  
- [ ] Index usage confirmed  

---

## 9. Complete Example

### Issue:
Query slow when filtering orders by customer_id and date range.

### Root Cause:
Seq Scan scanning 2M rows due to missing index.

### Fix:

CREATE INDEX idx_orders_customer_ts ON orders(customer_id, created_at DESC) INCLUDE (total);

### Result:
Latency improved from 900ms -> 6ms.

### Verification:
[check] Index Scan  
[check] Sort removed  
[check] No lock contention  

---

# END

# SQL EXPLAIN Analysis Template

*Purpose: Use this template to document, analyze, and act on SQL execution plans during query tuning or review.*

---

## When to Use

Use this template when:
- Reviewing slow or critical queries
- Making schema/index changes
- Preparing for production migrations
- Performing periodic performance audits

---

## Structure

This template has 3 sections:
1. **Plan Collection** — capture how/where EXPLAIN was run
2. **Plan Review Checklist** — quick operational checks
3. **Action Items & Verification** — optimizations, retesting, rollback

---

# TEMPLATE STARTS HERE

## 1. Plan Collection

- **Database:** [e.g., PostgreSQL 15, MySQL 8.0]
- **Command Used:** [e.g., EXPLAIN (ANALYZE, BUFFERS) SELECT ...]
- **Schema Version/Date:** [Timestamp or migration hash]
- **Relevant Query:** [Paste the SQL being analyzed]
- **EXPLAIN Output:**  
  [Paste plan text or attach screenshot/JSON if large]

---

## 2. Plan Review Checklist

- [ ] Are there any Seq/Table Scans?  
  - [ ] If yes, are they justified (small table, no filter)?
- [ ] Are join columns indexed on both tables?
- [ ] Does WHERE use sargable predicates (no function/wrapper on indexed col)?
- [ ] Are filesort/temp or sort nodes present?
- [ ] Are estimated and actual row counts close (within 10x)?
- [ ] Is there a major difference in cost between steps?
- [ ] Does the plan show index scans/seeks for main filter/join columns?
- [ ] Any indication of missing/outdated stats?

---

## 3. Action Items & Verification

- **Proposed Index or Query Rewrite:**  
  [e.g., add composite index, rewrite predicate, adjust join order]

- **Plan After Change:**  
  [Paste updated plan or describe expected result]

- **Performance Before/After:**  
  - Before: [e.g., 2,500 ms, 100k rows scanned]
  - After:  [e.g., 42 ms, 800 rows scanned]

- **Rollback Plan:**  
  [How to revert index or query if results degrade]

---

# COMPLETE EXAMPLE

## 1. Plan Collection

- **Database:** PostgreSQL 15
- **Command Used:** EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;
- **Schema Version/Date:** 2024-05-01 (prod schema v19)
- **Relevant Query:**  
  SELECT * FROM orders WHERE customer_id = 123;
- **EXPLAIN Output:**  
  Seq Scan on orders  (cost=0.00..1220.00 rows=2 width=...)  
    Filter: (customer_id = 123)  
    Rows Removed by Filter: 12500

---

## 2. Plan Review Checklist

- [x] Seq Scan present (not justified, large table)
- [ ] Index on customer_id missing
- [x] WHERE is sargable
- [ ] No sort, temp, or filesort
- [x] Estimated/actual rows match (plan estimates 2, actual 1)
- [ ] Stats may be slightly outdated

---

## 3. Action Items & Verification

- **Proposed Index or Query Rewrite:**  
  CREATE INDEX idx_orders_customer_id ON orders(customer_id);

- **Plan After Change:**  
  Index Scan on orders (cost much lower, no filter step)

- **Performance Before/After:**  
  - Before: 1,250 ms, 12,500 rows scanned
  - After: 4 ms, 1 row scanned

- **Rollback Plan:**  
  DROP INDEX idx_orders_customer_id;

---

## Quality Checklist

Before finalizing:
- [ ] Plan reviewed and pasted in ticket/docs
- [ ] Index or query change tested in staging/prod-like data
- [ ] Rollback plan documented and ready

# SQL Index Creation Template

*Purpose: Standardize the process of adding, documenting, and validating indexes for query optimization. Ensures all index changes are operationally safe and performance-tested.*

---

## When to Use

Use this template when:
- Proposing a new index for slow queries or schema review
- Refactoring existing indexes
- Preparing for deployment/migration that impacts query performance

---

## Structure

This template has 4 sections:
1. **Index Design & Rationale** — why and how the index is needed
2. **DDL & Implementation** — ready-to-run SQL
3. **Validation & EXPLAIN Review** — before/after impact
4. **Rollback & Maintenance** — revert and monitor plan

---

# TEMPLATE STARTS HERE

## 1. Index Design & Rationale

- **Query or Access Pattern:**  
  [Paste representative SQL using the target columns]

- **Current Plan/Problem:**  
  [EXPLAIN shows table scan, slow join, etc.]

- **Reason for Index:**  
  [e.g., WHERE on user_id is slow, frequent lookup by email, etc.]

- **Index Type:**  
  [Single, composite, covering/INCLUDE, partial/filtered, functional/expr]

- **Columns to Index (in order):**  
  [List columns, e.g., (user_id, created_at)]

---

## 2. DDL & Implementation

**Create Index Statement:**  

-- Edit as needed for your RDBMS CREATE INDEX idx_tablename_columns ON tablename(column1, column2) [INCLUDE (column3, ...)] -- Optional, Postgres/SQL Server [WHERE condition]; -- Optional, partial/filtered index

**Example:**  

CREATE INDEX idx_orders_customer_id_created_at ON orders(customer_id, created_at) INCLUDE (amount, status);

- **Expected Impact:**  
  [e.g., Should support queries filtering by customer_id and date, covering status/amount in result]

---

## 3. Validation & EXPLAIN Review

- **Before:**  
  - Query plan: [Paste pre-index plan]
  - Timing: [e.g., 1,300 ms]
  - Rows scanned: [e.g., 18,000]

- **After:**  
  - Query plan: [Paste post-index plan]
  - Timing: [e.g., 12 ms]
  - Rows scanned: [e.g., 18]

- **Other queries potentially affected:**  
  [Review for index bloat, overlaps, negative impact]

---

## 4. Rollback & Maintenance

- **Rollback Command:**  

DROP INDEX idx_tablename_columns;

- **Post-Deployment Monitoring:**  
  - Check index usage with pg_stat_user_indexes, INFORMATION_SCHEMA, or RDBMS-specific tools.
  - Monitor DML (insert/update/delete) latency for regression.
  - Schedule routine index maintenance (REINDEX, ANALYZE, OPTIMIZE TABLE).

---

# COMPLETE EXAMPLE

## 1. Index Design & Rationale

- **Query or Access Pattern:**  
  SELECT * FROM orders WHERE customer_id = 77 AND created_at >= '2024-01-01';

- **Current Plan/Problem:**  
  Table scan in EXPLAIN, query takes 800 ms

- **Reason for Index:**  
  Accelerate customer order lookups for analytics dashboard

- **Index Type:**  
  Composite

- **Columns to Index (in order):**  
  (customer_id, created_at)

---

## 2. DDL & Implementation

**Create Index Statement:**  

CREATE INDEX idx_orders_customer_id_created_at ON orders(customer_id, created_at);

- **Expected Impact:**  
  Filter + range scan, supports rapid dashboard queries

---

## 3. Validation & EXPLAIN Review

- **Before:**  
  Plan: Seq Scan on orders  
  Timing: 800 ms  
  Rows scanned: 12,000

- **After:**  
  Plan: Index Scan on orders  
  Timing: 15 ms  
  Rows scanned: 24

---

## 4. Rollback & Maintenance

- **Rollback Command:**  
  DROP INDEX idx_orders_customer_id_created_at;

- **Post-Deployment Monitoring:**  
  - Check pg_stat_user_indexes for hits
  - Monitor dashboard for improved response
  - Revisit in quarterly index review

---

## Quality Checklist

Before finalizing:

- [ ] Index reviewed with query and plan
- [ ] No overlapping/redundant indexes created
- [ ] EXPLAIN plans saved in ticket/docs
- [ ] Rollback and monitoring steps documented

# SQL Lock & Concurrency Analysis Template

*Purpose: A dedicated template for diagnosing lock contention, blocking chains, deadlocks, long-running transactions, and concurrency-related performance regressions.*

---

## 1. Incident Overview

**Issue Title:**  
[Lock contention / blocking / deadlock / long-running txn]

**Severity:**  
- [ ] P0 – Production outage  
- [ ] P1 – Major degradation  
- [ ] P2 – Minor  
- [ ] P3 – Low  

**Start Time:**  
[Timestamp]

**Business/User Impact:**  
[Describe impact: e.g., checkout failures, API timeouts]

**Databases Affected:**  
[List instances/clusters]

---

## 2. Symptoms

Check all that apply:

- [ ] Slow queries  
- [ ] Increased latency  
- [ ] API timeouts  
- [ ] Failed transactions  
- [ ] Replication lag  
- [ ] High CPU  
- [ ] High idle-in-transaction connections  

**Notes:**  
[Add brief description]

---

## 3. Environment Snapshot

### 3.1 Postgres

Run:

SELECT pid, usename, application_name, state, wait_event_type, wait_event, backend_xmin, backend_xid, query_start, xact_start, query FROM pg_stat_activity;

Locks:

SELECT * FROM pg_locks;

Blocking chain:

SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query, blocking_activity.query AS blocking_query FROM pg_locks blocked_locks JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid AND blocked_locks.pid <> blocking_locks.pid JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;

---

### 3.2 MySQL

Long-running transactions:

SELECT * FROM information_schema.innodb_trx;

Locks:

SELECT * FROM information_schema.innodb_locks;

Lock waits:

SELECT * FROM information_schema.innodb_lock_waits;

---

## 4. Findings

### 4.1 Blocking Query

**Blocking PID:**  
[x]

**Blocking Query Text:**  

<query>

**Start Time:**  
[Timestamp]

**App/Source:**  
[service name / migration / background worker]

---

### 4.2 Blocked Queries

List blocked PIDs and queries:

| PID | Duration | Query |
|-----|----------|--------|
| | | |
| | | |

**Notes:**  
[Describe patterns in blocked queries]

---

## 5. Lock Type Analysis

| Lock Type | Meaning | Impact |
|-----------|----------|---------|
| RowExclusiveLock | Writes | Medium |
| AccessShareLock | Reads | Low |
| AccessExclusiveLock | DDL | Highest |
| ShareLock | FK checks | Medium |
| RowShareLock | SELECT FOR SHARE | Medium |

Checklist:

- [ ] DDL causing lock?  
- [ ] Long-running UPDATE/DELETE?  
- [ ] SELECT FOR UPDATE misuse?  
- [ ] Index creation without CONCURRENTLY?  
- [ ] Idle in transaction but holding locks?  

---

## 6. Transaction Analysis

**Longest-running transaction:**

| PID | xact_start | Duration | Query |
|-----|------------|----------|--------|
| | | | |

Checklist:

- [ ] BEGIN without COMMIT/ROLLBACK  
- [ ] Large write transaction  
- [ ] Unbounded DELETE/UPDATE  
- [ ] ORM holding open session  
- [ ] App not releasing connection  

---

## 7. Deadlock Investigation

Paste deadlock log lines:

<deadlock logs>

Checklist:

- [ ] Conflicting UPDATE patterns  
- [ ] Opposite lock ordering  
- [ ] Same table accessed in different order  
- [ ] Conflicting FK cascades  
- [ ] SELECT FOR UPDATE used unnecessarily  

Common Fix Patterns:

- Normalize lock order across code paths  
- Reduce transaction scope  
- Remove FK cascades or reorder operations  
- Use SKIP LOCKED for concurrent workers  
- Replace SELECT FOR UPDATE with advisory locks if safe  

---

## 8. Bloat & Autovacuum Considerations (Postgres)

Check:

- [ ] Table bloat > 20%  
- [ ] Index bloat > 30%  
- [ ] Autovacuum stuck or lagging  
- [ ] HOT updates ineffective  
- [ ] Dead tuples accumulating  

Commands:

SELECT * FROM pg_stat_all_tables;

SELECT * FROM pgstattuple('table');

---

## 9. Root Cause Summary

**Primary Cause:**  
[e.g., ALTER TABLE blocking writes, long-running transaction, bad JOIN, deadlock loop]

**Contributing Factors:**  

- [ ] Missing index  
- [ ] ORM query explosion  
- [ ] Autovacuum freeze  
- [ ] Heavy write burst  
- [ ] Poor pagination  
- [ ] Unbounded updates  

---

## 10. Fixes Implemented

### Immediate Fixes

- [ ] Terminate blocking PID  
- [ ] Kill idle-in-transaction sessions  
- [ ] Disable problematic migration job  
- [ ] Reduce batch sizes  
- [ ] Apply temporary index  

### Permanent Fixes

- [ ] Rewrite blocking query  
- [ ] Adjust transaction scoping  
- [ ] Reorder operations to avoid deadlocks  
- [ ] Add missing index  
- [ ] Use CONCURRENTLY for DDL  

---

## 11. Verification

### 11.1 Lock & Activity Check

- [ ] No blocking PIDs  
- [ ] No long-running transactions  
- [ ] No deadlocks in logs  
- [ ] pg_locks normal  

### 11.2 Performance Check

- [ ] Latency normalized  
- [ ] CPU stable  
- [ ] I/O stable  
- [ ] No autovacuum starvation  

### 11.3 Replication Check

- [ ] Lag cleared  
- [ ] No WAL spikes  

---

## 12. Final Notes

[List follow-up tasks, monitoring additions, or code fixes required.]

---

## 13. Complete Example

**Issue:** `ALTER TABLE orders ADD COLUMN metadata JSONB` blocked all writes for 7 minutes.

**Root Cause:** DDL executed without `CONCURRENTLY` during traffic peak.

**Fix:**  

- Killed blocking PID  
- Postponed DDL to maintenance  
- Re-ran via online-safe alternative  
- Added alerts for AccessExclusiveLock > 3s

**Verification:** System stable, no replication lag.

---

# END

# SQL Migration Template (Zero-Downtime Safe Changes)

*Purpose: A production-ready template for designing, executing, and validating safe SQL schema changes, ensuring backward compatibility, no downtime, and minimal operational risk.*

---

## 1. Migration Summary

**Title:**  
[Add/modify/drop <column/table/index> safely]

**Author:**  
[Name]

**Date:**  
[YYYY-MM-DD]

**Environment(s):**  
- [ ] Production  
- [ ] Staging  
- [ ] Development  

**Migration Type:**  
- [ ] Add Column  
- [ ] Drop Column  
- [ ] Rename Column  
- [ ] Add Table  
- [ ] Change Type  
- [ ] Add/Modify Index  
- [ ] Add Constraint  
- [ ] Remove Constraint  
- [ ] Data Backfill  
- [ ] Structural Refactor  

**Business Rationale:**  
[Describe what this enables and why]

---

## 2. Impact Assessment

| Area | Impact |
|------|--------|
| Read workload | |
| Write workload | |
| Locking risk | |
| Disk growth | |
| Replication lag risk | |
| Rollout complexity | |

### Risk Level
- [ ] Low  
- [ ] Medium  
- [ ] High  

---

## 3. Compatibility Strategy

Is the migration **backward compatible**?

- [ ] Yes — Application works with old + new schema  
- [ ] No — Requires coordinated deployment  
- [ ] Partially — Requires dual-write or view abstraction  

### Strategy Selected

- [ ] Expand -> Migrate Data -> Contract  
- [ ] Blue/green rollout  
- [ ] Dual-read / dual-write  
- [ ] Shadow column  
- [ ] Compatibility view  
- [ ] Two-phase constraint validation  

---

## 4. Migration Steps (Detailed)

Document every step so ops/DBAs can execute safely.

### Step 1 — Pre-checks
- [ ] Backup available + verified  
- [ ] Sufficient disk space  
- [ ] No long-running transactions  
- [ ] No autovacuum freeze risk (Postgres)  
- [ ] No replication lag  
- [ ] Peak traffic window avoided  

---

### Step 2 — Schema Expansion (Non-Breaking)

(Add structures without removing or modifying existing ones.)

Examples:

**Add Column**

ALTER TABLE users ADD COLUMN timezone TEXT;

**Add Table**

CREATE TABLE audit_log (...);

**Add Index Concurrently (Postgres)**

CREATE INDEX CONCURRENTLY idx_users_timezone ON users(timezone);

Checklist:
- [ ] No blocking DDL  
- [ ] Constraints not enforced yet  
- [ ] Defaults not expensive (avoid volatile expressions)  

---

### Step 3 — Data Backfill (Batch Safe)

Perform in small chunks to avoid locks, I/O spikes, and replication lag.

UPDATE users SET timezone='UTC' WHERE timezone IS NULL LIMIT 5000;

Checklist:
- [ ] Batch size tuned  
- [ ] Progress logged  
- [ ] Autovacuum impact monitored  
- [ ] Replication lag tracked  
- [ ] Use retry-safe logic  

---

### Step 4 — Application Rollout

- [ ] App reads both old + new fields  
- [ ] Writes new field (dual-write if needed)  
- [ ] Feature flags applied  
- [ ] Observability in place (metrics, logs, dashboards)  

---

### Step 5 — Constraint Enforcement

Enable constraints only after data is consistent.

Examples:

**Set NOT NULL after backfill**

ALTER TABLE users ALTER COLUMN timezone SET NOT NULL;

**Foreign Key (safe Postgres pattern)**

ALTER TABLE orders ADD CONSTRAINT orders_user_fkey FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fkey;

Checklist:
- [ ] No NULL/invalid values remain  
- [ ] Validate constraint usage in staging  
- [ ] Constraint validation monitored  

---

### Step 6 — Cleanup (Contract Phase)

Remove deprecated structures after verifying stability.

Examples:

**Drop Old Column**

ALTER TABLE users DROP COLUMN timezone_old;

**Rename Safe**

ALTER TABLE users RENAME COLUMN timezone_new TO timezone;

Checklist:
- [ ] Confirm no traffic uses old structures  
- [ ] No references in code, functions, or triggers  
- [ ] Run plan on queries that depend on this table  

---

## 5. Rollback Plan

**Rollback Steps:**

1. Stop new writes if corruption suspected  
2. Re-enable old column/tables if dual-write was used  
3. Drop partially created constraints or indexes  
4. Apply backup restore (worst-case scenario)  
5. Roll back application code version  

Checklist:
- [ ] Rollback tested in staging  
- [ ] Data integrity maintained  
- [ ] Clear owner assigned for rollback execution  

---

## 6. Verification & Post-Migration Checks

### Functional Validation
- [ ] Application reads/writes correct data  
- [ ] Old pathways disabled  
- [ ] New schema recognized by ORM/tooling  

### Performance Validation
- [ ] No new seq scans  
- [ ] No excessive sorting or temp files  
- [ ] Index usage verified  

### Integrity Validation
- [ ] FKs valid  
- [ ] Unique constraints correct  
- [ ] Row counts consistent  

### Operational Validation
- [ ] Replication lag normalized  
- [ ] No long-running autovacuum  
- [ ] No lock alerts  
- [ ] No error spikes  

---

## 7. Example Completed Migration Template

**Goal:** Add `timezone` to `users` table for scheduled notification feature.

**Impact:** Moderate. Write overhead negligible.

**Steps:**  
1. Add NULL-able column  
2. Backfill ~12M rows in 5k batches  
3. Deploy app writing new column  
4. Set NOT NULL  
5. Drop fallback logic  

**Rollback:**  
- Remove NOT NULL  
- Re-enable dual-write  
- Fallback to old timezone behavior  

**Validation:**  
[check] All rows backfilled  
[check] Constraints applied cleanly  
[check] Query plans unchanged  
[check] Replication lag < 250ms  

---

# END

SQL Performance Tuning Worksheet (Explain -> Hypothesis -> Change -> Verify)

Systematic workflow: Explain -> Hypothesis -> Change -> Verify (with baseline measurement)

---

Core

Step 1: Explain (Baseline + Context)

Query Identification

Query under investigation:

-- Paste the slow query here

Current metrics:

Measurement commands:

-- PostgreSQL: Enable timing
\timing on

-- PostgreSQL: Get execution stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <your_query>;

-- MySQL: Enable profiling
SET profiling = 1;
<your_query>;
SHOW PROFILE FOR QUERY 1;

-- SQL Server: Enable statistics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
<your_query>;

Context Gathering

• [ ] Table row counts documented
• [ ] Index list captured
• [ ] Current statistics age checked
• [ ] Concurrent workload noted
• [ ] Hardware/resource constraints known

---

Step 2: Hypothesis (Plan-Based)

Execution Plan Review

Plan summary:

-- Paste EXPLAIN output here

Cost Breakdown

Red Flags Checklist

• [ ] Sequential scan on large table (>10k rows)
• [ ] Nested loop with high outer row count
• [ ] Sort operation spilling to disk
• [ ] Hash join with large build table
• [ ] Filter removing >50% of rows late in plan
• [ ] Estimate vs actual mismatch (>10x difference)
• [ ] Missing index hint in plan warnings

Root Cause Hypothesis

Primary hypothesis:

[State the most likely cause based on evidence]

---

Step 3: Change (Intervention)

Proposed Changes

Index Creation (if applicable)

-- PostgreSQL: B-tree index
CREATE INDEX CONCURRENTLY idx_table_column
ON table_name (column_name)
WHERE condition;  -- Partial index if applicable

-- MySQL: Index with prefix
CREATE INDEX idx_table_column
ON table_name (column_name(255));

-- Composite index (leftmost prefix rule)
CREATE INDEX idx_table_multi
ON table_name (col1, col2, col3);

Query Rewrite (if applicable)

Before:

-- Original slow query

After:

-- Optimized query

Changes made:

• [ ] Removed SELECT *
• [ ] Added explicit column list
• [ ] Converted subquery to JOIN
• [ ] Added predicate pushdown
• [ ] Used EXISTS instead of IN
• [ ] Added LIMIT for pagination
• [ ] Removed function on indexed column

Statistics Update (if applicable)

-- PostgreSQL: Update statistics
ANALYZE table_name;
ANALYZE table_name (specific_column);

-- PostgreSQL: More detailed stats
ALTER TABLE table_name ALTER COLUMN col SET STATISTICS 1000;
ANALYZE table_name;

-- MySQL: Update statistics
ANALYZE TABLE table_name;

---

Step 4: Verify (Validation)

Post-Change Metrics

New Execution Plan

-- Paste new EXPLAIN output here

Validation Checklist

• [ ] Query returns same results
• [ ] Execution time meets target
• [ ] No regression on related queries
• [ ] Index used as expected in plan
• [ ] No new warnings in plan

Regression Testing

-- Compare result sets (should be empty if equal)
SELECT * FROM (
    -- Original query results
) original
EXCEPT
SELECT * FROM (
    -- Optimized query results
) optimized;

Load Testing (if critical query)

• [ ] Tested under concurrent load
• [ ] Tested with production-like data volume
• [ ] Tested with cold cache

---

Decision Log

---

Do / Avoid

GOOD: Do

• Measure baseline before any change
• Change one variable at a time
• Verify results match after optimization
• Document all changes and rationale
• Test with representative data volumes
• Check for query plan regressions after index changes
• Update statistics before concluding "needs index"

BAD: Avoid

• Adding indexes without checking if they'll be used
• Optimizing queries without understanding the plan
• Assuming more indexes = better performance
• Ignoring write performance impact of indexes
• Skipping verification step
• Optimizing for test data volumes (not production)
• Making multiple changes simultaneously

---

Anti-Patterns Detected

---

Optional: AI/Automation

Note: AI tools should supplement, not replace, systematic analysis.

AI-Assisted Analysis

• EXPLAIN plan summarization (identify bottlenecks)
• Query rewrite suggestions (must be validated)
• Index recommendation review (check selectivity first)

Bounded Claims

• AI suggestions require human verification of correctness
• Automated index recommendations may miss workload context
• Query rewrites must be tested for result equivalence

---

Related Templates

• template-explain-analysis.md — Deep EXPLAIN plan interpretation
• template-index.md — Index design patterns
• template-slow-query.md — Slow query triage

---

Last Updated: December 2025

# SQL Query Tuning Template

*Purpose: Use this template for systematic, repeatable review and optimization of SQL queries before deployment or when troubleshooting slowness.*

---

## When to Use

Use this template when:
- Reviewing new or changed SQL queries
- Investigating slow report, dashboard, or transactional queries
- Preparing for migration or major database changes

---

## Structure

This template has 4 sections:
1. **Query & Table Details** — context, schema, purpose
2. **Performance Review Checklist** — operational checks
3. **EXPLAIN Analysis** — plan output & findings
4. **Optimization & Verification** — fixes and validation

---

# TEMPLATE STARTS HERE

## 1. Query & Table Details

- **Query Purpose:**  
  [Describe what business logic, report, or endpoint uses this query]

- **Primary Table(s):**  
  [e.g., orders, users, events]

- **Expected Result Set Size:**  
  [Row count or "single row", "top 100", etc.]

- **Is this user-facing or internal?**  
  [Yes/No]

---

## 2. Performance Review Checklist

- [ ] Only needed columns in SELECT (no `SELECT *`)
- [ ] WHERE clause is selective and matches index
- [ ] Joins are on indexed columns
- [ ] No functions/wrappers on indexed columns in WHERE/JOIN
- [ ] Results are paginated (`LIMIT` or similar)
- [ ] No unnecessary subqueries or CTEs

---

## 3. EXPLAIN Analysis

- **Plan Used:**  
  [Paste or summarize EXPLAIN (ANALYZE) output]

- **Table/Seq Scan present?**  
  [Yes/No. If yes, on which table(s)?]

- **Join Type(s) and Order:**  
  [e.g., Nested Loop, Hash Join]

- **Rows Examined vs. Returned:**  
  [Summarize, e.g. "100k scanned, 150 returned"]

- **Sorting/Aggregation Method:**  
  [Filesort, index, in-memory, etc.]

---

## 4. Optimization & Verification

- **Proposed Rewrite or Index Change:**  
  [e.g., add composite index, rewrite predicate]

- **Updated EXPLAIN Plan:**  
  [Paste new plan summary]

- **Performance Test Results:**  
  [Before: X ms/rows. After: Y ms/rows.]

- **Rollback Plan:**  
  [How to revert if performance regresses]

---

# COMPLETE EXAMPLE

## 1. Query & Table Details

- **Query Purpose:**  
  Used in dashboard to display top 10 customers by revenue last year

- **Primary Table(s):**  
  orders, customers

- **Expected Result Set Size:**  
  10 rows

- **Is this user-facing or internal?**  
  User-facing

---

## 2. Performance Review Checklist

- [x] Only needed columns selected
- [x] WHERE filters by year and customer_id
- [x] JOIN on indexed customer_id
- [x] No functions on WHERE columns
- [x] Results limited (LIMIT 10)
- [x] No unneeded subqueries

---

## 3. EXPLAIN Analysis

- **Plan Used:**  
  Index Scan on orders, Nested Loop join to customers

- **Table/Seq Scan present?**  
  No

- **Join Type(s) and Order:**  
  Nested Loop: orders -> customers

- **Rows Examined vs. Returned:**  
  1,200 scanned, 10 returned

- **Sorting/Aggregation Method:**  
  In-memory sort (on revenue desc)

---

## 4. Optimization & Verification

- **Proposed Rewrite or Index Change:**  
  Add composite index: `(customer_id, order_date) INCLUDE (amount)`

- **Updated EXPLAIN Plan:**  
  Index Scan + Sort (smaller temp file)

- **Performance Test Results:**  
  Before: 120 ms. After: 8 ms.

- **Rollback Plan:**  
  Drop index if query slows or write overhead spikes.

---

## Quality Checklist

Before finalizing:
- [ ] Query and plan reviewed with realistic data
- [ ] All changes tested in staging/prod-like environment
- [ ] Rollback steps documented

# SQL Schema Design Template

*Purpose: A complete template for designing new database schema structures or refactoring existing ones. Covers modeling, normalization, integrity, sizing, indexing, and operational impacts.*

---

## 1. Overview

**Feature / Component Name:**  
[Describe what this schema enables]

**Author:**  
[Name]

**Date:**  
[YYYY-MM-DD]

**Business Requirements (Summary):**  
- [Requirement 1]  
- [Requirement 2]  
- [Requirement 3]  

**Functional Requirements Impacting Schema:**  
- [ ] Must store historical versions  
- [ ] Must support search/filtering  
- [ ] Must support high write throughput  
- [ ] Must support analytics queries  
- [ ] Must support soft deletes  
- [ ] Must support multi-tenancy  
- [ ] Must support GDPR deletion  

---

## 2. Data Model Definition

### 2.1 Table Definitions

Use this format for each new/modified table.

Table: <table_name>

Columns:

• id BIGSERIAL PRIMARY KEY
• <column_name> <datatype> [NULL|NOT NULL] [DEFAULT ...]
• created_at TIMESTAMPTZ DEFAULT NOW()
• updated_at TIMESTAMPTZ DEFAULT NOW()

Notes:

• <describe purpose of table>
• <expected relationships>
• <expected row growth>

---

### 2.2 Column Details Table

| Column | Type | Nullable | Default | Description | Notes |
|--------|------|----------|----------|-------------|--------|
| id | BIGSERIAL | no | PK | unique identifier | |
|  |  |  |  |  | |

---

### 2.3 Relationship Plan

List all foreign keys and referential rules.

| Relation | Type | Cardinality | FK? | On Delete | Notes |
|----------|------|-------------|-----|-----------|--------|
| orders -> users | parent-child | N:1 | Yes | NO ACTION | |
| product_attributes -> products | detail | N:1 | Yes | CASCADE | |

---

## 3. Normalization & Data Modeling Decisions

### 3.1 Normalization Level

- [ ] 1NF  
- [ ] 2NF  
- [ ] 3NF  
- [ ] BCNF  
- [ ] Intentional denormalization  

**Rationale:**  
[Explain choice and trade-offs]

---

### 3.2 Anti-pattern Check

Mark any found:

- [ ] EAV (Entity-Attribute-Value)  
- [ ] Multi-value columns  
- [ ] Polymorphic associations  
- [ ] Oversized JSONB fields  
- [ ] Tables without primary keys  
- [ ] Overloaded “bucket” tables  

**Fix Strategy:**  
[Describe approach]

---

### 3.3 Cardinality & Fan-out Checks

| Table | Expected Rows | Growth Rate | High Fan-out? | Notes |
|--------|----------------|-------------|----------------|--------|
|  |  |  |  | |

---

### 3.4 Soft Delete Strategy

- [ ] Use `deleted_at` timestamp  
- [ ] Use status column  
- [ ] Avoid hard deletes  
- [ ] Partition deleted rows  

---

## 4. Index Strategy

### 4.1 Required Indexes

List required indexes and purpose:

| Index Name | Table | Columns | Type | Purpose |
|------------|--------|---------|------|---------|
| idx_orders_user_ts | orders | (user_id, created_at) | btree | lookup/sort |  
|  |  |  |  | |

---

### 4.2 Optional / Conditional Indexes

| Index | Condition to Add (when traffic reaches X) | Notes |
|--------|--------------------------------------------|--------|
| | | |

---

### 4.3 Fulltext / Search Indexes

If search heavy:

- [ ] Postgres GIN + `to_tsvector`  
- [ ] Trigram index for LIKE '%pattern%'  
- [ ] MySQL FULLTEXT  

---

## 5. Constraints & Integrity

### 5.1 Constraint Inventory

| Type | Applied? | Details |
|-------|----------|---------|
| NOT NULL | | |
| CHECK | | |
| UNIQUE | | |
| FOREIGN KEY | | |
| Composite Key | | |
| Exclusion Constraints (PG) | | |

---

### 5.2 CHECK Constraint Examples

ALTER TABLE orders ADD CONSTRAINT chk_total_nonnegative CHECK (total >= 0);

---

### 5.3 Multi-table Integrity Rules

- [ ] FK ensures referential integrity  
- [ ] No cascading deletes unless intentional  
- [ ] Use soft deletes with care when FKs exist  

---

## 6. Performance & Workload Considerations

### 6.1 Access Patterns

Mark applicable:

- [ ] Key-based lookups  
- [ ] Range scans  
- [ ] Aggregations  
- [ ] Joins across many tables  
- [ ] Time-series data  
- [ ] Batch writes  
- [ ] Read-heavy workload  
- [ ] Write-heavy workload  

---

### 6.2 Expected Query Examples

Paste sample queries:

SELECT ... FROM ... WHERE ...

---

### 6.3 Partitioning (if needed)

| Partition Type | Use Case |
|----------------|-----------|
| Range | date-based tables |
| List | tenants, categories |
| Hash | write scalability |

---

## 7. Storage Estimates

| Table | Expected Rows (1yr) | Row Size | Table Size | Index Size |
|--------|-----------------------|-----------|------------|-------------|
| users | 12M | 180 bytes | ~2GB | ~700MB |
|  |  |  |  | |

---

## 8. Migration & Deployment Strategy

- [ ] Expand -> Migrate -> Contract  
- [ ] Backfill in batches  
- [ ] Add indexes concurrently (Postgres)  
- [ ] Staged constraint validation  
- [ ] Dual-write and dual-read if needed  
- [ ] Application compatibility checked  
- [ ] Rollback plan documented  

---

## 9. Operational Considerations

### 9.1 Vacuum (Postgres)

- [ ] Expected dead tuple impact  
- [ ] HOT update friendliness  
- [ ] Autovacuum scaling required?  

### 9.2 Replication

- [ ] Will writes increase WAL/binlog?  
- [ ] Will schema affect replica performance?  

### 9.3 Backups / HA

- [ ] Large tables require PITR?  
- [ ] Partitioning helps backups?  

---

## 10. Final Review Checklist

### Modeling
- [ ] No anti-patterns present  
- [ ] Primary key defined  
- [ ] Constraints validated  
- [ ] Schema normalized appropriately  

### Performance
- [ ] Indexes validated with sample queries  
- [ ] ORDER BY coverage ensured  
- [ ] JOIN patterns evaluated  

### Safety
- [ ] Migration plan safe  
- [ ] Rollback documented  
- [ ] No blocking DDL in production  

### Maintenance
- [ ] Stats & autovacuum considered  
- [ ] Long-term scaling evaluated  

---

## 11. Complete Example

**Feature:** Tiered user profiles  
**New Table:** `user_profile_metadata`

CREATE TABLE user_profile_metadata ( user_id BIGINT PRIMARY KEY REFERENCES users(id), bio TEXT, tier TEXT NOT NULL, preferences JSONB, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() );

**Indexes:**

CREATE INDEX idx_user_profile_tier ON user_profile_metadata(tier); CREATE INDEX idx_user_profile_prefs_gin ON user_profile_metadata USING GIN (preferences);

**Notes:**
- Tier is filterable -> btree index  
- JSONB preferences search -> GIN  
- Data expected: ~50M rows in 12 months  

**Migration Strategy:**
- Add table  
- Backfill key metadata for active users  
- Deploy app writing to new table  

---

# END

# SQL Security & Audit Template

*Purpose: A complete operational checklist and documentation template for performing SQL security reviews, privilege audits, PII assessments, and compliance-focused database evaluations.*

---

# 1. Overview

**Environment:**  
- [ ] Production  
- [ ] Staging  
- [ ] Development  

**Database:**  
- [ ] Postgres  
- [ ] MySQL  
- [ ] MariaDB  
- [ ] Other: __________

**Scope of Audit:**  
- [ ] SQL injection review  
- [ ] Privilege/role audit  
- [ ] PII classification  
- [ ] Encryption review  
- [ ] Logging & audit trails  
- [ ] Compliance check (HIPAA/GDPR/SOX/PCI)  
- [ ] Network access controls  
- [ ] Data retention & deletion policy  

**Date:**  
[YYYY-MM-DD]  
**Auditor:**  
[Name]

---

# 2. SQL Injection Audit

### 2.1 Application Query Review

Check all relevant parts of the stack:

- [ ] All queries use parameters (no string interpolation)  
- [ ] ORM uses prepared statements  
- [ ] Dynamic SQL validated or rewritten  
- [ ] No concatenated conditions  
- [ ] No unsafe LIKE patterns with user input  
- [ ] No user input inside ORDER BY without whitelist validation  
- [ ] Sanitization functions applied when appropriate  

### 2.2 Direct SQL Interaction

- [ ] No direct SQL from UI components  
- [ ] No unsafe admin consoles  
- [ ] No ad-hoc manual scripts in production  
- [ ] No unreviewed stored procedures with dynamic SQL  

---

# 3. Privilege & Role Audit

### 3.1 Role Inventory

Paste current roles:

<list of roles, grants, and inheritance>

### 3.2 Check for Risks

- [ ] No SUPERUSER roles (Postgres)  
- [ ] No ALL PRIVILEGES grants (MySQL, Postgres)  
- [ ] No developers with production write access  
- [ ] Application roles follow least-privilege  
- [ ] Read-only roles truly read-only  
- [ ] Admin roles restricted to DBAs/SRE  
- [ ] No stale or unused roles > 90 days  
- [ ] Passwordless accounts reviewed  
- [ ] Rotation policy in place  

### 3.3 Privilege Escalation Checks

- [ ] No role inheritance that grants broad access  
- [ ] No ability to modify schemas without approval  
- [ ] No ability to disable audit logs  
- [ ] No ability to drop critical tables or indexes  
- [ ] No proxy roles with uncontrolled access  

---

# 4. Network & Access Controls

- [ ] Database not publicly accessible  
- [ ] Firewall restrictions applied  
- [ ] Allowlist enforced  
- [ ] TLS enforced for all connections  
- [ ] No plaintext credentials in code repos  
- [ ] Secrets stored in vault manager (AWS SecretManager, Vault, etc.)  
- [ ] IAM / service accounts use short-lived tokens  
- [ ] No hard-coded passwords in config files  

---

# 5. Data Classification & PII/PHI Review

### 5.1 Data Categories

Mark all applicable:

- [ ] Personal data (PII)  
- [ ] Financial data  
- [ ] Healthcare data (PHI)  
- [ ] Authentication data  
- [ ] Logs containing user identifiers  
- [ ] Transactional data  

### 5.2 PII Storage Rules

- [ ] Minimal retention  
- [ ] Proper deletion supported (GDPR 17)  
- [ ] Pseudonymization where possible  
- [ ] PII encrypted at rest  
- [ ] PII encrypted in transit  
- [ ] Audit logs exclude sensitive fields  
- [ ] No unnecessary duplication of PII across tables  

---

# 6. Encryption Review

### 6.1 Encryption at Rest

- [ ] Disk-level encryption enabled  
- [ ] Key rotation policy documented  
- [ ] Encrypted snapshots/backups  
- [ ] WAL/binlogs encrypted  
- [ ] TDE support evaluated (if applicable)  

### 6.2 Encryption in Transit

- [ ] TLS enforced for connections  
- [ ] Client certificates validated  
- [ ] SSLMode=verify-full (Postgres) where required  
- [ ] No plaintext connections allowed  

---

# 7. Logging & Audit Trails

### 7.1 Database Audit Logging

Check:

- [ ] SELECT audit for sensitive tables  
- [ ] DDL changes logged  
- [ ] Failed logins logged  
- [ ] Permission changes logged  
- [ ] Superuser actions logged  
- [ ] Query logs sanitized  

### 7.2 Log Storage & Privacy

- [ ] Logs encrypted in storage  
- [ ] No sensitive data in logs  
- [ ] Retention policy applied  
- [ ] Access to logs locked down  

---

# 8. Backup & Disaster Recovery Security

- [ ] Backups encrypted  
- [ ] Backup access restricted  
- [ ] Restore tested recently  
- [ ] PITR logs protected  
- [ ] Backups stored in separate region  
- [ ] Backups not left on local disk  
- [ ] Backups exclude unnecessary PII when possible  

---

# 9. Compliance Checks

Check if relevant:

## 9.1 GDPR
- [ ] Right-to-erasure implemented  
- [ ] Data minimization applied  
- [ ] Data export capability  

## 9.2 PCI
- [ ] Card data isolated  
- [ ] Encryption validated  
- [ ] Access restricted  

## 9.3 HIPAA
- [ ] PHI encrypted  
- [ ] Access logging enabled  
- [ ] Breach detection documented  

## 9.4 SOX
- [ ] DDL approvals required  
- [ ] Separate duties for review & execution  
- [ ] Change tracking enforced  

---

# 10. Security Red Flags (Yes = Bad)

| Issue | Yes/No | Notes |
|-------|--------|--------|
| Public DB endpoint | | |
| SUPERUSER accounts | | |
| No TLS | | |
| SQL injection vectors found | | |
| Sensitive logs | | |
| PII stored unencrypted | | |
| Weak passwords | | |
| No password rotation | | |
| Missing audit logs | | |
| Stale roles | | |
| Secrets in code repos | | |

---

# 11. Recommended Fixes

List actionable changes:

1.  
2.  
3.  
4.  

Each fix should include:  
- Expected impact  
- Owner  
- Timeline  
- Risk level  

---

# 12. Final Audit Conclusion

**Overall Security Posture:**  
- [ ] Excellent  
- [ ] Good  
- [ ] Needs Improvement  
- [ ] High Risk  

**Auditor Notes:**  
[Write conclusions]

**Next Review Due:**  
[Date]

---

# 13. Example Completed Audit

**Environment:** Production  
**DB:** Postgres 14  
**Findings:**  
- Stale read-only role with expired password (fixed)  
- Missing TLS enforcement (added `ssl=on`)  
- Logs contained raw emails (updated sanitizer)  
- SUPERUSER used for app migrations (moved to scoped role)  

**Final Rating:** Needs improvement, critical issues resolved.

---

# END

# Slow Query Analysis Template

*Purpose: A structured template for analyzing, diagnosing, and resolving slow SQL queries in production environments.*

---

## 1. Summary

**Query / Endpoint:**  
[Copy SQL or name of endpoint]

**Severity:**  
- [ ] P0 (critical outage)  
- [ ] P1 (major latency impact)  
- [ ] P2  
- [ ] P3  

**Start Time:**  
[Timestamp]

**User Impact:**  
[What users experience]

**System Impact:**  
- [ ] High CPU  
- [ ] High I/O  
- [ ] Connection saturation  
- [ ] Deadlocks  
- [ ] Increased error rate  
- [ ] Replication lag  

---

## 2. Query Example(s)

Paste real queries with real parameters:

SELECT ... FROM ... WHERE ... ORDER BY ...

**Parameter Notes:**  
- Typical:  
- Slowest:  
- Skewed values:  

---

## 3. Workload Characteristics

| Attribute | Value |
|----------|-------|
| Frequency | High / Medium / Low |
| Latency Target | [ms] |
| Peak Latency Observed | [ms] |
| Table Size | [row count] |
| Query Source | API, cron, ORM, report |

---

## 4. Initial Findings

### 4.1 Observed Symptoms
- [ ] Increased latency  
- [ ] Timeouts  
- [ ] CPU spike  
- [ ] Disk reads rising  
- [ ] Temp file usage  
- [ ] Lock waits  

### 4.2 Application Logs
Paste relevant logs or errors.

<logs>

---

## 5. Metrics Snapshot

### 5.1 Postgres

- `pg_stat_statements` entry  
- buffer hit ratio  
- deadlocks  
- autovacuum/backfill interactions  
- seq scan count  

### 5.2 MySQL

- slow query log entry  
- InnoDB row lock waits  
- buffer pool hit rate  
- temporary table usage  

Paste metric excerpts:

<metrics>

---

## 6. Execution Plan

Paste EXPLAIN output:

EXPLAIN (ANALYZE, BUFFERS) <query>

### Quick Checks

- [ ] Seq Scan unexpectedly  
- [ ] Bitmap scan that should be index scan  
- [ ] Hash join / sort spilled to disk  
- [ ] Estimated vs actual rows mismatch  
- [ ] Multiple nested loops  
- [ ] Wide rows causing I/O pressure  

---

## 7. Execution Plan Analysis

### 7.1 Scan Analysis

| Type | Reason | Notes |
|------|--------|-------|
| Seq Scan | Missing index | |
| Index Scan | Correct | |
| Index Only Scan | Ideal | |

Checklist:

- [ ] WHERE clause uses indexable patterns  
- [ ] No function on indexed column  
- [ ] Filter selectivity acceptable  

---

### 7.2 Join Analysis

| Join | Algorithm | Good? | Notes |
|------|-----------|--------|-------|
| table A -> B | Hash Join | Yes | |
| table B -> C | Nested Loop | No | Missing index |

Checklist:

- [ ] Join keys indexed  
- [ ] Large table joined first? (anti-pattern)  
- [ ] Rewrite join order needed?  

---

### 7.3 Sort / Aggregate Analysis

Issues:

- [ ] Sort too large -> disk spill  
- [ ] GROUP BY high cardinality  
- [ ] ORDER BY not backed by index  

Fixes:

- Add index matching ORDER BY  
- Pre-aggregate if meaningful  
- Reduce cardinality if possible  

---

## 8. Root Cause Hypotheses

Check all that apply:

- [ ] Missing composite index  
- [ ] Incorrect index order  
- [ ] Data skew (hot key)  
- [ ] Function preventing index usage  
- [ ] OR/filter causing full scan  
- [ ] JOIN reordering required  
- [ ] Statistics outdated  
- [ ] Table or index bloat  
- [ ] Lock contention  
- [ ] Inefficient pagination  
- [ ] ORM-generated inefficient SQL  

Notes:
[Explain key suspects]

---

## 9. Experiments

List short, reversible tests:

- [ ] Add temporary index  
- [ ] Force join order (`enable_hashjoin = off`, Postgres for debugging)  
- [ ] Parameter variation tests  
- [ ] Rewrite WHERE clause  
- [ ] Keyset pagination test  
- [ ] Increase memory locally (session-level work_mem)  
- [ ] Test LIMIT or narrowing results  

---

## 10. Final Fix

**Fix Implemented:**  
[Index added / Query rewritten / Stats updated / Pagination changed]

**SQL / DDL Applied:**

<paste changes>

**Reasoning:**  
[Why this fix works]

**Risk Level:**  

- [ ] Low  
- [ ] Medium  
- [ ] High  

---

## 11. Verification

### 11.1 Performance Comparison

| Test Case | Before (ms) | After (ms) |
|-----------|--------------|-------------|
| Typical params | | |
| Worst-case params | | |
| p95 latency | | |
| p99 latency | | |

### 11.2 Plan Verification

- [ ] Index scan used  
- [ ] Sort removed  
- [ ] Join algorithm optimal  
- [ ] Estimated vs actual rows aligned  

### 11.3 System Verification

- [ ] CPU normalized  
- [ ] I/O normalized  
- [ ] No lock waits  
- [ ] Replication lag normal  

---

## 12. Final Notes & Follow-Up

- [ ] Documented in runbook  
- [ ] Added regression test cases  
- [ ] Scheduled index review  
- [ ] Additional optimizations deferred list  

---

## 13. Complete Example

**Problem:** Slow user dashboard query scanning 2.1M rows.

**Root Cause:** Missing composite index on `(user_id, created_at DESC)`.

**Fix:**

CREATE INDEX idx_orders_user_ts ON orders(user_id, created_at DESC) INCLUDE (total);

**Result:**  
Latency improved 960ms -> 7ms.  
Sort eliminated.  
Index-only scan achieved.

**Verification:**  
[check] CPU drop  
[check] No spills  
[check] p95 < 10ms

---

# END

# SQL Server Execution Plan Analysis Template

*Purpose: Standardize the process for capturing, analyzing, and optimizing SQL Server query execution plans using SET STATISTICS and execution plan XML.*

---

## When to Use

Use this template for:
- Diagnosing slow queries in SQL Server
- Reviewing execution plans before/after index changes
- Analyzing T-SQL stored procedures or ad-hoc queries
- Performance tuning for production workloads

---

## Structure

This template includes:
1. **Query & Context**
2. **Execution Plan Capture**
3. **Plan Analysis Checklist**
4. **Action Items & Validation**

---

# TEMPLATE STARTS HERE

## 1. Query & Context

- **Query:**
  [Paste T-SQL statement being reviewed]

- **Database:**
  [Database name, SQL Server version, environment]

- **Table(s) Involved:**
  [e.g., Orders, Customers, OrderDetails]

- **Expected Result Size:**
  [Rows, typical use-case]

- **Query Type:**
  - [ ] Ad-hoc query
  - [ ] Stored procedure
  - [ ] View
  - [ ] Function
  - [ ] Trigger

---

## 2. Execution Plan Capture

### Method 1: Graphical Execution Plan (SSMS)

-- Enable actual execution plan in SSMS (Ctrl+M) -- Or use: SET STATISTICS XML ON; [Your SQL query here] SET STATISTICS XML OFF;

### Method 2: Text Statistics

SET STATISTICS IO ON; SET STATISTICS TIME ON;

[Your SQL query here]

SET STATISTICS IO OFF; SET STATISTICS TIME OFF;

### Method 3: Query Store (SQL Server 2016+)

-- Query from Query Store SELECT q.query_id, qt.query_sql_text, rs.avg_duration/1000 AS avg_duration_ms, rs.avg_logical_io_reads, rs.avg_physical_io_reads FROM sys.query_store_query q JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id JOIN sys.query_store_plan p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id WHERE qt.query_sql_text LIKE '%your_pattern%' ORDER BY rs.avg_duration DESC;

---

## 3. Execution Plan Analysis Checklist

### 3.1 Scan Operations
- [ ] Is there a **Table Scan** on a large table?
  - [ ] Should an index be used instead?
- [ ] Is **Index Scan** being used where **Index Seek** would be better?
- [ ] Are **Clustered Index Scans** appearing on large tables?

### 3.2 Join Operations
- [ ] **Nested Loop Join** - Good for small result sets with indexes
- [ ] **Hash Join** - Good for large unsorted tables
- [ ] **Merge Join** - Good for pre-sorted or indexed data
- [ ] Are join predicates using indexed columns?
- [ ] Are there implicit conversions causing index scans?

### 3.3 Key Lookups (RID/Key Lookup)
- [ ] Are there **Key Lookup** operations?
  - High cost lookups indicate missing covering indexes
- [ ] Consider adding included columns to index

### 3.4 Sorts and Spills
- [ ] Are there **Sort** operators with high cost?
- [ ] Are sorts spilling to tempdb? (Check STATISTICS IO)
- [ ] Can sorting be eliminated with an index?

### 3.5 Warnings and Issues
- [ ] **Missing Index** recommendations in plan?
- [ ] **Implicit conversions** (data type mismatches)?
- [ ] **Parameter sniffing** issues?
- [ ] **Statistics out of date**?
- [ ] **Parallelism** issues (CXPACKET waits)?

### 3.6 I/O Statistics
- [ ] **Logical reads** - pages read from buffer cache
- [ ] **Physical reads** - pages read from disk
- [ ] **Read-ahead reads** - large scans
- [ ] High logical reads indicate missing indexes

---

## 4. SQL Server 2025 Query Optimization Features

### Optional Parameter Plan Optimization (OPPO)

Addresses parameter sniffing issues automatically by choosing optimal plans based on runtime parameters:

-- Enable OPPO at database level ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

-- Check if OPPO is active for a query SELECT qsp.plan_id, qsp.is_plan_guide_plan, qsq.query_id FROM sys.query_store_plan qsp JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id WHERE qsp.plan_type = 2; -- Parameter sensitive plan

### Cardinality Estimation Feedback for Expressions

The engine learns from previous executions to improve row estimates:

- Automatically adjusts cardinality for calculated columns
- Learns from implicit conversions
- No manual intervention required

### Optimized Locking (TID + LAQ)

Reduces lock contention in high-concurrency environments:

-- Enable optimized locking ALTER DATABASE YourDatabase SET OPTIMIZED_LOCKING = ON;

-- Verify optimized locking is active SELECT name, is_optimized_locking_on FROM sys.databases WHERE name = 'YourDatabase';

**Benefits:**

- Transaction ID (TID) locking reduces lock memory consumption
- Lock After Qualification (LAQ) delays locks until predicates evaluated
- Lower `LCK_M_IX` wait times in benchmarks

### TempDB Resource Governance

Prevent runaway queries from filling tempdb:

-- Create resource pool with tempdb limit CREATE RESOURCE POOL TempDBLimitedPool WITH ( MAX_TEMPDB_PERCENT = 25 -- 25% of tempdb max );

-- Assign workload group to pool CREATE WORKLOAD GROUP LimitedGroup USING TempDBLimitedPool;

-- Classify sessions to workload group CREATE FUNCTION dbo.TempDBClassifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN IF APP_NAME() LIKE '%ReportingApp%' RETURN 'LimitedGroup'; RETURN 'default'; END;

### Optimized sp_executesql

Reduces compilation storms for large dynamic SQL:

- Better caching of parameterized queries
- Lower CPU contention during parallel compilations

### Query Store on Readable Secondaries

Query Store now runs on readable replicas by default:

- Performance history preserved during failovers
- Better tuning of read-only workloads

-- Verify Query Store on secondary SELECT actual_state_desc, readonly_reason FROM sys.database_query_store_options;

---

## 5. Common SQL Server Performance Issues

### Issue 1: Missing Index
**Symptom:** Table/Index Scan + Missing Index warning in plan

**Fix:**

-- Check missing index DMV SELECT OBJECT_NAME(d.object_id) AS table_name, d.equality_columns, d.inequality_columns, d.included_columns, s.avg_user_impact, s.user_seeks FROM sys.dm_db_missing_index_details d JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle WHERE d.database_id = DB_ID() ORDER BY s.avg_user_impact * s.user_seeks DESC;

### Issue 2: Key Lookup
**Symptom:** Index Seek + Key Lookup (expensive)

**Fix:** Create covering index

CREATE NONCLUSTERED INDEX IX_TableName_Covering ON TableName (FilterColumn) INCLUDE (Column1, Column2, Column3);

### Issue 3: Parameter Sniffing
**Symptom:** Query fast with some parameters, slow with others

**Fix Options:**

-- Option 1: OPTIMIZE FOR hint SELECT * FROM Orders WHERE CustomerId = @CustomerId OPTION (OPTIMIZE FOR (@CustomerId = 123));

-- Option 2: RECOMPILE SELECT * FROM Orders WHERE CustomerId = @CustomerId OPTION (RECOMPILE);

-- Option 3: Local variable DECLARE @LocalCustomerId INT = @CustomerId; SELECT * FROM Orders WHERE CustomerId = @LocalCustomerId;

### Issue 4: Implicit Conversion
**Symptom:** CONVERT_IMPLICIT in execution plan, index not used

**Fix:** Ensure data type match

-- Bad: CustomerId (INT) compared to VARCHAR WHERE CustomerId = '123'

-- Good: Use correct data type WHERE CustomerId = 123

### Issue 5: Statistics Out of Date
**Symptom:** Estimated rows << Actual rows in plan

**Fix:**

-- Update statistics UPDATE STATISTICS TableName WITH FULLSCAN;

-- Check statistics age SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name, STATS_DATE(s.object_id, s.stats_id) AS last_updated FROM sys.stats s WHERE OBJECT_NAME(s.object_id) = 'YourTableName';

---

## 6. Action Items & Validation

### 5.1 Optimization Steps Proposed
[List specific changes: index creation, query rewrite, statistics update, etc.]

**Example:**

-- Create missing index CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate ON Orders (CustomerId, OrderDate DESC) INCLUDE (OrderTotal);

-- Update statistics UPDATE STATISTICS Orders WITH FULLSCAN;

### 5.2 Execution Plan After Optimization
[Paste updated plan or screenshot]

**Changes:**
- Table Scan -> Index Seek
- Key Lookup eliminated
- Sort removed (index provides ordering)

### 5.3 Performance Before/After

| Metric | Before | After | Improvement |
|--------|--------|-------|-------------|
| Duration (ms) | | | |
| Logical reads | | | |
| Physical reads | | | |
| CPU time (ms) | | | |

### 5.4 Rollback Plan
[How to revert changes if needed]

**Example:**

DROP INDEX IX_Orders_CustomerId_OrderDate ON Orders;

---

## 7. Complete Example

### Problem: Slow customer order history query

**Query:**

SELECT o.OrderId, o.OrderDate, o.OrderTotal FROM Orders o WHERE o.CustomerId = 12345 ORDER BY o.OrderDate DESC;

**Issues Found:**
- [x] Clustered Index Scan (no non-clustered index on CustomerId)
- [x] Sort operation (expensive)
- [x] 1.2M logical reads for 10 rows returned

**Execution Plan Analysis:**

Clustered Index Scan (Cost: 95%) ├─ Rows: 10 (Estimated: 50000) ├─ Logical Reads: 1,200,000 └─ Sort (Cost: 5%)

**Fix Applied:**

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate ON Orders (CustomerId, OrderDate DESC) INCLUDE (OrderTotal);

**After Optimization:**

Index Seek (Cost: 100%) ├─ Rows: 10 (Estimated: 10) ├─ Logical Reads: 15 └─ Sort: Eliminated (index provides ordering)

**Results:**
- Duration: 1,850 ms -> 3 ms (99.8% improvement)
- Logical reads: 1,200,000 -> 15 (99.999% improvement)
- Sort eliminated

---

## 8. Quality Checklist

Before finalizing:

- [ ] Execution plan captured (XML or graphical)
- [ ] Statistics IO/TIME reviewed
- [ ] Index or query rewrite tested in non-prod
- [ ] Performance improvement validated
- [ ] Rollback steps documented
- [ ] Query Store monitoring enabled (if available)
- [ ] No negative impact on other queries verified

---

## 9. Useful DMV Queries

### Most Expensive Queries

SELECT TOP 10 qs.execution_count, qs.total_elapsed_time / 1000 AS total_elapsed_time_ms, qs.total_logical_reads, SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS query_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY qs.total_elapsed_time DESC;

### Index Usage Statistics

SELECT OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id WHERE OBJECT_NAME(s.object_id) = 'YourTableName';

# SQL Server Index Design Template

*Purpose: Structured template for designing, creating, and maintaining indexes in SQL Server with focus on clustered, non-clustered, columnstore, and filtered indexes.*

---

## When to Use

Use this template for:
- Creating new indexes based on query patterns
- Optimizing existing index strategy
- Resolving missing index recommendations
- Reducing index fragmentation
- Designing covering indexes

---

## Structure

1. **Index Design Decision**
2. **Index Creation**
3. **Validation & Monitoring**
4. **Maintenance Strategy**

---

# TEMPLATE STARTS HERE

## 1. Index Design Decision

### 1.1 Query Pattern Analysis

**Query Pattern:**
[Paste query or describe access pattern]

**Workload Type:**
- [ ] OLTP (frequent small transactions)
- [ ] OLAP (analytical, large scans)
- [ ] Mixed workload

**Query Filters (WHERE clause):**
- Column 1: [e.g., CustomerId]
- Column 2: [e.g., OrderDate]

**Sort/Order By:**
- [ ] None
- [ ] Column(s): [e.g., OrderDate DESC]

**Columns Returned (SELECT):**
- [ ] All columns (SELECT *)
- [ ] Specific columns: [list]

---

### 1.2 Index Type Selection

#### Clustered Index
**Use when:**
- Primary key or unique identifier
- Range queries benefit from physical ordering
- One per table (defines physical storage)

**Default:** Primary key usually gets clustered index automatically

#### Non-Clustered Index
**Use when:**
- Supporting WHERE clause filters
- Supporting JOIN conditions
- Supporting ORDER BY
- Multiple per table allowed

#### Covering Index (with INCLUDE)
**Use when:**
- Query reads only specific columns frequently
- Avoid key lookups (bookmark lookups)
- INCLUDE non-key columns to satisfy SELECT list

#### Filtered Index
**Use when:**
- Query targets subset of rows (e.g., WHERE Status = 'Active')
- Reduces index size and maintenance cost

#### Columnstore Index
**Use when:**
- Analytical queries (large aggregations)
- Data warehouse workloads
- Read-heavy scenarios

---

## 2. Index Creation

### 2.1 Clustered Index

**Standard Clustered Index:**

CREATE CLUSTERED INDEX IX_TableName_ClusteredColumn ON dbo.TableName (ColumnName);

**Primary Key with Clustered Index:**

ALTER TABLE dbo.TableName ADD CONSTRAINT PK_TableName PRIMARY KEY CLUSTERED (Id);

---

### 2.2 Non-Clustered Index

**Single Column Index:**

CREATE NONCLUSTERED INDEX IX_TableName_ColumnName ON dbo.TableName (ColumnName);

**Composite Index:**

CREATE NONCLUSTERED INDEX IX_TableName_Col1_Col2 ON dbo.TableName (Column1, Column2);

**Key Ordering Tips:**
- Most selective column first (for equality filters)
- ORDER BY columns follow WHERE columns
- Example: `(CustomerId, OrderDate DESC)` for `WHERE CustomerId = X ORDER BY OrderDate DESC`

---

### 2.3 Covering Index (with INCLUDE)

**Pattern:**

CREATE NONCLUSTERED INDEX IX_TableName_Covering ON dbo.TableName (KeyColumn1, KeyColumn2) INCLUDE (NonKeyColumn1, NonKeyColumn2, NonKeyColumn3);

**Example:**

-- Query: SELECT CustomerId, OrderDate, OrderTotal FROM Orders WHERE CustomerId = @Id CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Covering ON dbo.Orders (CustomerId) INCLUDE (OrderDate, OrderTotal);

**Benefits:**
- Eliminates key lookups (bookmark lookups)
- All data retrieved from index (no heap/clustered index access)

---

### 2.4 Filtered Index

**Pattern:**

CREATE NONCLUSTERED INDEX IX_TableName_Filtered ON dbo.TableName (ColumnName) WHERE FilterCondition;

**Example:**

-- Only index active customers CREATE NONCLUSTERED INDEX IX_Customers_Active ON dbo.Customers (CustomerId) WHERE Status = 'Active';

**Use Cases:**
- Sparse columns (many NULLs)
- Status-based filtering (Active/Inactive)
- Date ranges (recent data only)

---

### 2.5 Columnstore Index

**Clustered Columnstore (entire table):**

CREATE CLUSTERED COLUMNSTORE INDEX CCI_TableName ON dbo.TableName;

**Non-Clustered Columnstore (subset):**

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_TableName ON dbo.TableName (Column1, Column2, Column3);

**Use Cases:**
- Data warehouse fact tables
- Large aggregations (SUM, AVG, COUNT)
- Analytical queries

---

### 2.6 Online Index Creation (Enterprise Edition)

**Create index without blocking:**

CREATE NONCLUSTERED INDEX IX_TableName_ColumnName ON dbo.TableName (ColumnName) WITH (ONLINE = ON);

**Rebuild index online:**

ALTER INDEX IX_TableName_ColumnName ON dbo.TableName REBUILD WITH (ONLINE = ON);

---

## 3. Validation & Monitoring

### 3.1 Verify Index Usage

**Check if index is being used:**

SELECT OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name, i.type_desc, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, s.last_user_seek, s.last_user_scan FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id WHERE s.database_id = DB_ID() AND OBJECT_NAME(s.object_id) = 'YourTableName' ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;

### 3.2 Check Execution Plan

**Run query with execution plan:**

SET STATISTICS IO ON; SET STATISTICS TIME ON;

-- Your query here

SET STATISTICS IO OFF; SET STATISTICS TIME OFF;

**Verify:**
- [ ] Index Seek (not Index Scan or Table Scan)
- [ ] Low logical reads
- [ ] No Key Lookup warnings

### 3.3 Index Fragmentation

**Check fragmentation:**

SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name, ips.index_type_desc, ips.avg_fragmentation_in_percent, ips.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id WHERE ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 1000 ORDER BY ips.avg_fragmentation_in_percent DESC;

**Fragmentation thresholds:**
- < 10%: No action needed
- 10-30%: REORGANIZE
- > 30%: REBUILD

---

## 4. Index Maintenance

### 4.1 Reorganize Index (< 30% fragmentation)

ALTER INDEX IX_TableName_ColumnName ON dbo.TableName REORGANIZE;

### 4.2 Rebuild Index (> 30% fragmentation)

**Offline rebuild:**

ALTER INDEX IX_TableName_ColumnName ON dbo.TableName REBUILD;

**Online rebuild (Enterprise Edition):**

ALTER INDEX IX_TableName_ColumnName ON dbo.TableName REBUILD WITH (ONLINE = ON);

**Rebuild all indexes on a table:**

ALTER INDEX ALL ON dbo.TableName REBUILD;

### 4.3 Update Statistics

UPDATE STATISTICS dbo.TableName WITH FULLSCAN;

### 4.4 Drop Unused Indexes

**Find unused indexes:**

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, i.type_desc, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND (s.user_seeks = 0 OR s.user_seeks IS NULL) AND (s.user_scans = 0 OR s.user_scans IS NULL) AND (s.user_lookups = 0 OR s.user_lookups IS NULL) AND s.user_updates > 0 ORDER BY s.user_updates DESC;

**Drop unused index:**

DROP INDEX IX_TableName_UnusedIndex ON dbo.TableName;

---

## 5. Common Index Patterns

### Pattern 1: Lookups by ID

-- Query: SELECT * FROM Orders WHERE OrderId = @Id CREATE NONCLUSTERED INDEX IX_Orders_OrderId ON dbo.Orders (OrderId);

### Pattern 2: Foreign Key Joins

-- Query: JOIN Orders ON Orders.CustomerId = Customers.CustomerId CREATE NONCLUSTERED INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId);

### Pattern 3: Range Queries with Sort

-- Query: WHERE OrderDate >= @StartDate ORDER BY OrderDate DESC CREATE NONCLUSTERED INDEX IX_Orders_OrderDate ON dbo.Orders (OrderDate DESC);

### Pattern 4: Composite Filter + Sort

-- Query: WHERE CustomerId = @Id ORDER BY OrderDate DESC CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate ON dbo.Orders (CustomerId, OrderDate DESC);

### Pattern 5: Covering Index for Report

-- Query: SELECT CustomerId, OrderDate, OrderTotal WHERE OrderDate >= @Date CREATE NONCLUSTERED INDEX IX_Orders_Report ON dbo.Orders (OrderDate) INCLUDE (CustomerId, OrderTotal);

---

## 6. Complete Example

### Scenario: Optimize customer order history query

**Query:**

SELECT OrderId, OrderDate, OrderTotal, Status FROM Orders WHERE CustomerId = @CustomerId AND OrderDate >= DATEADD(MONTH, -6, GETDATE()) ORDER BY OrderDate DESC;

**Analysis:**
- Filter: CustomerId (equality), OrderDate (range)
- Sort: OrderDate DESC
- Select: OrderId, OrderDate, OrderTotal, Status

**Current Performance:**
- Clustered Index Scan
- 850,000 logical reads
- 1,200 ms execution time

**Index Design:**

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate ON dbo.Orders (CustomerId, OrderDate DESC) INCLUDE (OrderTotal, Status);

**Reasoning:**
1. `CustomerId` first (equality filter, most selective)
2. `OrderDate DESC` second (range filter + sort order)
3. `INCLUDE` non-key columns to avoid key lookups

**After Optimization:**
- Index Seek
- 12 logical reads
- 8 ms execution time

**Results:**
- 99.1% reduction in execution time
- 99.999% reduction in I/O

---

## 7. Quality Checklist

Before finalizing index:

- [ ] Execution plan shows Index Seek (not Scan)
- [ ] Logical reads significantly reduced
- [ ] No key lookups for covering queries
- [ ] Index is actually being used (check DMV)
- [ ] Fragmentation monitored
- [ ] Statistics up to date
- [ ] Index maintenance plan in place
- [ ] Impact on INSERT/UPDATE/DELETE acceptable
- [ ] No duplicate or redundant indexes

---

## 8. Index Anti-Patterns

### BAD: Anti-Pattern 1: Too Many Indexes
**Problem:** Slows down INSERT/UPDATE/DELETE
**Fix:** Drop unused indexes, consolidate overlapping indexes

### BAD: Anti-Pattern 2: Wrong Column Order
**Problem:** Index not used for queries
**Fix:** Equality columns first, then range/sort columns

### BAD: Anti-Pattern 3: Index on Low Selectivity Column
**Problem:** Index scan instead of seek
**Fix:** Avoid indexing columns with few distinct values (e.g., boolean flags)

### BAD: Anti-Pattern 4: Missing INCLUDE Columns
**Problem:** Key lookups remain
**Fix:** Add frequently accessed columns to INCLUDE

### BAD: Anti-Pattern 5: Ignoring Fragmentation
**Problem:** Performance degrades over time
**Fix:** Regular maintenance (reorganize/rebuild)

# MySQL EXPLAIN Analysis Template

*Purpose: Systematically document, review, and optimize MySQL query execution plans for performance tuning.*

---

## When to Use

Use this template when:
- Investigating slow MySQL queries
- Reviewing SQL before/after index or schema changes
- Performing regular database performance audits

---

## Structure

This template includes:
1. **Query & Context**
2. **EXPLAIN Output**
3. **Plan Review Checklist**
4. **Optimization & Verification**

---

# TEMPLATE STARTS HERE

## 1. Query & Context

- **SQL Query:**  
  [Paste the query being analyzed]

- **Schema/DB/Version:**  
  [e.g., mydb, MySQL 8.0.33, prod/staging]

- **Tables Involved:**  
  [List relevant tables]

- **Expected Result Size:**  
  [e.g., 1 row, top 100, full table, etc.]

---

## 2. EXPLAIN Output

- **Command Used:**  

EXPLAIN [FORMAT=JSON] [SQL...]

- **Plan Output:**  
  [Paste EXPLAIN table or JSON output]

---

## 3. Plan Review Checklist

- [ ] Are there any rows with `type: ALL`? (full table scan)
- [ ] Are appropriate indexes being used? (look at `key` column)
- [ ] Are all JOIN columns indexed?
- [ ] Any filesort/temp table in `Extra`? (slows ORDER BY, GROUP BY)
- [ ] Does `rows` estimate seem reasonable? (compare to table size)
- [ ] Any range/index_merge/index_subquery that can be improved?
- [ ] Is `Using where` present? (good—filtering at index)
- [ ] Are queries limited (`LIMIT`, appropriate WHERE)?
- [ ] Any sign of missing/outdated stats?

---

## 4. Optimization & Verification

- **Proposed Index or Query Rewrite:**  
  [Add new index, rewrite WHERE/JOIN, change join order, etc.]

- **Plan After Change:**  
  [Paste updated EXPLAIN, note differences]

- **Performance Before/After:**  
  - Before: [e.g., 900 ms, 8000 rows]
  - After:  [e.g., 13 ms, 50 rows]

- **Rollback Plan:**  
  [DROP INDEX, revert query, etc.]

---

# COMPLETE EXAMPLE

## 1. Query & Context

- **SQL Query:**  
  SELECT * FROM orders WHERE customer_id = 102 AND status = 'paid' ORDER BY created_at DESC LIMIT 5;
- **Schema/DB/Version:**  
  ordersdb, MySQL 8.0.33, production
- **Tables Involved:**  
  orders
- **Expected Result Size:**  
  5 rows

---

## 2. EXPLAIN Output

- **Command Used:**  
  EXPLAIN SELECT * FROM orders WHERE customer_id = 102 AND status = 'paid' ORDER BY created_at DESC LIMIT 5;
- **Plan Output:**  

  | id | select_type | table  | type | key  | key_len | ref   | rows | Extra                        |
  |----|-------------|--------|------|------|---------|-------|------|------------------------------|
  | 1  | SIMPLE      | orders | ALL  | NULL | NULL    | NULL  | 8000 | Using where; Using filesort  |

---

## 3. Plan Review Checklist

- [x] type: ALL (full table scan)
- [ ] No index used (key: NULL)
- [x] Using where present
- [x] Using filesort present
- [ ] Join not relevant (single table)
- [x] Query limited with LIMIT
- [ ] Stats up to date

---

## 4. Optimization & Verification

- **Proposed Index or Query Rewrite:**  
  CREATE INDEX idx_orders_customer_status_created ON orders(customer_id, status, created_at DESC);

- **Plan After Change:**  
  type: ref  
  key: idx_orders_customer_status_created  
  Extra: Using where

- **Performance Before/After:**  
  - Before: 880 ms, 8000 rows
  - After: 7 ms, 5 rows

- **Rollback Plan:**  
  DROP INDEX idx_orders_customer_status_created ON orders;

---

## Quality Checklist

Before finalizing:

- [ ] EXPLAIN plans captured and reviewed before/after
- [ ] Index/query changes tested with production-like data
- [ ] Rollback steps documented

# MySQL Index Creation Template

*Purpose: Safely design, document, implement, and validate new indexes in MySQL for reliable query acceleration.*

---

## When to Use

Use this template when:
- Adding a new index for a slow or high-traffic MySQL query
- Reviewing indexes during schema optimization or code review
- Planning index changes for migrations

---

## Structure

This template includes:
1. **Index Rationale & Design**
2. **DDL (Create Index)**
3. **Validation (EXPLAIN, Usage, Monitoring)**
4. **Rollback & Maintenance**

---

# TEMPLATE STARTS HERE

## 1. Index Rationale & Design

- **Query/Use-case:**  
  [Paste the SQL or describe the query pattern that needs optimization]

- **Observed Issue:**  
  [e.g., EXPLAIN shows type: ALL, no index, slow response]

- **Proposed Index:**  
  [Single-column, composite, covering, etc.]

- **Index Columns & Order:**  
  [e.g., (customer_id, status, created_at DESC)]

- **Additional Notes:**  
  [e.g., Consider index size, expected selectivity, possible overlaps]

---

## 2. DDL (Create Index)

**MySQL Example:**  

CREATE INDEX idx_tablename_columns ON tablename(column1, column2 [DESC], ...);

*Edit for your specific table and columns.*

---

## 3. Validation (EXPLAIN, Usage, Monitoring)

- **Before:**  
  - Query plan: [Paste EXPLAIN before index]
  - type: [e.g., ALL, range, ref, etc.]
  - key: [NULL or existing index]
  - Timing/Rows: [e.g., 700 ms, 8,000 rows scanned]

- **After:**  
  - Query plan: [Paste EXPLAIN after index]
  - type: [e.g., ref, range, index, etc.]
  - key: [Index used]
  - Timing/Rows: [e.g., 8 ms, 5 rows scanned]
  - Confirmed index usage: [Yes/No]

- **Other queries potentially affected:**  
  [List or mark N/A]

- **Index usage monitoring:**  
  - Use `SHOW INDEX FROM tablename;`
  - Monitor slow query log for regressions
  - Check with `SHOW STATUS LIKE 'Handler_read%';` as needed

---

## 4. Rollback & Maintenance

- **Rollback Command:**  

DROP INDEX idx_tablename_columns ON tablename;

- **Post-Deployment Monitoring:**  
  - Monitor query latency for key paths
  - Review DML (insert/update/delete) performance
  - Include index in periodic schema/index audits

---

# COMPLETE EXAMPLE

## 1. Index Rationale & Design

- **Query/Use-case:**  
  SELECT * FROM orders WHERE customer_id = 211 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;
- **Observed Issue:**  
  Full table scan (type: ALL), slow, no index used
- **Proposed Index:**  
  Composite
- **Index Columns & Order:**  
  (customer_id, status, created_at DESC)

---

## 2. DDL (Create Index)

CREATE INDEX idx_orders_customer_status_created ON orders(customer_id, status, created_at DESC);

---

## 3. Validation (EXPLAIN, Usage, Monitoring)

- **Before:**  
  type: ALL  
  key: NULL  
  Timing: 630 ms, 9,000 rows scanned

- **After:**  
  type: ref  
  key: idx_orders_customer_status_created  
  Timing: 6 ms, 10 rows scanned  
  Confirmed index usage: Yes

- **Index usage monitoring:**  
  SHOW INDEX FROM orders;

---

## 4. Rollback & Maintenance

- **Rollback Command:**  
  DROP INDEX idx_orders_customer_status_created ON orders;

- **Post-Deployment Monitoring:**  
  - Monitor dashboard, slow query log
  - Check for any impact on writes
  - Review in next schema/index audit

---

## Quality Checklist

Before finalizing:

- [ ] Index DDL reviewed and peer approved
- [ ] EXPLAIN plans and query timings saved before/after
- [ ] Rollback command ready
- [ ] Monitoring steps documented

# SQL Replication & High Availability Template

*Purpose: A production-ready template for diagnosing replication lag, evaluating high availability posture, planning failover, and documenting replica rebuild or resync procedures.*

---

## 1. Overview

**Database:**  
- [ ] Postgres  
- [ ] MySQL  
- [ ] MariaDB  
- [ ] Other: ___________

**Environment:**  
- [ ] Production  
- [ ] Staging  
- [ ] DR region  
- [ ] Read replica cluster  

**Type of Issue / Task:**  
- [ ] Replication lag investigation  
- [ ] Replica rebuild  
- [ ] Failover planning  
- [ ] Failover execution  
- [ ] HA readiness assessment  
- [ ] Sync/async configuration review  
- [ ] Backup-based replica provisioning  
- [ ] Cross-region replication  

**Severity:**  
- [ ] P0 – Production at risk  
- [ ] P1 – Partial degradation  
- [ ] P2 – Non-critical  
- [ ] P3 – Maintenance  

---

## 2. Replication Topology Summary

### 2.1 Architecture

- Primary Node: ____________________  
- Replica(s): ________________________  
- Replication Mode:  
  - [ ] Asynchronous  
  - [ ] Synchronous  
  - [ ] Semi-synchronous  
  - [ ] Logical  
  - [ ] Physical  

### 2.2 Workload Notes

- Write volume:  
- Read volume:  
- Transaction size patterns:  
- Cross-region latency:  

---

## 3. Replication Lag Investigation

### 3.1 Postgres Metrics

Run:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Additional:
- `pg_stat_wal_receiver`  
- `pg_stat_replication` on primary  
- Replay location vs flush location differences  

### 3.2 MySQL Metrics

Run:

SHOW SLAVE STATUS\G;

Check:
- `Seconds_Behind_Master`  
- `Relay_Log_Space`  
- `Executed_Gtid_Set` vs `Retrieved_Gtid_Set`  
- `Slave_IO_Running` / `Slave_SQL_Running`  

---

### 3.3 Lag Diagnostics Checklist

- [ ] High write volume spike  
- [ ] Long-running transactions on replica  
- [ ] Network latency issues  
- [ ] Disk I/O saturation  
- [ ] WAL/binlog generation spike  
- [ ] Replica SQL thread bottleneck  
- [ ] Huge autovacuum (Postgres)  
- [ ] Replica using slow storage  
- [ ] Large batch updates on primary  

**Notes:**  
[Describe findings]

---

## 4. Root Cause Hypotheses

Select all relevant:

- [ ] Replica I/O too slow  
- [ ] Large WAL/binlog burst from bulk ops  
- [ ] Long transaction blocking WAL replay  
- [ ] Vacuum freeze on replica  
- [ ] Write amplification (too many indexes)  
- [ ] Network bandwidth issue  
- [ ] Replica CPU saturated  
- [ ] Disk queue depth high  
- [ ] Misconfigured sync settings  

**Primary Suspect:**  
[Describe]

---

## 5. Fix Patterns

### 5.1 Immediate Actions

- [ ] Throttle writes on primary  
- [ ] Reduce large batch sizes  
- [ ] Pause heavy migrations  
- [ ] Stop read-intensive analytic jobs on replicas  
- [ ] Restart WAL receiver / replication threads  
- [ ] Increase network throughput  
- [ ] Move replica to faster storage  

---

### 5.2 Durable Long-Term Fixes

Postgres:
- Tune `max_wal_size`  
- Tune `checkpoint_completion_target`  
- Enable synchronous replication only when required  
- Add more replicas for read scaling  
- Reduce index count on write-heavy tables  

MySQL:
- Enable parallel replication  
- Tune replica SQL thread concurrency  
- Reduce row-based binlog amplification  
- Add appropriate covering indexes  

---

## 6. Replica Rebuild Procedure

### 6.1 When to Rebuild

Rebuild a replica if:
- [ ] Replica is too far behind  
- [ ] Replica has corruption or missing WAL/binlogs  
- [ ] GTID set divergence  
- [ ] Disk failure  
- [ ] Version mismatch after upgrade  
- [ ] Logical replication misalignment  

---

### 6.2 Postgres Rebuild (Physical)

SELECT pg_terminate_backend(pid) FROM pg_stat_replication WHERE application_name='<replica_name>';

Then on replica:

rm -rf $PGDATA/* pg_basebackup -h <primary> -D $PGDATA -U replicator -P -R

Restart:

systemctl restart postgresql

---

### 6.3 MySQL Rebuild (GTID)

1. Stop replica:

STOP SLAVE;

2. Drop data directory:

rm -rf /var/lib/mysql/*

3. Restore full backup:

xtrabackup --prepare xtrabackup --copy-back

4. Reset and connect:

RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST='...', MASTER_AUTO_POSITION=1; START SLAVE;

---

## 7. HA (High Availability) Evaluation

### 7.1 Failover Requirements

- [ ] RTO target documented  
- [ ] RPO target documented  
- [ ] Synchronous replication required?  
- [ ] Multi-AZ or multi-region required?  
- [ ] Automated failover enabled?  
- [ ] Stonith / fencing (if cluster-based)  

### 7.2 Readiness Checklist

- [ ] Replicas healthy  
- [ ] Replication lag < defined threshold  
- [ ] Primary CPU/I/O below safety threshold  
- [ ] WAL/binlog retention safe  
- [ ] Backup tested  
- [ ] Application connection retries configured  

---

## 8. Failover Plan

### 8.1 Failover Type

- [ ] Manual  
- [ ] Semi-automatic  
- [ ] Fully automatic (patroni/repmgr/Orchestrator/ProxySQL)  

### 8.2 Manual Failover Steps Example (Postgres)

1. Promote replica:

pg_ctl promote

2. Update connection strings  
3. Reconfigure load balancer  
4. Rebuild old primary as new replica  

---

### 8.3 Manual Failover Steps Example (MySQL)

1. `STOP SLAVE;` on failing node  
2. Promote replica:

RESET SLAVE ALL;

3. Update application configs  
4. Point other replicas to new primary:  

CHANGE MASTER TO MASTER_HOST='<new primary>';

---

## 9. Post-Failover Verification

### 9.1 Functional

- [ ] Application can read/write  
- [ ] No stale replicas  
- [ ] GTID/WAL positions correct  

### 9.2 Performance

- [ ] Query latency normal  
- [ ] CPU/I/O within thresholds  
- [ ] No lock pile-ups  

### 9.3 Consistency

- [ ] Data divergence check  
- [ ] Row count validation  
- [ ] Index structure validated  
- [ ] Spot-check key business queries  

---

## 10. DR (Disaster Recovery) Status

### 10.1 PITR Capability
- [ ] WAL/binlog archived  
- [ ] PITR tested in last 6 months  

### 10.2 Region Failure Simulation
- [ ] Replica in second region  
- [ ] Network isolation test  
- [ ] Restore-from-backup test  
- [ ] Failover tested end-to-end  

---

## 11. Final Notes

[Add any lessons learned, diagrams, improvements, or scheduled tasks.]

---

## 12. Completed Example

**Issue:** Replication lag > 45 minutes on Postgres.

**Root Cause:** Large UPDATE batch + replica on slow disk.

**Fixes:**  
- Throttled writes  
- Rebuilt replica using `pg_basebackup`  
- Moved to faster NVMe storage  
- Enabled monitoring for WAL spikes  

**Post-Fix Lag:** < 500ms steady.

---

# END

# Oracle Database Execution Plan Analysis Template

*Purpose: Standardize the process for capturing, analyzing, and optimizing Oracle SQL execution plans using EXPLAIN PLAN and DBMS_XPLAN.*

---

## When to Use

Use this template for:
- Diagnosing slow queries in Oracle Database
- Analyzing execution plans for SQL tuning
- Reviewing optimizer decisions
- Troubleshooting performance issues

---

## Structure

1. **Query & Context**
2. **Execution Plan Capture**
3. **Plan Analysis Checklist**
4. **Action Items & Validation**

---

# TEMPLATE STARTS HERE

## 1. Query & Context

- **Query:**
  [Paste SQL statement being reviewed]

- **Database:**
  [Database name, Oracle version, environment]

- **Schema/Tables:**
  [e.g., SALES.ORDERS, SALES.CUSTOMERS]

- **Expected Result Size:**
  [Rows, typical use-case]

- **Current Performance:**
  - Execution time: [seconds/minutes]
  - Buffer gets: [logical reads]

---

## 2. Execution Plan Capture

### Method 1: EXPLAIN PLAN (Estimated Plan)

EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 12345;

-- View the plan SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

### Method 2: Actual Execution Plan (with statistics)

-- Enable gathering of actual execution statistics ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- Run your query SELECT * FROM orders WHERE customer_id = 12345;

-- Display actual plan with runtime statistics SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

### Method 3: Using SQL_ID (from V$SQL)

-- Find SQL_ID SELECT sql_id, sql_text, executions, buffer_gets FROM v$sql WHERE sql_text LIKE '%customer_id%' ORDER BY buffer_gets DESC;

-- Display plan for specific SQL_ID SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

### Method 4: AWR/Statspack Historical Plan

-- Display plan from AWR SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

---

## 3. Execution Plan Analysis Checklist

### 3.1 Access Methods

- [ ] **TABLE ACCESS FULL** - Full table scan
  - Is the table small enough to justify full scan?
  - Should an index be used instead?

- [ ] **TABLE ACCESS BY INDEX ROWID** - Index seek + table access
  - Good for selective queries
  - Check cardinality (rows returned)

- [ ] **INDEX RANGE SCAN** - Scanning range of index
  - Good for range queries (BETWEEN, >, <)

- [ ] **INDEX UNIQUE SCAN** - Single row via unique index
  - Optimal for equality on unique column

- [ ] **INDEX FULL SCAN** - Reading entire index
  - May indicate missing better index
  - Can be useful if index is covering

- [ ] **INDEX FAST FULL SCAN** - Parallel index scan
  - Used when index contains all needed columns

### 3.2 Join Methods

- [ ] **NESTED LOOPS** - Good for small result sets with indexes
  - Driving table should be smaller
  - Joining column should be indexed

- [ ] **HASH JOIN** - Good for large unsorted tables
  - Requires memory for hash table
  - Check PGA memory allocation

- [ ] **SORT MERGE JOIN** - Good for pre-sorted data
  - Can be expensive if sorts required
  - Check for disk sorts (temp tablespace)

### 3.3 Cost and Cardinality

- [ ] Are estimated rows close to actual rows (E-Rows vs A-Rows)?
  - Large discrepancies indicate stale statistics
- [ ] Is the cost reasonable for the operation?
- [ ] Are there high-cost operations that could be optimized?

### 3.4 Predicates

- [ ] **Access predicates** - Used to seek into index
- [ ] **Filter predicates** - Applied after rows retrieved
  - Ideally, filters should be access predicates

### 3.5 Operations to Watch For

- [ ] **SORT ORDER BY** - Can be expensive for large result sets
- [ ] **SORT GROUP BY** - Consider using GROUP BY HASH if available
- [ ] **SORT UNIQUE** - For DISTINCT operations
- [ ] **HASH GROUP BY** - Preferred over SORT GROUP BY
- [ ] **VIEW** - Inline views or subqueries (check pushdown)
- [ ] **FILTER** - Row-by-row filtering (can be slow)

---

## 4. Common Oracle Performance Issues

### Issue 1: Full Table Scan on Large Table

**Symptom:** TABLE ACCESS FULL with high cost

**Check statistics:**

SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name = 'ORDERS';

**Gather statistics if stale:**

EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname => 'SCHEMA_NAME', tabname => 'ORDERS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

**Consider adding index:**

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

---

### Issue 2: Cardinality Misestimation

**Symptom:** E-Rows (estimated) very different from A-Rows (actual)

**Causes:**
- Stale statistics
- Missing histograms for skewed data
- Bind variable peeking issues

**Fix: Gather extended statistics for correlated columns:**

-- Create column group for correlated columns SELECT DBMS_STATS.CREATE_EXTENDED_STATS( ownname => 'SCHEMA_NAME', tabname => 'ORDERS', extension => '(customer_id, order_date)' ) FROM DUAL;

-- Gather statistics EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname => 'SCHEMA_NAME', tabname => 'ORDERS' );

---

### Issue 3: Bind Variable Peeking

**Symptom:** Query fast with some parameter values, slow with others

**Fix: Use adaptive cursor sharing (11g+) or:**

-- Use hints to avoid peeking SELECT /+ BIND_AWARE / * FROM orders WHERE customer_id = :cust_id;

-- Or use literals for dynamic sampling SELECT /+ DYNAMIC_SAMPLING(4) / * FROM orders WHERE customer_id = 12345;

---

### Issue 4: Wrong Join Order

**Symptom:** Large table driving nested loop join

**Check join order:**
- Smaller table should drive the join in nested loops
- Use ORDERED hint to force specific join order (testing only)

**Fix with hints:**

SELECT /+ LEADING(small_table large_table) USE_NL(large_table) / ... FROM small_table, large_table WHERE small_table.id = large_table.id;

---

### Issue 5: Index Not Being Used

**Reasons:**
- Function on indexed column (e.g., `WHERE UPPER(name) = 'SMITH'`)
- Implicit type conversion
- Statistics out of date
- Cost-based optimizer choosing full scan

**Check index usage:**

SELECT index_name, column_name, column_position FROM user_ind_columns WHERE table_name = 'ORDERS' ORDER BY index_name, column_position;

**Fix: Create function-based index if needed:**

CREATE INDEX idx_customers_upper_name ON customers(UPPER(last_name));

---

## 5. Action Items & Validation

### 5.1 Optimization Steps Proposed

**Example Actions:**
1. Gather statistics on ORDERS table
2. Create index on (customer_id, order_date)
3. Update SQL to avoid function on indexed column

**DDL:**

-- Gather statistics EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'ORDERS');

-- Create index CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

### 5.2 Execution Plan After Optimization

[Paste updated plan]

**Key Improvements:**
- TABLE ACCESS FULL -> INDEX RANGE SCAN
- Cost reduced from 5000 to 10
- Buffer gets reduced from 500,000 to 25

### 5.3 Performance Before/After

| Metric | Before | After | Improvement |
|--------|--------|-------|-------------|
| Execution time (sec) | | | |
| Buffer gets (logical I/O) | | | |
| Physical reads | | | |
| CPU time (sec) | | | |
| Rows processed | | | |

### 5.4 Rollback Plan

-- Drop index if needed DROP INDEX idx_orders_cust_date;

-- Restore old statistics (if backed up) EXEC DBMS_STATS.RESTORE_TABLE_STATS('SCHEMA', 'ORDERS', '&timestamp');

---

## 6. Complete Example

### Problem: Slow order lookup by customer

**Query:**

SELECT order_id, order_date, order_total FROM orders WHERE customer_id = 12345 ORDER BY order_date DESC;

**Current Plan:**

--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50 | 2000 | 1234 (1)| 00:00:15 | | 1 | SORT ORDER BY | | 50 | 2000 | 1234 (1)| 00:00:15 | |* 2 | TABLE ACCESS FULL| ORDERS | 50 | 2000 | 1233 (1)| 00:00:15 | ---------------------------------------------------------------------------

Predicate Information (identified by operation id): 2 - filter("CUSTOMER_ID"=12345)

**Issues:**
- [x] Full table scan (500,000 rows scanned for 50 returned)
- [x] Sort operation (expensive)
- [x] 500,000 buffer gets

**Fix:**

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);

-- Gather statistics EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA', 'IDX_ORDERS_CUSTOMER_DATE');

**Optimized Plan:**

----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50 | 2000 | 3 | | 1 | TABLE ACCESS BY INDEX ROWID | ORDERS | 50 | 2000 | 3 | |* 2 | INDEX RANGE SCAN DESCENDING| IDX_ORDERS_CUSTOMER_DATE| 50 | | 2 | -----------------------------------------------------------------------------------

Predicate Information (identified by operation id): 2 - access("CUSTOMER_ID"=12345)

**Results:**
- Execution time: 2.5s -> 0.02s (99.2% improvement)
- Buffer gets: 500,000 -> 15 (99.997% improvement)
- Sort eliminated (index provides ordering)

---

## 7. Useful Oracle Queries

### Find Expensive SQL

SELECT sql_id, executions, buffer_gets, disk_reads, elapsed_time/1000000 AS elapsed_seconds, SUBSTR(sql_text, 1, 100) AS sql_text FROM v$sql WHERE executions > 0 ORDER BY buffer_gets DESC FETCH FIRST 20 ROWS ONLY;

### Check Table Statistics

SELECT table_name, num_rows, blocks, last_analyzed FROM user_tables WHERE table_name IN ('ORDERS', 'CUSTOMERS') ORDER BY table_name;

### Check Index Statistics

SELECT index_name, blevel, leaf_blocks, distinct_keys, num_rows, last_analyzed FROM user_indexes WHERE table_name = 'ORDERS';

---

## 8. Quality Checklist

- [ ] Execution plan captured (estimated and actual)
- [ ] Statistics are up to date (< 1 week old for active tables)
- [ ] Access methods are optimal (index seeks vs full scans)
- [ ] Join methods appropriate for data volume
- [ ] Cardinality estimates are accurate
- [ ] Predicates optimized (access vs filter)
- [ ] Performance improvement validated
- [ ] Rollback plan documented

# PostgreSQL EXPLAIN/ANALYZE Template

*Purpose: Standardize the operational process for capturing, analyzing, and optimizing PostgreSQL query plans using EXPLAIN (ANALYZE, BUFFERS).*

---

## When to Use

Use this template for:
- Diagnosing slow queries in PostgreSQL
- Reviewing query plans before/after schema/index changes
- Routine performance tuning or periodic health checks

---

## Structure

This template includes:
1. **Query & Context**
2. **EXPLAIN Output**
3. **Plan Analysis Checklist**
4. **Action Items & Validation**

---

# TEMPLATE STARTS HERE

## 1. Query & Context

- **Query:**  
  [Paste SQL statement being reviewed]

- **Database/Schema:**  
  [Name, version, environment]

- **Table(s) Involved:**  
  [e.g., orders, users, etc.]

- **Expected Result Size:**  
  [Rows, typical use-case]

---

## 2. EXPLAIN Output

- **Command Used:**  

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) [SQL...]

- **Plan Output:**  
  [Paste raw plan or use gist/plan visualizer link]

---

## 3. Plan Analysis Checklist

- [ ] Is there a `Seq Scan` on a large table?  
  - [ ] If yes, should an index be used?
- [ ] Is `Index Scan` or `Index Only Scan` used for key filters?
- [ ] Any join type concerns? (`Nested Loop`, `Hash Join`, `Merge Join`)
- [ ] Are actual rows close to estimated? (`rows=` numbers)
- [ ] Any `Sort` or `HashAggregate` on large sets?
- [ ] Is any node showing high I/O (`Buffers: shared hit/read/dirtied/written`)?
- [ ] Is `Filter:` used where it could be index-based?
- [ ] Is the most selective filter applied earliest?
- [ ] Any sign of missing/up-to-date statistics?

---

## 4. Action Items & Validation

- **Optimization Steps Proposed:**  
  [e.g., add index, rewrite WHERE, change join order, ANALYZE]

- **Plan After Optimization:**  
  [Paste updated plan, highlight changes]

- **Performance Before/After:**  
  - Before: [timing, rows, I/O]
  - After:  [timing, rows, I/O]

- **Rollback Plan:**  
  [DROP INDEX, revert query, etc.]

---

# COMPLETE EXAMPLE

## 1. Query & Context

- **Query:**  
  SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
- **Database/Schema:**  
  prod, PostgreSQL 15
- **Table(s) Involved:**  
  orders
- **Expected Result Size:**  
  10 rows

---

## 2. EXPLAIN Output

- **Command Used:**  
  EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
- **Plan Output:**  
  Seq Scan on orders ... Filter: (customer_id = 42) ... 14,200 rows removed by filter

---

## 3. Plan Analysis Checklist

- [x] Seq Scan present on large table
- [ ] Index on (customer_id, created_at) missing
- [ ] Index Only Scan not possible
- [ ] No problematic joins
- [x] Actual rows (10) << estimated (15,000)
- [ ] Sort operation not optimized
- [ ] Statistics up to date

---

## 4. Action Items & Validation

- **Optimization Steps Proposed:**  
  CREATE INDEX idx_orders_customer_id_created_at ON orders(customer_id, created_at DESC);

- **Plan After Optimization:**  
  Index Scan on orders using idx_orders_customer_id_created_at

- **Performance Before/After:**  
  - Before: 920 ms, 14,210 rows scanned
  - After: 8 ms, 10 rows scanned

- **Rollback Plan:**  
  DROP INDEX idx_orders_customer_id_created_at;

---

## Quality Checklist

Before finalizing:

- [ ] EXPLAIN plan pasted in docs/ticket
- [ ] Index or rewrite tested in staging
- [ ] Rollback steps documented