Skip to content

charlieviettq/awesome-agent-skill / .claude/skills/data-sql-optimization

"data-sql-optimization"

Unverified

"Optimize SQL query performance using EXPLAIN analysis, indexing strategies, and common anti-pattern fixes. Use this skill when the user needs to speed up slow queries, design indexes, fix N+1 problems, or optimize database performance — even if they say 'this query is slow', 'optimize our database', 'which indexes do we need', or 'our dashboard takes 30 seconds to load'.".

SQL Query Optimization

Framework

IRON LAW: Measure Before Optimizing

NEVER guess which query is slow or why. Use EXPLAIN (EXPLAIN ANALYZE in
PostgreSQL) to see the actual execution plan. The database's plan often
differs from what you expect — a query you think is efficient may do
a full table scan, and a complex-looking query may use an index perfectly.

Measure → identify bottleneck → fix → measure again.

EXPLAIN Output Reading

Key metrics in EXPLAIN ANALYZE (PostgreSQL):

MetricWhat It MeansRed Flag
Seq ScanFull table scanOn large tables (>100K rows)
Index ScanUsing an indexExpected for filtered queries
Nested LoopJoin method (row-by-row)On large tables without index
Hash JoinJoin method (hash table)Normal for larger tables
SortSorting resultsWithout index support on large sets
Actual TimeMilliseconds for this stepCompare to identify bottleneck
RowsActual rows processed vs estimatedLarge mismatch = stale statistics

Indexing Strategy

When to IndexIndex TypeExample
WHERE clause columnB-Tree (default)CREATE INDEX idx_user_email ON users(email)
JOIN columnB-TreeCREATE INDEX idx_order_user ON orders(user_id)
Composite filterComposite indexCREATE INDEX idx_order_status_date ON orders(status, created_at)
Text searchGIN / Full-textCREATE INDEX idx_product_name_gin ON products USING gin(name gin_trgm_ops)
Range queriesB-TreeColumns used with BETWEEN, >, <

Composite index column order matters: Put the most selective (highest cardinality) column first. INDEX(status, date) is good if you always filter by status. INDEX(date, status) is better if you always filter by date range first.

Common Anti-Patterns

Anti-PatternProblemFix
SELECT *Reads all columns, prevents index-only scansSelect only needed columns
Subquery in WHERERe-executes for each rowRewrite as JOIN or CTE
OR in WHEREPrevents index useRewrite as UNION or separate queries
Function on indexed columnWHERE YEAR(date) = 2024 bypasses indexWHERE date >= '2024-01-01' AND date < '2025-01-01'
N+1 queries1 query for list + N queries for detailsJOIN or batch query with IN
Missing paginationFetching all rows when only showing 20LIMIT + OFFSET or keyset pagination
Implicit type conversionWHERE id = '123' (string vs int)Use correct type: WHERE id = 123

Optimization Workflow

  1. Identify slow queries: Database slow query log (pg_stat_statements, MySQL slow log)
  2. Run EXPLAIN ANALYZE on the slowest
  3. Find the bottleneck: Seq Scan on large table? Missing index? Expensive sort?
  4. Apply fix: Add index, rewrite query, or restructure schema
  5. Verify: Run EXPLAIN ANALYZE again — confirm improvement
  6. Monitor: Check that fix didn't degrade other queries

Partitioning (Large Tables)

When tables exceed millions of rows:

StrategyHow It WorksBest For
Range partitionSplit by date range (monthly, yearly)Time-series data, logs
Hash partitionDistribute by hash of a columnEven distribution, high-throughput
List partitionSplit by specific valuesMulti-tenant, status-based

Output Format

# Query Optimization: {Context}

## Slow Query
```sql
{the original slow query}
  • Execution time: {current ms}
  • Rows scanned: {N}
  • Problem: {what EXPLAIN revealed}

Fix Applied

{What was changed — new index, query rewrite, etc.}

Result

  • Execution time: {original ms} → {optimized ms} ({X% improvement})
  • Rows scanned: {original N} → {optimized N}

## Gotchas

- **Indexes have write cost**: Every INSERT/UPDATE must update all indexes. Over-indexing slows writes. Index what you query, not everything.
- **Statistics can be stale**: If EXPLAIN estimates are way off from actuals, run `ANALYZE` (PostgreSQL) or `ANALYZE TABLE` (MySQL) to update statistics.
- **Query cache hides problems**: A query may appear fast because it's cached. Test with cache cleared or cold start.
- **ORM-generated queries**: ORMs (Django, SQLAlchemy, ActiveRecord) generate SQL that may not be optimal. Always inspect the actual SQL for performance-critical paths.
- **Connection pooling**: Sometimes the bottleneck isn't the query but connection overhead. Use connection pooling (PgBouncer, ProxySQL) for high-concurrency applications.

## References

- For PostgreSQL-specific optimization, see `references/pg-optimization.md`
- For CTE vs temp table performance comparison, see `references/cte-vs-temp.md`
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexUnverifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexUnverifiedInstall