Skip to content

JPeetz/agent-skills / skills/devops-infrastructure/performance-optimization

performance-optimization

Verified

Use this skill when optimizing application performance across frontend (Core Web Vitals, bundles), backend (N+1 queries, connection pools, caching), and database (indexes, query plans). Profiles before fixing, pinpoints actual bottlenecks rather than assumed ones, verifies improvements by re-measuring the same way as baseline.

references/query-optimization.md

View on GitHub
# Query Optimization — Profiling and Index Design

## Before Adding an Index

1. Get the **actual slow query** (from slow query log, APM, or `pg_stat_activity`)
2. Run `EXPLAIN (ANALYZE, BUFFERS, TIMING)` on it
3. Identify the bottleneck: sequential scan? Nested loop? Sort?
4. Check existing indexes with `\d table_name` (PostgreSQL) or `SHOW INDEX FROM table` (MySQL)

## Index Design Principles

- **Composite indexes** — order columns by cardinality (most selective first) unless range conditions change the order
- **Covering indexes** — include all columns the query needs (PostgreSQL `INCLUDE` clause)
- **Partial indexes** — `WHERE` clause on the index itself (e.g. `WHERE status = 'active'`)
- **Drop unused indexes** — they slow writes and bloat memory

## Example: N+1 Query Fix

**Before (N+1):**

```sql
-- Implicit: one query per order to get customer, one per order to get items
SELECT * FROM orders;
```

**After (eager join):**

```sql
SELECT o.id, o.total, c.name, i.product_id, i.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at > now() - interval '30 days';
```

## Example: Index Verification

```sql
-- Before
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' AND created_at > now() - '1 day'::interval;

-- After adding index
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' AND created_at > now() - '1 day'::interval;

-- Compare: rows examined, buffers hit, actual time
```
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: 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: 118kUpdated Claude CodeCodexVerifiedInstall